Monthly Archives: December 2019

How to write SQL queries using MySQLi in PHP

MySQLi is the “MySQL Improved Extension” for PHP. You might have seen simple examples of it being used like this:

$sql = "SELECT * FROM persons";
$result = mysqli_query($conn, $sql);
echo "There are: " . mysqli_num_rows($result) . " persons in the database\n";

This particular example works. In my case, the output is “There are: 2 persons in the database”. Many queries are not so simple, however. Suppose you wanted info about a particular person named “Smith”. The following code will work (assuming $name has been set in the code somewhere above):

$name = "Smith";
...
$sql = "SELECT * FROM persons WHERE lastname = '$name'";
$result = mysqli_query($conn, $sql);
echo "There are: " . mysqli_num_rows($result) . " persons named $name\n";

The output is “There are: 1 persons named Smith”.

Because it works, it may lull you into a false sense of security; you might think that’s all there is to it. And it may work for quite a while before you run into a problem. The problem is that some names contain special characters that will break the code above. One example is the name O’Hara. This is what happens when “Smith” is replaced by “O’Hara” in the above code:

$name = "O'Hara";
...
$sql = "SELECT * FROM persons WHERE lastname = '$name'";
$result = mysqli_query($conn, $sql);
echo "There are: " . mysqli_num_rows($result) . " persons named $name\n";

When I run this code, I see an error:

PHP Fatal error:  Uncaught TypeError: mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given in /home/fullstackdev/tests/single-select-sql-apostrophe.php:29
Stack trace:...

This error might be confusing because the error is thrown from the line with mysqli_num_rows. That’s because the call to mysqli_query returned false, a sign that there was an error. Let me call mysqli_error before trying to use the $result, and print out the error, if there was one:

$name = "O'Hara";
...
$sql = "SELECT * FROM persons WHERE lastname = '$name'";
$result = mysqli_query($conn, $sql);
$error = mysqli_error($conn);
if ($error) {
        echo "$error\n";
} else {
        echo "There are: " . mysqli_num_rows($result) . " persons named $name\n";
}

Now, the output looks like:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Hara'' at line 1

This error is more helpful than the previous one, but if you’re new to SQL, it might still be confusing. The problem is that there’s an apostrophe in the name O’Hara, but the apostrophe is a special character with special meaning in SQL. It is used to signify the beginning or ending of a string. If I print out my SQL statement from the above code, it looks like this:

SELECT * FROM persons WHERE lastname = 'O'Hara'

That’s three apostrophes. There’s an unmatched apostrophe, and that means MySQL was expecting more characters; it doesn’t know what to do with this statement.

The way to handle this type of query is to “escape” the apostrophe in the SQL by putting a backslash character in front of it, like this:

SELECT * FROM persons WHERE lastname = 'O\'Hara'

That backslash notifies MySQL to treat the next character as part of the string, not as a special character delimiting a string.

Of course you don’t want to have to write special code to replace all instances of apostrophe in your string with backslash followed by apostrophe. There’s already a special PHP method which will do this for you. It’s called mysqli_real_escape_string.

Let’s use this in our code above:

$name = "O'Hara";
...
$esc_name = mysqli_real_escape_string($conn, $name);
$sql = "SELECT * FROM persons WHERE lastname = '$esc_name'";
echo "$sql\n";
$result = mysqli_query($conn, $sql);
$error = mysqli_error($conn);
if ($error) {
        echo "$error\n";
} else {
        echo "There are: " . mysqli_num_rows($result) . " persons named $name\n";
}

The output looks like this:

SELECT * FROM persons WHERE lastname = 'O\'Hara'
There are: 1 persons named O'Hara

As you can see, the SQL which I printed out has a properly “escaped” apostrophe in it. The function mysqli_real_escape_string should always be used to escape strings in cases like this.

Once you get to this point, you might think everything is hunky-dory and start writing all your code this way. However, it’s generally considered a really bad idea to write code like this, because it’s not safe against a SQL injection attack. To prevent this type of black-hat hacking on your website and database, you should always use prepared statements. It’s slightly more labor intensive, but it’s safe:

$sql = "SELECT * FROM persons WHERE lastname = ?";
$stmt = mysqli_stmt_init($conn);
mysqli_stmt_prepare($stmt, $sql);
mysqli_stmt_bind_param($stmt, 's', $name);
mysqli_stmt_bind_result($stmt, $id, $firstname, $lastname);
mysqli_stmt_execute($stmt);
$row = mysqli_stmt_fetch($stmt);
echo "Person $firstname $lastname was found\n";

