Paris Graphics

Home > Web Development Course > PHP and MySQL > You Are Here

PHP and MySQL

Intro

So far we've looked at databases using MySQL and seen that through using SQL we can create various tables to hold data, insert data, alter it and so on. We've also had a quick look at PHP and seen how it can be used for interactivity in web applications: getting input from a user and doing something with it. Now we are going to put them both together and see how this powerful combination can be used to create complete database driven web sites.

So that my online database doesn't fill up with too much nonsense (and spam) we will make some unusual decisions limiting the possible user input. In a real situation there are other solutions. For example, forms with user input should probably use a CAPTCHA and parts of your application intended for administrative use might be in password protected areas of the web site.

Set up

We will assume that you have a MySQL database set up on a server. At minimum you need to know this info so that PHP can "talk" to your database.

For the purpose of this tutorial, we will use a live database named myStore with a clients table that looks like this:

id fname lname gender phone state food
1 Jose Smith M 4158725941 CA Curry
2 Rayhana Jiloosh F 2124569987 NY Salad
3 Oliver Ramon M 9175543369 NY Stir Fry
4 Patty Green F 6195886412 CA Curry
5 Giselle Fortuna F 7189638541 NY Salad

 

Using PHP to Connect to the Database

Before we can do anything else, we need to have PHP connect to the database server. The following PHP function takes care of this for us:

mysql_connect(db_server, username, password);

Let's use this to connect to our sample database server (fakeserver.com) , then select our database (myStore) for use and then close the connection:

<?php

$dbhost = 'fakeserver.com';

$dbname = 'myStore';

$dbuser = 'webapp';

$dbpassword = 'Data2PlayWith';

 

$connection = mysql_connect($dbhost, $dbuser, $dbpassword) or die ('Cannot connect to mysql server $dbhost');

mysql_select_db($dbname) or die ('Cannot connect to the database: $dbname');

// SQL statements that interact with the database would go here

mysql_close($connection);

?>

Notice that we have supplied error messages to let us know if we cannot connect to the database server or to our database.

While there technically nothing wrong with this, it is more convenient and safer (i.e. security) to store your sensitive info somewhere else and NOT in the PHP scripts that people will use.

Generally I create a connection file and place it in a separate directory with some crazy name which is also password protected!

Let's see how this would work.

 

We could create a directory called "topsecret5tu44" off the root of our web directory. We might password protect that directory so it cannot be browsed. In that directory, we could create a file called dbconnect.inc like this:

//This is our db connection info

<?php

$dbhost = 'fakeserver.com';

$dbname = 'myStore';

$dbuser = 'webapp';

$dbpassword = 'Data2PlayWith';

 

$connection = mysql_connect($dbhost, $dbuser, $dbpassword) or die ('Cannot connect to mysql server $dbhost');

mysql_select_db($dbname) or die ('Cannot connect to the database: $dbname');

?>

and we might have another file called dbcloseConnect.inc like this:

<?php

mysql_close($connection);

?>

NOW we never need to put this info into the php files that the user will access. Instead we use the PHP include directive. Let's say we have a web page called searchOurProducts.php, we can open and close the connection like this:

<?php

//make the connection like this:

include 'topsecret5tu44/dbconnect.inc';

//SQL statements go here

include 'topsecret5tu44/dbcloseConnect.inc';

?>

Using PHP to Select Records

Now let's take a look at using PHP to connect and then select records from the client table of our database. There's several new things going on here, so take a look first and then see the discussion below the code:

<?php

//make the connection like this:

include 'topsecret5tu44/dbconnect.inc';

// good idea to hold query in a variable you can copy and paste into a
// command line or other mysql tool and run it to make sure results
// are what you want

$query="SELECT * FROM clients";

$result=mysql_query($query) or die('cannot run query $query');

while ($row = mysql_fetch_array($result)) {
echo "<p>";
echo $row['id']. " : " .$row['fname']. " : " .$row['lname']. " : " .$row['gender']. " : " .$row['phone'] . " : " .$row['state']. " : " .$row['food'];
echo "</p>";
}

include 'topsecret5tu44/dbcloseConnect.inc';

?>

You can view the script in action here.

 

Step by step here's what's going on:

