Important alert: (current site time 8/30/2014 6:13:11 PM EDT)
 

article

Painless Form submission and Database Insertions

Email
Submitted on: 2/8/2005 4:38:45 PM
By: AlexHogan  
Level: Intermediate
User Rating: By 5 Users
Compatibility: PHP 4.0
Views: 25110
(About the author)
 
     How would you like to process a form and insert its data into a database with only a couple of lines of code? With this tutorial I will show you how to process forms and insert data into databases with the least effort.

 
 
Terms of Agreement:   
By using this article, you agree to the following terms...   
  1. You may use this article in your own programs (and may compile it into a program and distribute it in compiled format for languages that allow it) freely and with no charge.
  2. You MAY NOT redistribute this article (for example to a web site) without written permission from the original author. Failure to do so is a violation of copyright laws.   
  3. You may link to this article from another website, but ONLY if it is not wrapped in a frame. 
  4. You will abide by any additional copyright restrictions which the author may have placed in the article or article's description.
				
 

Painless form submissions and Databases insertions..

If you are like me one of the things that you find most tedious is inserting data into databases. Having to name all the fields in the query and match them with all the values that you're inserting in to those fields. Every time you submit a form you have to go through this process.

Ever think.., "There's got to be an easier way"?

Well there is, and in this tutorial I'm going to show you how.

First let's say that you have a form that allows visitors to your web site to register for things like free downloads, tutorials and added content that isn't available to non-registered visitors. We'll call this table "registration". This table contains the fields firstname, lastname, email, username and password.

Your form would look something like this;

My Form
First Name:
Last Name:
Email Address:
Username:
Password:
Submit Reset

When the user presses the 'Submit' button you will need to process that request and enter the data into the database. You would probably use some code that looks like this;

First you have to get the values from the form;

$firstname = $_POST['firstname'];
$lastname = $_POST['lastname'];
$email = $_POST['email'];
$username = $_POST['username'];
$password = $_POST['password'];

Then you have to insert those values into the database;

$insert
=

"INSERT INTO registration
(firstname, lastname, email, username, password)
VALUES
($firstname, $lastname, $email, $username, $password) "
;

Let's not forget that you'll want to see if there is anything in the $_POST array before trying to assign a variable to the value.

All in all not a whole lot of code to write. But, what if your form contained 30 or 40 items with values, or if you have a dozen forms on your site, or if you have an application that makes 10 or 20 database inserts during the course of the users session. Multiply the above code by 20 or 30 times with 10 to 20 more fields in the form and you can see how this can become a tedious and taxing task.

So, how are we going to make our lives easier?

Let's start by looking at the $_POST array. Yes I said $_POST array! All of the data that is posted from your forms is transferred from the form page to the functionality page as an array. So why not use PHPs array manipulation functions to our advantage to help us process our forms and insert data into our databases?

Let's create a class called, "dbinsert" and while we're at it declare some variables.

class dbinsert{
var $i;
var $n;
var $fld;
var $val;

}

The variable $i and $n will simply be counters, while $fld and $val will represent field names and field values respectively.

The next thing we'll want to do is to create a method that will parse through the $_POST data. We'll call this "gotcha" and we'll pass it two parameters $arr, which will be the $_POST array, and $table, which will be the name of our table in our database. We will also initialize $i.

function gotcha($arr, $table){
$this->i = 0;

}

Now we'll want to cycle through the $_POST array with a foreach loop and seperate the array keys and values into temp variables using $key => $value. We then assign the values to our variables $fld and $val. You may notice at this point that the name of the form object is going to be the same as the name in the $fld variable. What we are going to do is insure that we name the form objects the EXACT same names as we have named our fields in our database. In the above example we have named the textbox that will contain the users first name, "firstname", just the same as we've named our field in the database. You'll also notice that we're escaping quotes in our assignment of the $val variable. This is because we want to allow the user to be free to add apostrophes and quotes to any field that would be approapreate to have them.

foreach($arr as $key => $value){
$this->fld[$this->i] = $key;
$this->val[$this->i] = "\"$value\"";

$this->i++;
}

The last thing we want to do is to increment the variable $i before we start the loop over again.

Now if you think about what we are getting in the $_POST you should see that we have a little bit of a problem. Our post array will look like this;
[firstname]=>John, [lastname]=>Smith, [email]=>mymail@mydomain.com, [username]=>myusername, [password]=>mypassword, [Submit]=>Submit

We've also picked up the name and value of the button on the form. Obviously we don't and won't have a field in the database named "Submit" so we'll need to make sure we get rid of that key and value. To do that we use an if statement. We'll check to see if any of the $key(s) are equal to "Submit". In this case we are saying that if $key is not equal to Submit then assign the values to the variables $fld and $val, and when $key does equal Submit then break out of this loop.

