Building a Basic SQL Query Runner PHP Help

Now that you can connect to SOL, you’re ready to take on something more ambitious: building your own version of a MySOL command-line tool. Of course, you’re a PHP developer and programmer now, so mentally scratch out “command-line” and replace it with “web-based.”

It turns out that you already have most of the tools you need. You can easily build an HTML form with which you and your users can enter in a SOL query; you know how to connect to MySQL and select a database; and you can run a query. All that’s left is to figure out how to interpret that PHP resource that mysql_ query returns when it’s not a list of table names

Creating an HTML Form with a Big Empty Box

Before gatting to mysql_ query and its results, though, start with what you know: an HTML form. Keep things simple for now by creating a form with a few basic buttons and a single text area into which you can type queries.

Start your text editor and create with the following code

<html>
<head>
<link href=./css/phpWol.css•.rel=”stylesheet” type=”text/css” />
</head>
<body>
<div id=”header”><h1>PHP & MySQL: The Missing Manual</h1></div>
<div id=”example”>Example S-2</div>
<div id·”content”>
<hl>SQL Query Runner</hl>
<p>Enter your SQL query in the box below:</p>
<form action=”scripts/run_query.php” method=”POST”>
dieldset>
<textarea id=”querLtext” name=”query”
cols=”6S” rows·”8″></textarea>
</fieldset>
<br />
dieldset class=”center”>
<input type=”submit” value·”Run Query” />
<input type=”reset” value=”Clear and Restart” />
</fieldset>
</form>
</div>
<div id·”footer”></div>
</body>
</html>

SQL Query Runner

SQL Query Runner

Connecting to Your Database (Again)

Now that you have your HTML form. exactly as with the you created on  you need to write a script that connects to MySQL and then USEs your database. This code should be pretty familiar by now; create a new script in your directory called  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 MySOL, using database” . DATABASE_NAME. “</p>”;
?>

You’ve already written this code before (page 133), and in fact, you have to write it every single time you connect to MySQL. That sort of duplication isn’t good for the same reason why you moved your database constants into you wanted to be able to keep code that’s always the same in a single place rather than ten or a hundred

You’ve seen how easy it is to require a file, and pull in some constant values. And you can do the same thing with your database connection code. Open a new file and call it aetetese conoectiottotn: Save this new script right alongside app_config. pnp (in your entire site’s senors;’ directory, not alongside your chapter-specific examples) and enter 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 MySOL!</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>”;
?>

You now have all your database code tucked nicely away, so you can radically overhaul run Instead of all the code at the top of this section, you just need the following:

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

How’s that for short code? More important, notice that there’s no longer a reason to require one. Your script requires  and it’s

