Editing MySQL Data Using PHP
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.
Popularity: unranked [?]
Related Posts
on June 8th, 2009 at 12:22 pm
This tutorial is superb, despite a few errors i made,on careful inspection it turned out wonderfully .
thanks guys. u are great
on June 19th, 2009 at 4:39 pm
What are the errors? please share them because i need this to!
on June 29th, 2009 at 7:18 pm
I think there is a mistake in the listing or at least I found something that did not work for me.
If in ‘editinfo.php’ I use the option ‘disabled’ for the ID then apparently the POST method does not transfer the of ID to ‘Updateinfo.php’ and the function does not work. If I remove the ‘disabled’ option in the textbox it does work.
Perhaps a tip for others that run into errors.
After adding some of my own field names, I spent hours debugging until I realized the sql statement needs ` instead of ‘ around the field names.
In spite of my remarks Great code!!!
on June 29th, 2009 at 7:33 pm
Coz I kinda liked the look of the ‘disabled’ Id field, I did not want to take that out so I added:
<input type="hidden" value="” name=”x” />
Directly under the
<input type="text" value="” name=”id” disabled/>
line
on June 29th, 2009 at 7:34 pm
Oops
that should be
on June 29th, 2009 at 8:14 pm
Oops that should be
on June 29th, 2009 at 8:15 pm
Hmm some problem posting. It should be
And if the code again does not come true: that x should be Id
on June 29th, 2009 at 8:16 pm
so:
input type=”hidden” value=”” name=”Id” /
and put fish hooks around it
on June 30th, 2009 at 4:02 pm
What if my ID wasn’t hard wired but what if I had a table of all the users which has all the rows of users in the database, and when i click on a button next to a single row (like edit or delete) I want to get the ID for that row (user) which i clicked, so I could then use the ID to edit or delete only that user??? How would that go?
on June 30th, 2009 at 5:19 pm
The ID’s in this tutorial are not “hard wired” We get the id from the database in the initial query. Therefore your question is already answered.
on July 1st, 2009 at 2:56 am
Yeh but it’s already defined as 1, how would u do it if you wanted to click on a button, and when you clicked it (the edit button) you got the ID for that row? So when you clicked it took you to a page that contains the form for editing but it knew which row u were editing, by checking the id against the thing u clicked or something like that? Im a newbie so I really don’t know this stuff and could use the help.
on July 1st, 2009 at 12:37 pm
You choose the user ID and write it in but the program should know the user which to update according to a button next to the row of the particular user (in a table of users) otherwise whats the point of creating a form for updating a user when it is a form for only a single user, if you wanted to update a different user you would have to go into the PHP code and change the ID. What I want to do is do it by having a button next to a row which when clicked will automatically find the ID of the row and save the ID so it could later be called by the UPDATE query. If anyone could tell me how to do that i would be eternally gratefull
on July 1st, 2009 at 2:55 pm
Dino
I have a listing for that, But I do not know how to put code here. But basically I have a form for search criteria that gives me the record(s) that I am looking for.
Then next to each record found I have a link (but it might as well be a button) to edit that record. What the link does it takes the ID of the found record(s) and adds that with a Get method to the link of the routine above. So when I click the “edit this record link” it calls the above form with the proper ID.
If someone can tell me how I place code here, I’d be happy to share it.
on July 1st, 2009 at 2:57 pm
Obviously for that I made as light alteration to the code above to read the ID from the link string
on July 1st, 2009 at 5:47 pm
That would be awesome, I dunno how to post code here but if you have facebook or msn that would work (facebook would be better cos I don’t wanna post my email on the internet even though i don’t keep any sensitive information on it) but if you don’t just say so and I’ll give u my email.
As for the search thing, is it like a search engine on the page or is it just some code that tells it to search the database for the ID of the thing clicked without the user having to do anything to make it happen??? (because in my thing its also a link that sends you first to a php code page thats supposed to do what you say your does, but doesn’t, and then to a page with the form for updating the thing)
on July 2nd, 2009 at 4:02 am
Not sure what you mean with the various ‘things’. it is just a search form that gives results after you enter criteria. Those results have 2 links next to them 1 link to choose the full record and one link to edit that record.
What I will do is put the code on a site where I know how to put code and leave the link here. I will do that somewhat later today, ultimately tomorrow.
on July 2nd, 2009 at 9:59 am
Thanx that will work, I appreciate it. Since its a search engine that gives u the the rows to edit its a bit different than mine cos mine is just a list with all the rows in the database but the principle should be the same that is, saving the ID by clicking on edit and getting it later. Anyhoo thanks a bunch
on July 3rd, 2009 at 9:24 am
Dino, check: http://programmaatjes.blogspot.com/2009/07/searching-and-updating-database.html
You’ll be interested in the program dbsearch.php around the link ‘Edit data’
Just understand that my fieldnames are different than in the example above and my Id field is called ‘x’
What dbsearch. php does is that it puts the id no of the found record(s) in a link, that passes the id nummer to the (adapted) program editinfo.php (above). My adaptations are the field names ofcourse + a get statement to read the id number.
Hope this helps
on July 3rd, 2009 at 9:38 am
just let me add that the “edit data” link could easily be turned into a button. I just did not have the time yet to design it.
also, for me this works on a search, but you could ofcourse add that link also to a full read of the database.
on July 3rd, 2009 at 9:42 am
So where is the link??? Did you post it, cos I can’t see it?
on July 3rd, 2009 at 2:17 pm
I posted the link at 9.24 am, before the 9.38 one but I see that comment is still awaiting moderation. perhaps because it has a link in it.
on July 3rd, 2009 at 2:19 pm
So perhaps without the usual ht… start, try
programmaatjes.blogspot.com
on July 3rd, 2009 at 7:21 pm
After trying your code (which I am very gratefull for) i run into the following error
Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in C:\Adi\Web Design\wamp\www\Dino vol.2\dbsearch.php on line 40
I changed the code to reflect my database, and the error seems to be occuring in this piece of code
$query = “SELECT SQL_CALC_FOUND_ROWS *,
MATCH(ImeTendera) AGAINST (‘$q’ IN BOOLEAN mode) AS score1,
MATCH(Predmet) AGAINST (‘$q’ IN BOOLEAN mode) AS score2
FROM tenderi
WHERE
MATCH(ImeTendera, Predmet) AGAINST (‘$q’ IN BOOLEAN mode)
ORDER BY score1 DESC, score2 DESC LIMIT $limit, $max”;
// Perform the query
$sql = mysql_query($query);
I dunno what I’m doing wrong if u want I can post the whole of my edited code.
on July 3rd, 2009 at 7:22 pm
By the way i hope u got the last message with the problem cos i couldn’t post it on the link u gave me cos I don’t have a google account
on July 4th, 2009 at 1:58 am
Odd, coz it works for me, I mean I just copied it from my working program. I’ll check if maybe in posting something changed.
You can post on the blog from now on as I changed the settings
on July 4th, 2009 at 2:07 am
Dino, let’s take the conversation over to my blog and let me know what exactly is your line 40
on July 4th, 2009 at 2:12 am
Heb je overigens ook de ‘include db.php’ aangepast?
on July 4th, 2009 at 2:24 am
oops, wrong language: did you provide a db.php connection file of your own?
on July 5th, 2009 at 7:46 am
Hey I just posted on your blog but ill post here as well, the late reply is due to some difficulties towards connecting to the internet as they are painting my house, as for the code It’s on the page here dunno if you can see it but line 40 is this line if(mysql_num_rows($sql) > 0){ which I presume means that the query which is supposed to store some values in the sql array is no good which is why i posted it. Anyhoo I tried posting all the code on your blog but it said it didn’t allow php code and yes the connection to the database is in the page (I didn’t include it I just connect right there on every page its needed to connect type out the code there)
on July 5th, 2009 at 8:44 am
I am going to need your full code. as neither of us likes to post their email address, would you know of a way to get it to me? can I download it from your server?
on July 5th, 2009 at 8:49 am
the server is localhost so no. Umm I dunno how to get u the code, can I post it somehow on your blog, can you allow that?
on July 10th, 2009 at 3:29 am
using ur code i have used in my aplication , i am geting information from dbase into edit form but when i am updating its not affecting the dbase below is my editinfo.php and updateinfo.php .
see and tell my mistake
editinfo.php
<?php
//replace usernaem,password, and yourdb with the information for your database
//mysql_connect("localhost","root","") or die("Error: ".mysqlerror());
//mysql_select_db("shopperlists7");
//replace TestTable with the name of your table
//also in a real app you would get the id dynamically
//*******************************starts here **************************
//Database Information
$dbhost = "localhost";
$dbname = "shopperlists7";
$dbuser = "root";
$dbpass = "";
//Connect to database
mysql_connect ( $dbhost, $dbuser, $dbpass)or die("Could not connect: ".mysql_error());
mysql_select_db($dbname) or die(mysql_error());
session_start();
$username = $_POST['name'];
$password = md5($_POST['password']);
$query = "select name, email, vendorID , contact from vendors where name='$username' and password='$password'";
$result = mysql_query($query);
if (mysql_num_rows($result) != 1)
{
$error = "Bad Login";
echo "user doesnot exists”;
include “vendorlogin.php”;
}
else
{
$_SESSION['name'] = “$username”;
//$sql = “select * from `vendors` where VendorID = 1″;
//$query = mysql_query($sql);
while ($row = mysql_fetch_array($result))
{
$vendorID = $row['vendorID'];
$name = $row['name'];
$email = $row['email'];
$contact = $row['contact'];
//we will echo these into the proper fields
}
}
mysql_free_result($result);
?>
Edit User Info
userid:
<input type="text" value="” name=”vendorID” disabled/>
Organisation :
<input type="text" value="” name=”name”/>
Email :
<input type="text" value="” name=”email”/>
Contacts :
<input type="text" value="” name=”contact”/>
**************************
here updateinfo.php
<?php
//replace usernaem,password, and yourdb with the information for your database
mysql_connect("localhost","root","") or die("Error: ".mysqlerror());
mysql_select_db("shopperlists7");
//get the variables we transmitted from the form
$vendorID = $_POST['vendorID'];
$name = $_POST['name'];
$email = $_POST['email'];
$contact = $_POST['contact'];
//replace TestTable with the name of your table
$sql = "UPDATE 'vendors' SET 'name' = '$name','email' = '$email','contact' = '$contact' WHERE 'vendors'.'vendorID' = '$vendorID' LIMIT 1";
mysql_query($sql) or die ("Error: ".mysql_error());
echo "Database updated. Return to edit info“;
?>
on July 12th, 2009 at 6:59 pm
Dino
I’ll set something up but make sure you read the comment I left on my blog for you
on July 19th, 2009 at 2:07 pm
Thanks for this, I’m going to combine this with your PHP CSV parser to try and create a WP plugin which can import my Affiliate data feeds.
on September 8th, 2009 at 10:41 am
To change the location of MySQL data storage you will need to specify the datadir in the MySQL configuration file (my.ini) and restart the server.
on September 20th, 2009 at 9:48 pm
OK, so this is the deal. I have a “Guestbook” type deal, but it is for a few select Admins of my organization. This is where we update changes and what not. There is like 8 fields and I am needing to make this so you can change things like “Info Given” – “Status” and -”Topic” where Topic is a Drop down menu and Status is a option field with 3 options. I can get the script to pull the data from the database per the ID if I put
$sql = “select * from `post` where id = 84″;
But I am unable to make this the id “Dynamic” so to be able to change and pull that tables field for that ID. However even pulling a static table, when I submit the change, it does not take. I get no errors or anything of the such.
Is there anyway I can get some more guidance on this? Thanks a lot.
on October 6th, 2009 at 12:29 pm
Worked like a dream. Once again a great tutorial.
on October 6th, 2009 at 12:29 pm
Worked like a dream. Awesome work!
on November 19th, 2009 at 4:23 pm
@Dino GET variables will be displayed in the url
on December 10th, 2009 at 5:42 pm
I had to remove the disable from the form to make it work. other than that, it was fantastic. This is a great tutorial.
on January 2nd, 2010 at 11:41 pm
this is a good tutorial:D it’s complete though i haven’t tried it out yet.. does anyone here knows how to generate a USERNAME from getting a user’s input (input: LAST NAME,FIRST NAME,ID) in php???thanks
on January 2nd, 2010 at 11:42 pm
this is a good tutorial:D it’s complete though i haven’t tried it out yet.. does anyone here knows how to generate a USERNAME from getting a user’s input (input: LAST NAME,FIRST NAME,ID) in php???thanks
on January 24th, 2010 at 1:51 am
Excellent awesome work.
on January 24th, 2010 at 1:53 am
You have excellent work and thanks for tutorial. Thanks for sharing.
on January 27th, 2010 at 5:19 am
Providing more details would be helpful. Providing more details or code snippet would actually allow somebody to answer your question, however, based on the given info, one thing off the top of my head that might be related to your problem is bind length, if you are using any out bind variables.
on February 3rd, 2010 at 9:43 am
Does anyone know if and how I can set up somewhere locally on my computer or something where i can test my pages and database as if it were live on a web server?
on February 22nd, 2010 at 5:46 am
I have tried your codings but its not working for me due to different version of MySQL server…This is the error syntax that i get
“Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ”Month_reminder’='0′, Year_reminder=’0′, Contact_Name=’ sdasd’, Contact_address=’ at line 1″
Could you please help me on this…
on February 22nd, 2010 at 8:04 pm
”Month_reminder’=’0′,
This line here looks like you used an apostrophe on one side of the zero and a tick on the other side. Verify that you have apostrophe (or single quote) surrounding each variable. This should take care of the issue. That error does not mean your version of MySQL is causing the issue. It simply means that it is unable to parse the commands you have in the query due to a syntactical error.