The output here is Person Karen O'Hara was found.

As a final note, while MySQLi is fine to work with, IMHO, but you’ll also want to become familiar with PDO because it’s also quite popular (and different).

PS if you want to play around with the sample code above, you’ll need a MySQL database installed. Here are my dead simple database commands:

CREATE DATABASE testing;
USE testing;
CREATE TABLE persons (
    id INT AUTO_INCREMENT PRIMARY KEY,
    firstname varchar(128),
    lastname varchar(128)
);
INSERT INTO persons (firstname, lastname) VALUES ("John", "Smith");
INSERT INTO persons (firstname, lastname) VALUES ("Karen", "O'Hara");

If you found this interesting, click the subscribe button below! I write a new post about once a week.

How to comment your MySQL database

Technically, I’m not a database administrator. Practically, I write SQL statements on a regular basis. I also create new tables and add columns to existing tables with some frequency. And I’ve noticed that most people don’t “comment” their databases. This is probably fine for scratch projects that never make it to production and are only ever maintained by one person. However, if there’s the slightest chance that someone else might be maintaining the database that you created, it’s a good idea to comment your database, just as a developer will comment their code.

Here’s an example of how you do it in MySQL:

CREATE TABLE `prices` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'table index',
  `ticker` varchar(16) NOT NULL COMMENT 'stock ticker symbol, e.g. GOOG',
  `day` date NOT NULL COMMENT 'date of price info for this stock ticker',
  `open` DECIMAL(20, 8) DEFAULT NULL COMMENT 'opening price for this stock',
  `close` DECIMAL(20, 8) DEFAULT NULL COMMENT 'closing price for this stock',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='record of opening and closing prices for stock' AUTO_INCREMENT = 1000;

If you’re adding a column to a table, you can comment it at the same time, like this:

ALTER TABLE prices ADD COLUMN `volume` int(11) DEFAULT 0 COMMENT 'trading volume for this stock' AFTER `close`;

In my example, the columns and their names might seem obvious to people who are remotely savvy with finance. But just because you know something about finance, doesn’t mean the next dev will have this somewhat specialized knowledge. Your comment can help them find the information they need to understand what the column means and how to use it. Furthermore, in many business applications, database schemas may consist mostly of fields which only have meaning within the business itself. Comments on those fields can be a very handy source of documentation when the original developers are long gone.

As an aside, my prices table is only an example table for holding stock prices. You can see I really went overboard in allowing 8 decimal places for a stock price. Let’s just say I “future-proofed” it. Here’s a sample INSERT statement:

INSERT INTO prices (ticker, day, open, close) values ('GOOG', '2059-12-21', 401200022445.09787456, 401200023899.09787456);
SELECT * FROM prices;
+------+--------+------------+-----------------------+-----------------------+
| id   | ticker | day        | open                  | close                 |
+------+--------+------------+-----------------------+-----------------------+
| 1000 | GOOG   | 2059-12-21 | 401200022445.09787456 | 401200023899.09787456 |
+------+--------+------------+-----------------------+-----------------------+
1 row in set (0.00 sec)

And here’s the CREATE TABLE statement as viewed by a developer trying to figure things out:

SHOW CREATE TABLE prices;
...
| prices | CREATE TABLE `prices` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'table index',
  `ticker` varchar(16) NOT NULL COMMENT 'stock ticker symbol, e.g. GOOG',
  `day` date NOT NULL COMMENT 'date of price info for this stock ticker',
  `open` decimal(20,8) DEFAULT NULL COMMENT 'opening price for this stock',
  `close` decimal(20,8) DEFAULT NULL COMMENT 'closing price for this stock',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8 COMMENT='record of opening and closing prices for stock' |
1 row in set (0.00 sec)

If you found this interesting, click the subscribe button below! I write a new post about once a week.

How to create a scrollable list of items on an html page

If you have a long list of items, they can be displayed very easily using an HTML list.

Some people don’t like the way that looks, though, and they’d rather print out the list in one div after another, like this:

<div>Dashing through the snow</div>
<div>In a one-horse open sleigh</div>
<div>O'er the fields we go</div>
<div>Laughing all the way.</div>

