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.