To verify that this works, visit your ()!/’r v Run nf.”. htr”{ page and click the “Run Query” button. You should see something like Figure 5-8, all without anything but a single require in your main script.

Running Your User’s SQL Query (Again)

You’re finally ready to combine what you know about PHP and what you know about SQL. You’ve already captured anything the user puts into the big text area on your form through the $REQUEST variable (which, as explained on is an array), and you also can use mysql_ query to run a query.

$query_text = $_REQUEST[‘query’);
$result = mysql_query($query_text);

if (!$result) {
die(“<p>Error in executing the SOL query” • $querLtext • “: ”
mysql_errorO • “</p>”);
echo “<p>Results from your query:</p>”;
echo “<ul>”;
while ($row = mysql_fetch_row($result»
echo “<li>{$row[o]}</li>”;
}
echo “</ul>”;
?>

In other words, grab the correct 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_ fetc_h _row to print out the results from the query.

When Not to Abstract Out

Good question! You’ve correctly noticed that just as you’re constantly connecting to MySQL-with the same user name and password, repeatedly-and selecting a database-often the same database, repeatedly-you’ll be calling mysql_ query, over and over and over. At first glance, it seems to make sense to place that in another file and then require that file

You could move mysql_ query out of your main script, and pass to it the part of the statement that keeps changing: the SQLquery. You’d need to create a custom function that takes in your query from your main script and hand that query to mysql_query. Then, when mysql_query finished running, the custom function would need to pass back anything it returned to your main script.

That might sound like a mouthful, and a lot of work. It’s actually pretty easy, though, and once you start writing your own functions-something you’ll be doing in Chapter 8 quite a bit-you’ll have no problem doing just this. But, what would you gain? You’d still have to pass in a query and get back a response. you wouldn’t actually gain anything from building your own function; it would basically replace mysql_ query, but you wouldn’t get any extra functionality, and it wouldn’t add any protection from changes or anything like that to your code.

However, before you go thinking that you shouldn’t worry about this sort of thing, take a minute. Asking yourself, “Could I pull this code out into another general file? Should I make this a custom function?” is a very good thing! You want to think like that, even if you decide-as is the case here-that it’s hi! a good thing. The more you roll around new ideas and ways to approach your code, the better a programmer you’ll be. So, keep asking yourself these questions; just don’t be afraid to answer your own questions with “No, that’s not such a great idea …in {iii, case

Entering Your First Web-Based Query

In” or ~o, you’re connecting to a database and you have a way to run a query, but you probably don’t have much ‘ your database yet. so start by creating a new table. Call the table u (it’s going to contain web addresses). Here’s the SQL you’ll need

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

Of course, because you have a nice big text area on  you could also spread that out:

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

SQL Query Runner

SQL Query Runner

Go ahead and click Run Query. What did you get?

Not so good, right? You’re probably staring at a surprising screen, sort of like the one shown in Figure 5-10.

SQL Query Runner

SQL Query Runner

If you want to really become confused, press the Back button on your browser and run your CREATE query again. You’ll see a message like the one shown in Figure 5-11

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

Wait a second, though. 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 rows.

Handling Queries That Don’t SELECT Information

The important point demonstrated in the previous section is that mysql_ query is happy to take in a CREATE statement. It even did what you asked, which is why the second time you entered that query in Figure 5-11,MySQL returned an error, saying that the table was already created. When mysql_ query receives a CREATE statement, it returns false if there was an error-which your script handles-but true if there’s not an error. If there’s not an error, it wil! not return any rows. You get a true value in $resul t, but nothing else. And that’s where things went wrong.

In fact, that’s what mysql_ query does when it gets most of the SQL statements that don’t select data, such as CREATE, INSERT. UPDATE, DELETE, DROP, and a few others. For each of these, you just get back true (if things worked) or false (if they didn’t).

Fortunately, now that you know this is going on, it’s not too hard to deal with the problem. You just need to see whether the SQL query string that the user supplied has one of these special words. If so, it must be handled differently. But, it just so happens_you’re plenty comfortable with searching through strings

Take a moment to think this through; what you really want is something like this:

1. Grab the user’s query from the HTML form.
2. Pass the query into mysql_queryand store the result in a variable.
3. See if the result is false, which isbad no matter what type of SQL was passed in.
4. If the result is not false, see if the query has one of the special keywords in it: CREATE, INSERT, UPDATE, DELETE, or DROP.
(There are others, but this covers the most common ones.)
5. If the query has one of these special words, just see whether the result of running the query was true, and let the user know that things went well.
6. If the query does not have one of these words, try to print out the result rows as you’ve already been doing.

You know how to do all of these things individually; all you need to do is put them together. Start out with a variable that indicates whether the user’s SQL will return anything and set it to false:

$return_rows = false;

Now you can search the user’s query by using strpos, looking for one of the SQL keywords that tells you, “No, rows will not be returned by this Query.”

$return_rows = false;
$location = strpos($query-text, “CREATE”);

If nothing was found, check the next keyword …and the next…and so on:

$return_rows = false;
$location = strpos($querL text, “CREATE”);
if ($location === false) {
$location = strpos($querLtext, “INSERT”);
if ($location === false) {
$location = strpos($querLtext, “UPDATE”);
if ($location === false) {
$location = strpos($query_text, “DELETE”);
if ($location === false) {
$location = strpos($querLtext, “DROP”);
if ($location === false) { •
II If we got here, it’s not a CREATE, INSERT, UPDATE,
II DELETE, or DROP query. It should return rows.
$return_rows = true;

}
}
}
}
}

That code might look complicated, but it’s clear when you walk through it, line by line. Basically, you have the same if statement, repeated over and over, with each of those statements containing another nested if statement:

Finally, if all of the if statements fail, CREATE, INSERT, UPDATE, DELETE, or DROP are not in the query string:

The challenge 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 do it with one call to strpos at a time.

At each step, if the search string is found, it means that the user has entered one of those special SOL keywords that does not return rows, so the variable $return JOws is set to false, which is 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 $returnsJows has a value of true because none of the searches matched, or it’s false because one of them did

You’re ready to use $returns JOws to print out a result:
<?php
// require and database connection code
// run the query
// _handle errors in the result
$return_rows = false;
$location = strpos($querLtext, “CREATE”);
if ($location === false) {
$location = strposj squerytext , “INSERT”);
if ($location === false) {
$location = strpost squery , text, “UPDATE”);
if ($location === false) {
$location = strpos($query_text, !’DELETE”);
if ($location === false) {
$location = strpos($querLtext, “DROP”);
if ($location === false) {
II If we got here, it’s not a CREATE, INSERT, UPDATE,
II DELETE, or DROP query. It should return rows.
$return_rows = true;

}
}
}
}
}

if ($return_rows) {
II We have rows to show from the query
echo “<p>Results from your query:</p>”j
echo “<ul>”j
while ($row = mysql_fetch_row($result» {
echo “<li>{$row[o]}</li>”

echo “</ub”;
} else {
II No rows. Just report if the query ran or not
if ($result) {
echo “<p>Your query was processed successfully.</p>”
echo “<p>{$querLtext}</p>”;
}
}
?>

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

Then, in the else branch of that if, your script reports whether things went OK. As an additional aid, this branch of the if statement prints out the original query so that the user can know what was executed

Technically, you don’t really need that if ($result). Because 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) {
II 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[oJ}</li>”;
}
echo “</ul>”;
} else {
II No rows. Just report if the query ran or not
echo “<p>Your query was processed successfully.</p>”;
echo “<p>{$querLtext}</p>”;
}

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

You probably created the <i,”- table earlier-even though your PHP script didn’t let you know that. 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 5-12.

Dealing with Humans

Unfortunately, there’s still a problem in one of those lines. Right now, if your user types the query DROP TABLE ur ls j, your set of if statements catches 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?
drop table urIs;
Do you see a problem? Here’s the if statement that should indicate a match:
$location = strpos($querLtext, “DROP”);
if ($location === false) {
// this should return true, and so there are no return rows
}

But that line searches for “DROP”, which will not match “drop” at,.all.strpos searches for strings, but it sees a lowercase letter, like “d,’ as a different letter than an uppercase “D.” Thus, that search will find “DROP” but not “drop” or “dRoP.”

And, as always, it’s humans who are using your app, not robots. You can’t simply assume that those humans will be good SOL citizens and always use capital letters. You could even put a little message on the

So, how do you fix the issue of lowercase and uppercase? It turns out to be fairly simple: you convert $query _string to all CAPITAL letters before starting to search through it:

$return_rows = false;
$query_text = strtoupper($query_text);
$location = strpos($querLtext, “CREATE”);
// All the nested if blocks

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

But there’s another problem! Do you see what it is?

Avoid Changing User Input Whenever Possible

This one is a bit trickier, and it really is a potential problem, as opposed to something creates havoc right now. Here’s 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>”;

SQL Query Runner

SQL Query Runner

What’s the big deal? Look closely, and then flip back to Figure 5-12, Do you see the problem? In the latter version, in Figure 5-13, 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, However, 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.

Suppose that the original query was something like this:

SELECT *
FROM users
WHERE last name = “MaClachlan”;

Now, consider this same query, converted to all uppercase letters:

SELECT *
FROM USERS
WHERE LAST NAME = “MACLACHLAN”;

Believe it or not. these are (lotthe same query. SELECT-and most of the other SQL queries-are going to treat a last name of “MacLachlan” as totally different than “MACLACHLAN”. As a result, those two queries are not identical at all

Right at this juncture, this doesn’t create any trouble. Your script never reruns 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 and avoid directly changing input from a user to steer clear of exactly this sort of problem: you might need to use that input again, and once you’ve changed it, you can’t go back

Luckily, this is a really easy fix: you just don’t change the user’s input. Instead, you use a new variable to store the uppercase version of the query:

$return_row> = false;
$uppercase_query_text = strtoupper($query_text);
$location = strpos($querL text, “CREATE”);

Now.s you should use this new variable in all your string comparisons:

$uppercase_query_text = strtoupper($query_text);
$location = strpos($uppercase_querLtext, “CREATE”);
if ($location === false) {
$location = strpos($uppercase_querLtext, “~NSERT”);
if ($location === false) {
$location = strpos($uppercase_querLtext, “UPDATE”);
if ($location === false) {
$location = strpos($uppercase_querLtext, “DELETE”);
if ($location === false) {
$location = strpos($uppercase_querLtext, “DROP”);
if ($location === false) {
II If we got here, it’s not a CREATE, INSERT, UPDATE,
II 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 got a tool that will execute any SQL query you throw at it. But there’s work still to do. All that search code clutters up your script, and there’s just no getting around it: your script is pretty hard to understand at first glance (and even at second glance). In the next chapter, you’ll tackle all of this, transforming your handy little form to a really nice exercise of your PHP muscle.

Get Specific with Position and Whitespace Trimming

You’re definitely making Tn) a lot better, but there are still problems. Suppose that someone wrote SQL like this:

SELECT *
FROM registrar_activities
WHERE name = ‘Update GPA’
OR name = ‘Drop a class’

rup. Your code that searches for “update” and “drop” will report that this Query has both words in it, and simply return “Your SOL was run without any problems.” But that Ii a problem

What can you do? Well, think about the structure of SQL.Those special keywords-CREATE, INSERT,and their friends-all are the first word in the Query, Thus, you’d need to get the position of the match and check whether that position is position O.You can do that by adding to your if conditions and using
the logical or II operator in PHP

The double-pipe (II) means “or” to PHP.So, this line says if there’s no match at all ($location === false) or the match isn’t starting at the first position (position 0), then look for the next keyword. Of course, you’d have to change all your if statements, which is even messier. Clearly, this is an improvement, although it’s one th~t .nrtters up your code even further

Here, you have a new problem: this isn’t a SELECT,but your search code won’t find one of those special words at the beginning. The first character is just a space

You can solve this problem, too, by using another familiar function: trim, trim gets rid of white space, and if you do that before you search, you should be in good shape:

And, as something to look forward to, you’re just about to learn some handy techniques to make all this messy code a lot simpler. So keep going, dealing with human-type input, and know that your code is only going to get cleaner and simpler

Posted on January 13, 2016 in Connecting PHP to MySQL

Share the Story

Back to Top