That's it! By developing on the above in combination with other SQL statement, you can make any selections, send them to the screen, send them via email, save them to a file, etc.

 

Using PHP to Insert Records

Another common situation is to take the values of a form and insert them into the database.

Here's an example:

<?php

include 'topsecret5tu44/dbconnect.inc';

$lastpage = $_SERVER['HTTP_REFERER'];
$fname=htmlspecialchars($_POST["fname"]);
$lname=htmlspecialchars($_POST["lname"]);
$gender=$_POST["gender"];
$phone=$_POST["phone"];
$state =$_POST["state"];
$food=$_POST["food"];

if (!isset($_POST['Submit'])) {
?>

<form action="<?php echo htmlentities($PHP_SELF);?>" method="post">
<div>
<span><label for="firstname">First Name:</label></span>
<span><input type="text" name="fname" title="Text input: First Name" id="fname" size="20" /></span>
</div>
<div>
<span><label for="lastname">Last Name:</label></span>
<span><input type="text" name="lname" title="Text input: Last Name" id="lname" size="20" /></span>
</div>
<div>
<span><label for="food">Gender:</label></span>
<span><input type="radio" name="gender" value="F" /> Female
<input type="radio" name="gender" value="M" /> Male
</span>
</div>
<div>
<span><label for="phone">Phone Number (no spaces no dashes, e.g. 9145551234:</label></span>
<span><input type="text" name="phone" title="Text input: Phone Number" id="phone" size="10" /></span>
</div>
<div>
<span><label for="state">State:</label></span>
<span><select name="state" title="Text input: State" id="state">
<option value="CA">CA</option>
<option value="NY">NY</option>
</select>
</span>
</div>
<div>
<span><label for="food">Food:</label></span>
<span><select name="food" title="Text input: Food" id="food">
<option value="Curry">Curry</option>
<option value="Salad">Salad</option>
<option value="Stir Fry">Stir Fry</option>
</select>
</span>
</div>
<div>
<span><input type="reset" name="Reset" value="Reset" />
<input type="submit" name="Submit" value="Submit" />
</span>
</div>
</form>

<?php
} else {

if ($lastpage <> "http://www.parisgraphics.com/wdc/php-mysql-FormInput.php") {
echo "<h2>This page can only be accessed with the correct form!</p>";
exit;
}

if ($fname=="") {
echo "<p>The First Name was not entered. Please resubmit the form with the First Name.</p>";
exit;
}
if ($lname=="") {
echo "<p>The Last Name was not entered. Please resubmit the form with the Last Name.</p>";
exit;
}
if ($gender=="") {
echo "<p>The Gender was not entered. Please resubmit the form with the Gender Choice.</p>";
exit;
}

if ($phone=="") {
echo "<p>The Phone number was not entered. Please resubmit the form with the Phone Number.</p>";
exit;
}

if ($state=="") {
echo "<p>The State was not entered. Please resubmit the form with the State.</p>";
exit;
}

if ($food=="") {
echo "<p>The Food was not entered. Please resubmit the form with the Food.</p>";
exit;
}

if (($phone=="") || (!is_numeric($phone))){
echo "<p>ERROR: There is a problem with the entry for the Phone Number. <br />It was either not entered OR it is not a number. Please resubmit the form with a number.</p>";
exit;
}

$query="INSERT INTO clients(fname,lname,gender,phone,state,food) VALUES('$fname','$lname','$gender','$phone','$state','$food')";

//mysql_query($query) or die ('Error cannot run $query');

if (mysql_query($query)) {
echo "<h2>Data entered!</h2>";
} else {
echo "<h2>ERROR: Cannot run $query</h2>";
}

include 'topsecret5tu44/dbcloseConnect.inc';

 

echo "<p>Hello $fname!</p>";
echo "<p>Your last name is: $lname</p>";
echo "<p>Your food choice is: $food</p>";

}

?>

You can see it in action here.

 

After entering a record or two, you can see the updated database here.

 

Where to now?

We have only looked at two examples - selecting and inserting - but this should give you a basis for doing the majority of your PHP/MySQL tasks. The rest will be using various SQL commands for your $query (e.g. updating records, more complex search criteria and so on) and more PHP to format the results, process more advanced forms, etc.

A few points to remember: