Edit a Row In mySQL

This tutorial goes along with several others that use the same news database such as Add a Row to mySQL, Displaying a Database, and Deleting a Row In mySQL. There are many reasons you might want to edit one of the rows in your mySQL database. Say for instance, you made a spelling error in one of your news posts and you want to edit it.

We have a database called spoono_news, with a table called news. In the mySQL news table, we have 6 fields: id, title, message, who, date, and time. You can see the mySQL code here: mysql.txt. Open up a new HTML page, save it as edit.php and write all of the following code inside the Body tag. You can see my code that I worked on and made sure worked by right clicking and saving edit.txt.

Editing a mySQL row is basically a three step process. The first process involves displaying all the rows so you can select one to edit. The next step lets you edit the information for the row, and then the final step involves placing the new edits you made into the mySQL database. Lets get started by writing the code to display all the rows. Spoono enthusiasts would know that this step and code is almost identical to the one at "Deleting a Row in mySQL." Here is what we have to write in English to pick a row to edit:

1. Connect to the mySQL
2. If a command to edit has not been initialized, then display all the news posts
3. When displaying, make the title a link that would go to edit that particular post

<? 
//connect to mysql
//change user and password to your mySQL name and password
mysql_connect("localhost","user","password"); 
	
//select which database you want to edit
mysql_select_db("spoono_news"); 

//If cmd has not been initialized
if(!isset($cmd)) 
{
   //display all the news
   $result = mysql_query("select * from news order by id"); 
   
   //run the while loop that grabs all the news scripts
   while($r=mysql_fetch_array($result)) 
   { 
      //grab the title and the ID of the news
      $title=$r["title"];//take out the title
      $id=$r["id"];//take out the id
     
	 //make the title a link
      echo "<a href='edit.php?cmd=edit&id=$id'>$title - Edit</a>";
      echo "<br>";
    }
}
?>

The second part of the tutorial involves displaying the information from the mySQL row which you can edit. Take a look at the coding:

<?
if($_GET["cmd"]=="edit" || $_POST["cmd"]=="edit")
{
   if (!isset($_POST["submit"]))
   {
      $id = $_GET["id"];
      $sql = "SELECT * FROM news WHERE id=$id";
      $result = mysql_query($sql);        
      $myrow = mysql_fetch_array($result);
      ?>
	  
      <form action="edit.php" method="post">
      <input type=hidden name="id" value="<?php echo $myrow["id"] ?>">
   
      Title:<INPUT TYPE="TEXT" NAME="title" VALUE="<?php echo $myrow["title"] ?>" SIZE=30><br>
      Message:<TEXTAREA NAME="message" ROWS=10 COLS=30><? echo $myrow["message"] ?></TEXTAREA><br>
      Who:<INPUT TYPE="TEXT" NAME="who" VALUE="<?php echo $myrow["who"] ?>" SIZE=30><br>
   
      <input type="hidden" name="cmd" value="edit">
   
      <input type="submit" name="submit" value="submit">
   
      </form>
   
   <? } ?>

Alright, that looks confusing too, but again its simpler than it looks. The second part works right after you click the Title in the first part. The $cmd variable has been set to "edit" and no sign of a $submit variable exists. So, we select the post from the news that matches the $id sent from part one. We fetch that row and display the Title, the Message, and Who submitted it. We finally display a hidden variable that defines $cmd to "edit" again and this time create a submit button and $submit variable.

The third part is fairly, simple. Take a look:

<?
   if ($_POST["$submit"])
   {
      $title = $_POST["title"];
      $message = $_POST["message"];
      $who = $_POST["who"];

      $sql = "UPDATE news SET title='$title',message='$message',who='$who' WHERE id=$id";

      $result = mysql_query($sql);
      echo "Thank you! Information updated.";
   }
}
?>

The third part is the easiest but the most important. $cmd="edit" and $submit is true, so that means we have the edited data and all we have to do is send it back. We update the 'news' table with all the new information, where title, message, and who are rows in the mySQL table. Finally, we echo out that the information has been updated. A lot of people ask if all this code can all be placed on a single page, and the answer is yes, thats the way its supposed to work :)
Not too tough was it? Well thats it folks. You can see my code that I worked on and made sure worked by right clicking and saving edit.txt. If you need help, you can E-mail us at webmaster@spoono.com.