So far these pages have shown how to SELECT data and display it and to INSERT new records. Sometimes users will also want to change data.
To change data first display it. Base this on the form and code from your INSERT page (mysqlinsert.php). The steps are:
- connect to the database
- grab the data to be changed and display it in the form
- process/update the data when the form is submitted
This chunk of code goes at the top of the PHP page:
$mysqlserver="localhost";
$mysqlusername="test";
$mysqlpassword="test";
$link=mysql_connect(localhost, $mysqlusername, $mysqlpassword) or die ("Error connecting to mysql server: ".mysql_error());
$dbname = 'test';
mysql_select_db($dbname, $link) or die ("Error selecting specified database on mysql server: ".mysql_error());
Then the next chunk goes inside the IF part of a standard form & processing IF/ELSE statement:
$cdReference="1";
$cdquery="SELECT * FROM firsttable WHERE cdReference='$cdReference'";
$cdresult=mysql_query($cdquery) or die("Query to get data from firsttable failed with this error: ".mysql_error());
$cdrow=mysql_fetch_array($cdresult);
$cdArtist=$cdrow[cdArtist];
$cdTitle=$cdrow[cdTitle];
$cdPrice=$cdrow[cdPrice];
$cdLabel=$cdrow[cdLabel];
The cdReference is set to 1 (better ways come soon). The query is a SELECT query just like the others. The $result is then used to get the single record which has a cdReference of 1. Finally, the data is put into separate variables ready for display. To display put the next chunk of code after the previous bit (still inside the IF):
echo "<form method=\"POST\" action=\"fred.php\">";
echo "<p>";
echo "<label for=\"cdReference\">Reference: </label><input type=\"text\" id=\"cdReference\" name=\"cdReference\" size=\"10\" value=\"$cdReference\"/>";
echo "<label for=\"cdArtist\">Artist/Group: </label><input type=\"text\" id=\"cdArtist\" name=\"cdArtist\" size=\"30\" value=\"$cdArtist\"/>";
echo "<label for=\"cdTitle\">Title: </label><input type=\"text\" id=\"cdTitle\" name=\"cdTitle\" size=\"30\" value=\"$cdTitle\" />";
echo "<label for=\"cdPrice\">Price: £</label><input type=\"text\" id=\"cdPrice\" name=\"cdPrice\" size=\"10\" value=\"$cdPrice\" />";
echo "<label for=\"cdLabel\">Record Label: </label><input type=\"text\" id=\"cdLabel\" name=\"cdLabel\" size=\"30\" value=\"$cdLabel\" />";
echo "</p>";
echo "<p><input type=\"submit\"></p>";
echo "</form>";
The only change to this form is at the end of each INPUT element. An attribute pair of VALUE="$somevariable" has been added.
The code for processing the form is similar to the INSERT version:
$cdReference=$_POST[cdReference];
$cdArtist=$_POST[cdArtist];
$cdTitle=$_POST[cdTitle];
$cdPrice=$_POST[cdPrice];
$cdLabel=$_POST[cdLabel];
$cdupdatequery="UPDATE firsttable SET cdArtist='$cdArtist', cdTitle='$cdTitle', cdPrice='$cdPrice', cdLabel='$cdLabel' WHERE cdReference='$cdReference';";
mysql_query($cdupdatequery) or die("Query to update record in firsttable failed with this error: ".mysql_error());
The query updates the chosen record (the WHERE makes sure you only change that one) with each field being set to the corresponding variable.
Paste the final chunk into the PHP page inside the ELSE braces.
You should now have this structure:
- IF form has not been POSTed:
- grab data from server and display in the form
- ELSE the data has been changed so process it:
- save the changed data
Save the page as mysqlupdate.php. Upload and try it. Improve and fiddle. One useful improvement is to add a limit to the number of changes which will be made:
$cdupdatequery="UPDATE firsttable SET cdArtist='$cdArtist', cdTitle='$cdTitle', cdPrice='$cdPrice', cdLabel='$cdLabel' WHERE cdReference='$cdReference' LIMIT 1;";
That way if you forget the WHERE or get it wrong only one record will be changed instead of all of them. You still messed up but you didn't wipe all of your data!
