Mysql or die mysql error

(PHP 4, PHP 5)

mysql_error — Returns the text of the error message from previous MySQL operation

Description

mysql_error(resource $link_identifier = NULL): string

Parameters

link_identifier

The MySQL connection. If the
link identifier is not specified, the last link opened by
mysql_connect() is assumed. If no such link is found, it
will try to create one as if mysql_connect() had been called
with no arguments. If no connection is found or established, an
E_WARNING level error is generated.

Return Values

Returns the error text from the last MySQL function, or
'' (empty string) if no error occurred.

Examples

1049: Unknown database 'nonexistentdb'
1146: Table 'kossu.nonexistenttable' doesn't exist

See Also

• mysql_errno() — Returns the numerical value of the error message from previous MySQL operation
• » MySQL error codes

aleczapka _at) gmx dot net ¶

18 years ago

Florian Sidler ¶

13 years ago

Pendragon Castle ¶

14 years ago

l dot poot at twing dot nl ¶

16 years ago

Anonymous ¶

18 years ago

olaf at amen-online dot de ¶

18 years ago

Anonymous ¶

21 years ago

Gianluigi_Zanettini-MegaLab.it ¶

15 years ago

scott at rocketpack dot net ¶

19 years ago

josh ><> ¶

19 years ago

phpnet at robzazueta dot com ¶

16 years ago

miko_il AT yahoo DOT com ¶

19 years ago

Gianluigi_Zanettini-MegaLab.it ¶

15 years ago

Gerrit ¶

8 years ago

Writing a Simple PHP Connection Script

1. Connect to a MySQL installation.

2. USE the right MySQL database.

3. Send SQL to the database.

4. Get the results back.

5. Do something with the results.

First, you have to tell your PHP script how to connect to a database. This process is basically telling PHP to do what you did when you started up your MySQL command-line client (MySQL on Mac OS X). When you connected to your web server’s database, you probably used a command like the followng:

bmclaugh@akila:~$ mysql --host=dc2-mysql-02.kattare.com
                        --user=bmclaugh --password

You need those same pieces of information to give PHP so it can connect: your database host, your username, and a password.

Fire up your text editor and create a new script; call it connect.php. This script will be as simple as possible, because all you need it to do is connect to your database, USE the right database, and then run a sample SQL query to make sure things are working correctly.

In your script, type the following lines:

<?php
  mysql_connect("your.database.host",
                "your-username", "your-password")
    or die("<p>Error connecting to database: " .
           mysql_error() . "</p>");

  echo "<p>Connected to MySQL!</p>";
?>

It’s really that simple! And like most of the other PHP scripts you’ve been writing, although there are some new commands, you probably already know almost exactly what’s going on here.

First, there’s a new command: mysql_connect. This command simply takes in a database host, a username, and a password, and connects. It’s exactly as if you’re running your mysql tool and connecting to a remote database.

But what about the die bit? Sounds a little gruesome (like Lord of the Flies gruesome, not Twilight teen-angst gruesome). In fact, it is a bit nasty: you use die when something may go wrong in your script. Think of die as saying, “If my code dies, then do something less nasty than tossing out an error code to my user. In this case, die prints an error message that won’t scare off your users.

But before you can understand die, you have to know a little bit about the inner workings of mysql_connect. When mysql_connect runs, it either creates or reuses an existing connection to your database. It then returns that connection to your PHP program, and makes available all the other PHP-to-MySQL commands you’ll learn about soon. But if mysql_connect can’t create that connection—for example, if your database isn’t running or you’ve got a bad host or username—mysql_connect returns a very different value: false.

So what’s really happening in your script is something like this:

<?php
  // This isn't working code, but you get the idea
  if (i_can_connect_to_mysql_with("my.database.host",
                                 "my-username", "my-password"))
    go_do_cool_database_stuff();
  else
    send_error_to_user_using_die
?>
But that's a lot of typing, so PHP lets you shorten it to the following:
<?php
  mysql_connect("your.database.host",
                "your-username", "your-password")
    or die("<p>Error connecting to database: " .
           mysql_error() . "</p>");

  echo "<p>Connected to MySQL!</p>";
?>

Not only is this script shorter, but it flips things around a bit. It basically says, try to connect (using mysql_connect), and if the result isn’t true (the or part of the code), then die. die prints out an error message, but it also “dies.” In other words, it ends your script. So if mysql_connect returns false, and die runs, your script will exit. Your users won’t ever see the “Connected to MySQL!” line because the script will have stopped running. It’s dead on the server room floor, in search of a working database connection. (See the box on Everybody Dies at Some Point for more detail on the die command.)

Not only that, but mysql_connect sets up another function when it can’t connect. It makes the errors it ran into in trying to connect available through another command, mysql_error. So you can call mysql_error as part of your die statement to show what really happened.

If mysql_connect connects without any problems, it returns that connection. PHP will skip the die line, and then execute this line:

echo "<p>Connected to MySQL!</p>";

To see this command in action, create a simple HTML form, and call it connect.html. You can use this HTML to get you going:

<html>
 <head>
  <link href="../css/phpMM.css" rel="stylesheet" type="text/css" />
 </head>

 <body>
  <div id="header"><h1>PHP & MySQL: The Missing Manual</h1></div>
  <div id="example">Example 4-1</div>

  <div id="content">
    <h1>SQL Connection test</h1>
    <form action="scripts/connect.php" method="POST">
      <fieldset class="center">
        <input type="submit" value="Connect to MySQL" />
      </fieldset>
    </form>
  </div>
  <div id="footer"></div>
 </body>
</html>

This script is about as simple as it gets: it builds a form, drops a single button into place, and attaches that button to your new connect.php script. Load up your form in a browser (see Figure 4-1), and click “Connect to MySQL.”

Hopefully, you see one of the simplest, happiest messages of your burgeoning PHP and MySQL programming career: you’re connected! Check out Figure 4-2 for the sweet sight of success.

There’s something wonderful waiting around the programming corner now: almost all the mysql_ family of functions works the same: you give them some values, and they return something useful. If something bad happens, you usually get back either false or a nonexistent object (something most programmers call null or nil).

So now you need to tell MySQL which database your PHP script wants to use. There’s a function for that: mysql_select_db.

You give mysql_select_db a database name, and—you guessed it—it USEs that database, or returns false. So update connect.php to USE the right database:

<?php
  mysql_connect("your.database.host",
                "your-username", "your-password")
    or die("<p>Error connecting to database: " .
           mysql_error() . "</p>");

  echo "<p>Connected to MySQL!</p>";

  mysql_select_db("your-database-name")
    or die("<p>Error selecting the database bmclaugh: " .
           mysql_error() . "</p>");

  echo "<p>Connected to MySQL, using database bmclaugh.</p>";
?>

You should already see the pattern. die makes sure that if bad things happen, your script reports an error, your users read that error, and then the script exits. If things do go well, another happy message should get printed.

Try this new version out. Visit connect.html again and try and connect (and now USE) your database. You want to see something like Figure 4-3. Next up: talking SQL to your database.

Now that you’ve got a connection, and you’re tied in to the right database, you should see just what you’ve got to work with. In your MySQL tool, one of the first things you did was see what tables existed, and then start creating tables of your own. There’s some more work to do in creating tables, and you’ll do that in a bit, with a new HTML form and a new script.

But before diving into that, you can easily have your script see which tables are available in your database. Open up connect.php again, and add the following line:

<?php
  // All your existing database connection code

  $result = mysql_query("SHOW TABLES;");
?>

Here’s another new PHP-to-MySQL function: mysql_query. You’ll become very, very familiar with this one; it’s the key to passing SQL in to your database. This function takes in SQL, and you’ve given it some really simple SQL:

SHOW TABLES;

This command is exactly like typing SQL into your command-line tool.

<?php
  // All your existing database connection code

  $result = mysql_query("SHOW TABLES;");

  if ($result === false) {
    die("<p>Error in listing tables: " . mysql_error() . "</p>");
  }
?>

<?php
  // All your existing database connection code

  $result = mysql_query("SHOW TABLES;");

  if (!$result) {
    die("<p>Error in listing tables: " . mysql_error() . "</p>");
  }
?>

<?php
  // All your existing database connection code

  $result = mysql_query("SHOWN TABLES;");

  if (!$result) {
    die("<p>Error in listing tables: " . mysql_error() . "</p>");
  }
