A tutorial to edit existing data from a MySQL database.
Before you can edit any data you need to select it you can select the item to be edited by using it's ID from the table it is in.
You can either manually input the ID into the query to retrieve the data or use a variable to store the ID in, this is a better option as you can use the page edit script to edit all items for the same table without having to modify anything.
To store the ID into a variable on a page that lists the rows of a database you can assign the row ID to a variable to be used later on in the select query. To do this create a hyper link that links to the edit page in this case edit.php then use a question mark and add a name the name can be anything, but keep this to something relevant this name will store the row ID once you've add a name after the question mark put an equals symbol then echo the ID for that row like this:
<a href="edit.php?itemID=<?php echo $row->newsID;?>">Edit</a>
Lets move onto the edit.php page
Before you can edit anything from the database you need to connect to it either include a file with the connection details in using include or require, or insert the connection details at the top of the page.
// include connect to database details
require ('config/globel.php');
The itemID becomes a number as the ID is inside it, To access the ID inside it you use $_GET['itemID']
First lets get the row ID and assign it to another variable to be cleaned before going into a query.
//get the row ID for the item
$itemID = $_GET['itemID'];
$itemID = mysql_real_escape_string($itemID);
Notice I've cleaned the variable with the function mysql_real_escape_string this makes the item safe to go into a database query and avoid any possible MySQL injection attacks.
Now run a query to select the row from the database use mysql_query to run the query then SELECT * FROM news which means get everything from the news table for that row. Then we directing where the row is in the table using it's ID WHERE newsID'$itemID' the variable itemID is what we created at the start of the script that contains the stored ID of the row that is being edited. Then finally run a die command to alert you if there is an error.
// get data from database to edit
$sql = mysql_query("SELECT * FROM news WHERE newsID='$itemID'")or die(mysql_erorr());
Then we check to see if the from has been submitted by using an if statement. The form has been submitted if isset which will be true when submit is clicked. So start the validation process:
//start validation input
// check fields are not empty
if (empty($newsTitle)) {
$error['newsTitle'] = 'enter a title.';
}
$newsContent = trim($_POST['newsContent']);
if (empty($newsContent)) {
$error['newsContent'] = 'Please enter your content.';
}
We first trim any whitespace from the field using the trim function which removes any whitespace before or after the text.
To get the data from the form you need to post the data and add it to a variable if the form was sent using get then you would use $_GET. If the form was sent via post then use $_POST.
In this case I used post to send the form so to get the date and add it to a variable use:
$newsContent = trim($_POST['newsContent']);
You could equally have used:
$newsContent = $_POST['newsContent'];
that would have posted the data but not removed any whitespace. Now check so see if the entry newsContent is empty using an if statement. If the data is empty then add en error message to the error array.
$error['newsContent'] = 'Please enter your content.';
This error will be printed above the form if there is an error. Repeat this process for all fields you want to validate.
After all validation we check to see if there has been an error:
// if validation is okay then carry on
if (!$error) {
$newsID = $_POST['newsID'];
$newsTitle = $_POST['newsTitle'];
$newsContent = $_POST['newsContent'];
If there was no error then carry on and post all the form data again adding the form data to a variable. But if there was an error stop the script and show the errors.
Now we check to see if magic quotes are active on the server by using the not ! operator and if they are not active then use the function addslashes to combat problematic characters.
if(!get_magic_quotes_gpc())
{
$newsTitle = addslashes($newsTitle);
$newsContent = addslashes($newsContent);
}
Then run a query to update the news item use the command UPDATE followed by the table name then SET which tells MySQL which columns to update. Put the column name followed by the varible for it then a comma do this for all columsn that need updating leaving a comma of the final column.
Then dictate where to update by using WHERE then the ID for the row. Then after the query show a success message. Then put two a closing brackets to close the if no erros and if form submitted if statements.
//update news item
$sql = mysql_query("UPDATE news SET newsTitle ='$newsTitle', newsCont ='$newsCont' WHERE newsID='$newsID'")or die(mysql_error());
//show success message
echo "<h1>Updated</h1> ";
}//close if error
}//close if form submit
Now close the brackets for errors and form submit to end the PHP section and then show any errors if they are set.
} //end validation
} // end form submitted
// input validation checks input not empty
if (isset($error['newsTitle'])) {
echo "<p><span class="warning">".$error['newsTitle']."</span></p> ";
}
if (isset($error['newsContent'])) {
echo "<p><span class="warning">".$error['newsContent']."</span></p> ";
}
Using an if statement check to see if an error has been set and if it has show the error. I have used CSS To style the error but its not needed for it to work.
Now we loop through the data from the database and add all items to an array object and assign them to a variable, remember the variable on the end which is the variable that runs the the select query at the start of the page.
while ($row = mysql_fetch_object($sql)){
Now show the form to update the news item, Tell the form to reload the page when sent by using:
<form action="<?php $_SERVER['PHP_SELF'];?>" method="post">
Then add a hidden input row to add the news item's ID to make available for the script. For the value print out the newsID from the database using $row-> this part is used as the items are all object as defined by mysql_fetch_object earlier in the script
<input type="hidden" name="newsID" value="<?php echo $row->newsID;?>" />
Then add the columns to be edited, for a single line item use an input field for multi lines use a text area field.
<p><label>News Title</label><br /><input name="newsTitle" type="text" value="<?php echo $row->newsTitle;?>" size="50" maxlength="255" /></p>
<p><label>News Content</label><br /><textarea name="newsCont" cols="60" rows="10"><?php echo $row->newsCont;?></textarea></p>
<p>Then put an input field with a type of submit which will submit the form</p>
<input type="submit" name="submit" value="Update News">
<p>Then put a closing bracket to close the loop</p>
<?php } ?>
Here's the full script
<?php
// include connect to database details
require ('config/globel.php');
//get the row ID for the item
$itemID = $_GET['itemID'];
$itemID = mysql_real_escape_string($itemID);
// get data from database to edit
$sql = mysql_query("SELECT * FROM news WHERE newsID='$itemID'")or die(mysql_erorr());
// if form has been submitted
if (isset($_POST['submit'])){
if (empty($newsTitle)) {
$error['newsTitle'] = 'enter a title.';
}
$newsContent = trim($_POST['newsContent']);
if (empty($newsContent)) {
$error['newsContent'] = 'Please enter your content.';
}
// if validation is okay then carry on
if (!$error) {
// post form data
$newsID = $_POST['newsID'];
$newsTitle = $_POST['newsTitle'];
$newsCont = $_POST['newsCont'];
// add slashes if needed
if(!get_magic_quotes_gpc())
{
$newsTitle = addslashes($newsTitle);
$newsCont = addslashes($newsCont);
}
//update news item
$sql = "UPDATE news SET newsTitle ='$newsTitle', newsCont ='$newsCont' WHERE newsID='$newsID'";
$resultupdate = mysql_query($sql)or die(mysql_error());
//show success message
echo "<h1>Updated</h1> ";
}//close if error
}//close if form submit
// input validation checks input not empty
if (isset($error['newsTitle'])) {
echo "<p><span class="warning">".$error['newsTitle']."</span></p> ";
}
if (isset($error['newsContent'])) {
echo "<p><span class="warning">".$error['newsContent']."</span></p> ";
}
while ($row = mysql_fetch_object($sql)){
?>
<form action="<?php $_SERVER['PHP_SELF'];?>" method="post">
<input type="hidden" name="newsID" value="<?php echo $row->newsID;?>" />
<p><label>News Title</label><br /><input name="newsTitle" type="text" value="<?php echo $row->newsTitle;?>" size="50" maxlength="255" /></p>
<p><label>News Content</label><br /><textarea name="newsCont" cols="60" rows="10"><?php echo $row->newsCont;?></textarea></p>
<input type="submit" name="submit" value="Update News">
</form>
<?php } ?>
That's all there is to updating items stored inside a MySQL database.
