Category Archives: MySQL

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");

Got comments? Send them to me in an email at fullstackdev@fullstackoasis.com. If you found this interesting, you might want to hit the subscribe button above. 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)

Got comments? Put them in your database! Or, send them to me in an email at fullstackdev@fullstackoasis.com. If you found this interesting, you might want to hit the subscribe button above. I write a new post about once a week.

How to access an AWS RDS using JDBC in your Android app – Part I

You’ve got a huge spreadsheet that has a lot of data in it, and you’ve built an Android app which works like a search engine on the data. Nice! But there’s a problem: when you build your app with all of that data in it, the APK is huge! You want to reduce the size of the app. And you also want to offload the search functionality onto a relational database, which is probably going to provide a more efficient search. How do you start?

This blog post explores one way to do it. It’s “quick and dirty”, and it’s not recommended to do things exactly this way. I’ll talk about why in Part II. But this method will give you a start.

Here’s a quick sketch of the idea: You put your data in the cloud using the Amazon Relational Database Service (RDS). Then you add JDBC calls to your app to access the cloud. It’s pretty quick. Here are the steps, using a simple example that I tried for myself.

Technical Details: My development environment runs Ubuntu 16.04, and I have a MySQL client and the MySQL database already installed on my local machine. I use Android Studio 3.5 IDE for building Android apps. Also, I have an Amazon AWS account set up already. You can follow this tutorial if you don’t have any of that, but then specific steps will differ for you.

Get Your Data Source Ready

For my data source, I downloaded some food inspection data from healthdata.gov in a csv (“comma-separated values”) format. I opened the csv file in a spreadsheet, selected some of the columns that I wanted, and exported them to another file, also in csv format. You can use the csv file that I generated by starting with this small, truncated version of the data. Later, you can use or create your own, very large data source for experiments.

Create an Amazon RDS MySQL Database

Visit the Amazon MySQL RDS page and click “Get Started”. If you don’t have an AWS account, you will need to sign up for one, first. Check out the pricing, if you are worried. There’s a free tier, great!

If you’re already signed in, another way to get started is to visit the AWS Management Console, search for “RDS”, and click the result for “Managed Relational Database Service”.

At this point, you’ll see a “Create Database” button. Choose “MySQL”, and click the “free tier”. Type in healthdata-1 for the name. Choose a username when requested. I’m using fullstackdev. Pick a secure password. The other parts of the form are straightforward. You can think about using IAM based authentication later. For this proof-of-concept piece of work, let’s keep it simple, and use password based authentication. For the rest, accept all defaults.

At this point, a page opens which says the database is being created.

AWS RDS creating database

Click the “modify” button. You’ll see that you can modify various things about the database later, if you want. Just be aware of this. For right now, you’ll need to “modify” the RDS so that it can be accessed from external sources – so choose “Public accessibility” and set it to Yes, and make sure to click the “Continue” button at the bottom of the page to save your changes. You need to do this so that you can create a database, load data into it, and access it via JDBC.

Now we’ve got an RDS in the cloud, and it’s accessible from our home environment. Next, we need to create a database.

Create Your Database and Manage Access

If you click the DB identifier in your RDS console, you will see an area called “Connectivity & security”. That area tells you what your endpoint is, and what your port is. The port defaults to 3306. Your endpoint will be something like healthdata-1.c84gpzpanfrn.us-east-1.rds.amazonaws.com. This is a URL you can use to access the database from another machine.

In the ‘Security’ pane, at the right, you will see your VPC (Virtual Private Cloud) security groups with a link to the default. Click that. It will take you to your Security Groups area. The default VPC security group should be preselected. Look at the bottom panel, where you should see the “Description”, “Inbound”, “Outbound”, and “Tags” tabs. Click “Inbound” and hit the “Edit” button. Click the “Add Rule” button, select MySQL/Aurora, make sure that the protocol is set to TCP/IP and the port to 3306, thne choose “MyIP” as the source. Your IP address will be set when doing this. Then hit the “Save” button.

Remember that you’ve added this rule just for your own IP address! You’re doing this for test purposes. Later, if you want, you can make different inbound rules, but this setup is good for a proof-of-concept.

Now the RDS is accessible. I am comfortable using the command line for MySQL client, so I used this to step into the cloud, and create my database. You can use whatever tool you want to do this.

First, I connected via this command:

