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%')"
Category: SELECT
LIMIT Results
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(','," […]
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"); […]
Null
WHERE statements UPDATE statements Is result Null?
ORDER BY
Sorting Results Use this To Sort On Multiple Values ORDER BY With $wpdb->prepare ORDER BY and %s does not work with $wpdb->prepare, in our tests the following order by gets ignored: Whereas this works as expected. So it seems that $wpdb->prepare() adding single quotes around the inserted name field causes it to break. Date Order […]
Querying Across Tables
This is PHP4 Code! Using IN Within A Normal SELECT Query You can select from within another tables results really easily like this: SqlCommand1->CommandText = "SELECT ProjectId \ FROM tblMyTable1 \ WHERE ProjectIsActive='TRUE' AND ProjectId IN (SELECT ProjectId FROM tblMyTable2 WHERE ProjectUsersAll='TRUE' OR UserId=@UserId) \ ORDER BY ProjectStartDate DESC"; Simple Find Name From Another Table […]
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, '') = ''
Ternary Operator if( , , )
if(Condition, ConditionIsTrue, ConditionIsNotTrue) SELECT depending on condition UPDATE depending on condition Example – Increment value if below # or reset to 1