PHP/MySQL Tutorial - PHP MySQL Connection

One of the most basic PHP tags is the PHPinfo. Open just any text editor (like notepad) and type in the following:

<?
phpinfo();
?>

And save it as phpinfo.php

Now upload this to your public_html directory and go to it in your browser using the following link:

http://yourdomain.com/phpinfo.php

You will see a huge page with all the details of the PHP installation on it, scroll down through all this information and you will see a section where the MySQL details are fitted.

Before you can do anything with your database, you must create a table. A table is a section of the database for storing related information. In a table you will set up the different fields which will be used in that table. Because of this construction, nearly all of a site's database needs can be satisfied using just one database.

Creating a table in PHPMyAdmin is simple, just type the name, select the number of fields and click the button. You will then be taken to a setup screen where you must create the fields for the database. If you are using a PHP script to create your database, the whole creation and setup will be done in one command.

Creating a table in PHP is slightly more difficult than with MySQL. It takes the following format:

CREATE TABLE tablename {

Fields

}

The fields are defined as follows:

fieldname type(length) extra info,

The final field entered should not have a comma after it.

How to Execute Server Commands

The first thing you must do before you can do any work at all is to connect to the MySQL database. This is an extremely important step as, if you are not connected, your commands to the database will fail.

Good practice for using databases is to specify the username, password and database name first so that if you change any of them at a later date you will only have to change one line:

$username="username";
$password="password";
$database="your_database";

At this point you may be wondering if it is a security risk, keeping your password in the file. You don't need to worry, though, because the PHP source code is processed by the server before being sent to the browser so it is impossible for the user to see the script's source.

Next, you will need to issue the command to start a database connection:

mysql_connect(localhost,$username,$password);

