Inserting data

How to store data in a database

You know how to get data from page to page, using a form or a weblink. The database is up and running. Now it is time to get some data in your database! The table my_first_table in our example database is of course for the storage of weblinks. So, let us assume you have a form on your website that will allow people to submit links to your database, and you place them on your website. How would you go about inserting these links into your database? Let’s get started, shall we? First, you need the form to submit the links. In this form, make sure the following fields are present:

URL
Name
Description

These wil result in three entries in the submitted data array, $_POST. In the processing page we need to extract the values of the variables from the $_POST array:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
<? 
 $error="";
 if(isset($_POST['URL'])) 
   {
   $nw_url = $_POST['URL'];
   }
  else 
   {
   $error .= "The url seems to be missing!";
   }
 
 if(isset($_POST['Name']))
   {
   $nw_name = $_POST['Name'];
   }
 else 
   {
   $error .= "The name seems to be missing!";
   }
 if(isset($_POST['Description']))
   {
   $nw_description= $_POST['Description'];
   }
 else 
   {
   $error .= "The description seems to be missing!";
   }
 ?>
<? 
 $error="";
 if(isset($_POST['URL'])) 
   {
   $nw_url = $_POST['URL'];
   }
  else 
   {
   $error .= "The url seems to be missing!";
   }
 
 if(isset($_POST['Name']))
   {
   $nw_name = $_POST['Name'];
   }
 else 
   {
   $error .= "The name seems to be missing!";
   }
 if(isset($_POST['Description']))
   {
   $nw_description= $_POST['Description'];
   }
 else 
   {
   $error .= "The description seems to be missing!";
   }
 ?>

As you mayhave noticed, we have filled a variable $error in case something was missing. You can now use that to test, and report an error back, if some fields were left empty, Of course, this is followed by the alternative, there are no missing entries, and the rest or the script is run:

1
2
3
4
5
6
7
8
9
10
<? 
 if($error <> "")
   {
   echo "Some errors occurred: ".$error;
   }
 else 
   {  
   // All the stuff for the processing goes here. 
   }
 ?>
<? 
 if($error <> "")
   {
   echo "Some errors occurred: ".$error;
   }
 else 
   {  
   // All the stuff for the processing goes here. 
   }
 ?>

OK, now we have the variables. You may want to do some other checking of user input, but we will deal with that at a later stage. The next step is to connect to he database. I have these commands sitting in a seperate file, since they require passwords and usernames, which you might want to store outside the web-accessable section of your webserver. But for trying and getting started, please just include these commands directly in your processing file:

1
2
3
4
5
6
7
8
9
10
11
12
<? 
 $db_host="localhost";
 $db_user="****";  // Set this to the user which is allowed to acces the database
 $db_password="****";  // Set this to the user which is allowed to acces the database
 $db_name="example";
 $table_body="";
 
 mysql_connect($db_host, $db_user, $db_password)
   or die ("<strong>Couldn't connect to server</strong>");
 
mysql_select_db($db_name);
?>
<? 
 $db_host="localhost";
 $db_user="****";  // Set this to the user which is allowed to acces the database
 $db_password="****";  // Set this to the user which is allowed to acces the database
 $db_name="example";
 $table_body="";
 
 mysql_connect($db_host, $db_user, $db_password)
   or die ("<strong>Couldn't connect to server</strong>");

mysql_select_db($db_name);
?>

So, we are ready to access the database, and start inserting data. Let us look at the syntax for inserting:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
<? 
 $insertquery = "insert into my_first_table (M_name, M_url, M_description) 
                 values ('$nw_name', '$nw_url', '$nw_description' )";
 
 echo $insertquery;
 
 $result = mysql_query($insertquery)  
             or die("An error occured in the script: ".mysql_error());
 
 if($result)
   {
   echo "The data was inserted";
   }
 ?>
<? 
 $insertquery = "insert into my_first_table (M_name, M_url, M_description) 
                 values ('$nw_name', '$nw_url', '$nw_description' )";
 
 echo $insertquery;
 
 $result = mysql_query($insertquery)  
             or die("An error occured in the script: ".mysql_error());
 
 if($result)
   {
   echo "The data was inserted";
   }
 ?>

So what did I just do there? Well, first of all.. A variable insertquery was defined, which holds the query string needed to insert the data. The actual querystring first tells what to do (insert) where this needs to be done (into my_first_table), which colums you will be inserting data to (M_name, M_url, M_description) and which values to be inserted ($nw_name, $nw_url, $nw_description). The quotes around the variables to be inserted are needed for text-based variables. For umbers you can leave them out.

The next line is just there for debugging purposes. It will spit out the querystring to screen. You can then see what command is actually given to the database, and in case of errors check the commands. The third line does the actual magic: It tells the PhP compiler to go to MySQL, and perform a query (mysql_query), and the query to perform is the one we defined in $insertquery. If there is a serious problem, the script will stop (die() ) and give an error message (An error occured in the script) and will give the error that caused the script to stop (mysql_error()). A succesfull query returns the boolean TRUE, so you can test whether a query was succesfull, and based on this continue the script.

This is the basic routine for inserting data into your database. Go ahead and try it.

Leave a Reply