Databases in PHP - MySQL in PHP - Running Some Queries

By Vince Barnes

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.


 

 



Make a Comment

Loading Comments...

  • Web Development Newsletter Signup

    Invalid email
    You have successfuly registered to our newsletter.
  •  
  •  
  •  
Thanks for your registration, follow us on our social networks to keep up-to-date