?>

<?php
  // All your existing database connection code

  $result = mysql_query("SHOW TABLES;");

  if (!$result) {
    die("<p>Error in listing tables: " . mysql_error() . "</p>");
  }

  echo "<p>Tables in database:</p>";
  echo "<ul>";
  while ($row = mysql_fetch_row($result)) {
    // Do something with $row
  }
  echo "</ul>";

?>

<?php
  // All your existing database connection code

  $result = mysql_query("SHOW TABLES;");

  if (!$result) {
    die("<p>Error in listing tables: " . mysql_error() . "</p>");
  }

  echo "<p>Tables in database:</p>";
  echo "<ul>";
  while ($row = mysql_fetch_row($result)) {
    echo "<li>Table: {$row[0]}</li>";
  }
  echo "</ul>";

?>

while ($row = mysql_fetch_row($result)) {
  echo "<li>Table: " . $row[0] . "</li>";
}

Cleaning Up Your Code with Multiple Files

So suppose you want your code to look more like this (actually, you really do want it to look more like this):

<?php
  mysql_connect($database_host, $username, $password)
    or die("<p>Error connecting to database: " .
           mysql_error() . "</p>");

  echo "<p>Connected to MySQL!</p>";

  mysql_select_db($database_name)
    or die("<p>Error selecting the database bmclaugh: " .
           mysql_error() . "</p>");

  echo "<p>Connected to MySQL, using database bmclaugh.</p>";

  // And so on...
?>

All you’re doing is writing something that looks a bit like a variable in place of hand-typing the username or database name. Now you can define those variables up above your connection code:

<?php
  $database_host = "your.database.host";
  $username = "your-username";
  $password = "your-password";
  $database_name = "your-database-name";

  // Database connection code
?>

But is this really that much better? Not yet; you’ve still got these same values hand-typed into your script. You want to stick the values in a file so no human has to type them. Read on.

Your goal is to get these values out of connect.php, into some place that all your PHP scripts can access them with no typing from you. Open up a new file, and call it app_config.php. Now drop your variables into this new file:

<?php
// Database connection constants
$database_host = "your.database.host";
$username = "your-username";
$password = "your-password";
$database_name = "your-database-name";

?>

When you move to a production version of your application, you probably want to place this file outside of the site root. That way, web users can’t simply type the path to your configuration script and get all your passwords. Alternatively, you could add security to this directory, although simply getting it out of the web-serving directories altogether is usually easiest.

Now, you can have all your different PHP scripts use these shared variables. Change a variable here in app_config.php, and that change affects all your PHP scripts that use these shared variables.

But how do you actually access these variables? Go back to connect.php, and remove where you defined these variables yourself. If you try and access connect.php through connect.html now, though, you’ll get a nasty error, as shown in see Figure 4-6.

The error occurs because connect.php now has no idea what $username or $password refers to. You need to inform PHP that before it tries to do anything in connect.php, it’s required to load app_config.php. And that’s (almost) exactly what you type in your script:

<?php

  require '../../scripts/app_config.php';

  // Database connection code
?>

Now, PHP loads the file ../../scripts/app_config.php before it runs your mysql_connect function. In effect, require says, “Hey PHP, if you can’t load the file I’m giving, then throw a nasty error, because nothing else is going to work.”

Try and run your connection script again, and you should see your table listing, which means things are working well again.

There’s just one more nagging little problem with your code: you’re still using variables for your username and password, along with the database host and database name. And what’s a variable? Something that varies or changes. Accordingly, PHP happily lets you write the following code in connect.php:

mysql_connect($database_host, $username, $password)
  or die("<p>Error connecting to database: " . mysql_error() . "</p>");

// This is allowed, but some bad mojo
$password = "hijinks";

So what happens when some other script—which also requires app_config.php—tries to connect with mysql_connect? It’s going to use $password, but now $password isn’t correct anymore. It’s set to “hijinks,” and chaos will ensue.

What you really want is for those values in app_config.php to be constant, and never change. You can do this with the special define function. Open up app_config.php and change your code:

<?php
// Database connection constants
define("DATABASE_HOST", "your.database.host");
define("DATABASE_USERNAME", "your-username");
define("DATABASE_PASSWORD", "your-password");
define("DATABASE_NAME", "your-database-name");
?>

