Mixing in PHP variables

Now we come to the really fun stuff: changing the query sent to MySQL based upon user input. We have already looked at creating forms to accept user input using HTML forms, and now we're going to look at how to accept that data and use it inside our table.

Given that the parameter for mysqli_query() is just a string, we can use variables as we would in any other string - if the string is double-quoted, it is simply a matter of using the variable directly inside it, as PHP will convert it to its value automatically.

Consider the following part of a script:

$result = mysqli_query($db, "SELECT ID FROM webpages WHERE Title = '$SearchCriteria';"); $numhits = mysqli_num_rows($result);
print "Your search for $SearchCriteria yielded $numhits results";

The example above shows how easy it is to modify your SQL queries to respond directly to user submission. Imagine for a moment you stored all the pages in your website in an SQL database. You'd need to create your table something like this:

CREATE TABLE webpages (ID INT AUTO_INCREMENT PRIMARY KEY, Title VARCHAR(255), Content TEXT, DateModified INT);

With that table structure, it is very easy to create a simple search engine for the site. Our first attempt above is a little too simple - it will only return pages that match the exact submission from users. However, we've already looked at both the LIKE operator and FULLTEXT indexes, so you should know that you can modify the query to be much more helpful by doing this:

ALTER TABLE webpages ADD FULLTEXT (Title);
ALTER TABLE webpages ADD FULLTEXT (Content);

With FULLTEXT indexes on the two important fields in the web pages table, it is now easy to do a proper search through the table, even allowing visitors to use boolean search techniques with the following query:

SELECT ID FROM webpages WHERE MATCH(Title, Content) AGAINST ('$SearchCriteria' IN BOOLEAN MODE);

This time visitors can search for pages and find any pages that have a matching title or even matching content, and they can use +, -, or phrase searching to get more control over the results.

It is possible to use PHP variables wherever you want inside SQL queries, as long as at the end of the day you end up with a valid SQL query, otherwise mysqli_query() will return false. Consider the following function:

function simplequery($table, $field, $needle, $haystack) {
    GLOBAL $db;
    $result = mysqli_query($db, "SELECT $field FROM $table WHERE $haystack = $needle LIMIT 1;");

    if ($result) {
        if (mysqli_num_rows($result)) {
            $row = mysqli_fetch_assoc($result);
            return $row[$field];
        }
    } else {
        print "Error in query<br />";
    }
}

That function allows you to pass in the name of the table you want to read, the field you are interested in, and the criteria it should match, then executes the appropriate query and sends the requested value back as its return value. This function can therefore be used like this:

$firstname = simplequery("usertable", "firstname", "ID", $UserID);

The advantage to this is that you can program all sorts of error checking into simplequery() without making your scripts any more cluttered to read. You should now be able to see that you can use PHP variables throughout your SQL code if you want to, although it is not often you use it quite so much!

Although mixing PHP variables into your MySQL calls is where the real power of PHP's database systems comes into play, you must be very, very careful not to allow your users to abuse your scripts to hack into your systems. The first but by no means only defence in this fight is the function mysqli_real_escape_string(), which is designed to make PHP variables a little more safe when used inside MySQL queries.

To use this function, pass your current database connection as its first parameter and the string in that you wish to make safer, and it will return the new value. The function works by escaping all potentially dangerous characters in the string you pass in, including single quotes, so you should be wary about using this function in combination with addslashes(). That said, if you're relying on addslashes() for any meaningful database work, you've probably slipped up somewhere!

Copyright ©2015 Paul Hudson. Follow me: @twostraws.