foreach($arr as $key => $value){

if($key != 'Submit'){
$this->fld[$this->i] = $key;
$this->val[$this->i] = "\"$value\"";
}
else{
break;
}

$this->
i++;

}

We now have two arrays. The first being the $fld array that contains the names of the fields that we are going to insert data into, and the $val array that contains the values or data for each of those fields.

The last line in our method is;
$this->insert($table, $this->fld, $this->val);

Here we are calling another method that will actually insert the values into the database in the appropreiate fields.

So the entire gotcha method looks like this;

function gotcha($arr, $table){
$this->i = 0;

foreach($arr as $key => $value){
if($key != 'Submit'){
$this->fld[$this->i] = $key;
$this->val[$this->i] = "\"$value\"";
}
else{
break;
}

$this->i++;

}

$this->insert($table, $this->fld, $this->val);

}

All thats left now is the actual function, or method that will insert the data into our database. We'll call this method "insert" just like we called it in our gotcha method. In this method we'll pass in the table name, $table, the fields, $fld and the field values, $val.

function insert($table, $fld, $val){

}

Next we have to build our SQL statement that will insert the data to our database. Here we are assigning the variable $query with the insert statement. At first it looks like what we've done above with the normal insert statement with the exception that we have used $table for the table name and (%s) for both the field names and the field values.

$query = "INSERT INTO $table (%s) VALUES (%s)";
$query = sprintf($query, implode(",", $fld), implode(",", $val));

On the next line we're letting PHP break apart the arrays that we created in the "gotcha" method and using the "implode" function we are creating a string that has all the field names seperated by a comma and using the sprintf() function then replacing the %s after $table with the string. The same is done with the $val array and all the values are broken into a string and replace the %s after values.

The next line passes the $query to the function mysql_query() just like normal.

$result = mysql_query($query) or die;

The completed method looks like this;

function insert($table, $fld, $val){
$query = "INSERT INTO $table (%s) VALUES (%s)";
$query = sprintf($query, implode(",", $fld), implode(",", $val));
$result = mssql_query($query) or die;

}

Put it all together and the finished class looks like this;

class dbinsert{
var $i;
var $n;
var $fld;
var $val;

function gotcha($arr, $table){
$this->i = 0;

foreach($arr as $key => $value){
if($key != 'Submit'){
$this->fld[$this->i] = $key;
$this->val[$this->i] = "\"$value\"";
}
else{
break;
}

$this->i++;

}

$this->insert($table, $this->fld, $this->val);

}

function insert($table, $fld, $val){
$query = "INSERT INTO $table (%s) VALUES (%s)";
$query = sprintf($query, implode(",", $fld), implode(",", $val));
$result = mssql_query($query) or die;

}

}

Now save this off as "class_one.php" or what ever you like.

We've got our class now how do we access it?

In order to make this as streamlined and maintenance free as possible we're going to create another file called, "submit_forms.php". This is the file that we'll call from our form action.

At the top of this page we have to include our class_one.php file so we'll use the requier_once() to add it to our page.

require_once('class_one.php');

We now have to find out where this page is being called from. Since we are going to use this for all of our form processing we need to know where the information came from.

$from = substr(strrchr($_SERVER['HTTP_REFERER'],'/'),1);

Here we are using the substr(), strrchr() and $_SERVER['HTTP_REFERER'] functions to find out what the page name is that called this "submit_forms.php" page and we are going to use that to route the user after we've inserted the data to the database. Just in case you haven't noticed we still need to identify what table in the database we're going to insert our data into. Since we'll want to make this as expandable as possible we're going to use a switch statement.

switch ($from){
case 'myform.php':
$table = 'registration';
$location = 'thankyou.php';
break;

}

In this case we have identified $from to have the value of the name of our form file, "myform.php", and we've identified the table as being "registration" and now we have a location to go after the data has been entered into the database as "thankyou.php" where the user will be thanked for registering on our web site.'

Now we have to call our class. First we'll create a new instance of the class.

$dbinsert = new dbinsert;

Now we'll call our method "gotcha", and pass it the $_POST array and $table variable.

$rs = $dbinsert->gotcha($_POST, $table);

The last thing we need to do is to actually redirect the user after their data has been entered.

header("location:".$location);

The completed file will look like this;

require_once('class_one.php');

$from = substr(strrchr($_SERVER['HTTP_REFERER'],'/'),1);

switch ($from){
case 'myform.php':
$table = 'registration';
$location = 'thankyou.php';
break;

}

$dbinsert = new dbinsert;
$rs = $dbinsert->gotcha($_POST, $table);

