HTML WebForm with PHP and MySQL - Part 4

Part 4 - PHP Script to insert data into database

Recap: Part 1 | Part 2 | Part 3

Now that we have the database and all of the columns created, we now need to get that data into the database. We can do that with a PHP script.

First we need to build a PHP file that we can edit. Create a new file on your webserver and name it whatever you want (I called mine "pushtodb.php"). Now open that file with your editor of choice. I like notepad++ but you can use whatever is more comfortable for you.

Just like in HTML, we have to open the PHP script with <?PHP (and don't forget to close the tag now so you don't forget later - close it with ?>  ). This tells the browser that you are going to use PHP as oppose to just HTML. 

Lets create a variable to open up a connection to the SQL server. You do that with this command:

     $con = mysql_connect("SQL_server_name","username","password");

Now we have to tell the SQL server which database to use. This command will connect to the SQL server and open up the proper database:

     mysql_select_db("database_name", $con);

Notice the "$con" in that statement? That will take the first statement that we wrote and inject it into this one. Trust me, putting things in variables will make your life a LOT easier as you can reuse the variables throughout the script. 

Time to write that SQL statement that we will actually insert values into the database. Let's make this in a variable named "$sql" so we can use it over and over. At this point (if you read Part 1) you should have all your inputs from your webform in a text file so we can input them in the SQL statement. You have them in notepad++ right??? Right??? Here is the SQL statement; copy and paste this into notepad++ so you can edit it as you need to:

     $sql="INSERT INTO table_name (input_1, input_2, input_3)
     VALUES
     ('$_POST[input_1]','$_POST[input_2]','$_POST[input_3]')";

Ok we have all the connections out of the way, now time for use to actually perform the command(s). Here is the code that will take all the variables we defined and put them in a IF statement:

     if (!mysql_query($sql,$con))
     {
     die('Error: ' . mysql_error());
     }

One more piece of code that we will need now but will make more sense later on:

      $id = mysql_insert_id ( );

Whew. Come on Bob aren't we done yet? Nope, we have three more steps to finish. 

1. Assign the URL that we want people to go to after the script has been ran

2. Setup the email that will alert us when something has been submitted

3. close the SQL connection

Task 1:  Assign the URL

Put this in a variable, I used the variable "$url" but you can use whatever helps you remember what it is.

     $url = 'http://www.domain.com/thankyou.html';

The command that will actually take users to the next page is (look for that variable):

     echo '<META HTTP-EQUIV=Refresh CONTENT="0; URL='.$url.'">';

Task 2: Setup the email

You are going to see variables from above in this one. You can see why variables are important.

     $emailadd = 'email@yourdomain.com';
     $subject = 'New contract was submitted';
     $body = "This is the body of the email:";
     $text = $body. " " . $id;

     mail($emailadd, $subject, $text, 'From: '.$emailadd.'');

Pay attention to the bottom line. That is the actual command that will send the email out. As you can see, we are building the email with all the variables we make right above it. See that "$id" variable there. I put that in the email in a future lesson we will use that to lookup this form. We need a place holder that we can call this specific form from the database, and since ID is always unique, its the perfect one to use. 

Task 3: Closing the SQL connection (the easy one)

You are going to find this a real challenge. Here is the code (look for the variable that we assigned earlier):

    mysql_close($con)

At the bottom of the script you should see (if you followed directions from above) the ?> to close the PHP script. 

That's it. Post that to your site and give it a shot. Let me know if it works!!!! Good luck.