This line tells PHP to connect to the MySQL database server at 'localhost' (localhost means the server that the site is running one. Another vital command is:

mysql_close();

This is a very important command as it closes the connection to the database server. Your script will still run if you do not include this command but too many open MySQL connections can cause problems for your account. It is good practice to always include this line once you have issued all your commands to the database, to keep the server running well.

After you have connected to the database server you must then select the database you wish to use. This must be a database to which your username has access. The following command:

@mysql_select_db($database) or die( "Unable to select database");

is used to do this. This tells PHP to select the database stored in the variable $database (which you have set earlier). If it cannot connect it will stop executing the script and output the text:

Unable to select database

This extra 'or die' part is good to leave in as it provides a little error control but it is not essential.

Now you have connected to the server and selected the database you want to work with, you can begin executing commands on the server.

How to Display MySQL Table Data?

There are two ways of executing a command. One is to just enter the command in PHP. This way is used if there will be no results from the operation.

The other way is to define the command as a variable. This will set the variable with the results of the operation.

In this part of the tutorial we will use the first way as we are not expecting a response from the database. The command will look like this:

mysql_query($query);

The useful thing about using this form of the command is that you can just repeat the same command over and over again without learning new ones. All you need to do is to change the variable.

Here is the complete code that should be used to create a MySQL table in PHP:

<?
$user="username";
$password="password";
$database="database";
mysql_connect(localhost,$user,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="CREATE TABLE tablename(id int(6) NOT NULL auto_increment,first varchar(15) NOT NULL,last varchar(15) NOT NULL,field1-name varchar(20) NOT NULL,fiels1-name varchar(20) NOT NULL,field3-name varchar(20) NOT NULL,field4-name varchar(30) NOT NULL,field5-name varchar(30) NOT NULL,PRIMARY KEY (id),UNIQUE id (id),KEY id_2 (id))";
mysql_query($query);
mysql_close();
?>

Enter your database, MySQL username and MySQL password in the appropriate positions on the first three lines above.

The next query should fill in the table. Here is a sample one:

$query = "INSERT INTO tablename VALUES
('','$field1-name','$field2-name','$field3-name','$field4-name','$field5-name')";

You can't insert more values than the number of fields you have created with the previous query.

After you have created the table and filled it with data, you will probably need to display it. This is usually done using basic HTML code. Putting the data using HTML pages is almost identical to inserting it using a PHP script. The benefit, though, is that you do not need to change the script for each piece of data you want to input and you can also allow your users to input their own data.

The following code inserted in an HTML page will display the data from the databases with textboxes in which the appropriate details are fitted:

<form action="insert.php" method="post">
Value1: <input type="text" name="field1-name"><br>
Value2: <input type="text" name="field2-name"><br>
Value3: <input type="text" name="field3-name"><br>
Value4: <input type="text" name="field4-name"><br>
Value5: <input type="text" name="field5-name"><br>
<input type="Submit">
</form>

The next thing you need is a new PHP script, which instead of filling in the database with data, will get the data and display it.

<?
$username="username";
$password="password";
$database="your_database";

$field1-name=$_POST['Value1'];
$field2-name=$_POST['Value2'];
$field3-name=$_POST['Value3'];
$field4-name=$_POST['Value4'];
$field5-name=$_POST['Value5'];

mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

$query = "INSERT INTO tablename VALUES
('','$field1-name','$field2-name','$field3-name','$field4-name','$field5-name')" ;mysql_query($query);

mysql_close();
?>

This script should then be saved as insert.php so that it can be called by the HTML form. It works because, instead of the data being entered locally, it is being entered into the form and stored in variables which are then passed to the PHP.

Now that you have at least one record, if not many more, in your database you will want to know how you can output this data using PHP.

The first command you will need to use is a MySQL query made up like this:

SELECT * FROM tablename

This is a basic MySQL command which will tell the script to select all the records in the tablename table. Because there will be output from this command it must be executed with the results being assigned to a variable:

$query="SELECT * FROM tablename";
$result=mysql_query($query);

In this case the whole contents of the database is now contained in a special array with the name $result. Before you can output this data you must change each piece into a separate variable. There are two stages to this.

The first one is counting the rows. Before you can go through the data in your result variable, you must know how many database rows there are. You could, of course, just type this into your code but it is not a very good solution as the whole script would need to be changed every time a new row was added. Instead you can use the command:

$num=mysql_numrows($result);

This will set the value of $num to be the number of rows stored in $result (the output you got from the database). This can then be used in a loop to get all the data and output it on the screen.

The second stage is to set up the loop. You must now set up a loop to take each row of the result and print out the data held there. By using $num, which you created above, you can loop through all the rows quite easily. In the code below, $i is the number of times the loop has run and is used to make sure the loop stops at the end of the results so there are no errors.

$i=0;
while ($i < $num) {

CODE

$i++;
}

This is a basic PHP loop and will execute the code the correct number of times. Each time $i will be one greater than the time before. This is useful, as $i can be used to tell the script which line of the results should be read. As the first line in MySQL output is 0, this will work correctly.

The final part of this output script is to assign each piece of data to its own variable. The following code is used to do this:

$variable=mysql_result($result,$i,"fieldname");

So to take each individual piece of data in our database we would use the following:

$field1-name=mysql_result($result,$i,"field1-name");
$field2-name=mysql_result($result,$i,"field2-name");
$field3-name=mysql_result($result,$i,"field3-name");
$field4-name=mysql_result($result,$i,"field4-name");
$field5-name=mysql_result($result,$i,"field5-name");

You do not need to get the ID field because there is no use for it in the output page.

You can now write a full script to output the data. In this script the data is not formatted when it is output:

<?
$username="username";
$password="password";
$database="your_database";

mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT * FROM tablename";
$result=mysql_query($query);

$num=mysql_numrows($result);

mysql_close();

echo "<b><center>Database Output</center></b><br><br>";

$i=0;
while ($i < $num) {

$field1-name=mysql_result($result,$i,"field1-name");
$field2-name=mysql_result($result,$i,"field2-name");
$field3-name=mysql_result($result,$i,"field3-name");
$field4-name=mysql_result($result,$i,"field4-name");
$field5-name=mysql_result($result,$i,"field5-name");

echo "<b>$field1-name
$field2-name2</b><br>$field3-name<br>$field4-name<br>$field5-name<hr><br>";

$i++;
}

?>

This outputs a list of all the Values stored in the database. This just gave you a very basic output, though and is not particularly useful for a working website. Instead, it would be better if you could format it into a table and display it like this. Doing this formatting is not particularly complicated. All you need to do is use PHP to output HTML and include your variables in the correct spaces. The easiest way to do this is by closing your PHP tag and entering the HTML normally. When you reach a variable position, include it as follows:

<? echo $variablename; ?>

in the correct position in your code.

You can also use the PHP loop to repeat the appropriate code and include it as part of a larger table. For example:

<table border="0" cellspacing="2" cellpadding="2">
<tr>
<th><font face="Arial, Helvetica, sans-serif">Value1</font></th>
<th><font face="Arial, Helvetica, sans-serif">Value2</font></th>
<th><font face="Arial, Helvetica, sans-serif">Value3</font></th>
<th><font face="Arial, Helvetica, sans-serif">Value4</font></th>
<th><font face="Arial, Helvetica, sans-serif">Value5</font></th>
</tr>

<?
$i=0;
while ($i < $num) {

$field1-name=mysql_result($result,$i,"field1-name");
$field2-name=mysql_result($result,$i,"field2-name");
$field3-name=mysql_result($result,$i,"field3-name");
$field4-name=mysql_result($result,$i,"field4-name");
$field5-name=mysql_result($result,$i,"field5-name");
?>

<tr>
<td><font face="Arial, Helvetica, sans-serif"><? echo $field1-name; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo $field2-name; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo $field3-name; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo $field4-name; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo $field5-name; ?></font></td>
</tr>

<?
$i++;
}

echo "</table>";

This code will print out table headers, and then add an extra row for each record in the database, formatting the data as it is output.

How to Select Individual Records

As well as showing the whole database, PHP can be used to select individual records, or records which match certain criteria. To do this you must use a variation of the SELECT query. To display the whole table you used the query:

SELECT * FROM tablename

If we just wanted to select ones which have value=1 in the field1-name row you would use the following query:

SELECT * FROM tablename WHERE fiels1-name='1'

As with other MySQL queries, it is almost like plain English text. In the same way you could select records based on any field in the database. You can also select ones with more than one field by adding more:

field='value'

sections onto the query.

For further reference you can visit the official websites of PHP and MySQL.