Using IN operator Example using an array
Category: SELECT
Ternary Operator if( , , )
if(Condition, ConditionIsTrue, ConditionIsNotTrue) SELECT depending on condition UPDATE depending on condition Example – Increment value if below # or reset to 1
DateTime SELECT
Date or DateTime from strings Get time difference between now and a DateTime field Argument can be: If DateTime value is Null? You will get a Null value returned from TIMESTAMPDIFF Specifying Returned Date Format Get SQL Server’s Current DateTime Same as Now() but giving just the date:
Null
WHERE statements UPDATE statements
Count – Get number of results
How many rows in a table Count and Group Combined List the number of customers in each country Adding values returned as a the total Use SUM()
GROUP BY
Get a single result for each MyColumnName value that matches the search criteria For example, say you have multiple rows where MyColumnName=1 that each matches the query, you’ll get back just one result with MyColumnName=1 Same but with an additional field that tells you how many rows matched for each MyColumnName COUNT and GROUP BY […]
Strings
Null or Empty Strings An empty field can be either an empty string or a NULL. Is field is not empty AND not null (i.e. it has an actual string in it): WHERE field_name > '' Is field is empty OR null: WHERE IFNULL(field_name, '') = ''
Maths based results
This is PHP4 Code! Get Number of Results $query1 = @mysql_query("SELECT COUNT(*) FROM some_table WHERE some_column != 1"); $number_of_rows = @mysql_fetch_array($query1); $number_of_rows = $number_of_rows[0]; echo "This many: $number_of_rows"; Results Based On An Average Score Calculation $query1 = @mysql_query("SELECT university_name, CONVERT((score / games_played), UNSIGNED INTEGER) as ave_score FROM tbl_universities ORDER BY ave_score DESC, university_name ASC"); […]
DateTime general
ORDER BY Date Order ASC = oldest first DESC = most recent first Note it’s “MINUTE”, “HOUR”, etc not “MINUTES” or “HOURS” etc Convert DateTime to Date Specifying Date and DateTime from a string It is recommended to use CAST() Last Day Of Month (This produces a DATE result, not DATETIME) Get SQL Server’s Current […]
LIKE to search within text fields
This is PHP4 Code! Simple Example foreach ($search_tags_array as $tag_word) { $sql = "SELECT indexer FROM videos WHERE indexer != '$vid' AND title LIKE '%$tag_word%'"; $query = @mysql_query($sql); while ($result1 = @mysql_fetch_array($query)) $tmp_result_search[] = $result1[‘indexer’]; } Multiple LIKE tests " WHERE (title LIKE '%$keyword%' OR tags LIKE '%$keyword%' OR description LIKE '%$keyword%')"