MySql SQL Mindset Tutorial

Posted on by
MySql SQL Mindset Tutorial

MySql SQL Mindset Tutorial

We spend a lot of the working time with this blog in a “what we’d describe as” a …

Javascript mindset

… which “we’d describe as” …

This optimistic, and can be chaotic for choice, “anything’s possible”, but is “syntactically logical”, mindset, where Javascript is this “sponge of the Internet” not missing out on anything?! Sound familiar?! Huh?!

With yesterday’s WordPress Same Action Item Tutorial though, we got a bit of a lesson bringing that “Javascript mindset” into an issue where “MySql SQL” was the duck doing all the paddling coming up with a solution to that “Same Cut to the Chase Action Item” dropdown idea.

We found out, probably four hours later …

Regarding MySql SQL work (which we debug and “live in” the great incredible stupendous phpMyAdmin environment wooooorrrrlllllldddd regarding) just because an SQL statement looks “syntactically logical” does not always guarantee success.

Take a look at …

What did not work Error Message

SELECT wps_posts.post_title, wps_posts.guid, wps_posts.post_content, LOCATE('/PHP/animegif/tutorial_to_animated_gif.php',wps_posts.post_content) as tfind, LOCATE('/PHP/animegif/tutorial_to_animated_gif.php',wps_posts.post_title) as tother, wps_posts.post_date as post_date FROM wps_posts
WHERE wps_posts.post_status = 'publish'
AND (1 = 11 OR LOWER(wps_posts.post_title) = LOWER('/PHP/animegif/tutorial_to_animated_gif.php'))
AND (wps_posts.post_content like CONCAT(CONCAT('%?p=',wps_posts.guid), '%')
OR SUBSTRING(wps_posts.post_content,1,240) like '%/PHP/animegif/tutorial_to_animated_gif.php"%') UNION SELECT 'Code Download Table' as post_title, wps_posts.post_content as guid, '' as post_content, 1 as tfind, 0 as tother, CURTIME() as post_date FROM wps_posts
WHERE wps_posts.post_status = 'publish'
AND LOWER(wps_posts.post_title) = LOWER('/PHP/animegif/tutorial_to_animated_gif.php')
AND wps_posts.post_content like '%GETME%' ORDER BY post_date
 		MySQL returned an empty result set (ie. zero rows). (Query took 5.8648 seconds).
Versus what did work

SELECT wps_posts.post_title, wps_posts.guid, wps_posts.post_content, LOCATE('/PHP/animegif/tutorial_to_animated_gif.php',wps_posts.post_content) as tfind, LOCATE('/PHP/animegif/tutorial_to_animated_gif.php',wps_posts.post_title) as tother, wps_posts.post_date as post_date FROM wps_posts
WHERE wps_posts.post_status = 'publish'
AND SUBSTRING(wps_posts.post_content,1,240) like '%/PHP/animegif/tutorial_to_animated_gif.php"%' UNION SELECT 'Code Download Table' as post_title, wps_posts.post_content as guid, '' as post_content, 1 as tfind, 0 as tother, CURTIME() as post_date FROM wps_posts
WHERE wps_posts.post_status = 'publish'
AND LOWER(wps_posts.post_title) = LOWER('/PHP/animegif/tutorial_to_animated_gif.php')
AND wps_posts.post_content like '%GETME%' ORDER BY post_date

… as a case in point?! We tried this ask (can mysql sql return zero rows just because of the overly complex where clause?) of Google to try to find out why, regarding “relative database” mindsets this might be so …

Yes, a complex WHERE clause in MySQL can cause a query to return zero rows if it inadvertently filters out all data. This often happens due to logical errors, such as conflicting conditions, NULL values treated as equal to other values, or using NOT IN with subqueries that include NULL.

Interesting, huh?!

Perhaps the KISS principle should apply here, especially if you are doing MySql SQL outside the original indexing database design thoughts ( as we were needing to use here with the MySql SQL function SUBSTRING ). Also, if we’re into the “blame game” here, question whether a solution you are achieving is inefficient, and wasteful, before applying it as your final offering!

If this was interesting you may be interested in this too.

This entry was posted in Database, eLearning, Tutorials and tagged , , , , , , , , , , , , , , , , , , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *