Editing MySQL Data Using PHP

by

Access a MySQL Database=”border: 1px solid #fd2d2d; background: #ffb5b5; padding: 10px; margin-bottom: 20px; text-shadow: none;”>

You should not use this code on a production website.

Warning: This tutorial uses old techniques. It is insecure and will leave your server vulnerable to SQL Injection attacks.This tutorials also uses mysql_ functions that are no longer supported. For updated tutorials look for a MySQLi tutorial.This post will be delete or revised in the future.

We have had a few tutorial that show how to display and add data to a MySQL database. Now I am going to show you how to edit a row in your database. In previous examples we setup a table which contains: ID, FName, LName and PHON. We will be retreiving the data, making changes, then updating the row in the database. This tutorial is designed for the user to update there own information so we will only be editing row for this user.

If you haven’t taken a look at the past tutorials you may want to:
How to Access a MySQL Database Using PHP
Inserting Data Into a MySQL Database using PHP

You are going to need to create the database if you haven’t done so yet. I called my table `TestTable` and populated the following fields:

ID – Integer, Auto increment
FName – varchar (first name)
LName – varchar (last name)
PHON – varchar (phone number)

Populate some test data into your database and you should be ready to go.

We are going to need to create two files in order to edit the date.
editinfo.php – We will get the user info from the DB and put it into a form.
updateinfo.php – We will send the changes from editinfo.php to this for and update the database.

First create editinfo.php

We are going to connect to the database:

<?php
mysql_connect("localhost","USERNAME","PASSWORD") or die("Error: ".mysql_error()); //add your DB username and password
mysql_select_db("DBNAME");//add your dbname

?>



Since I am only editing 1 row I am going to just use the user with ID 1. If you were using this in an application you would have this information store when the user is authenticated, but I am going to hard code the user ID into my queries for the example.


<?php
mysql_connect("localhost","USERNAME","PASSWORD") or die("Error: ".mysql_error()); //add your DB username and password
mysql_select_db("DBNAME");//add your dbname

$sql = "select * from `TestTable` where ID = 1";
$query = mysql_query($sql);

while ($row = mysql_fetch_array($query)){

	$id = $row['ID'];
	$fname = $row['FName'];
	$lname = $row['LName'];
	$phone = $row['PHON'];

	//we will echo these into the proper fields

}
mysql_free_result($query);
?>



As you can see by the comment about, we will return the rows as an array called $row. Once we build the HTML form we will be able to populate the data into the field. Now I will start with the HTML for the form.


<html>
<head>
<title>Edit User Info</title>
</head>

<body>

<form action="updateinfo.php" method="post">

</form>
</body>
</html>




The important things to note in the HTML above is the form action and method. Action is where we will be sending the data. We want to pass all the changes to updateinfo.php and then updateinfo.php will update the database. The method is the method that we will be using to transmit the variables. You can use either GET or POST variables. GET variables will be displayed in the url, for this example we do not want that, we can you the post method.


Next add the form fields. We will use the input tag for this form. Pay attention to the different values if you are not familiar with them.


type=”text” –just means we are using a text field

value=”” –this is what the box will display by default.

name=”id” –this is what the variable will be named. This is what we will send to updateinfo.php


<html>
<head>
<title>Edit User Info</title>
</head>

<body>

<form action="updateinfo.php" method="post">

userid:<br/>
<input type="text" value="" name="id" disabled/>

<br/>

Last Name:<br/>
<input type="text" value="" name="fname"/>

<br/>

Last Name:<br/>
<input type="text" value="" name="lname"/>

<br/>

Phone Number:<br/>
<input type="text" value="" name="phon"/>

</br>

<input type="submit" value="submit changes"/>

</form>

</form>
</body>
</html>




Now we have the fields setup. You will notice that the input box for id is disable. This is because I do not want the user to be able to change their ID.


Next step is to populate the form with some data. We will echo the values that we set above in the PHP code.



<html>
<head>
<title>Edit User Info</title>
</head>

<body>

<form action="updateinfo.php" method="post">

