Entering data into MySql with PHP

For some reason, there is very little direct instruction on the Web about how to enter data into a MySQL database from a Web page, using PHP. This dearth of info is surprising insofar as this procedure is one of the most commonly executed actions on the Web.

There are plenty of online tutorials, but for some reason, they tend to be made more complicated than they need to be.

Anyway, forthwith, here is the very basic mechanism used for entering data into a database over the Web.

First gather the ingredients: This tutorial uses Linux for the Web server (Ubuntu 8.04), with Apache running the PHP module, with the MySQL package. Also, MySQL (5.1 in this example) is already set up and running, with all the permissions worked out.

In this example, we will use a new database to be run inside MySQL, called "test." It will have a single table called "sample" with one column of data text data. What it is called is not relevant. You will also require a username and password for the database.

For this action to happen, we will need TWO web pages, an HTML landing page where the user enters the data, and a PHP page that the HTML page will call that will do the actual work of entering the data into the database. (These can be combined into one page with the "form action = self" html declaration, which I'll figure out another day).

First, you create an HTML page, where the user can enter data.

In the code on this page, the user has a box in which some data can be entered, which the page tags as the variable "somedata" when the submit button is pushed. Also, when the submit button is pushed, this page will call up a PHP page in the same directory, called "InsertingIntoDatabase.php" (which we will create in a moment).

<FORM ACTION="InsertingIntoDatabase.php" METHOD="get">
<p>
Enter Data: <INPUT TYPE="text" NAME="somedata" />
</p>
<INPUT TYPE=SUBMIT VALUE="GO" />
</FORM>

Note: For the full working code, click here, and save page, and replace the .txt extension in the file name with a .html extension.

For this setup, I used the HTML "get" method. I could have also used "post." The differences between the two are explained here.

Now onto the PHP page. Basics on creating a PHP page are here. The PHP code block will have a number of discrete steps:

1. Connect to the database server:

$open = @mysql_connect("localhost","[USERNAME]", "[PASSWORD]");
  if (!$open) {
    echo( "Can't connect to database!" );
    exit();
  }

In this chunk, you are opening the database.

The @mysql_connect PHP command is the command that does the actual work of opening the database. It requires three things: Type of connection, user name and password. Since the Web page is on the same server as the database, the connection will be "localhost". You need to enter a username and password for an account that has write privileges to the database you will be using.

You save this command and its arguments as a variable, called $open, in this case. $open is run when the Web page is called. If the connection does not work, (signified by "!$open") either because the database is not found, or your username or password doesn't work, then you will get an error message ("Can't connect to database!").

2. Gather the data and specify database to be used.

$somedata = $_GET["somedata"];
$db = ("test");

In this step, you grab the data from the server that was saved by the HTML page under the variable "somedata". In PHP, it will also be called somedata, as $somedata. You also specify the name of the database within MySQL that you will open. Here it is called "test" but you set the name when you created the database.

3. Prepare the SQL query:

$sql = "INSERT into sample VALUE('".$somedata."') ";

Here is the the actual query you will submit to the database. It is formatted exactly like any other MySQL query. (Note: in SQL speak, even inserting data is called a "query," evidently).

For this query, we are sending the contents of $somedata to a one-column table called "sample." (Obviously you need to replace "sample" with the name of your own table, the one that you created when you created your database within Mysql). Note all the query is in quotes, except for the PHP variable $somedata.

In PHP, variables can not be called from within a quoted stream of text. So we are concatenating a string together by breaking the query into two strings, one before the variable (which is placed where it is needed in the statement) and the other after. Concatenating elements together in PHP is done with the period, "."

Also, MySQL itself (at least the 5.1 version) requires that text data must be entered with SINGLE, not double quotes, hence the single quotes above.

This query is saved in php as a variable, called $sql. It looks complicated, but what PHP is storing is a straight-forward MySQL data insert query.

4. Run the query Here is the code for doing this..

$doit = mysql_db_query($db, $sql);

The database is already open, so all we need to do is enter the query. The PHP "mysql_db_query" command runs the actual query. It requires two bits of information: First element is the name of the database (captured as a variable called "$db" here). The second element is the SQL-formatted query ("$sql").

The entire command is captured as the variable "$doit" which, again, runs when the page is called.

5. Report results on the page:

If the query for some reason does not take, we need to alert the user. Likewise, if it works, then we should also report that. This mightbe best handled by the PHP "If ... Else" statement:

if (!$doit) {
         echo("It didn't work because of this: " .mysql_error()); 
		}
else
         echo ("Data entered!");

Here, if the data was not inserted due to some error ("!$doit") then the PHP returns a statement saying so ("It didn't work because of this: ") along with the error message returned by MySQL itself ("mysql_error()") concatenated onto the end.

If the transaction did work ("else"),a message of success is returned.

6. Close database

mysql_close($open);

And that is how you enter data into a MySQL database using PHP. There are lots more you can do it terms of security, handling of errors, input-checking and so on, but this is the bare-bones approach.

Note: For the full working code for this PHP page, click here, and save page, but replace the .txt extension in the file name with a .php extension. Also replace the [USERNAME] [PASSWORD] and [DATABASENAME] with your own variables.