MySQL PHP Tutorial

4.1. Getting info on databases and tables.

Fire up the MySQL client by typing the following:

[todsah@sharky]~$ mysql -u todsah
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 464 to server version: 3.22.32-log

Type 'help' for help.

mysql> _

Here we are at the MySQL command-prompt. Please note that if your MySQL account has a password, you should append a -p option to the 'mysql -u todsah' line.

First, we'll check out the server to see if some databases already exist:

mysql> show databases;
+------------+
| Database   |
+------------+
| forum      |
| intranet   |
| mysql      |
| news       |
+------------+
4 rows in set (0.26 sec)

Okay, so we've got 4 databases here. We can select one with the following command:

mysql> use forum;
Database changed

We are now working on the 'forum' database. Please note that you should NEVER FORGET THE SEMI-COLON ( ; ) If you've ever used any real programming language, you'll be used to the semi-colon thingy.

Right! Now let's see what tables this 'forum' database contains:

mysql> show tables;
+-----------------+
| Tables in forum |
+-----------------+
| posts           |
+-----------------+
1 row in set (0.01 sec)

a table 'posts'? Let's refresh my memory and look how it's build up:

mysql> describe posts;
+-------------+-----------------------+------+-----+-----------+----------------+
| Field       | Type                  | Null | Key | Default   | Extra          |
+-------------+-----------------------+------+-----+-----------+----------------+
| id          | mediumint(8) unsigned |      | PRI | 0         | auto_increment |
| parent      | mediumint(8) unsigned | YES  |     | 0         |                |
| authorname  | varchar(40)           | YES  |     | anonymous |                |
| authoremail | varchar(100)          | YES  |     | NULL      |                |
| date        | datetime              | YES  |     | NULL      |                |
| subject     | varchar(255)          | YES  |     | NULL      |                |
| text        | text                  | YES  |     | NULL      |                |
+-------------+-----------------------+------+-----+-----------+----------------+
7 rows in set (0.00 sec)

Ah yes! That's right. Let me explain to you.

4.2. Creating a database and user.

Let's pretend for a moment that the above database does not yet exist. We'll create it using the following steps:

• Create a new file, using your favorite editor ( I use joe ), name it something like forum.mysql. This file will contain a couple of commands, just as we would enter them in the MySQL console.

  Here's the contents of the file:

    CREATE DATABASE forum; 
    GRANT SELECT,INSERT,DELETE,UPDATE ON forum.* TO forum@localhost IDENTIFIED BY 'secretpw'; 
    USE forum; 
  
    CREATE TABLE posts
       (	
      id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, 
         parent MEDIUMINT,
         authorname VARCHAR(40) DEFAULT 'anonymous',
         authoremail VARCHAR(100),
         date DATETIME,
         subject varchar(255),
         text TEXT
    ); 
  				

  

  Here's where we tell MySQL to create a new database. If the database already exists, you'll get an error. In that case, you can delete the old database by typing:

  mysql> DROP DATABASE forum;

  Be careful with that. It deletes all data.

  

  This line creates a new user, which can access all tables in the forum database. It only has the rights to manipulate data within the tables themselves. This is what the rights mean:

  SELECT

  Retrieve data from tables.

  INSERT

  Insert new rows of data into tables.

  DELETE

  Deleting of rows from the tables.

  UPDATE

  Changing existing data in the tables.

  The forum@localhost part identifies the username, and from which hosts that user may use the database. In this case, the user forum may only connect to the database from the same machine on which the server runs.

  secretpw' is his password. Hence, he should connect like this:

  [todsah@sharky]~$ mysql -u forum -h localhost -p
  Enter password: 
  Welcome....

  Please note, that in some cases, the user root does not yet have the rights necessary to GRANT other users. Why this sometimes is the case, is a mystery. To fix this, you can let the root user first give itself the right to GRANT permissions on other users. I know, it sounds strange, but it worked for me (and believe me, I spent quite some time figuring THAT one out) Here's how to do that:

  GRANT all privileges ON *.* TO root@localhost identified by 'secret' WITH GRANT OPTION;

  

  Tell MySQL we want to work on the 'forum' database.

  

  Okay MySQL client. We are now gonna create a new table in the 'forum' database called 'posts'.

  

  the column specifications end here. After this, you can repeat 5 through 6 in order to create extra tables

• Now we must tell MySQL that it should run the commands we just entered into this file. The reason we are using a file, is because if you accidently made a typo in one of the commands, and you happened to be using the command-line client, you can start all over again. This inconvenience will not happen if we use a file. Alright. Lets tell mysql to parse the file. It's done like this:

  [todsah@sharky]~$ mysql -u root -p < forum.mysql
  Enter password:
  [todsah@sharky]~$ _

  No output.. that means no errors. That's a Good Thing. It doesn't neccasery have to done by the 'root' user. The user just needs to be able to add databases and grant rights to users. Lets check out if it worked:

  [todsah@sharky]~$ mysql -u forum -h localhost -p 
  Enter password:
  Welcome...
  mysql> describe forum.posts;

  You should now see your created table.

  There you have it. We've created a database, a table and a user to match. Ain't it grand?

