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…

The console now says “Enter password”. If you use this console like this you will always login with root. So in this case just hit enter and that should be that.

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,
FST_NAME VARCHAR(50),
LST_NAME VARCHAR(100),
STREET VARCHAR(200),
HOUSE_NR INT,
CITY VARCHAR (100),
ZIPCODE VARCHAR(30),
PHONE VARCHAR(30),
EMAIL VARCHAR(100),
COMPANY_NAME VARCHAR(200),
PASSWORD VARCHAR(10)
);

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!

We’ll need to create an HTML form first with a form which allows us to enter data. I’ll just give you my code without any further explanation since this is not a HTML or Javascript post ;-).

This is what it looks like (it’s too big to put in a screenshot).

<HTML>
<HEAD>
<TITLE>Customer Registration</TITLE>
<script type=”text/javascript”>
function check_passwords()
{
if (document.forms[“REGISTER”][“PASSWORD”].value==document.forms[“REGISTER”][“PASSWORD_CONFIRM”].value)
{
return true;
}
else
{
alert(“Passwords don’t match”);
return false;
}
}
</script>
</HEAD>
<BODY>
<FORM NAME=”REGISTER” onsubmit=”return check_passwords();”  method=”POST” ACTION=”insert.php”>
<TABLE BORDER=”0″>
<TR>
<TD>Company Name:</TD>
<TD><INPUT TYPE=”TEXT” NAME=”COMPANY_NAME” /></TD>
</TR>
<TR>
<TD>First Name:</TD>
<TD><INPUT TYPE=”TEXT” NAME=”FST_NAME” /></TD>
</TR>
<TR>
<TD>Last Name:</TD>
<TD><INPUT TYPE=”TEXT” NAME=”LST_NAME” /></TD>
</TR>
<TR>
<TD>Street + Housenumber:</TD>
<TD><INPUT TYPE=”TEXT” NAME=”STREET” /></TD>
<TD><INPUT TYPE=”TEXT” NAME=”HOUSE_NR” SIZE=”4″/></TD>
</TR>
<TR>
<TD>City + Zipcode:</TD>
<TD><INPUT TYPE=”TEXT” NAME=”CITY”/></TD>
<TD><INPUT TYPE=”TEXT” NAME=”ZIPCODE” SIZE=”4″/></TD>
</TR>
<TR>
<TD>Phone:</TD>
<TD><INPUT TYPE=”TEXT” NAME=”PHONE”/></TD>
</TR>
<TR>
<TD>E-mail:</TD>
<TD><INPUT TYPE=”TEXT” NAME=”EMAIL”/></TD>
<TR>
<TR>
<TD>Password:</TD>
<TD><INPUT TYPE=”PASSWORD” NAME=”PASSWORD”/></TD>
<TR>
<TR>
<TD>Confirm Password:</TD>
<TD><INPUT TYPE=”PASSWORD” NAME=”PASSWORD_CONFIRM”/></TD>
<TR>
<INPUT TYPE=”SUBMIT” VALUE=”Register”/>
</TABLE>
</FORM>
</BODY>
</HTML>

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.

if (!$con)
{
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.
mysql_select_db(“tutorial”, $con);
==> 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
(NULL,    ‘$_POST[FST_NAME]’,
‘$_POST[LST_NAME]’,
‘$_POST[STREET]’,
‘$_POST[HOUSE_NR]’,
‘$_POST[CITY]’,
‘$_POST[ZIPCODE]’,
‘$_POST[PHONE]’,
‘$_POST[EMAIL]’,
‘$_POST[COMPANY_NAME]’,
‘$_POST[PASSWORD]’
)”;

==> 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.
if(!mysql_query($sql,$con))
{
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

mysql_close($con);
==> 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?

Let’s see what happens if I hit register:

Yay! That worked :’)… We should now see this record in the database. Do a select from the CUSTOMER table in the MySQL console.

I love it when a plan comes together :’)…

Advertisements

2 thoughts on “Advanced webcoding: PHP – Part 2: Inserts

  1. Pingback: Advanced webcoding: PHP – Part 3: Selects « Tech-o-rama

  2. Pingback: Beginners guide to advanced webcoding « Tech-o-rama

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: