Inserting MySQL Data

Once we have successfully connected to the database, we will need to create some tables on it. MySQL tables are a way of arranging the data, much like HTML tables!

$table = "guestbook";	// database table

// create table on database
$create = "create table $table (
id int(11) NOT NULL auto_increment,
name char(30) NOT NULL,
email char(80) NOT NULL,
comment char(250) NOT NULL,
primary key (id)
);";

mysql_query($create)
or die ("Could not create tables because ".mysql_error());

That's right, more variables! $table is the name of the database table we'll be creating and using. You can put this up the top of the script, or in an include, with your other variables for ease of use.

$create is the command for creating the table. In the table we have four fields: id, name, email and comment.

We set the id field as an integer, and the primary key, which is the way we identify and select table rows. auto_increment means the integer value increases by one for each entry, so the first entry will be 1, the second 2, etc.

name is a field with a limit of 30 characters, email a field with a limit of 80 characters, and comment a field with a limit of 250 characters. They are all set to be NOT NULL, which means they cannot be empty.

After defining the $create variable, we then use it in a mysql_query to create the tables on the database. If something goes wrong, we will get an error message telling us so. If not, we will then need to insert some data:

$name = "myname";
$email = "me@mydomain";
$comment = "testing the database";

// insert data into database
$insert = mysql_query("insert into $table values ('NULL', '$name', '$email', '$comment')", $link)
or die("Could not insert data because ".mysql_error());

Here we define the data, then use the mysql_query function to insert it into the table. The data must be entered in the correct order; we created the table in the order of id, name, email and comment, so we enter the values in the same order.

Notice that for the first field, id, we are entering NULL, because as id is auto_increment, it will automatically be assigned an integer value.

In this case, the $link part isn't really necessary; if you have more than one MySQL connection open, it will point the query to the right one, however we only have one connection anyway.

When writing a real guestbook, you would have a HTMLform with name, email and comment fields, and would post it to this script, which would then insert those values into the table.

If this is successful, next you'll need to know how to retrieve data from the database.