You define the name of a constant and the value for that constant, and PHP creates a new constant. That way, you can type DATABASE_HOST into your code, and PHP really sees “your.database.host”. Perfect! And since this is a constant, it can’t be changed anywhere along the line.

Constants are typed in all-uppercase letters. Caps aren’t required, but it’s another one of those “speak like a PHP programmer” things. You want constants to look different from variables, and using all uppercase names is one way to do that. Constants also don’t have the $ before their name, which is yet another way to differentiate a constant from a variable.

Now you need to make some quick changes to connect.php to use these new capitalized names of constants:

<?php
  require '../../scripts/app_config.php';

  mysql_connect(DATABASE_HOST, DATABASE_USERNAME, DATABASE_PASSWORD)
    or die("<p>Error connecting to database: " .
           mysql_error() . "</p>");

  echo "<p>Connected to MySQL!</p>";

  mysql_select_db(DATABASE_NAME)
    or die("<p>Error selecting the database " . DATABASE_NAME .
           mysql_error() . "</p>");

  echo "<p>Connected to MySQL, using database " . DATABASE_NAME . "</p>";

  // SQL query-running goodness proceeds...
?>

Try connect.php again. You should get a perfectly good list of table names. But this time, you’ve got constants for your important information, safely tucked away in a file separated out of connect.php.

Building a Basic SQL Query Runner

Before getting to mysql_query and its results, though, start with what you know: an HTML form. Keep things simple for now, and just create a form with a single text area into which you can type queries, and a few basic buttons.

Open up your text editor, and create queryRunner.html:

<html>
 <head>
  <link href="../css/phpMM.css" rel="stylesheet" type="text/css" />
 </head>

 <body>
  <div id="header"><h1>PHP & MySQL: The Missing Manual</h1></div>
  <div id="example">Example 4-2</div>

  <div id="content">
    <h1>SQL Query Runner</h1>
    <p>Enter your SQL query in the box below:</p>
    <form action="scripts/run_query.php" method="POST">
      <fieldset>
        <textarea id="query_text" name="query"
                  cols="65" rows="8"></textarea>
      </fieldset>
      <br />
      <fieldset class="center">
        <input type="submit" value="Run Query" />
        <input type="reset" value="Clear and Restart" />
      </fieldset>
    </form>
  </div>

  <div id="footer"></div>
 </body>
</html>

Fire up your favorite browser, and make sure things look like Figure 4-7.

You know what’s next: you need to connect to MySQL and then USE your database. This code should be pretty familiar by now; open up run_query.php and go to work:

<?php
  require '../../scripts/app_config.php';

  mysql_connect(DATABASE_HOST, DATABASE_USERNAME, DATABASE_PASSWORD)
    or die("<p>Error connecting to database: " .
           mysql_error() . "</p>");

  echo "<p>Connected to MySQL!</p>";

  mysql_select_db(DATABASE_NAME)
    or die("<p>Error selecting the database " . DATABASE_NAME .
           mysql_error() . "</p>");

  echo "<p>Connected to MySQL, using database " . DATABASE_NAME . "</p>";
?>

But wait…this is familiar. You’ve written this code before, back on Selecting the Database to USE, and in fact, you have to write it every single time you connect to MySQL. That sort of duplication isn’t good. That’s why you moved your database constants into app_config.php: you wanted to be able to keep code that is always the same in a single place, rather than ten or a hundred places.

You’ve seen how easy it is to require a file (Abstracting Important Values into a Separate File), and pull in some constant values. You can do the same thing with your database connection code. Open up a new file, and call it database_connection.php. Save this new script right alongside app_config.php, and type the following code:

<?php
  require 'app_config.php';

  mysql_connect(DATABASE_HOST, DATABASE_USERNAME, DATABASE_PASSWORD)
    or die("<p>Error connecting to database: " .
           mysql_error() . "</p>");

  echo "<p>Connected to MySQL!</p>";

  mysql_select_db(DATABASE_NAME)
    or die("<p>Error selecting the database " .
           DATABASE_NAME . mysql_error() . "</p>");

  echo "<p>Connected to MySQL, using database " .
       DATABASE_NAME . ".</p>";
?>

