A Quick PHP/MySQL Help Page

    Please keep in mind that "<?" indicates the start of the PHP code and "?>" indicates the end of the PHP code in your HTML document.  In writing this we're assuming the reader has some prior programming knowledge.  Prior experience with PHP is recommended.  ;-)

Connecting to the server.

Before you can use your database in your pages, you must connect to the SQL server.   To connect to the SQL server use:

<? mysql_connect("SQL Server", "SQL Username", "SQL Password"); ?>

Suppose you're on the main Kattare.com web server 'mysql.kattare.com', your username is bob, and your password is cat.  Then to connect to the SQL server, you would use:

<? mysql_connect("mysql.kattare.com", "bob", "cat"); ?>

Creating a table.

If you haven't already created a table, you need to do so.  Use the code:

<? mysql("DatabaseName" , "CREATE TABLE TableName (TableParameters)"); ?>

Where DatabaseName is the name of the database your system administrator has assigned, TableName is the name you wish to give your table, and TableParameters are the parameters for the data columns in your table.

<? mysql("bobDB" , "CREATE TABLE info (numdisplays INT, numhits INT, url CHAR(100), picurl CHAR(100))"); ?>

The above code would create a table named "info" in the "bobDB" database with four data columns.   Two named "numdisplays" and "numhits" to hold intergers, and two named "url" and "picurl" to hold up to 100 characters of a string.

Some Additional MySQL data types:

TINYINT[(length)] [UNSIGNED] [ZEROFILL]
SMALLINT[(length)] [UNSIGNED] [ZEROFILL]
MEDIUMINT[(length)] [UNSIGNED] [ZEROFILL]
INT[(length)] [UNSIGNED] [ZEROFILL]
INTEGER[(length)] [UNSIGNED] [ZEROFILL]
BIGINT[(length)] [UNSIGNED] [ZEROFILL]
REAL[(length,decimals)] [UNSIGNED] [ZEROFILL]
DOUBLE[(length,decimals)] [UNSIGNED] [ZEROFILL]
FLOAT[(length,decimals)] [UNSIGNED] [ZEROFILL]
DECIMAL[(length,decimals)] [UNSIGNED] [ZEROFILL]
NUMERIC[(length,decimals)] [UNSIGNED] [ZEROFILL]
CHAR(length) [BINARY],
VARCHAR(length) [BINARY],
DATE
TIME
TIMESTAMP
DATETIME
TINYBLOB
BLOB
MEDIUMBLOB
LONGBLOB
TINYTEXT
TEXT
MEDIUMTEXT
ENUM(value1,value2,value3...)
SET(value1,value2,value3...)

Deleting a table.

Suppose you want to delete (drop) a table, use:

<? mysql("DatabaseName" , "DROP TABLE TableName"); ?>

Inserting information.

Now that you have a table in place, you probably want to put some information into it.   The Syntax is:

<? mysql("DatabaseName" , "insert into TableName values (TableParameters)"); ?>

For the table we created above it would  work like this:

<?
$numdisplays = 4;
$numhits = 2;
$url = "http://www.kattare.com";
$picurl = "http://www.kattare.com/images/logo.jpg";

mysql("bobDB" , "insert into info values ('$numdisplays', '$numhits', '$url', '$picurl')");
?>

In that example the variables are assigned, but in the real world you would have a form or some kind of input to set the information you wanted to put into the database.

Retrieving information.

Ok, so you've put some information into your database, now you want to retrieve it.  To list everything you'd do something like this:

<?
$result = mysql("bobDB" , "select * from info");     /* puts everything from info into variable $result. */
$num = mysql_numrows($result);                    /* gets the number of entries and puts it into $num. */

$i = 0;
while ($i < $num) {                                             /* loops once for each database entry. */

   $numdisplays = mysql_result($result,$i,"numdisplays"); /* assigns the info from $result into the four variables. */
    $numhits = mysql_result($result,$i,"numhits");
    $url = mysql_result($result,$i,"url");
    $picurl = mysql_result($result,$i,"picurl");

    echo "<CENTER> $numdisplays $numhits $url $picurl </CENTER> \n";  /* prints the information! */
    $i++;
}
?>

The above program uses several new commands.  The folowing command selects all the information from a selected table and puts it into an variable array called result.

$result = mysql("DatabaseName" , "select * from TableName");

The below command assigns the information from a selected cell into the variable numhits by using a row number and a column name:

$numhits = mysql_result($result, RowNumber,"ColumnName");

Deleting information.

To delete a row of information from a table you have to somehow select which row you wish to delete.  You would use the following command:

<? mysql("DatabaseName" , "delete from TableName where ColumnName like '$keyword'"); ?>

Here $keyword is a variable containing a sample of what you want to delete.

<?
$keyword = "4";
mysql("bobDB" , "delete from info where numhits like '$keyword'");
?>

The above example would delete every entry where the numhits column contained the value "4."

Additional reading.

I reccomend you take a look at the following sites:

The PHP Online Manual
The PHP Homepage
The MySQL Manual
The MySQL Homepage

Copyright 1999 Kattare's Virtual Web Service.  All Rights Reserved.
Written by Ethan Burnside.