Ternary Operator if( , , )

if(Condition, ConditionIsTrue, ConditionIsNotTrue) SELECT depending on condition UPDATE depending on condition Example – Increment value if below # or reset to 1

Read More

DateTime SELECT

Get time difference between now and a DateTime field Argument can be: MICROSECOND SECOND MINUTE HOUR DAY WEEK MONTH QUARTER YEAR If DateTime value is Null? You will get a Null value returned from TIMESTAMPDIFF Specifying Returned Date Format

Read More

GROUP BY

COUNT and GROUP BY in same query Good example: https://www.w3resource.com/mysql/aggregate-functions-and-grouping/aggregate-functions-and-grouping-count-with-group-by.php Getting total count of results based on multiple groupings This will return an individual TotalCount result for every combination of MyRow1 and MyRow2 found. If you add TotalCount together for all of the results returned you’ll get the total as if the GROUP BY was […]

Read More

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, '') = ''    

Read More

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"); […]

Read More

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 […]

Read More

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

Read More

Manipulating Returned Results

$sql1 = "SELECT indexer, user_id, friends_id AS from_id, //Change result name to from_id 'friend' AS type, //For each record found add a result called 'type' containing 'friend' todays_date FROM friends WHERE friends_id = $user_id"; Outputting Results As A Comma Separated List (Or any character separated) $QueryString = "SELECT CONCAT_WS(',',SomeColumnName0,SomeColumnName1,SomeColumnName2) FROM MyTable WHERE SomeColumn = 12"; //"CONCAT_WS(','," […]

Read More