Example 3: Querying from phpMyAdmin

In the previous examples [1, 2], I showed some simple querying. In this example I will show how to use phpMyAdmin to generate the SQL queries.

This example uses a simple message board with several posts. This message board can be created using the following statement:

CREATE TABLE `example` (
  `posttime` TIMESTAMP NOT NULL ,
  `poster` VARCHAR( 25 ) NOT NULL ,
  `posts` TEXT NOT NULL ,
  PRIMARY KEY ( `posttime` )
);

I then populated the table with several posts. This can be done through the phpMyAdmin Insert page, or manually. phpMyAdmin allows the direct input of SQL satements from the SQL page. Here are two example inserts generated by the Insert page of phpMyAdmin:

INSERT INTO `example` ( `posttime` , `poster` , `posts` )
  VALUES (
  NOW( ) , 'Poster1', 'This is a message'
);

INSERT INTO `example` ( `posttime` , `poster` , `posts` )
  VALUES (
  NOW( ) , 'Poster1', 'I\'m posting again.'
);

The Browse tab can be used to display the entire table. This can also be done using the SQL statement:

SELECT * FROM `example`;

In mysql, this query gives me the result:

+----------------+----------+--------------------+
| posttime       | poster   | posts              |
+----------------+----------+--------------------+
| 20040220182919 | Poster1  | This is a message  |
| 20040220182957 | User1    | Hey!               |
| 20040220183009 | User1    | Hello!             |
| 20040220183033 | SysAdmin | Shh                |
| 20040220183051 | Poster1  | I'm posting again. |
+----------------+----------+--------------------+

I can now search for the posts by one user. For example "Poster1". In phpMyAdmin, select the Search tab. Select "posts" from the Select fields menu. From the query by example section, select "Poster", "=", and enter "Poster1". This will generate SQL:

SELECT `posts`
  FROM `example`
  WHERE `poster` = 'Poster1';

Running this command in mysql returns the following table

+--------------------+
| posts              |
+--------------------+
| This is a message  |
| I'm posting again. |
+--------------------+

You can play around with the options, or manually write SQL, to come up with more interesting queries. For example, if I want to display the two most recent posts I can issue the command:

SELECT `poster` , `posts`
  FROM `example`
  WHERE 1
  ORDER BY `posttime` DESC
  LIMIT 2 ;

The ORDER BY tells mySQL to sort based on the "posttime" field. By adding DESC to the command, I force mySQL to give the most recent post first. The code LIMIT 2 tells mySQL to stop after finding the two most recent posts. When I execute this code, I got a result like this:

+----------+--------------------+
| poster   | posts              |
+----------+--------------------+
| Poster1  | I'm posting again. |
| SysAdmin | Shh                |
+----------+--------------------+

Notice that the results are in the opposite order of the previous listing of the entire table.