• Home
• Programming
• Computer Science
• Twisting Puzzles
• Arduino
• BBC micro:bit
• Raspberry Pi Pico

Introduction To PHP

• Programming Section Home
• Introduction To PHP Home

First Steps

• Escaping From HTML
• A GET Request
• More GETting
• Just Can't GET Enough
• GET With A Form
• Wait A Minute, Mr POSTman
• Using Includes
• More Includes
• Simple Date Selection
• More Date Selection
• Using Radio Buttons
• Using Checkboxes
• Functions In PHP
• User Authentication
• Image Output - Squares
• Image Output - Squares 2
• Image Output - Pie
• Form Validation
• Security Issues

Database Operations

• Connecting To MySQL
• Inserting Records
• Deleting Records
• Updating Records

Connecting To MySQL

We can connect to MySQL databases in our PHP scripts. We use SQL to describe the database operations that we want to carry out. With SQL, we can carry out all standard database operations. We can,

• Create and delete databases
• Create and delete tables
• Use SELECT queries to view data from one or more tables
• Use INSERT queries to add records to tables
• Use UPDATE queries to change the data stored in records
• Use DELETE queries to remove records from a table

This page describes a couple of strategies for using simple SELECT queries with a table. You will need to create the database and table first. Your WAMP installation probably came with an installation of PHPMyAdmin, a web-based tool for database management. Load that up and start by creating a database called, seeds.

Data Dictionary

Notice that the data types are expressed differently in MySQL. A manual can be downloaded from the MySQL home page which will explain data types further. The purpose of including this here is to show how MySQL allows SQL statements for data definitionas well as data manipulation.

The SQL to create this is as follows,

Create your table by typing or copying the SQL into the query window in PHPMyAdmin

Finally, you need the data for the table. This comes in SQL form (a large INSERT query). Import this into PHPMyAdmin so that you have some data to work with.

Click on the import link in PHPMyAdmin and choose to import the following file (which includes SQL statements to insert rows in the table).

Seeds Data SQL

Connecting To The Database

The basic code for a database connection is as follows,

The first variable, $h, represents the location of the database (the host). We are using the value, localhost because the database is on the same server that the will interpret the PHP script. Although it is possible to connect to databases on other servers, you are more likely to be using a database on the same server, even when you publish your pages online.

The second variable, $u, is the username for the database. For the test environment in XAMPP, the only preset username and password is the username root and an empty password. That is what is used in this script. Clearly, you would never use a root password in a script you published online, but it is fine for testing and learning.

Bear in mind that the database password is encoded in the file. You would probably use an include to do this and, preferably, store it outside of the WWW directory of the server (ie not in htdocs). You would also make certain that any files containing passwords cannot be downloaded and viewed plain text.

Extracting & Viewing Data

The following script produces an HTML table of the contents of the tblseeds table in the database, seeds. <?php
$h = "localhost";
$u = "root";
$p = "";
$connection = mysql_connect($h,$u,$p);
if (!$connection)
{
   die("Could not connect to the database");
}
mysql_select_db("seeds");
$query = "SELECT * FROM tblseeds";
$result = mysql_query($query);
echo "<table>
<tr>
<th>Name</th><th>Type</th><th>Seed</th><th>Price</th>
</tr>";
while ($row = mysql_fetch_array($result))
{
   extract($row);
   echo "<tr><td>".$name."</td><td>".$type."</td><td>".$seed."</td><td>".$price."</td> </tr>";
}
echo "</tr>\n</table>";
mysql_close($connection);
?>

This PHP does not create a complete HTML page, just the table of data. Make sure you put it into some HTML or PHP that does the rest of the job.

Look carefully at the PHP. The while loop iterates through each row of results. The extract statement is used to convert each row of the results into the separate values stored in each field. Notice that the variable names match the field names.

A Little More

The previous script showed only a small amount of the information stored in the table. We can make a slightly more interesting page than that though. This time, we'll start by having the script display a list of the seed names. When the user clicks on one of the seed names, they will be able to view all of the details of that seed. Like the last script, you need to create a page to put this code into.

Mucking About With This Example

You can have a play with this example to do a little more interesting work. Some of the fields have similar values for groups of seeds. A SELECT DISTINCT query could be used to give you a list of unique seed types. Clicking on one of the seed types should then lead to a list of all seeds of that type, selecting a seed then leads you to the details for that particular seed.

And Security

Notice that the some of the scripts on this page take information directly from the URL's query string and put it into an SQL statement. Clearly, you can directly change the URL and get a different display on the page. On the minor irritation side of things, you can make the script fail by typing in an unknown seed name. But think carefully. By doing this, the user is effectively being granted some form of access to the database. The implications of this need to be considered and the risks analysed when writing web applications that connect to databases.