PHP Retrieve, Amend and Update MySQL database

PHP Scripts

Simple retrieve, amend and update MySQL database

1. 1st page to aquire reference ( display all to make it easy)
2. 2nd page display results and allow viewer to change
3. Update the database

amend.php

<html><TD WIDTH="29%" HEIGHT="60"><DIV ALIGN="LEFT"><BR>Input
the Reference, to make sure we have the right one:<BR><BR><FONT SIZE="1">(quick
reference listed below for your convenience)</FONT></DIV>

<?php
// Show simple format of the records so person can choose the reference name/number
// this is then passed to the next page, for all details

$db = mysql_connect("db_host", "db_name", "db_password");
mysql_select_db("database",$db) or die ('Unable to connect to database');

$q="SELECT * FROM table ORDER BY reference ASC";

$result = mysql_query( $q, $db )
or die(" - Failed More Information:<br><pre>$q</pre><br>Error: " . mysql_error());

$num_rows = mysql_num_rows($result);
if ($myrow = mysql_fetch_array($result)) {

echo "<br>A Quick View<BR><br>";
echo "<table border=1>\n";
echo "<tr><td><b>Ref:</b></td><td>Description:</td><td>Consultant:</td><td>Thumb:</td></tr>\n";
do {
printf("<tr><td>%s</td><td>%s</td><td>%s</td><td>%s</td></tr>\n", $myrow["reference"], $myrow["shortdescription"], $myrow["consultant"], $myrow["thumb"]);
} while ($myrow = mysql_fetch_array($result));
echo "</table>\n";
} else {
echo "$ref: That record appears to be unavailable";
}

mysql_free_result($result);
mysql_close($db);
?></DIV></TD></html>

amend1.php

<?PHP
session_start();
?>
<HTML>

<?php
$record = $_POST['record'];
echo "Reference: $record<br><BR>";

$host = "db_host";
$login_name = "db_name";
$password = "db_password";

//Connecting to MYSQL
MySQL_connect("$host","$login_name","$password");

//Select the database we want to use
mysql_select_db("database") or die("Could not find database");

$result=mysql_query(" SELECT * FROM table WHERE reference='$record'");
$num=mysql_num_rows($result);
$i=0;
while ($i < $num) {

// collect all details for our one reference
$description=mysql_result($result,$i,"description");
$text2=mysql_result($result,$i,"text2");
$reference=mysql_result($result,$i,"reference");
$thumb=mysql_result($result,$i,"thumb");
$img1=mysql_result($result,$i,"image1");
$img2=mysql_result($result,$i,"image2");
$img3=mysql_result($result,$i,"image3");

$f='<font face=Verdana,Arial,Helvetica size=2 Color=Blue';

$view='<img src=http://your_website/thumbs/';
echo "<br>$view$thumb.jpg><br><br>";

//next we display only the details we want to allow to be changed in a form object
// the other details that we won't allow to be changed can be echoed to the screen
//note the hidden input line 3 below. We don't need to echo it to the screen
?>

<FORM ACTION="amend2.php" METHOD="post">
<P ALIGN="LEFT">
<INPUT TYPE="hidden" NAME="ud_id" VALUE="<? echo "$record" ?>">
<BR>Description1:<BR><TEXTAREA NAME="ud_description" COLS="50" ROWS="4">
<? echo "$description"?></TEXTAREA></P><P ALIGN="LEFT">Description:<BR>
<TEXTAREA NAME="ud_text2" COLS="50" ROWS="4"><? echo "$text2"?></TEXTAREA></P><HR><B>
</B><P ALIGN="LEFT">Thumbnail:<BR> <INPUT TYPE="text" NAME="ud_thumb" VALUE="<? echo "$thumb"?>" SIZE="30" MAXLENGTH="50"></P><P ALIGN="LEFT">Image
1 :<BR> <INPUT TYPE="text" NAME="ud_img1" VALUE="<? echo "$img1"?>" SIZE="30" MAXLENGTH="50"></P><P ALIGN="LEFT">Image
2 :<BR> <INPUT TYPE="text" NAME="ud_img2" VALUE="<? echo "$img2"?>" SIZE="30" MAXLENGTH="50"></P><P ALIGN="LEFT">Image
3 :<BR> <INPUT TYPE="text" NAME="ud_img3" VALUE="<? echo "$img3"?>" SIZE="30" MAXLENGTH="50"><BR><BR>
</P><P><INPUT TYPE="Submit" VALUE="Update the Record" NAME="Submit"> </P></FORM></TD></TR></TABLE>

<?
++$i;
}
?>

amend2.php

<?PHP
session_start();
?>

<?php

$ud_id=$_POST['ud_id'];
$ud_description=$_POST['ud_description'];
$ud_text2=$_POST['ud_text2'];
$ud_thumb=$_POST['ud_thumb'];
$ud_img1=$_POST['ud_img1'];
$ud_img2=$_POST['ud_img2'];
$ud_img3=$_POST['ud_img3'];

if ($ud_id == "") echo "! No identifier retrieved";
else
echo "Amending record $ud_id";

//clean up any carriage returns etc

$ud_description = preg_replace("/[\n\r]*/","",$ud_description);
$ud_text2 = preg_replace("/[\n\r]*/","",$ud_text2);

$host = "db_host";
$login_name = "db_name";
$password = "db_password";

//Connecting to MYSQL
MySQL_connect("$host","$login_name","$password");

//Select the database we want to use
mysql_select_db("db_database") or die("Could not select database");

mysql_query(" UPDATE db_table
SET description='$ud_description', text2='$ud_text2', thumb='$ud_thumb', image1='$ud_img1', image2='$ud_img2', image3='$ud_img3', WHERE reference='$ud_id'");

echo "<BR>Record $ud_id <-- Updated<BR><BR>";
?>

<?php
//if you want to check it's ok, display new data

echo "Search on $ud_id<BR>";

$db = mysql_connect("host", "name", "password");
mysql_select_db("db_database",$db) or die ('Unable to connect to database');

$q="SELECT * FROM db_table WHERE reference ='$ud_id'";

$result = mysql_query( $q, $db )
or die(" - Failed More Information:<br><pre>$q</pre><br>Error: " . mysql_error());

$num_rows = mysql_num_rows($result);
if ($myrow = mysql_fetch_array($result)) {

$thumb='<img src=http://your_website/thumbs/';

//if the record leads to a specific page
$web='http://www.your_website/detail/index.php?name=';

echo "<table border=0>\n";
echo "<tr><td></td><td></td><td></td><td></td></tr>\n";

do {
printf("<tr><td>$thumb%s.jpg></td><td>%s</td><td>%s</td><td>%s</td>
</tr>\n", $myrow["thumb"], $myrow["reference"], $myrow["description"], $myrow["text2"]);

} while ($myrow = mysql_fetch_array($result));
echo "</table>\n";
} else {
echo "Sorry, no records were found";
}

mysql_free_result($result);
mysql_close($db);
session_destroy();

Back