Data driven websites in PHP using MySQL

---

This article was last updated on 2009-12-16 20:07:53

This tutorial is intended as an introduction to working with MySQL with PHP, a very popular open-source combination of technologies for creating dynamic websites.

By Joe Barber

This article assumes that you have PHP and MySQL installed correctly on your server to begin with; if not, you may first want to read our tutorial on 'The basics of setting up a LAMP environment'.

Connecting to a database

In MySQL, set up a user for your database - in the example below, our database is called 'Customers', our user is called 'web' with a password of 'password' (you should choose a much more secure password than this).

Once the user is set up, create the following file called db.cfg.php in which these parameters will be stored to allow your PHP scripts to connect to your database.

define("DB_HOST","localhost"); //localhost can be replaced with the IP address of the database server
define("DB_USER","web");
define("DB_PASS","password");
define("DB_DATABASE","Customers"); //change to the name of the database you want to connect to

Fig. 1 db.cfg.php

For security, save this file in a different directory to your main codebase; in this example we will save the file under /home/DBConnect/db.cfg.php. The next step is to write a PHP script that tries to connect to the Customers database through using this file:

define("DB_DIR","/home/DBConnect");
require(DB_DIR . "/db.cfg.php");

if($conn = @mysql_connect(DB_HOST,DB_USER,DB_PASS)) {
if($database = @mysql_select_db(DB_DATABASE)) { $success = TRUE; }
else { $success = FALSE; }
}

if(!$success) {
echo "Db connection error. The Database returned: ";
echo mysql_errno();
echo mysql_error();
exit();
}
else {
echo "Connection established to the database!";
}

Fig. 2 Connecting to the database

Calling the database using SQL

If your page shows the message "Connection established to the database!", then your data-driven site is up and running, and it's time to make some calls to the database!

$sql = "SELECT CustomerId, CustomerName FROM Customers";
$query = @mysql_query($sql);

if(!$query) {
echo $sql . "\n\n";
echo mysql_error() . "\n\n";
}

$result = array();

while($sql = mysql_fetch_array($query,MYSQL_ASSOC)) {
$result[] = $sql;
}

if ($result) {
foreach ($result as $value) {
$customer_id = $value['CustomerId'];
$customer_name = $value['CustomerName'];
echo $customer_id . ": " . $customer_name;
}
}

Fig. 3 A simple SELECT query returning all customer IDs and customer names from the Customers table in the Customers database

$name = "John Smith";
$email = "jsmith@gmail.com"

$sql = "INSERT INTO Customers (CustomerName, CustomerEmail) VALUES ('$name', '$email')";
$query = @mysql_query($sql);

if(!$query) {
echo $sql . "\n\n";
echo mysql_error() . "\n\n";
}

//make a note of the customer ID that has just been inserted for future use if necessary
$customer_id = @mysql_insert_id();

Fig. 4 A simple INSERT query adding a new customer 'John Smith' to the Customers table in the Customers database