mysql -u fullstackdev -P 3306 -p -h healthdata-1.c84gpzpanfrn.us-east-1.rds.amazonaws.com healthdata-1

The -p option tells the client to ask for a password interactively. I gave the password that I had set up earlier, and immediately, I was connected. This is what I saw:

Type: MySQL/Aurora,
Protocol: TCP
Port Range: 3306
Source: MyIP
Description: MySQL client

show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| innodb             |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.03 sec)

It’s the usual default MySQL database setup.

I had already designed a database around the food inspection data that I had decided to import. I created my own database like this:

CREATE DATABASE food_inspections;
USE food_inspections;
DROP TABLE health_reports;
CREATE TABLE health_reports (
    id INT AUTO_INCREMENT PRIMARY KEY,
	inspection_id INT,
	dba_name TEXT,
	aka_name TEXT,
	license_num INT,
	facility_type TEXT,
	risk TEXT, address TEXT,
	city TEXT, state TEXT,
	zip TEXT, inspection_date DATE,
	inspection_type TEXT, results TEXT,
	violations TEXT, location TEXT
);

I didn’t add any indexes for the columns other than the primary key. That can all be added later, when performance tuning.

Push Your Data to Amazon RDS MySQL Database

AWS provides instructions for pushing data to a MySQL RDS in the cloud. Since we have a new RDS which is already set up, we can skip straight to step 5, “Load the Data”.

They tell you to use the mysqlimport command, and you can do that if you want. There are other tools that can be used to import data, too. However, since I was already in the MySQL client, I used the LOAD DATA command, like so:

LOAD DATA LOCAL INFILE 'Food_Inspections_small.csv' INTO TABLE health_reports
	FIELDS TERMINATED BY ',' ENCLOSED BY '"'
    LINES TERMINATED BY '\n' (@inspection_id, @dba_name, @aka_name,
		@license_num, @facility_type, @risk, @address, @city, @state, @zip,
		@inspection_date, @inspection_type, @results, @violations, @location)
	SET inspection_id = @inspection_id, dba_name = @dba_name, aka_name = @aka_name,
		license_num = @license_num, facility_type = @facility_type, risk = @risk,
		address = @address, city = @city, state = @state, zip = @zip,
		inspection_date = @inspection_date, inspection_type = @inspection_type,
		results = @results, violations = @violations, location = @location;

Keep in mind that you may need to modify this command for your own purposes. I had launched the MySQL client from within the same directory where my Food_Inspections_small.csv was located, , so this command worked for me straightaway.

Now, my RDS is all set up, complete with data! That is half the battle. In my next blog post, I’ll cover how to access the RDS using an Android app.

Got comments? Send me an email at fullstackdev@fullstackoasis.com. Interested in more posts like this? Hit the subscribe button above. I write a new post about once a week.

How to fix that PHP bug using error logging

Imagine you’ve got 20 lines of PHP code. There’s an UPDATE SQL statement deep within some nested logic statements. That UPDATE is just not working, even though you expect it to. You know that because you check the database, and the values in your table are not being updated.

The code looks like this:

<?php
...
if ($blah == 0) {
    ...
    $banana = !empty($_POST['banana']) ? $_POST['banana'] : '';
    $coconut = $_POST['coconut'];
    ...
    if (!empty($banana)) {
        $res = $db->query("SELECT ... SOME SQL THING");
        ...
        if ($res->num_rows > 0) {
            // avoid duplicates
            exit();
        } else {
            $insert = $db->query("INSERT INTO .... MORE SQL");

            if ($insert && !empty($coconut)) {
                $update = $db->query("UPDATE ... ANOTHER TABLE");
                // Gnash!! The UPDATE does not take place as expected!
                ...
// etc.
?>

How do you debug something like this? Here’s my process.

First, I look at the PHP logs. If error logging is enabled, it may be that some error is being logged which points to the problem right away. In that case, I might not have to do any further debugging. If I’m lucky, the fix is quick and easy! Maybe I’ll see some SQL error, like Undefined index or foreign key constraint fails, that makes the problem clear.

But suppose error logging is disabled. Then I enable it, at least for this script.

To do that, I add error_reporting(E_ALL); to the top of the script. Then, as a good test, I also add error_log('bananas'); to the top of the script, run the script, and check to make sure that my comment, “bananas”, was logged in the expected error log file. If it wasn’t, I’ll need to hunt down the file where errors are being logged! I won’t go into that here, though. Let’s assume I’ve found my error log, and I can print statements to it.

If I don’t see any errors in the log, I add a bunch of logging statements to figure out exactly where in the code things go wrong. Look at the code above, again. There are so many possible ways that the UPDATE statement might not have run – every logic branch needs to be tested to see where execution stops. If I expect that my UPDATE statement is definitely being run, I can log a short comment just before that, run the code, and check to see if that comment appears in the logs.

If my framework already has some form of logging enabled, great, I might just use that to add debugging comments. If not, that’s okay. As mentioned above, PHP comes with built-in error logging. I might add something like error_log("before UPDATE"), run the script, and check to see if my comment appears in the error log. If I don’t see “before UPDATE” in the logs, I’d probably add a bunch of error_log statements immediately after every branch in the logic: after each if and else statement in the code above.

Usually, by adding logging statements, I can quickly figure out where the problem occurs. Where does execution stop? Then, I add more debug statements to figure out why, maybe printing out variables.

A word of caution: before adding logging, I often copy the file that I’m going to be working on to a safe place. I do that something like this: cp -p myfile.php myfile.php.2019-10-21 (the -p option to cp preserves the file’s mode, permissions, and timestamps). This is true even if I’m working on code that is checked into a version control system such as git. It’s just a really quick way to be able to revert my changes, if needed.

There are other things that I might do to figure out what’s causing this bug, too. If the source code has a test framework in place, it can be super helpful to add tests which exercise the code. That way, I can isolate the buggy code outside of the website, and test if it works on its own. Usually, when a bug is reported, I do like to add at least one test which exhibits the error by failing, and which then passes when the bug is fixed. But I don’t always have the luxury of working with source code that has a test framework set up.

Note: I’m usually working in a Linux environment, so YMMV with any commands (cp) mentioned above.

Got comments? Send me an email at fullstackdev@fullstackoasis.com. I value your feedback! If you found this interesting, you can hit the subscribe button above. I post new content about once a week.

How to migrate PHP mysql to mysqli extension

Recently, I had a to migrate a website that was running PHP 5.6 over to PHP 7. As usual, the first thing I did was to just copy the site over to the new host, and check the logs to see what the errors were. I saw a ton of messages like this:

PHP Fatal error:  Uncaught Error: Call to undefined function mysql_connect()
...
PHP Fatal error:  Uncaught Error: Call to undefined function mysql_query()
...
PHP Fatal error:  Uncaught Error: Call to undefined function mysql_num_rows()
...

I was really lucky! The main problem was that the site relied on database extensions for MySQL that have been deprecated since PHP 5.5, and were removed in PHP 7.

This turned out to be a pretty easy fix. You can check out the documentation for the new MySQLi improved extension over at PHP.net. Here are the steps that I took to rework the code so that the site functioned normally, again.

mysql_connect and mysql_select_db

The previous connection method was mysql_connect, called like this:

$conn = mysql_connect(mysql_server_name, username, password);

I replaced that with this:

$conn = mysqli_connect(mysql_server_name, username, password, database_name);

Since I could pass in the database name, I could then throw out an old call to mysql_select_db as well.

mysql_real_escape_string

mysql_real_escape_string($str) was replaced with $conn->real_escape_string($str). I could have also used mysqli_real_escape_string($str).

mysql_query

$results = mysql_query($sql_query) was replaced with $results = $conn->query($sql_query).

mysql_num_rows

mysql_num_rows($results) was replaced with $results->num_rows.

mysql_fetch_assoc

$row = mysql_fetch_assoc($results) was replaced with $results->fetch_assoc().

mysql_error

$err = mysql_error($conn) was replaced with $err = $conn->error.

mysql_close

mysql_close($conn) was replaced with $conn->close().

Aside from these problems with using the old MySQL extension, I noticed one other problem. There were a few messages in the PHP logs, like this:

PHP Notice:  Undefined variable: blah

This happened when the variable had never been defined before it was used. For example, I saw code like this:

$myArr = getSomething($blah);
...

The variable $blah was only found once in that source code – it had never been initialized. So I just initialized it to NULL, and the notice went away. Probably it was a typo or copy/paste error. I also double-checked to make sure that NULL was an acceptable input, and not a lurking bug.

The PHP notice message didn’t do much harm, but it was cluttering up the logs, making it hard to see the real problems, so getting rid of it was a good thing. As a general rule, I like to get rid of messages like this. That way, if something goes wrong, it’s easier to find new problems by checking the logs.