Basic MySQL query finding mismatched results

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Basic MySQL query finding mismatched results

Post by simonmlewis »

Code: Select all

$q=$_GET["q"];
include "dbconn.php";

$result = mysql_query ("SELECT id, photoprimary, title, price, category FROM products WHERE (title LIKE '%$q%' OR description LIKE '%$q%') AND pause = 'off' AND rcstock = 'in stock' LIMIT 50")or die(mysql_error());
If I pass the word "cats" through to this, it should be producing 21 results.
If I enter this query into phpmyadmin, it does.

But when I use this via AJAX, it produces 26 results. The most bizarre part of this problem tho is that it produces results WITHOUT the word CATS anywhere in the page.

For example, if it comes up with a result that shouldn't be there, I click it to open that product and search the page for "cats" and the only place it finds the word is in the 'include.inc' menu, not the MySQL database results rendered on the page.

Where have I cocked up??

EDIT: Does it make a difference if in the description field of a result it says "Stoppers are designed" and you've entered "red" ??
"stoppers aRE Designed"...???
S.
Last edited by simonmlewis on Fri Nov 19, 2010 3:42 am, edited 1 time in total.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
Neilos
Forum Contributor
Posts: 179
Joined: Fri Nov 19, 2010 2:07 am

Re: Basic MySQL query finding mismatched results

Post by Neilos »

Hello,

Could it just be that it is returning results with words that contain the word cats (as this is that the % wildcards do) also I don't know how the LIKE operator treats spaces because potentially you could have a result returned for;

Marc at Sandra's house.

Just a thought. :)

Edit*

Hmmm just thought you would be entering the same query into phpMyAdmin wouldn't you. And therefore expect the same, I see.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Basic MySQL query finding mismatched results

Post by califdon »

If it's the same database and the same query, the results should be identical, so I would say that there is something that is not the same, although it appears to be, to you. You may have something slightly different in your SQL when you pass it to Javascript and on to the server. You can either show us your Ajax code and the PHP in the receiving script, or you can echo out the SQL from the script that receives the request, and look for the difference yourself. Something is definitely different. The same SQL will always produce the same results from the same data, believe me!

No, the LIKE operator matches exactly the characters between the delimiters (%), other than case, so a space is treated just like any other character. So "cats" will not match "cat s".
Post Reply