MySQL

The prac today was about MySQL commands and using them to manipulate data. I'll be doing quite a bit of this interacting with php for my site. I've created a new page to display the final task in the prac- it took me a couple of tries to LIMIT, ORDER BY and SELECT in the same command, but here's what I came up with:

SELECT * FROM `books` WHERE LEFT(title, 1) = 'M' ORDER BY title LIMIT 0,3

This piece of code selects from the table called 'books' all titles that begin with M (where the left most character of the field 'title' is 'M'). It then orders the selection by title and limits it to three entries. The trick with this is that the ORDER BY needs to be done before the LIMIT- otherwise it doesn't work.

Just for a bit of fun, I had a play around with acually displaying the data on a site, and here's the code to do that:

<?php
$database="a4186211_wp";
mysql_connect ("mysql12.000webhost.com", "a4186211_tsr", "password");
@mysql_select_db($database) or die( "Unable to select database");
$result = mysql_query( "SELECT name, tags, url FROM Resources" )
or die("SELECT Error: ".mysql_error());


$query="SELECT * FROM `books` WHERE LEFT(title, 1) = 'M' ORDER BY title LIMIT 0,3";
$result=mysql_query($query);

$num_rows = mysql_num_rows($result);
print "There are $num_rows records.<P>";
print "<table width=200 border=1>\n";
while ($get_info = mysql_fetch_row($result)){
print "<tr>\n";
foreach ($get_info as $field)
print "\t<td><font face=arial size=1/>$field</font></td>\n";
print "</tr>\n";
}
print "</table>\n";

?>

Here's the link:
http://www.tracyr.comlu.com/Resources/new%202.php