That’s all great, but suppose your list is very long? Maybe most of your users don’t really care about the entire list, and will be satisfied to see just a few lines of it. You don’t want your users to have to scroll pages down to get past all the list items and see more content below it. It’s kind of a tricky problem, but one easy way to solve it is to follow a few simple steps:

(1) List all of your items in individual divs.
(2) Put all items into a containing div, and limit the height of that div to a fixed size.
(3) Then, style the containing div to set overflow: scroll.

The result is that your content is displayed inside a scrollable div. Click that link to see a demo page of how it works; you can take a look at the source code by right-clicking and choosing the view page source menu item. Here’s a screenshot of what it looks like:

If you found this interesting, click the subscribe button below! I write a new post about once a week.

PHP in_array or array_search vs JavaScript includes or indexOf

In my previous post, I’d created a bug in my PHP code by improper use of PHP’s array_search function. If you only write PHP, this kind of thing will probably never happen to you. I also write Java and JavaScript code, and at some point, your memory banks overflow, and you forget stuff!

JavaScript has a couple of different methods for searching through an array to find an item. Let’s use the array method includes to search for an item in JavaScript, just like I did in my PHP example.

var foods = new Array();
foods.push('ice cream');
foods.push('hamburger');
foods.push('brussels sprouts');

var do_important_things = function() { console.log('Important!'); };

if (foods.includes('ice cream')) {
    do_important_things();
}
...

If you print out foods, you can verify that ‘ice cream’ is located at index 0. The code foods.includes('ice cream') returns true because that string is found in the array. No confusion there – a boolean is returned, not an index.

If I’m doing a more complicated comparison, I might use JavaScript’s some, like this:

if (foods.some(function(el) { return el.toLowerCase() === 'ice cream'; })) {
    do_important_things();
}

The some method loops over all elements in the array until the callback method returns true, at which point it stops, and returns true. If no item is found which satisfies the condition in the callback, then false is returned. Again, since a boolean is returned and not an index, there’s no room for error.

There’s a JavaScript method which is the equivalent of the PHP array_search function: indexOf. It returns the index of the matching item, if found, and otherwise returns -1. Here’s an example:

if (foods.indexOf('ice cream')) {
    do_important_things();
}

Oops, this method of searching exhibits the same bug as array_search in PHP! In my opinion, the indexOf JavaScript method is more appropriately named – it makes it clear that you are going to get an index as a return value. So it makes it less likely that the all-too-human developer will create the bug that I demonstrated in my previous post.

Now, when researching this topic, I found that PHP has a function that is more appropriate than array_search for my use case. It’s the in_array function. It returns TRUE if the item being sought is found in the input array. That’s really what I wanted! Here’s the example code that I used in my previous post, only now it uses in_array:

$foods = Array();
$foods[] = 'ice cream';
$foods[] = 'hamburger';
$foods[] = 'brussels sprouts';

function do_important_things() {
    echo 'Important!';
};

if (in_array('ice cream', $foods)) {
    do_important_things();
}

In this example, do_important_things is called.

If you found this interesting, click the subscribe button below! I write a new post about once a week.

The Coding Blacklist. No 52: PHP array_search

PHP has a few “gotchas” which have bitten me in the past. I just got bitten again, today!

I used this PHP code:

$foods = Array();
$foods[] = 'ice cream';
$foods[] = 'hamburger';
$foods[] = 'brussels sprouts';

function do_important_things() {
	echo 'Important!';
};

if (array_search('ice cream', $foods)) {
	do_important_things();
}

I banged my head against the wall for a few minutes when the do_important_things function never got called. I printed out the $foods array and could see that 'ice cream' was in it. So, array_search should return true, right?

So what was going on? Go figure it out for yourself if you want…. or scroll down to get the quick answer.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

PHP’s array_search returns the key of the first value that was matched in the array. Since 'ice cream' had key zero (first item in the array), 0 was returned. In PHP, 0 evaluates to FALSE when it is used in a boolean sense. Like here. They kind of warn you about that in the array_search documentation, but who looks up the docs every time they use an array function?

I kind of went ‘Doh!’ when this happened, because I’ve had this experience once before. I’d just forgotten it. This is a case where an editor plugin might help, but I don’t know of any good solution, except to be careful when using PHP array functions.

If you found this interesting, click the subscribe button below! I write a new post about once a week.