Write a script to connect to the MySQL database server and to query it

OK, so we have created the database and the table, we have put some information into the database and we know how to query it, so now what? what good is this database in the real world? Well this database would be particular useful if we could access it from a web page wouldn't it? Well you can, and here is how. You need to be able to write in a scripting language such as PHP or perl which have the ability to access MySQL servers and then embed them in your web page so that when people access them the scripts query the database and do some useful with the information that they relieve. If you don't know any scripting languages capable of this then I would suggest reading up about them and learning how to use them otherwise the database you have just created is not all that useful really.

What we are going to do is to write a simple PHP script that is called by a <form> in a HTML document with the arguments $bookname and $isbn, so here is what the form might look like;

	<form action="oursearch.php3" method=post>
	Enter book name: <input type=text name=bookname maxlength=250 size=50><br>
	Enter ISBN number: <input type=text name=isbn maxlength=10 size=10<>br<
	<input type=submit value="Find book details">
	</form>
   

This form would then load the "oursearch.php3" PHP3 script and give it the details that have been specified, the PHP3 script can then do what it wanted with those details. The PHP3 script might for example find the book in our database using the details given and then print the details, the code for such a script might be like the following, of course if you wanted to use this then you would have to substitute the values in for `username',`password' and `databasename';

	<?php

	// Attempt to make a connection to the MySQL server, or else die
	$db = mysql_connect("localhost","username","password")
	        or die("Unable to connect to connect to the MySQL server");

	// Select the appropriate database that we wish to use
	mysql_select_db("databasename",$db)
	        or die("Cannot access database `books'");
	
	// Query the database with the information given
	$result = mysql_query("select * from books where bookname like '%$bookname%' and isbn like '%$isbn%'",$db);
	
	// See if there were any details and put the retrieved details into an array and print them out, then
	// loop around until all the details available have been printed to the screen
	if($details = mysql_fetch_array($result)){
	        do{
	                echo "Book name = `".$details["bookname"]."'<br>";
	                echo "ISBN number = `".$details["isbn"]."'<br>";
	                echo "Price = `£".$details["price"]."'<br>";
	                echo "Purchase date = `".$details["datepurchased"]."'<br><br>";
	        } while($details = mysql_fetch_array($result));
	}
	else    // If not details were found print a message saying not
	        echo "Sorry, the book you were searching for does not exist in the database<br>";
	
	// Free the result from the server
	mysql_free_result($result);
	
	// Close the connection to the database server, although this is not always necessary as the connection will
	// be closed at the end of the script if it is still open
	mysql_close($db);

	?>
   

This script would query the database for the book name to appear anywhere in the field `bookname' case insensitive and for the ISBN number to appear anywhere in the field `isbn', so only if the details are in both fields does the entry get returned in the result.