Now you’ve got all your database code tucked nicely away, which means you can radically overhaul run_query.php:

<?php
  require '../../scripts/database_connection.php';
?>

How’s that for short code? Also notice that you no longer have a reason to require app_config.php. Your script requires database_connection.php, and it’s data-base_connection.php that handles bringing in app_config.php. Your code is much nicer and neater now.

Just to make sure this works, you should visit your queryRunner.html page and click Run Query. You should get something like Figure 4-8—all without anything but a single require in your main script!

At this point, you finally get to combine what you know about PHP and what you know about SQL. You already have anything the user puts into the big text area on your form through the $REQUEST variable, which as you remember is an array (The $_REQUEST Variable). And you also can use mysql_query to run a query.

You just need to put those two things together:

<?php
  require '../../scripts/database_connection.php';

  $query_text = $_REQUEST['query'];
  $result = mysql_query($query_text);

  if (!$result) {
    die("<p>Error in executing the SQL query " . $query_text . ": " .
        mysql_error() . "</p>");
  }

  echo "<p>Results from your query:</p>";
  echo "<ul>";
  while ($row = mysql_fetch_row($result)) {
    echo "<li>{$row[0]}</li>";
  }
  echo "</ul>";
?>

Just grab the appropriate field from the input from your HTML form, pass it to mysql_query, and you’re good to go. You can then pass in the returned PHP resource, $result, to an error-handling if statement, and finally to mysql_fetch_row to print out the results from the query.

The script looks pretty good, so now you’re ready to actually try things out.

You probably don’t have much in your database at this point, so start by creating a new table called urls. Here’s the SQL you’ll need:

CREATE TABLE urls (id int, url varchar(100), description varchar(100));

Of course, since you’ve got a nice big text area, you could also spread that out:

CREATE TABLE urls (
  id int,
  url varchar(100),
  description varchar(100)
)

Either way, you want a form that looks something like Figure 4-9.

Now click Run Query. What did you get? Well, you’re probably staring at a surprising screen, sort of like the one shown in Figure 4-10.

Well, what happened? If you want to really get confused, click the Back button on your browser, and run your CREATE query again. You’ll see a message like the one shown in Figure 4-11.

The first time you ran the CREATE TABLE query on Making Tables with CREATE, you got nothing—no results at all. The next time, MySQL tells you the urls table already exists! In fact, if you hop to your command-line tool, you see that, yes, the table does exist in your database:

mysql> describe urls;
+-------------+--------------+------+-----+---------+-------+
| Field       | Type         | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| id          | int(11)      | YES  |     | NULL    |       |
| url         | varchar(100) | YES  |     | NULL    |       |
| description | varchar(100) | YES  |     | NULL    |       |
+-------------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

Look carefully at your code again:

<?php
  require '../../scripts/database_connection.php';

  $query_text = $_REQUEST['query'];
  $result = mysql_query($query_text);

  if (!$result) {
    die("<p>Error in executing the SQL query " . $query_text . ": " .
        mysql_error() . "</p>");
  }

  echo "<p>Results from your query:</p>";
  echo "<ul>";
  while ($row = mysql_fetch_row($result)) {
    echo "<li>{$row[0]}</li>";
  }
  echo "</ul>";
?>

The if (!$result) code block is not running, so clearly $result came back as something other than false. But the while loop never ran; you never saw any results.

But wait a second. Your query was a CREATE query. What rows would be returned from that sort of query? There wouldn’t be any rows, because you weren’t asking for rows. You were just asking MySQL to create a table; in fact, a place to put rows.

The secret here is that mysql_query is happy to take in a CREATE statement. It even does what you asked, which is why the second time you entered that query, MySQL gave you an error, saying that the urls table was already created. When mysql_query gets a CREATE statement, it returns false if there was an error—which your script handles—but true if there’s not an error. And if there’s not an error, it doesn’t return any rows. You get a true value in $result, but nothing else. And that’s where things went wrong.

When mysql_query gets most of the SQL statements that don’t select data, like CREATE, INSERT, UPDATE, DELETE, DROP, and a few others, it just returns true (if things worked) or false (if they didn’t).

Fortunately, now that you know what’s going on, it’s not too hard to deal with the problem. You just need your script to find out whether the SQL query string the user supplied has one of these special words. If so, you must handle it differently. And it just so happens you’re plenty comfortable with searching through strings.

