Continuing the process of adding access to a MySQL Database to our HTML pages, we
now need to interact with the database we previously selected from the server to
which we also previously connected.
I have previously talked about the fact that almost everything you want to do
in terms of interacting with a SQL database is called a "query" even if it’s an
instruction and not actually asking a question. Ours is not to reason why,
ours is just to do and query. Consequently we’ll continue with
queries without further ado! For our MySQL database this means using the
mysql_query ( ) function. Let’s take a look at an example:
mysql_query ("CREATE TABLE customers (cust_id INT UNSIGNED NOT NULL
AUTO_INCREMENT PRIMARY KEY, firstname VARCHAR(30) NOT NULL, lastname VARCHAR(30)
NOT NULL)");
Breaking this down, we can see that the mysql_query function is taking one
parameter, which contained within the outermost parentheses and delimited with
quotes because it is a string. At this point, don’t worry too much about
the details of the query itself – we’ll have more on that soon enough!
Personally, I like to make my code easier for me to read. In this
instance, one way to do that is to put the query itself into a string, like
this:
$query = "CREATE TABLE customers (cust_id INT UNSIGNED NOT NULL
AUTO_INCREMENT PRIMARY KEY, firstname VARCHAR(30) NOT NULL, lastname VARCHAR(30)
NOT NULL)";
mysql_query ($query);
This way, when you add in you error controls, the meaning of your code
doesn’t become to buried in nests of parens within quotes within parens, etc.,
something like this:
$query = "CREATE TABLE customers (cust_id INT UNSIGNED NOT NULL
AUTO_INCREMENT PRIMARY KEY, firstname VARCHAR(30) NOT NULL, lastname VARCHAR(30)
NOT NULL)";
if (@mysql_query ($query) )
{ print ‘<p> Connected to MySQL Server. </p>’;
}
else
{ die ( ‘ <p> Database creation failed because ‘ . mysql_error ( ) . ‘ </p>’
‘ <p>
The query was: ‘ . $query . ‘</p>’);
Now let’s take a look at a couple of queries themselves.
Selecting A Database
Here’s something that’s not too complicated: to select a MySQL database in
PHP use the mysql_select_db ( ) function, like this:
mysql_select_db ("mydatabase");
Couldn’t be simpler, as long as everything went well — more on that in a
minute.
Creating a Database
OK, we’re going to create a database, and to honor the one responsible for
this need, our database will be called "Murphy". A database is created by
using a query, and therefore the mysql_query ( ) function. (Remember that
I talked about the way that everything is thought of as a query, even though it
might not actually be making any sort of "enquiry".) The query will be
given the CREATE DATABASE parameter, like this:
mysql_query (‘CREATE DATABASE murphy’);
Again not too complicated, as long as everything went well. Now it’s
time to think about what to do if everything didn’t quite go as planned.
I’ve said this before, and I’ll add the caveat that I use the word "hacker" in
it’s old sense: the difference between a hacker and a programmer is that a
hacker works and works until they find a way to make their code work, but a
programmer works and works until they also find every way it won’t work.
Being so, as programmers, we must find a way to handle every error that might
come our way.
