Advanced webcoding: PHP – Part 2: Inserts
Following to the web development series I would like to s how you today how you can create an HTML form which calls a PHP page that inserts data in a MySql database. If this doesn’t sound familiar to you, please read my first post on PHP.
Let’s start with creating a new user for the MySQL database. We can use this user to connect from our PHP page to the database. There are 2 ways of doing this. Either by using the command line or by using a tool. For a change, since I always use code, I’ll show you how to do this by using a tool.
SQLBuddy, this is delivered with your WAMP installation.
If you run WAMP on your own computer, you should normally be able to reach SQLBuddy from this link: http://localhost/sqlbuddy/login.php.
The default login shows root as user. If you haven’t change anything you can just hit enter and you’ll login (how very secure). In other words, just leave the screen by default and hit the submit button.
Right, now let’s create us a user shall we. Click the link Users in the top left corner of the screen. Add a new user by filling in the form halfway the page. Give the user privileges to all databases (you can finetune security later).
I named my user tutorial with the same password.
Alright, we are good to go. First things first. Let’s create a new database. Open up the MySQL console by clicking the WAMP icon next to your windows clock and going to MySQL and selecting the MySQL console icon…
Create a new database by typing following command: create database <insert_fancy_name_here>;
In my case I named my database…. tutorial.. indeed. In order to use this database type the command: use <database_name>.
The console now says, database changed. So far so good! :)…
Let’s create a customer table to store our data in. I used following statement to create a table:
CREATE TABLE CUSTOMER(
ID INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
CITY VARCHAR (100),
A little note, take a look at the field ID. I put quite a lot of things next to it. It should be an INT, okay so far so good, it’s the primary key of the table, it shouldn’t be empty and it has auto_increment.
This auto increment means that this field will be filled automatically with a number starting from 1. Like this you will always have a unique primary key.
Right we are now all set to start writing our PHP page!
This is what it looks like (it’s too big to put in a screenshot).
alert(“Passwords don’t match”);
<FORM NAME=”REGISTER” onsubmit=”return check_passwords();” method=”POST” ACTION=”insert.php”>
<TD><INPUT TYPE=”TEXT” NAME=”COMPANY_NAME” /></TD>
<TD><INPUT TYPE=”TEXT” NAME=”FST_NAME” /></TD>
<TD><INPUT TYPE=”TEXT” NAME=”LST_NAME” /></TD>
<TD>Street + Housenumber:</TD>
<TD><INPUT TYPE=”TEXT” NAME=”STREET” /></TD>
<TD><INPUT TYPE=”TEXT” NAME=”HOUSE_NR” SIZE=”4″/></TD>
<TD>City + Zipcode:</TD>
<TD><INPUT TYPE=”TEXT” NAME=”CITY”/></TD>
<TD><INPUT TYPE=”TEXT” NAME=”ZIPCODE” SIZE=”4″/></TD>
<TD><INPUT TYPE=”TEXT” NAME=”PHONE”/></TD>
<TD><INPUT TYPE=”TEXT” NAME=”EMAIL”/></TD>
<TD><INPUT TYPE=”PASSWORD” NAME=”PASSWORD”/></TD>
<TD><INPUT TYPE=”PASSWORD” NAME=”PASSWORD_CONFIRM”/></TD>
<INPUT TYPE=”SUBMIT” VALUE=”Register”/>
There is just one line that I would like to clarify since you will need to understand it.
<FORM NAME=”REGISTER” onsubmit=”return check_passwords();” method=”POST” ACTION=”insert.php“>
If you click the button of the form it will launch the function check_passwords(). My HTML page has 2 password fields, one to type it and one to confirm it. If passwords don’t match an error will be raised.
The method this form uses is POST, so it will send data and it will send it to the link insert.php. This is the page that we’ll need to create and that contains our PHP code. So this HTML line will basically send all the data in the form to the MySQL database.
This is vitually the only new thing in here :), everything else was explained in my previous posts.
Right, for the PHP page. I wrote it as following:
And I suppose some explaining is in order here ;-)…
<?PHP ==> This starts the PHP script
$con = mysql_connect(“localhost”,”tutorial”,”tutorial”); ==> This creates a variable CON, the value of this variable is filled by the sql function mysql_connect. This function requests 3 parameters: Host, User, Password. Which is in my case (and probably in your case as well) localhost, tutorial, tutorial.
die(‘Could not connect: ‘ . mysql_error());
==> This is a check. If the con variable has failed an error will be show. This allows us to debug the script.
==> Since this code will just be replacing our MySQL console, we’ll need to write this page as if we are entering the code ourself. So we need to switch to the correct database. This can be done with the function mysql_select_db. This function requires 2 parameters. The name of the database between quotes and a connection. Since we already made the connection in the con variable we can use this as parameter.
$sql=”INSERT INTO CUSTOMER VALUES
==> This is the action insert statement. I created a variable named sql and I just entered an insert statement here. Notice the $_POST entries I made. These just say: retrieve the value of the field with following name from the form that has been sent to us. Also notice I entered NULL for the first value (this is because the ID field has auto increment, remember ;-)).
Basically this like will just enter data in the database. All the data is coming from the form in the HTML page.
die(‘Error: ‘ . mysql_error());
==> Another check, just like before. If the insert statement fails the error will be shown.
echo “1 record added”;
==> This is what will be shown on the page when the insert succeeds
==> Very important step. Close the connection to the database! This is done with the function mysql_close. This function requires 1 parameter, being a connection. In my case this is that variable con.
?> ==> This closes the PHP script.
So, that’s about it… Let’s take it for a drive shall we?
Yay! That worked :’)… We should now see this record in the database. Do a select from the CUSTOMER table in the MySQL console.