header("location:".$location);

Recap...

You're going to find that life is going to be much easier when you are processing your forms and entering data into a database now. All you will have to do is:

  • Name your form object the same as your fields in your database
  • Set the action of your form to "submit_forms.php"
  • Add a case to the switch statement that names your table and give a location to send the users after their data has been entered into the database

And that's it!!!

This is a beginning to what you will need for a full production version, but it gets you going in the right direction. You will want to add datatype checking to the class so you can validate the data from the forms and check that against the datatype of the fields..,

But that's a different tutorial...


Report Bad Submission
Use this form to tell us if this entry should be deleted (i.e contains no code, is a virus, etc.).
This submission should be removed because:

Your Vote

What do you think of this article (in the Intermediate category)?
(The article with your highest vote will win this month's coding contest!)
Excellent  Good  Average  Below Average  Poor (See voting log ...)
 

Other User Comments

2/15/2005 3:21:12 AMThangky

Any full workable project sample?
(If this comment was disrespectful, please report it.)

 
2/15/2005 9:21:30 AMhogana

[snip]
Any full workable project sample?
[/snip]

Yes I have several projects that I use this in. I will be posting a followup(s) to this tutorial that will add additional functionality like updates and validation.

I had no idea that it would recieve this many hits.
(If this comment was disrespectful, please report it.)

 
2/22/2005 3:05:33 AM

I like your idea of processing it into a class, but, i can do the same processing in about 4 lines...

I can see the idea behind it, but it jus seems very long winded. Wouldnt it be easier simple to do...

foreach($_POST as $var => $val){
$fields.="'".$var."',";
$values.="'".$val."',";
}
then strip the last comma off and then do the same thing.

As i said i like the idea, i just think there would be quicker ways. I just dont get why you would take it out of $_POST to put it into another array. maybe i missed something, but it seems you have taken a long winded way to doing something.
(If this comment was disrespectful, please report it.)

 
2/22/2005 8:16:28 AM

Good morning,

I love the class! It works like a dream, the only thing is I can't get it to recognize multiple selects. It just puts the word array in there. Is there any way to do this using this class?
(If this comment was disrespectful, please report it.)

 
2/22/2005 8:55:49 AMhogana

[snip]
I like your idea of processing it into a class, but, i can do the same processing in about 4 lines... [/snip]
I can understand your way of thinking but, this is only the first part of the class. The other methods in the class will do things like datatype validation and update(s). So while I can see your point you have to take into account the ability to expand the functionality without overloading.., at least at this point.
(If this comment was disrespectful, please report it.)

 
2/22/2005 8:57:55 AMhogana

[snip]I can't get it to recognize multiple selects[/snip]
Yes you can get it to process Select queries. I was going to cover that in another tutorial.
(If this comment was disrespectful, please report it.)

 
2/25/2005 7:54:27 AM

Okay here is the answer if you need to do mutiple selects exchange:$this->val[$this->i] = "\"$value\"";

for $this->val[$this->i] = "'" . (is_array($value) ? join(',',$value) : $value) . "'";

Works like a dream.

Laura
(If this comment was disrespectful, please report it.)

 
3/2/2005 3:00:38 AMerame

I am a newbie in php. Could anyone help with articles and tutorials or links to php tutorials. Thanks
(If this comment was disrespectful, please report it.)

 
10/4/2005 8:42:38 AMHendrik

Insert into DB
(If this comment was disrespectful, please report it.)

 
2/27/2006 10:42:17 AMDavid

I'm new to databases and php, so I know this will sound silly but I'll need to include the at the beginning and end of each of these codes, correct?

Also, I 'll have to add a database connection include file on each code also, right? Would this have it's own tags or just insert it into the other code's tags?
(If this comment was disrespectful, please report it.)

 
2/27/2006 11:09:19 AMDavid

I keep getting this error message:

Fatal error: Call to undefined function: mssql_query() in class_one.php

I put a database connection include on all the codes.

(If this comment was disrespectful, please report it.)

 
2/27/2006 1:37:52 PMhogana

Yes you'll need to include the tags and you'll also need to have a database connection.

The error you're getting is the database type. I use a MS SQL database and you are probably using a MySQL database. Change it from mssql_query() to mysql_query() and it'll work.
(If this comment was disrespectful, please report it.)

 
6/2/2007 1:09:10 AMum

well.. good code but its much more complex than it has to be..
(If this comment was disrespectful, please report it.)

 

Add Your Feedback
Your feedback will be posted below and an email sent to the author. Please remember that the author was kind enough to share this with you, so any criticisms must be stated politely, or they will be deleted. (For feedback not related to this particular article, please click here instead.)
 

To post feedback, first please login.