So think this through; what you really want is something like this:

You’ve already got a lot of this code, so you need to only add an if (or maybe a few ifs, actually), and some searching:

$return_rows = false;
$location = strpos($query_text, "CREATE");
if ($location === false) {
  $location = strpos($query_text, "INSERT");
  if ($location === false) {
    $location = strpos($query_text, "UPDATE");
    if ($location === false) {
      $location = strpos($query_text, "DELETE");
      if ($location === false) {
        $location = strpos($query_text, "DROP");
        if ($location === false) {
          // If we got here, it's not a CREATE, INSERT, UPDATE,
          //   DELETE, or DROP query. It should return rows.
          $return_rows = true;
        }
      }
    }
  }
}

This code may appear tricky, but it’s straightforward if you walk through it line by line. Basically, you have the same if statement, repeated over and over, with each of those having another nested if statement:

$location = strpos($query_text, "SEARCH_STRING");
if ($location === false) {
  // Try again with another SEARCH_STRING
}

Finally, if all the if statements fail, then you don’t have CREATE, INSERT, UPDATE, DELETE, or DROP anywhere in the query string:

// This is the innermost if statement
if ($location === false) {
  // If we got here, it's not a CREATE, INSERT, UPDATE,
  //   DELETE, or DROP query. It should return rows.
  $return_rows = true;
}

But why is this so complex? The problem here is that you really want to search the user’s query string, not just for a single matching word, like CREATE or INSERT, but for several matching words. That’s a little tricky, so you’ve got to work with one call to strpos at a time.

At each step, if the search string is found, the user has put in one of those special SQL keywords that doesn’t return rows, so the variable $return_rows is set to false, different from its original value, true.

Finally, at the end of this curly-brace love fest, the if statements unwind back to the main program, and either $returns_rows has a value of true because none of the searches matched, or false because one of them did.

Now you’re ready to use $returns_rows to print a result:

<?php
  // require and database connection code

  // run the query

  // handle errors in the result

  // see if the query has result rows or not

  if ($return_rows) {
    // We have rows to show from the query
    echo "<p>Results from your query:</p>";
    echo "<ul>";
    while ($row = mysql_fetch_row($result)) {
      echo "<li>{$row[0]}</li>";
    }
    echo "</ul>";
  } else {
    // No rows. Just report if the query ran or not
    if ($result) {
      echo "<p>Your query was processed successfully.</p>"
      echo "<p>{$query_text}</p>";
    }
  }
?>

Most of this script is familiar. All the code you’ve been using to print out rows stays the same. That code just moves inside the if ($return_rows) block, because it only applies if the user put in something like a SELECT that returns (potentially) lots of results.

Then, in the else to that if, your script just reports whether or not things went OK. As an additional help, this section of the if prints out the original query so the user can know what was executed.

Now, technically, you don’t really need that if ($result). Since you tested earlier to see if $result is false, if your script gets to this last bit, you know that $result is true, so you can simplify things at the end a bit:

if ($return_rows) {
  // We have rows to show from the query
  echo "<p>Results from your query:</p>";
  echo "<ul>";
  while ($row = mysql_fetch_row($result)) {
    echo "<li>{$row[0]}</li>";
  }
  echo "</ul>";
} else {
  // No rows. Just report if the query ran or not
  echo "<p>Your query was processed successfully.</p>"
  echo "<p>{$query_text}</p>";
}

This script is getting to be a little long, but you know what every single line is doing at this point. Go ahead and try it out.

You probably created the urls table (Entering Your First Web-Based Query)—even though your PHP script didn’t let you know that. So try entering DROP TABLE urls; as your SQL query. Then run your query, and this time, you should get a helpful message back, specific to your rowless query, as you can see in Figure 4-12.

Unfortunately, there’s still a problem in one of the lines in the code in the previous section. Right now, if your user types the following query, what happens?

DROP TABLE urls;

Your set of if statements understands that DROP is part of the query, realizes it has no return rows, and does the right thing: reports that the query either ran without problems, or that an error occurred.

But what about this query? Do you see where the problem is?

drop table urls;

Here’s the statement that should indicate a match:

$location = strpos($query_text, "DROP");
if ($location === false) {
  // this should return true, and so there are no return rows
}

But that line searches for DROP, which doesn’t match “drop” at all. strpos searches for strings, but it sees a lowercase letter, like “d”, as a different letter from an uppercase “D”. So that search finds “DROP” but not “drop” or “dRoP”.

And, as always, you’ve got humans using your app, not robots. You can’t just assume that those humans will be good SQL citizens and always use capital letters. You could put a little message on the form: Please type your SQL in all capital letters, but humans will be humans, and they tend to ignore instructions like this.

You’ll spend at least as much of your time dealing with the human factor in your code as writing code that handles the so-called normal flow of operation. In fact, once you add real people to your line of thinking, you’ll realize that “normal” isn’t a very useful concept. Instead, your code simply has to deal with as many possibilities as you can imagine (and some you can’t).

Fixing the lowercase and uppercase issue turns out to be fairly simple: you can simply convert $query_string to all capital letters before starting to search through it:

$return_rows = false;
$query_text = strtoupper($query_text);
$location = strpos($query_text, "CREATE");
// All the nested if blocks.

Now, if a user types drop table urls or DROP table UrLS, the search string is DROP TABLE URLS, and searching for “DROP” will return a match.

But there’s another problem! Before you read on, see if you can figure out what it is.

To spot this potential problem with your query script, take a look at the last bit of your code that’s run if the user enters a rowless query like DROP or INSERT:

// No rows. Just report if the query ran or not
echo "<p>Your query was processed successfully.</p>"
echo "<p>{$query_text}</p>";

Run this code, and you’ll get something like you see in Figure 4-13.

Carefully compare the code in Figure 4-12 and Figure 4-13. In Figure 4-13, you’ll notice everything is in uppercase. That makes sense, because to make searching easier, you added this line to your script:

$query_text = strtoupper($query_text);

Then, when you output $query_text at the end, the output is shown in all uppercase letters. Is this a big deal? Well, it doesn’t seem to be, at least not here. But it does reveal something: after that $query_text string is converted to uppercase, any time it’s used, it’s coming back with all uppercase letters. So suppose the original query was something like this:

SELECT *
  FROM users
 WHERE last_name = "MacLachlan";

Now consider this same query, turned into all uppercase letters:

SELECT *
  FROM USERS
 WHERE LAST_NAME = "MACLACHLAN";

Believe it or not, these are not the same query. SELECT—and most of the other SQL queries—are going to treat a last name of “MacLachlan” as being totally different from “MACLACHLAN.” So those two queries are not identical at all.

At the moment, this doesn’t create any trouble. Your script never re-runs the query, and mysql_query runs with $query_text before its turned into its uppercase version. But this is a problem waiting to happen.

In general, you want to try to avoid directly changing input from a user. That’s because you get exactly this sort of problem: you may need to use that input again, and once you’ve changed it, you can’t go back.

Luckily, the remedy is simple: instead of changing the user’s input, you instead use a new variable to store the uppercase version of the query:

$return_rows = false;
$uppercase_query_text = strtoupper($query_text);
$location = strpos($query_text, "CREATE");

You should use this new variable in all your string comparisons:

$return_rows = false;
$uppercase_query_text = strtoupper($query_text);
$location = strpos($uppercase_query_text, "CREATE");
if ($location === false) {
  $location = strpos($uppercase_query_text, "INSERT");
  if ($location === false) {
    $location = strpos($uppercase_query_text, "UPDATE");
    if ($location === false) {
      $location = strpos($uppercase_query_text, "DELETE");
      if ($location === false) {
        $location = strpos($uppercase_query_text, "DROP");
        if ($location === false) {
          // If we got here, it's not a CREATE, INSERT, UPDATE,
          //   DELETE, or DROP query. It should return rows.
          $return_rows = true;
        }
      }
    }
  }
}

As small a change as that is, it protects you in case you ever need to use that query string again.

And just like that you’ve actually got a tool that will execute any SQL query you throw at it. But there’s work still to do. All that search code really does clutter up your script, and there’s just no getting around it: your script is pretty hard to understand at first (and even second) glance. In the next chapter, you’ll tackle all this, taking your form from a basic handy form to a really nice exercise of your PHP muscle.