userid:<br/>
<input type="text" value="<?php echo $id;?>" name="id" disabled/>

<br/>

Last Name:<br/>
<input type="text" value="<?php echo $fname;?>" name="fname"/>

<br/>

Last Name:<br/>
<input type="text" value="<?php echo $lname;?>" name="lname"/>

<br/>

Phone Number:<br/>
<input type="text" value="<?php echo $phone;?>" name="phon"/>

</br>

<input type="submit" value="submit changes"/>

</form>
</body>
</html>



Now you should have you fields populated. The next step is to create the updateinfo.php file and actually update some data. We will start by capturing the data. First connect to your database.


<?php
mysql_connect("localhost","USERNAME","PASSWORD") or die("Error: ".mysql_error()); //add your DB username and password
mysql_select_db("DBNAME");//add your dbname

//get the variables we transmitted from the form
$fname = $_POST['fname'];
$lname = $_POST['lname'];
$phon = $_POST['phon'];

?>



Build the update query and execute.


<?php
mysql_connect("localhost","USERNAME","PASSWORD") or die("Error: ".mysql_error()); //add your DB username and password
mysql_select_db("DBNAME");//add your dbname

//get the variables we transmitted from the form
$fname = $_POST['fname'];
$lname = $_POST['lname'];
$phon = $_POST['phon'];

//replace TestTable with the name of your table
//replace id with the ID of your user
$sql = "UPDATE `TestTable` SET `FName` = '$fname',`LName` = '$lname',`PHON` = '$phon' WHERE `TestTable`.`ID` = '$id' LIMIT 1";

mysql_query($sql) or die ("Error: ".mysql_error());

echo "Database updated. <a href='editinfo.php'>Return to edit info</a>";

?>



That is all you need to allow a user to update their info. Now this is a very basic version. If you were to use this on a production web site you would want to do form validation and also protect yourself from sql injection in the update form.


Here is the full source for both files:


editinfo.php


<?php

//replace usernaem,password, and yourdb with the information for your database
mysql_connect("localhost","USERNAME","PASSWORD") or die("Error: ".mysqlerror());
mysql_select_db("YOURDB"); 

//replace TestTable with the name of your table
//also in a real app you would get the id dynamically
$sql = "select * from `TestTable` where ID = 1";
$query = mysql_query($sql);

while ($row = mysql_fetch_array($query)){

	$id = $row['ID'];
	$fname = $row['FName'];
	$lname = $row['LName'];
	$phone = $row['PHON'];

	//we will echo these into the proper fields

}
mysql_free_result($query);
?>

<html>
<head>
<title>Edit User Info</title>
</head>

<body>

<form action="updateinfo.php" method="post">

userid:<br/>
<input type="text" value="<?php echo $id;?>" name="id" disabled/>

<br/>

Last Name:<br/>
<input type="text" value="<?php echo $fname;?>" name="fname"/>

<br/>

Last Name:<br/>
<input type="text" value="<?php echo $lname;?>" name="lname"/>

<br/>

Phone Number:<br/>
<input type="text" value="<?php echo $phone;?>" name="phon"/>

</br>

<input type="submit" value="submit changes"/>

</form>
</body>
</html>




updateinfo.php


<?php
//replace usernaem,password, and yourdb with the information for your database
mysql_connect("localhost","USERNAME","PASSWORD") or die("Error: ".mysqlerror());
mysql_select_db("YOURDB"); 

//get the variables we transmitted from the form
$id = $_POST['id'];
$fname = $_POST['fname'];
$lname = $_POST['lname'];
$phon = $_POST['phon'];

//replace TestTable with the name of your table
$sql = "UPDATE `TestTable` SET `FName` = '$fname',`LName` = '$lname',`PHON` = '$phon' WHERE `TestTable`.`ID` = '$id' LIMIT 1";

mysql_query($sql) or die ("Error: ".mysql_error());

echo "Database updated. <a href='editinfo.php'>Return to edit info</a>";
?>



If you are going to ask a question please take the time to go through the tutorial first. Thanks.