Inserting Data Into a MySQL Database using PHP
This tutorial is a continuation on the “How to Access a MySQL Database Using PHP” tutorial that showed you how to setup a database using phpMyAdmin and how to read data from the database using PHP. In the tutorial I will show you how to write data to the database directly through a from on your website. This is going to be a very basic writing tutorial. We will get into more advanced stuff later on.
You will need to follow the first part of the the “How to Access a MySQL Database Using PHP” tutorial to setup the database and user permissions. After you do that you can continue with this tutorial.
At this point you should have the database setup with some test data in it. I am going to show you how to update the date by using a form on a website. First we will create the form. You can use any editor you would like. Sometimes I use notepad, but for this tutorial I will be using cPanel’s web base editor. It allows me to edit the files directly on my server and also color codes the lines nicely.
First we will create our form. We will name it “form.php”.
Now we will create the form using some basic html, nothing fancy. Start with the form tag. Notice that the form action is set to update.php, that is the script that will update the database.
Now we will add 3 input boxes for our 3 columns in the database. FName, LName, and PHON. There is a 4th column in the database we created, the ID field. That field is auto generated so we do not need to update it.
We created the input boxes for each field in the database. You will notice that the size of each input is limited to the size of our fields in the database. Each field is a text because we want a text box.
Next we need to add a submit button.
A submit button is also an input field. The type is submit, which will create a button and the value gives the button a label that you will see from your browser.
That is it for form.php. I might as well mention that there is no reason that I named this with a .php extension. It could have been called form.html and it would have functioned the same way because there is no php code on the form. Save the fiel and open it in your browser. It should look like this.
The next step is to create the update.php file. This file will update the database with the new information. Create a new file called update.php.
Now to start coding a php file we always start with the open and close php tags.
The first thing we are going to do is capture the data that was sent from the form.php file and store the info as variables. Because the form method was set to post, we can use the post variable function to collect the data. You will notice that the names in the post function will match the names we used on the form.
Now we will make a connection to the database. You will have to replace:
“localhost” with the location of your server
“projectc_testuse” with your username
“password” with your password
Then we are selecting the “projectc_Test” database you will change that to whatever you named the database in the first tutorial.
Next we are going to create the query that will insert a new row in the database. As you can see we are inserting in the table called TestTable and were are inserting the fields ID, FName, LName, and PHON. The actual values are coming from the variables. The ID fields is left NULL because it is auto generated by the database.
Now all we have to do is have the query execute. Then if the query fails we will print an error. If it is successful we will print the info that was added to the database.
Now you can test out the form.
When you submit the form you should see your results displayed.
Now if you run the php file from part one of this tutorial you should see you updated info in displayed along with everything else that was in the database.
So that is a very basic way of writing to a MySQL database using PHP. Now you should be able to create a database, write data to the database, and read from the database. I hope to create some more advanced PHP/MySQL tutorials in the future.
Popularity: unranked [?]
on February 4th, 2008 at 1:29 am
Good post for those new to php/mysql. Glad to see regular posts after a long inactivity. Please continue posting regularly if not frequently.
on February 26th, 2008 at 2:30 am
That is a great help
on February 29th, 2008 at 4:21 am
Hello there.
Just found your site. Great job!
I like it much.
look here http://live.com
on February 29th, 2008 at 10:55 am
dasdasdasd
on March 11th, 2008 at 5:31 am
Hello
Just found your site. !
I like it and thanks .
on March 15th, 2008 at 1:32 am
I run the script and it echos it was updated but the table doesnt show any info was inserted, what could i have possibly one wrong?
on March 15th, 2008 at 5:09 am
Do you have any info in the table at all. For some reason if I do not enter the first row of data manually using php my admin, then the table will not update from the php file.
on March 15th, 2008 at 9:20 pm
i entered data myself through phpmyadmin and then tried to with the script and it did squat
on March 15th, 2008 at 10:19 pm
It’s hard to tell what could be happening. I would check the code one more time. It is strange that it is not throwing a MySQl error. Make sure the query is right.
on March 16th, 2008 at 8:27 pm
i checked it but i also found a friends script and its doing what i need but not exactly i need to code a script that pulls info from a database but can also put it in and yeah theres to much to explain in this lol, i just need to find someone to talk mono on mono on like a chat thing or something and see if they can help me
on March 17th, 2008 at 6:50 pm
Thank you very much for the training lesson above. It has come in handy close to a deadline.
on March 25th, 2008 at 2:32 pm
Thanks a lot….it clears my doubts
on March 31st, 2008 at 11:17 am
Thanx a lot!
I’ll be waiting for the next script, the one that will searching the database
on April 16th, 2008 at 11:45 pm
thank a lot…
it is very helpful for me to do assignment..
on April 27th, 2008 at 6:38 pm
I tried you’re code and no matter what i did it would not work
on May 5th, 2008 at 6:28 am
Hi!
I need to extract data from mysql by using php….and I want as a link; so user can click to see details of that link (more info)
can u give me a hand pls!!!!!
on May 17th, 2008 at 7:06 am
hello
Thanks for the tips, but why do firefox & ie both return this parse error:
“syntax error, unexpected ‘;’ in /home/gypsy/public_html/update.php on line 10″
? please help
on May 17th, 2008 at 9:30 am
ok dont worry, figured it out, I think I am on a slightly newer version of php or something, because changing my syntax slightly fixed the error.
instead of
echo “updated with: ” .variable. ;
I wrote
“updated with: “, variable ;
on May 24th, 2008 at 2:10 am
Thank you very much
on June 12th, 2008 at 1:58 am
hi
i want set a update and view buton
on June 16th, 2008 at 10:35 am
if you please help me! i am a student and i have web project
if i have a page in html with checkbox and i want to select one or more
pieces how can i write the php page to add the selected pieces to the invoice!
with my thanks
on June 17th, 2008 at 7:18 pm
@ sanae
There is an easy way to do this. I am going to do a tutorial on this that will post next week some time. For starters you would need to make all the checkboxes be in an array.
eg:
Box 1
Box 2
Box 3
You receiving file should then contain a for next loop that will take each box and if they were checked and if they were to run a query to enter them into the database like so:
foreach($cats as $key => $value) {
if ($value > 0) {
$sql = sprintf(”insert INTO value_table(row1,rrow2) VALUES(’%s’,'%s’);”,
$value, $old_no);
mysql_query($sql);
}
}
That should do what you need to do. Let us know if you have issues and thanks for the great tutorial idea.
on June 17th, 2008 at 7:22 pm
(HTML not allowed in comments) Here is what Box1, Box2, and Box3 should be.
input type=’checkbox’ name=’boxes[]‘ value=’1′ />Box 1
input type=’checkbox’ name=’boxes[]‘ value=’2′ />Box 2
input type=’checkbox’ name=’boxes[]‘ value=’3′ />Box 3
on June 27th, 2008 at 10:45 am
Nice tutorial, syntax highlight really helps in understanding the code
on June 28th, 2008 at 10:00 am
nice tutorial
on June 30th, 2008 at 10:01 am
I tried it and it did not update…not sure why.
I typed the exact thing you have
on June 30th, 2008 at 10:12 am
Here is my code:
when ran, it gives the error “error updating database”
Please help!!
html file
————–
Members Search
Enter Member Information:
ID:
First Name:
Last Name:
Phone Number:
Php file
————-
$fname = $_POST['FirstName'];
$lname = $_POST['LastName'];
$phone = $_POST['PhoneNumber'];
mysql_connect(”localhost”, “bookorama”, “bookorama123″) or die (’Error: no db connection ‘ . mysql_error());
mysql_select_db(”test”);
$query=”INSERT INTO members (id, firstname, lname, phone)VALUES (’NULL’, ‘”.$fname.”‘, ‘”.$lname.”‘, ‘”.$phone.”‘)”;
mysql_query($query) or die (’error updating database’);
echo “database was updated with: ” .$fname.” “.$lname.” “.$phone;
// close connection
mysql_close();
on June 30th, 2008 at 2:01 pm
@IJ
Can you give us the exact error message? That will make troubleshooting much easier.
on July 2nd, 2008 at 10:09 pm
@John
the error is :
error updating database