Simple MySQL tutorial

MySQL is a free to use database program that follows the SQL language guidelines so what you will learn here applies to all databases that will accept a SQL connection which is nearly all of them. A database is a collection of tables, each table is rather like a spreadsheet grid, the columns being called 'fields' and the rows being called entries.

The fields have types applied which specifies what sort of information they may contain (text,numerical etc) and this information is used by the database when sorting, storing retrieving and manipulating the data stored. To create a database you have to specify a database name, a user name and a password.

Sometimes this information is put in by yourself via your sites control panel, or sometimes you have to email your web hosting company to do it for you. You then need to know the host name for the mysql installation - usually but not always it is 'localhost'- its worth asking.

Once a database exists it will be blank, you need to decide what to put in it- and we are ready to rock and roll!

Connecting to a MySQL Database from PHP

In php you must do this at the top of each page that uses MySQL use:

$dbhandle = mysql_connect(hostname, username, password) or die("Cannot connect to DB");

Then you must specify what database (it may be a shared installation with thousands on) you wish to work on:

mysql_select_db(databasename) or die("Connected but error accessing DB");

Now PHP has opened an internal connection on your server to the database software.

Creating a MySQL Table

You will only need to do this step once at the start of your project, or when you want to add a new table. This lays down a 'skeleton' for the data structure you will use. As an example we will have a simple picture gallery manager. This will be a simple list of pictures. Each will have an ID number, the title, the artist or photographers name,the filename where the picture is stored (as a URL so pictures may be taken from off site),the x dimension, the Y dimension, a comments field and finally the number of times the picture is viewed.

We create the table like this:

$sql_command = "CREATE TABLE `pictures` (
`id` int unsigned NOT NULL auto_increment,
`title` varchar(50) NOT NULL default 'Unknown Title',
`artist` varchar(50) NOT NULL default 'Unknown Artist',
`url` varchar(50) NOT NULL default 'http://www.mygallery.com/pics/unknown.jpg',
`width` int unsigned NOT NULL default '100',
`height` int unsigned NOT NULL default '100',
`comments` longtext, `views` int unsigned NOT NULL default '1',
PRIMARY KEY (`id`),
KEY `search_title` (`title`),
KEY `search_name` (`artist`), ) TYPE=MyISAM;";
mysql_query($sql_command);
if(mysql_error())echo "Error creating pictures table -".mysql_error();

The first line is the instruction to create the table which we are calling pictures. Then we have a line for each field in the table. This specifies the field name, the type of field, the keywords NOT NULL means that the database checks when an entry is created that it is not empty and then the default value is entered if it is. Its a very good idea to use not null and default values wherever possible.

The first field (id) is specified as auto_increment - this means that each time a new entry is created in the table this id number will increase by one automatically. The Comments field is specified as longtext which means you could have the entire contents of war and peace in there, or nothing. The database will handle both extremes. The other text fields are specified as varchar(50) - a max 50 chars long string. You will have to police the length in your PHP program.

Now we have Key definitions. The primary key is the default way that you will usually look up each picture - and it will only ever return a single line because it must be unique for each line. We have set it to 'id'. So that we can easily list and search by title and artist we have added two other ordinary keys as well called search_title and search_artist. Then we have a TYPE statement and you should never need anything other than MyISAM unless you have a big book about MySQL!

We send the command to the database using mysql_query and check for and report any errors. Thats it! You created a table. Note that its important to use capitals for the commands - the old joke is that SQL is deaf and you have to SHOUT!

Adding an Entry to a MySQL table

To add an entry first you get the data from form fields using $_POST['fieldname'] or calculate them from your program, retrieve them from the net or whatever. Next you check the lengths of any strings and filter for any harmful characters (especially if the input comes from a public form!). This will filter out bad characters:

For all integer values: $value=intval(0+$value);
For all floating numbers: $value=(intval(10000*(0+$value)))/10000;rounds off to 5dp
For all strings: Sstring=mysql_real_escape_string($string);

Having done this you would use:

$sql = "INSERT INTO pictures (title,artist,url,width,height,comments,views)
VALUES('$title','$artist','$picurl',`$picwidth`,'$picheight',$comments,'0'}";
mysql_query($sql);
if(mysql_error())echo "Error adding row $title -".mysql_error();

Assuming the PHP variable $title etc were what you stored the data in.

Retrieving an Entry from a MySQL table

If we want just one entry and we know what the id of the picture is (we stored it in $picid) then:

$sql = "SELECT * FROM pictures WHERE id='$picid'";
$result = mysql_query($sql) or die(mysql_error());
$row = mysql_fetch_array($result)

We can then acess the returned data from the $row array using field names
$title=$rows['title'] and so on

Retrieving multiple Entries from a MySQL table

We can retrieve the whole table by leaving out the where clause:

$sql = "SELECT * FROM pictures";
$result = mysql_query($sql) or die(mysql_error());
$html='All pictures in our gallery-";
while($row = mysql_fetch_array($result)){
$html.="$rows['title'] $rows['artist']";
}

and show all our pictures... or if we wanted to show them sorted by artist:

$sql = "SELECT * FROM pictures ORDER BY artist";

The rest of the code is the same as above. Order by can be order by any field so if we wanted to show them sorted by popularity:

$sql = "SELECT * FROM pictures ORDER BY views";

instead!

Deleting an entry or entries from a MySQL table

$sql="DELETE FROM example WHERE id='15'"; would remove one row whose id was 15
$result = mysql_query($sql) or die(mysql_error());

or

sql="DELETE FROM example WHERE artist='Dudley Dogood'"; removes all pictures by Dudley Dogood $result = mysql_query($sql) or die(mysql_error());

Modifying an entry in a MySQL Table

Each time we display a picture in our example we would have to update the number of views. Since we just displayed it we would know the picid and number of views existing so:

$views++;
$sql=("UPDATE pictures SET views='$views' WHERE id='$picid'");
$result = mysql_query($sql) or die(mysql_error());

Thats all you need to know to get a simple application up and running! Have fun... A link back would be appreciated if you found this useful. Malcolm at Webmaster Alpha