Sample SQL query select and join

Posted under » PHP » MySQL on 23 August 2012

Pretty basic.

"select * from newevents where eventstarttime = '2012-03-24'"

The AND statement. Similar with the OR statement.

"select * from newevents WHERE eventstarttime > '2012-03-24' AND eventstarttime < '".$hedate."'"

The WHERE statement = sign requires that it is exact. This could pose a problem if your field is a varchar. There could be trailing spaces so in some cases, it would be better to use the LIKE statement.

The LIKE statement.

"select * from heh where title LIKE '%$filter%' AND title NOT LIKE '%$exclude%'"

The JOIN statement.

"SELECT dvdtag.aid AS aids, dvdtag.did, director.name AS dir,
dvd.name AS title FROM dvdtag INNER JOIN director ON dvdtag.aid=director.aid
INNER JOIN dvd ON dvdtag.did=dvd.did WHERE dvdtag.aid = $p"

You will notice that inner joins can be more than one. The WHERE clause is before the ORDER. ORDER is the last. There can be more than 1 order separated by comma. It is assumed that the order is in ascending (ASC) order.

PHP is forgiving in joins. A newbie mistake is when you try to join to a table that does not exist in the database.

The WHERE statement. You can have 2 of them, just put an AND. Another newbie mistake is that we put ambigous id instead of a definitive id. This (aid) is wrong and this (dvdtag.aid) is correct.

"SELECT A.email FROM dvdtag A, director B WHERE B.dir_uid = A.dvd_uid AND B.dir_delete = 'N'"

The ORDER statement. ASC is the default.

"SELECT A.email FROM dvdtag A, director B WHERE B.dir_uid = A.dvd_uid AND B.dir_delete = 'N' ORDER by A.email, B.dir_uid DESC"

The LIMIT statement. Probably the last statement.

"SELECT A.email FROM dvdtag A, director B WHERE B.dir_uid = A.dvd_uid AND B.dir_delete = 'N' ORDER by A.email LIMIT 2"

See an earlier article or a newer article.