Here we have done all the major work. We created a new variable called $sql to house our SQL string. SQL is the language used to construct queries to a mySQL server. The SQL is pretty easy to understand by just looking at it but let's take a look at more in depth to avoid confusion.

CREATE TABLE is the command to, you guessed it, create a new table. We gave the table a name of table0 and created three fields for it, ID, Name, and Age.

ID serves as an index for each record, just like a page number does for a notebook. In the line that reads :

INT means the field will house integers, NOT NULL means the field always requires some sort of value and can not be empty, AUTO_INCREMENT means that the the value for each record will always increment automatically with each record inserted, starting with 1, PRIMARY KEY means that this field is the table's primary key. It will be sorted in accordance to it.

The same idea can be applied to the other two remaining fields, Name and Age, except that they have less properties.

After defining the query we simply issue it to the mySQL database by way of the function mysql_query. This function accepts one argument, and that is the SQL query to issue. We already created the query in a variable called $sql that is why we passed it as an argument.

Finally, we closed our connection to mySQL by way of the function mysql_close.



Inserting Data
Now that we have an actual table in our database, let's learn how to insert data into it. Inserting data into a mySQL database via PHP is a very easy task. Let's see some code first than explain how:

<?php // Open Connection To mySQL. // Replace 'username' and 'password' with appropiate login values for your server. $mysqlLink = mysql_connect( 'localhost' , 'username' , 'password' ); // Select mySQL Database. mysql_select_db( 'examples' , $mysqlLink ); // Create Query To Create Table. $sql = 'INSERT INTO `table0` (`ID`, `Name`, `Age`) VALUES (NULL, \'Mike\', \'19\');'; // Issue Query. mysql_query( $sql ); // Close mySQL Connection. mysql_close( $mysqlLink ); ?>

The code is basically the same as before, the only important difference is the query we defined in the variable $sql. Let's break it down further to explain the query in detail :

$sql = 'INSERT INTO `table0` (`ID`, `Name`, `Age`) VALUES (NULL, \'Mike\', \'19\');';

The SQL command to insert data into a table. Between the ` marks is the table name we wish to insert data into.

$sql = 'INSERT INTO `table0` (`ID`, `Name`, `Age`) VALUES (NULL, \'Mike\', \'19\');';

The affected fields we wish to insert data into. Remember that a table is made up of fields and each field will require some sort of value.

$sql = 'INSERT INTO `table0` (`ID`, `Name`, `Age`) VALUES (NULL, \'Mike\', \'19\');';

The values for each field. They must be in the same order as the fields. So in the example, NULL will be inserted into ID, Mike will be inserted into Name, and 19 will be inserted into Age.   You might be wondering why there are backslashes in the values. Each value to insert must be between single quotation marks but because this is a PHP string started with single quotation marks, any other one's must be escaped so PHP does not produce a parse error.

Here is an interesting note. We are adding a NULL value to the field ID even though in our definition when we created the table, we said we always want this field to expect some sort of value. This is the beauty of the AUTO_INCREMENT option for this field. We do not need to provide a value for this field as mySQL will always do it for us automatically.



Editing Data
Let's learn how to edit existing data in a table. Again let's see the code first :

<?php // Open Connection To mySQL. // Replace 'username' and 'password' with appropiate login values for your server. $mysqlLink = mysql_connect( 'localhost' , 'username' , 'password' ); // Select mySQL Database. mysql_select_db( 'examples' , $mysqlLink ); // Create Query To Create Table. $sql = 'UPDATE `table0` SET `Name` = \'Sam\', `Age` = \'21\' WHERE `ID` = 1;'; // Issue Query. mysql_query( $sql ); // Close mySQL Connection. mysql_close( $mysqlLink ); ?>

Again the same basic structure, except for the SQL query. Let's further examine it :

$sql = 'UPDATE `table0` SET `Name` = \'Sam\', `Age` = \'21\' WHERE `ID` = 1;';

This is the SQL command to edit an existing value on a specific table in our database.

$sql = 'UPDATE `table0` SET `Name` = \'Sam\', `Age` = \'21\' WHERE `ID` = 1;';

We use the keyword SET to define all fields we want to edit along with their new values. Multiple fields are separated by a comma.  Also take notice that we put the new values in single quotation marks, taking care to escape them to avoid any PHP parse errors.

$sql = 'UPDATE `table0` SET `Name` = \'Sam\', `Age` = \'21\' WHERE `ID` = 1;';

This is perhaps the most important part of the query. We have to tell mySQL which record in our database we want to update. In this case we told it to update the record where the field ID has a value of 1.

Remember before we said that ID is our unique identifier for each record in our table. No two records will ever have the same value for ID. What happens if we change our query to be like this:

$sql = 'UPDATE `table0` SET `Name` = \'Sam\', `Age` = \'21\' WHERE `Name` = \'Alex\';';

Name is just an ordinary field. We can have multiple users with the name Alex so this query will update all records that have a Name of Alex, which is not what we want in this case.

- Tutorial written by Limitless

Pages (3): <Prev 1 [2] 3 Next>

Automatic Translations: