MySQL II : PHP

Using MySQL With PHP

If necessary, get the PHP API connector at http://dev.mysql.com/downloads/.

 

PHP Basics

Creating Scripts

Open and close scripts like this:

<?php
$var = “Hello world!”;
print $var;
?>

 

Variables

PHP variables (like many Perl and Bash variables) begin with $ .

Use a single = sign to assign a value:

$interpreted_variables = “variables that get interpreted”;
$uninterpreted_variables = “variables that DON’T get interpreted”;

$var1 = “strings and $interpreted_variables”;
$var2 = ‘strings and $uninterpreted_variables’;

Use a double == sign to read a value:

if ($var1==”strings and variables that get interpreted”) {
//do something;
}
else
{
//do something else;
}

Use a print statement to print out the variable’s value:

print $var1;
//This gets you: strings and variables that get interpreted
print $var2;
//This gets you: strings and $uninterpreted_variables

 

Constants

Constants look like this:

DEFINE(DB_USER, “username”);
DEFINE(DB_PASSWORD, “password”);
DEFINE(DB_HOST, “hostname”);
//this could be “localhost”
DEFINE(DB_NAME, “database_name”);
//this could be “albums”

 

Concatenation

Concatenate string or variable values with “dot” syntax: “string1”.”string2″ or value1.value2

 

The addslashes() Function

Place any string that might contain characters that need to be escaped (with the escape character, \) into an addslashes function. This is especially true of a string that will be returned when a variable is interpreted:

$safe_string = addslashes($HTTP_POST_VARS[‘text_box_name’]);

 

Connecting To MySQL

Use the mysql_connect() function:

$db_connection=mysql_connect(host, user, password)

 

When you’re done, be sure to close the connection:

mysql_close();

 

Selecting A Database

Use the mysql_select_db() function:

mysql_select_db(‘albums’);

or:

mysql_select_db(DB_NAME);

or:

use an include file as described below.

 

Executing A Query

Use the mysql_query() function:

$query = “SELECT * FROM albums”;
$query_result=mysql_query($query);

BE CLEAR! DO NOT USE A SEMICOLON ( ; ) INSIDE THE QUERY IN PHP!

Be sure to clear this variable when you’re done:

mysql_free_result($query_result);

 

Iterating Through Query Results

Use the mysql_fetch_array(array, parameter) function:

$query = “SELECT * FROM albums”;
$query_result=mysql_query($query);
while($row=mysql_fetch_array($query_result) {
//Do things with $row
}

One great use for this is creating values that will be placed in a pick list (a drop-down menu):

echo ‘<form action=”album_info.php” method=”get”>’;
echo ‘Choose an album:
<select name=”album_list”>’;

$query = “SELECT album_id, album_name FROM albums”;
$query_result=mysql_query($query);
while($row=mysql_fetch_array($query_result, MYSQL_NUM) {
echo “<option value=\”$row[0]\”>$row[1]</option>”;
}

echo ‘</select>
<input type=”submit” name=”submit” value=”Show This Album”>
</form>’;

The mysql_fetch_array() function does an interesting thing: by default, it returns every row twice, once with an associative key, and once with a numeric (index) key. You can specify which you want by using the optional second parameter: MYSQL_NUM, MYSQL_ASSOC, or MYSQL_BOTH (which really just does the same thing as using no parameter):

Using MYSQL_NUM

Notice that in the example above, the two values returned in each row are accessed by column index, starting with 0. The first column (album_id) is $row[0], and the second column (album_name) is $row[1]. This results from adding the MYSQL_NUM parameter to the mysql_fetch_array() function

Using MYSQL_ASSOC

When you specify MYSQL_ASSOC, the two values returned in each row are accessed by the index column value. If the first column value (album_id) is “Beatles 1”, and the second column (album_name) is “Meet the Beatles,” you would refer to this row as $row[‘Beatles 1’].

Using MYSQL_BOTH

This takes you right back to returning every row twice. Perhaps this is useful in some situations(?).

 

Inserting A Value From A Form

Get the value from the $HTTP_POST_VARS() array:

$query=”INSERT INTO albums VALUES (NULL, ‘{HTTP_POST_VARS[‘album_title’]'”;

This requires a text box, of course, named “album_titles.”

Note that you can use the “superglobal arrays” $_POST and $_GET, which function the same way but are global in scope. (See this page for a discussion of scope.)

Then execute the query:

$query_result=mysql_query($query);

If you actually get a successful query result, $query_result() will return a positive value. So you can use it in a conditional:

if ($query_result) {
echo “It worked.”;
}
else
{
echo “It didn’t work.”;
}

You could combine these last two code blocks:

if (mysql_query($query)) {
echo “It worked.”;
}
else
{
echo “It didn’t work.”;
}

 

Creating A Page And Form

Use standard HTML syntax:

<html>
<head></head>
<body>

<?php
//php code here
?>

<form action=”albums.php” method=”post”>
<!– Notice that the comment characters have changed. –>
<!– Also: be clear about when you use post and when you use get!–>
Name: <input type=”text” name=”name” maxlength=10>
Password: <input type=”password” name=”password” maxlength=8>
<input type=”submit” name=”submit” value=”Log In”>
</form>

<?php
//concluding code here
?>

 

Check That The Form Has Been Submitted With The Submit Button

Use a conditional like this:

if (isset($HTTP_POST_VARS[‘submit’])) {
//Do things
}
else
{
//Do other things
}

This will check the post variables array for a value named “submit.” In order for this variable to be set, your submit button must be named “submit.” You can change the button’s name, but you must also change this value if you do.

 

Check That Required Fields Have Been Filled

Use a conditional here as well:

if (strlen($HTTP_POST_VARS[‘user_name’]) > 0) {
//Do things: note the strlen() function, which returns
//the string’s length as an integer
}
else
{
echo “Hey, you forget to enter your user name.”;
}

 

Capturing The Last-Inserted Auto-Increment ID: mysql_insert_id()

PHP has a function similar to the LAST_INSERT_ID() function in MySQL, which is the mysql_insert_id() function:

$last_id=mysql_insert_id();

You’re likely to need this when you’re using the primary key from one table as a foreign key in another, for instance an albums table with three columns, album_id, album_title, and album_genre:

$query=’INSERT INTO genre VALUES (NULL, “60s Rock”)’;
$last_id=mysql_insert_id();
$query_result=mysql_query($query);
$query2=’INSERT INTO albums VALUES (NULL, “Meet the Beatles”,’.$last_id.’)’;

 

Error Handling

Output the error number if something failed:

$db_connection=mysql_connect(host_name, user_name, password) or die(mysql_errno());
//die will stop the script at this point if the connection fails, and the error number will print

Output the error description if something failed:

$db_connection=mysql_connect(host_name, user_name, password) or die(mysql_error());
//die will stop the script at this point if the connection fails, and an error message will print

You can also suppress error messages:

$query_result=@mysql_query($query);

Because there is no “die” statement, execution will continue, even if an error occurs.

 

Using An Included File For Security

Get the mysql script to create the database: board.sql. Run this script in the mysql monitor.

Get this PHP script: index.php. You must copy it to your web root (/var/www/html). Is your web server running? Is your MySQL database running?

Now, consider this code:

<html><head><title>My PHP Page</title></head>
<body>

<form action=”index.php” method=”post”>
Name: <input type=”text” name=”myname” maxlength=20>
Comment: <input type=”text” name=”message” maxlength=255>
<input type=”submit” name=”submit” value=”Submit Comments”>
</form>

<?php
// Define login info.
$host = ‘localhost’; // DATABASE SERVER;
$database = ‘board’; // DATABASE NAME;
$user = ‘root’; // USER TO CONNECT TO DATABASE;
$password = ‘private’; // USER PASSWORD;

// Establish a link to MySQL.
$db_link = mysql_connect(“$host”,”$user”,”$password”);
if ($db_link==FALSE) {
print “Database connection failed”;
exit;
}

// Select the database
mysql_select_db(“$database”);

// Set up a query, and perform an insert
$query=”INSERT INTO board.guestbook VALUES (‘{$_POST[‘myname’]}’,'{$_POST[‘message’]}’,NOW())”;
$result=mysql_query($query);

// Now perform a select
$query=”SELECT * FROM guestbook”;
$result=mysql_query($query);

// Loop through the result set.
$num = mysql_num_rows($result);
$cur = 1;

while ($row = mysql_fetch_array($result)) {
print $row[‘myname’].’ – ‘.$row[‘message’].’ – ‘.$row[‘date’].'<br>’;
}

// Free up resources
mysql_free_result($result);
mysql_close();
?>
</body></html>

This is a good starting point, but it has one major weakness: if the page fails to load properly for some reason, the viewer will likely be able to view this source code and get your user name, password, hostname and database name.

You must never allow this to happen.

Place your database connection information outside the root of your web site, preferably one or more folders up from public_html. Remember: PHP runs in the context of your local server, not in the web server’s context (limited to the web root and below).

You can place an included connection script in a completely separate file, typically using the .inc file extension, to indicate an include file. Name this file something like for instance, db_connection.inc:

<?php
$server = ‘rhonda’; // DATABASE SERVER;
$database = ‘med_records’; // DATABASE NAME;
$user = ‘joe_user’; // USER TO CONNECT TO DATABASE;
$password = ‘PrEc1s10n’; // USER PASSWORD;
?>

Now you can use this connection file as an include from each web page html file:

include(‘../db_connection.inc);

or:

require_once(‘../db_connection.inc);

 

Security

1. Always conceal your database connection information. See Includes, above.

2. Retrieve submitted values using the $HTTP_POST_VARS() and $HTTP_GET_VARS() arrays, or the “superglobal arrays” $_POST and $_GET, which function the same way but are global in scope. (See this page for a discussion of scope.) See Inserting Values, above.

These arrays specifically decode hexadecimal encoded characters for you, saving you the trouble of dealing with un-encoding with the urldecode() and similar functions (see this discussion for some examples of how allowing encoded characters can make you vulnerable to SQL injection, among other things).

3. You should also look at the preg_replace() function, and particularly its use in preventing cross-site scripting here, by removing http:// references to other web sites.

4. To correctly (safely) handle special characters, use one of these functions:

addslashes()

or:

mysql_escape_string() //as of PHP 4.0.3

$query=”INSERT INTO albums VALUES (NULL, ‘ “. mysql_escape_string($HTTP_POST_VARS[‘album_name’]) ” ‘ “;

or see if your web server PHP configuration has Magic Quotes enabled (Magic Quotes is disabled by default).

Resources

Elizabeth Fulghum’s PHP Series:

Learning PHP – http://www.developer.com/lang/php/article.php/3288671

Building Database-Driven Applications with PHP and MySQL – http://www.developer.com/lang/php/article.php/2203971