4.3. Miscellaneous MySQL queries.

This section explains some basic MySQL queries. First I'll summarize what we've already learned:

SELECT [columns] FROM [table] WHERE [specification];

mysql> SELECT * FROM posts WHERE id=1;

SELECT id,authorname,authoremail FROM posts;

SELECT [columns] FROM [table] WHERE [specification] ORDER BY [field [ASC / DESC]] LIMIT [from, rows];

ORDER BY [field [ASC / DESC]]

LIMIT [from, rows]

SELECT * FROM posts ORDER BY date LIMIT 0,15;
SELECT * FROM posts ORDER BY date LIMIT 15,15;

SELECT * FROM posts ORDER BY date DESC LIMIT 0.15;

SELECT * FROM posts WHERE subject LIKE '%forum%';

SELECT * FROM posts WHERE subject like 'forum%';
SELECT * FROM posts WHERE subject like '%forum';

SELECT * FROM posts WHERE (subject like '%forum%') AND (authorname='toDSaH');

INSERT INTO [table] [col1,col2] VALUES ( [val1, val2] );

INSERT INTO [table] SET col1=val1, col2=val2;

INSERT INTO posts VALUES ('',0,'toDSaH','todsah@arise.nl', 2000-10-12 22:38:20,'Test','Hello, this is a test.');

INSERT INTO posts SET authoremail='todsah@arise.nl', date=2000-10-12 22:38:20, text='Hello, this is a test.';

UPDATE [table] SET col1=val1, col2=val2 WHERE [specification];

UPDATE forum SET authorname='anonymous' WHERE authorname='toDSaH';

DELETE FROM posts WHERE id=1;

DELETE FROM posts WHERE id < 10;

5.1. Retrieving data from the database.

Here's a small example on how to retrieve information from a database:

<?
	dl ("mysql.so"); 

	$db = mysql_connect ('localhost', 'forum', 'secretpw'; 
	mysql_select_db('forum', $db); 

	$result = mysql_query("SELECT * FROM posts ORDER BY date",$db); 
	if (!$result) { echo "SELECT query failed"; } 

	while ($row = mysql_fetch_row($result)) { 
		echo "ID:           " . $row[0] . "<br>\n"; 
   		echo "Replies to:   " . $row[1] . "<br>\n";
   		echo "Author:       " . $row[2] . "<br>\n";
   		echo "Author email: " . $row[3] . "<br>\n";
   		echo "Date:         " . $row[4] . "<br>\n";
   		echo "Subject:      " . $row[5] . "<br>\n";
   		echo "Text:         " . $row[6] . "<br>\n";
	}
?>

Well, that's easy, ain't it? Remember, the line numbers are there just for the convenience of it. They're not actually part of the code. Let's dissect it:

Okay, so it works. Great. But what if we change the order of the fields in the database? For instance, we DROP the entire table, and create a new one, in which we want to store the author's homepage? The new table would look like this:

+-------------+-----------------------+------+-----+-----------+----------------+
| id          | mediumint(8) unsigned |      | PRI | 0         | auto_increment |
| parent      | int(10) unsigned      | YES  |     | 0         |                |
| authorname  | varchar(40)           | YES  |     | anonymous |                |
| authoremail | varchar(100)          | YES  |     | NULL      |                |
| authorurl   | varchar(100)          | YES  |     | NULL      |                |
| date        | datetime              | YES  |     | NULL      |                |
etc...

If we look at the PHP example, we'll notice that $row[4] used to be the date, but now it's the 'authorurl' field. Instead, 'date' is now $row[5]. Time for a complete code rewrite? Yes! But this time, we're going to do it another way:

while ($row = mysql_fetch_array($result)) { 
	echo "ID:              " . $row["id"]          . "<br>\n";
	echo "Replies to:      " . $row["parent"]      . "<br>\n";
	echo "Author:          " . $row["authorname"]  . "<br>\n";
	echo "Author email:    " . $row["authoremail"] . "<br>\n";
	echo "Author homepage: " . $row["authorurl"]   . "<br>\n";
	echo "Date:            " . $row["date"]        . "<br>\n";
	echo "Subject:         " . $row["subject"]     . "<br>\n";
	echo "Text:            " . $row["text"]        . "<br>\n";
}

Notice the differences? Instead of fetching the results using mysql_fetch_ROW, we are now using mysql_fetch_ARRAY. The difference between these functions is, that we can now use names to identify the fields. So instead of saying: $row[2] we now say $row["authorname"]. The mysql_fetch_array() function is somewhat slower than mysql_fetch_row(), but it will give you less problems in the long run. If you're absolutely certain that the table structure will _never_ change, you may just as well use the mysql_fetch_row() function. Now, if we only want to list all author's and subjects of all posts, we don't have to retrieve all the other information, that would be a waste of CPU cycles. Here's how we do it:

?>
	dl ("mysql.so");

	$db = mysql_connect ('localhost', 'forum', 'secretpw';
	mysql_select_db('forum', $db);

	$result = mysql_query("SELECT author,subject FROM posts ORDER BY date",$db);
	if (!$result) { echo "SELECT query failed"; }

	while ($row = mysql_fetch_array($result)) {
		echo "Author:       " . $row["author"]  . "<br>\n";
		echo "Subject:      " . $row["subject"] . "<br>\n";
	}
<?

The difference is in the SELECT query. In the first examples I used 'SELECT * FROM' and now I'm using 'SELECT author,subject FROM'.

5.2. Inserting data into the database.

Ok, here's some more small examples:

<?
	dl ("mysql.so");

	$db = mysql_connect ('localhost', 'forum', 'secretpw';
	mysql_select_db('forum', $db);

	$result = mysql_query("INSERT INTO posts SET parent=1, 
	authorname='toDSaH', authoremail='todsah@arise.nl',
	subject='Wassup dog?',text='This is the text field'",$db);
	
	if (!$result) { echo "INSERT query failed"; }
?>

Here we insert some new data into the table. Not all the fields are specified, so they will get their default values from MySQL. Of course the mysql_query line shouldn't be spread over multiple lines in the real code. There's also another way to insert data into the database:

<?
	dl ("mysql.so");
	
	$db = mysql_connect ('localhost', 'forum', 'secretpw';
	mysql_select_db('forum', $db);

	$result = mysql_query("INSERT INTO posts VALUES ('','0','Anonymous',
	'anonymous@nohost.com',now(),'My post','This is my post'",$db);
	
	if (!result) { echo "INSERT query failed"; }
?>

In this example, we don't specify any columns, so we have to specify ALL values for each column. Since MySQL doesn't know which column we are refering to, this query type is quite error sensitive. I suggest you only use the first INSERT example.

5.3. Deleting data from the database.

To delete data from the database we use the following:

$result = mysql_query("DELETE FROM posts WHERE author='anonymous'",$db);

I've left out the rest of the program, because you should be able to create that part yourself by now. This query deletes all anonymous posts.

Another example:

$result = mysql_query("DELETE FROM posts WHERE id < 11",$db);

This will delete all the posts who's ID is smaller than 11.

Okay, now let's say you want to delete all posts which contain nasty words:

$result = mysql_query("DELETE FROM posts WHERE text LIKE '%fuck%'",$db);
		

This query will delete all posts who's text contain the word 'fuck' (LIKE '%fuck%') The percent-sign ( % ) is a wildcard which matches one or more characters. If you leave one of the percent-signs out, you could only search for text at the beginning or end of the 'text' field contents.

5.4. Changing data in the database.

If you want to change data in the database you can do it like this:

$result = mysql_query("UPDATE posts SET author='Anonymous coward' WHERE
author='anonymous'",$db);

This will change all author fields which are 'anonymous' into 'Anonymous coward'. You may specify multiple SET's by comma-delimiting them.

5.5. Storing dates and times in MySQL tables.

MySQL has it's own internal column format for storing date and time stamps. But I've found that they are hard to use in PHP, since their format is fixed. Although I might be wrong, and the internal MySQL DATETIME column is sufficient, I've found another solution, which works just fine for me.

I use a INT column type for date and time storage. In this I save a number, which represents the date and time. This is called a timestamp. You can get the timestamp for a date and time in PHP by using the mktime() function. Here's the description of the function from the PHP manual:

int mktime (int hour, int minute, int second, int month, int day, int year [, int is_dst])

So mktime (22,48,0,11,27,2000) will return the timestamp for 27 December 2000, 22:48 : 975361680

You can reverse a timestamp with the date() function. It's description:

string date (string format [, int timestamp])

date ("d F Y, G:i", 975361680); will return: 27 November 2000, 22:48

The date() function's format parameter can contain a large number of possible way's to describe a date/time. This is a great advantage. If you only want to show the year of a timestamp, you could just use date("Y",975361680), which will only return "2000".

The other advantage is, that the MySQL database can be easily sorted by date using timestamps. In fact, MySQL internally also uses timestamps for it's DATETIME column type. Yet another advantage is, that it's easy to substract two timestamps from each other, and show how many days apart they are, for instance. If you want your dates to be displayed in your own language, use the following:

<?
	setlocale(LC_TIME,"dutch");
	print strftime("%A, %e %B %Y (%R)");
?>

Will print: zondag, 15 januari 2001 (11:51). Please refer to PHP's Date and Time functions section for more date and time related functions. See the setlocale function in the manual to learn more about using different languages in your php script.

7.1. Document changes

This document has undergone the following changes: