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

The WHERE statement. You can have 2 of them, just put an AND.

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

web security linux ubuntu GIT svn Raspberry apache mysql php drupal cake symfony javascript Ajax css