PHP and MySQL Library Tutorial

General
Site Root
Database Home
Conect to MySQL
MySQL_Login.php
nav.php
dump.php

Clean Up resets the
database to contain
a few sensible
records.

Download Tutorial

Database
Create
Drop

Person Table
Create
Drop

Insert
Delete
Update
Search
Date-Search

Row Dump
Better View 
Table View 
Form View

Book Table
Create
Drop

Insert
Delete
Update
Search




Table View 
Form View

Loan Table
Create
Drop

Insert
Delete

Search




Table View

Documentation
Data Requirements
User Interface Design
Entity Relationship Diagrams
Data Flow Diagrams
Data Dictionary
Implementation Notes
Table Designs
Goals, Testing and Appraisal

Note - Create, Drop and Clean Up would be hidden on a real life system.
Contact - nbauers at samphire dot demon dot co dot uk
Copyright © - You may use and reproduce this material in a "not for profit" setting.

PHP and MySQL - Notes

PHP TAGS

<?php

?>

PHP scripts begin and end with these tags. The PHP preprocessor executes all the statements enclosed between these tags. Anything not enclosed is treated as normal HTML or text.

INCLUDING FILES

require "filename";

Include the named file as if it were part of the file it is being included into. The script fails if the file can not be found.

include "filename";

Include the named file as if it were part of the file it is being included into. Gives a warning message if the file can not be found but the script continues.

COMMENTS

<!-- html comment -->

This is an HTML comment. It will be ignored by the web browser.

// PHP Comment

This is a PHP comment. It will be ignored by the PHP preprocessor. The comment ends at the end of the line.

/* PHP Comment */

This is a PHP comment. It will be ignored by the PHP preprocessor. The comment ends at the closing */ marker. This style of comment can span several lines.

FILE HANDLING

$fp = fopen($fileName, "r");

Open the file named by $fileName for reading ("r"). $fp is a file pointer that is used to refer to the file until it is closed later.

fclose($fp);

Close the file referred to by $fp.

while (!feof($fp))
{

}

! means NOT. feof tests for the end of file. $fp is a pointer to the file being tested. The entire statement repeats until the end of file is encountered.

$in = fgets($fp, 4094);

File Get String. Read a line of text from the file that $fp refers to. Store the line of text into the $in variable.
4094 - The maximum number of characters allowed to be fetched.

TEXT OUTPUT

echo "<table cellpadding=\"10\">\n";

echo is used to send text to the standard output. For web based scripts, this is the html sent to the web browser. The text being sent is enclosed in "double" or 'single' quotes. If 'single' quotes are used, the enclosed text is simply sent to the browser. If "double" quotes are used, any embedded variables are processed and their values returned. This carries a processing overhead and should be avoided unless there are embedded variables. Ignoring this rule is common.

\n sends a new line to the output stream.
\r\n sends a Carriage Return/Line Feed combination typical of Windows text files.

\" sends a quote to the output stream. Without the \ the quote would close the text string.

&lt;
&gt;
&nbsp;
&amp;

< begins an html tag. To display a < use &lt;
> ends an html tag. To display a > use &gt;
&nbsp; provides a non breaking space.
&amp; providea an & (ampersand).

MySQL STATEMENTS - Connecting, selcting a database and closing.

$link = mysql_connect($host, $user, $password) or die(mysql_error());

mysql_select_db ($database);

mysql_close($link);

Connect to a MySQL database. Store a reference to the connection in $link. The $host variable usually contains the value "localhost". The $user variable might be "root" or another user name assigned to you or by you. In general only major administration should be carried out by the root user. The $password variable contains the password known only to you and possibly the MySQL administrator. or die(mysql_error()) echoes an error message and kills the script if something goes wrong.

mysql_select_db() is used to specify which database to use. MySQL can manage many different databases. $database is a variable which contains the name of the database. In this example the database is named "aardvark".

mysql_close($link) is used when you have finished with the connection. If you forget to do this, PHP will eventually clean up after you.

MySQL STATEMENTS - EXECUTING SQL Queries
$sql = "DROP DATABASE aardvark";

$result = mysql_query($sql, $link) or die(mysql_error());

DROP DATABASE is used to delete an entire database. aardvark is the name of the database.

mysql_query is used to run the SQL. $sql contains the SQL statement. $link contains the reference to the database connection.

or die(mysql_error()) echoes an error message and kills the script if something goes wrong.

MySQL STATEMENTS - SQL Examples

$sql = "DROP DATABASE aardvark";

Delete the entire databse.

$sql = "CREATE DATABASE aardvark"

Create the database (there will be no tables at this stage).

$sql = "CREATE TABLE person
        (
          person_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
          title VARCHAR(5),
          last_name VARCHAR(30) NOT NULL,
          first_name VARCHAR(30) NOT NULL,
          date_of_birth DATE
        )";

CREATE TABLE person - creates a table named "person". The table has five columns.
person_id - uniquely identifies the person. The values are generated automatically.
title - is used to store the title. Up to 5 characters are allowed. This may be left blank.
last_name - is used to store the last name. Up to 20 characters are allowed. This may NOT be left blank.
first_name - is used to store the first name. Up to 20 characters are allowed. This may NOT be left blank.
date_of_birth - is a date and may be left blank.
MEDIUMINT - is a whole number.
UNSIGNED - means that there are no negative values.
NOT NULL - means that the enrty is required.
AUTO_INCREMENT - means that the entries are automatically numbered. Numbers are never re-used.
PRIMARY KEY - means that this field is unique.
VARCHAR (5) - is a variable length text field. The (5) specifies the maximum length.
DATE - is a date type. Beware because illegal dates can be stored like the 31st of February. This is a Unix date stoted as yyyy-mm-dd

$sql = "INSERT INTO person
        (
          title, last_name, first_name, date_of_birth
        )
        VALUES
        (
          \"Mr\", \"Bloggs\", \"Fred\", \"1992-12-27\"
        )";

INSERT INTO person - this SQL code inserts a record into the person table.
(title, last_name ... etc) - These are the columns that will be inserted into. You don't always have to insert into every column.
VALUES - this key word begins the list of values to be inserted.
(\"Mr\", \"Fred\", ...); - these are the values to be inserted. The "double" quotes have to have a "\" or PHP will treat the quote as the end of the text. With raw SQL, you don't need the "\" symbols.
1999-11-21 - This is a Unix date. It consists of YYYY-MM-DD. This is a good system because dates can be sorted alphabetically and thay come out in the correct order. One day all dates will be like this.

$DELETE FROM
    person
WHERE
    person_id = "3"";

DELETE FROM person - This deletes from the person table.
WHERE - This is a test. If there is a match, the row will be deleted.
Beware. If you don't specify a WHERE clause, all the rows will be deleted.
$value - contains the valus to be matched if the row is to be deleted.

HTML Forms and $_POST

<form name="form1" method="post" action="">
  <input name="title" type="text" id="title">
  <input type="submit" name="Submit" value="Submit">
</form>

Here are the main elements of an HTML form. This form has a text field and a submit button. When the button is pressed, the form data is passed to another web page. If action="" the data is submitted back to the current page and the current page is re-loaded. Data can be submitted to a different web page if action is made to point to a different page - eg action="http://foo.bar.com/"

<?php echo $_POST['title']; ?>

 


print_r($_POST);

When a page is loaded, if any data was posted from a form, it is available to PHP.
$_POST['title'] - Contains the data entered into the title text field on the form above. PHP can process this input data in any way you like.
print_r is used to echo the entire contents of an array. It is very useful for debugging scripts.
$_POST is an array containing the data posted from a form to the current web page.
PHP arrays are associative. This means that the array locations are not necessarily numbered. The $_POST array contains named locations. The names are the same as the text field names on the form used to submit the data.

foreach ($_POST as $key => $value)
{
  if ($key != "Submit")
  {
    $sql = "DELETE FROM person WHERE person_id = \"$value\"";
    mysql_query($sql, $link);
  }
}

foreach - is used to repeat and visit each location in an array.
$_POST - is the array being used by foreach.
$key - On each repetition, the array key is stored into $key. For example 'title', 'last_name', etc.
$value - On each repetition, the array data indexed by $key is stored into $value.
if ($key != "Submit") - We want to use all the keys except the Submit button. != means Not Equal.
mysql_query - Run the SQL query to delete where $value contains the data to be matched.

$sql = "SELECT * FROM person";

$rows = mysql_query($sql, $link);

while ($row = mysql_fetch_array($rows, MYSQL_ASSOC)) { ... }

Here an SQL query is selecting all the columns (*) from the person table.
$rows - This is a PHP resource where the query result is stored.
while (true / false) { ... } - This will repeat the code enclosed inside the curly brackets as long as the statement inside the round brackets remains true.
$row - contains a table row or record.
mysql_fetch_array(...) - This fetches a row / record from the resource, $rows.
MYSQL_ASSOC - is a constant that tells mysql_fetch_array how to behave. An associative rather than a numbered array is returned.
If the last row has been fetched, mysql_fetch_array returns False and the while loop quits.