Website Database Basics With PHP and MySQL - Weird SQL: What The Books Don't Tell You

In the section PHP submits data to MySQL I pointed out that when a MySQL line is part of a PHP line, the semi-colon at the end of the MySQL line of omitted. For example:

mysql_query ("INSERT INTO tablename (first_name, last_name) 
VALUES ('$first_name', '$last_name')
");

As I wrote, the weird part is that SELECT and INSERT will work with or without the extra semi-colon, but UPDATE won't work. SELECT and INSERT are the first MySQL functions you use, so you're happily coding with both semi-colons, and then when you want to UPDATE a record everything stops working. I haven't experimented with DELETE, which is the fourth SQL command.

The other time you don't use a semi-colon is when you want to see all the fields (what SQL calls "columns") displayed vertically down your monitor, instead of horizontally across your monitor. With a terminal emulator (at least with my old terminal emulator) you have a choice of 80 or 132 columns (of characters), but you can't scroll over to see stuff farther to the right. So you end the SQL line with \G instead:

SELECT * FROM PENPALS
WHERE USER_ID = 1\G

TEXT, DATE, and SET datatypes

TEXT is not a datatype, despite what some books tell you. The datatype is called LONG VARCHAR or MEDIUMTEXT.

For VARCHAR weirdness, see the section on wildcards.

The DATE dataset formats dates as YYYY-MM-DD, e.g., 1999-12-08. This is logical because we write numbers with the biggest (e.g., millions) to the left, then smaller numbers (e.g., thousands, hundreds, tens, ones) progressively to the right. You can retrieve the current date, in the same format with the PHP function

date("Y-m-d")

$age = ($current_date - $birthdate);

SELECT * FROM dbname
WHERE USER_ID LIKE '%';

Another non-obvious thing is that the % wildcard requires using LIKE. It won't work with =.

There's another wildcard, with a different meaning. The underscore (_) means "any single character."

• Insert the value NULL. This is the default action.
• If you declared the column NOT NULL (when you created the column, or by modifying the column), MySQL will leave the record empty.
• In an ENUM datatype, if you declared the column NOT NULL, MySQL will insert the first value of the enumerated set. In other words, MySQL treats the ENUM datatype as if you declared the first value to be the DEFAULT value. To work around this weirdness, make the first value a pair of single quotes (''), which means " empty set".

if (!$CITY) {$CITY = "%";}
$selectresult = mysql_query ("SELECT * FROM dbname
WHERE FIRST_NAME = 'Bob'
AND LAST_NAME = 'Smith'
AND CITY LIKE '$CITY'
");

If every record contains a city, then the query returns all Bob Smiths in your database. If some CITY records are empty, the query also returns all Bob Smiths in your database. But if some CITY records contain NULL, your query won't return the Bob Smiths with a NULL value in the CITY column.

Can we solve that problem with:

if (!$CITY) {$CITY = "%";}
$selectresult = mysql_query ("SELECT * FROM dbname
WHERE FIRST_NAME = 'Bob'
AND LAST_NAME = 'Smith'
AND (CITY LIKE '$CITY' OR CITY IS NULL)
");

If the user enters "Altoona" for the city, the query returns every Bob Smith in Altoona, and every Bob Smith with NULL in the CITY field. That isn't what the user wanted. It'd better to declare every column to be NOT NULL and avoid this problem.

One last pitfall to watch out for. If you add (or modify) columns after some records are already in your database, you may get a mixture of NULL and empty records. This is certain to screw up your SELECTqueries.