Home arrow PHP arrow Page 5 - Website Database Basics With PHP and MySQL

Weird SQL: What The Books Don't Tell You - PHP

The World Wide Web (WWW) does only one thing - provide information. If you have information about something, you can share it with the world by building a website. As your website grows you may run into two problems: Your website has so much information that visitors can't quickly find what they want and visitors want to give you information. Both of these problems can be solved by building a database on a website. This introductory article shows you how to do this using basic PHP-MySQL interaction.

TABLE OF CONTENTS:
  1. Website Database Basics With PHP and MySQL
  2. HTML talks to PHP talks to MySQL
  3. Verifying form data
  4. Using cookies to identify and track visitors
  5. Weird SQL: What The Books Don't Tell You
  6. Checkboxes and other HTML form processing
By: Thomas Kehoe
Rating: starstarstarstarstar / 100
January 11, 2000

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement
SQL is supposed to be like English. You just tell your database what you want, and it finds it, right? Well, there's a few surprises…
  1. The trailing semi-colon, or lack thereof
  2. Datatypes
  3. Wild cards
  4. NOT NULL and empty records

The trailing semi-colon, or lack thereof

The first thing about MySQL you learn is that every line ends with a semi-colon (;). Well…there are (at least) two exceptions.

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


This is done because PHP lines also end with a semi-colon, so an extra semi-colon might confuse the PHP parser. You leave off the semi-colon, and PHP automatically puts it back in for you.

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

MySQL fields must have a datatype specified. There are about 25 choices. Most are fairly straightforward. Some details to remember:

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



It's simple to subtract a stored date (e.g., someone's birthdate) from the current date:



$age = ($current_date - $birthdate);



SET is a useful datatype. It's like ENUM except that it'll store multiple values. Also, it can only take up to 64 predefined (enumerated) values, when ENUM can handle up to 65,535 predefined values. But if you need more than 64 values, it's easy to divide your list into two or more columns. More about the SET datatype in the checkboxes chapter.


Wildcards

SQL sometimes uses the asterisk (*) as a wildcard, and sometimes use a percent sign (%). E.g., suppose you want to see all of the records in your database:


SELECT * FROM dbname WHERE USER_ID LIKE '%';


Yes, I know that SELECT * FROM dbname; will work just as well. My point is that there are two wildcards, meaning the same thing but used in different contexts, for no reason I can see.

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."


NOT NULL and empty records

What happens when a user leaves a field blank? If you require a value in the field, you wrote a verification script requiring a value. But some fields are OK to leave empty. MySQL will do any of three things:
  • 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".
The difference between NULL and an empty record is that the % wild card finds empty records, but doesn't find NULL records. I have yet to find a situation where the latter result is desirable. In my experience, all columns should be declared NOT NULL. Then the following SELECT query works:


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


The first line says that if the user doesn't specify a city, the % wild card is used for the search, to find any city, or empty CITY records.

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


Note that to search for NULL you must use IS. = or LIKE will not find NULL values.

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.



 
 
>>> More PHP Articles          >>> More By Thomas Kehoe
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

PHP ARTICLES

- Hackers Compromise PHP Sites to Launch Attac...
- Red Hat, Zend Form OpenShift PaaS Alliance
- PHP IDE News
- BCD, Zend Extend PHP Partnership
- PHP FAQ Highlight
- PHP Creator Didn't Set Out to Create a Langu...
- PHP Trends Revealed in Zend Study
- PHP: Best Methods for Running Scheduled Jobs
- PHP Array Functions: array_change_key_case
- PHP array_combine Function
- PHP array_chunk Function
- PHP Closures as View Helpers: Lazy-Loading F...
- Using PHP Closures as View Helpers
- PHP File and Operating System Program Execut...
- PHP: Effects of Wrapping Code in Class Const...

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: