|
|
Posted by Miguel Cruz on 07/29/06 17:59
"Frankie" <frankie66@earthlink.net> wrote:
>>> $query =
>>> sprintf ("SELECT itemNumber, thumbnailURL, title, description, price
>>> FROM apparel,hats
>>> WHERE apparel.title OR apparel.description OR hats.title OR
>>> hats.description
>>> LIKE '%s'",
>>> $_POST['tfSearch']);
>
> Only now it appears I need a new approach. This query is producing bizarre
> results (1225 records, when there aren't near that many rows in my tables).
> I tried using DISTINCT after SELECT, but same result.
There are two fundamental problems with your SQL.
1) When you select from two tables like that, without specifying a JOIN
clause or any WHERE clause that links the two, you will get many more
results than you might expect. You get all the results that match for
table 1 (apparel), and all the results that match for table 2 (hats),
and you get every combination of those matches. For instance, if apparel
matches on "shirt", "socks", and "pants", and hats matches on "fedora"
and "bowler" then you will get one result row for each of these
combinations:
shirt fedora
shirt bowler
socks fedora
socks bowler
pants fedora
pants bowler
When all you really wanted was
shirt
socks
pants
bowler
fedora
In your case, you need to do a union query or do separate queries.
2) Your WHERE is not doing what you think it is. When you write "where
apparel.title or apparel.description or hats.title or hats.description
like '%s'", the only thing that is actually getting compared to
$_POST['tfSearch'] is hats.description. For apparel,title,
apparel.description, and hats.title, it is only checking that they do
not contain a value that equates to "false". So basically any value for
those fields will be a match, regardless of what $_POST['tfSearch']
contains.
This is because you have to have a complete expression before and after
conjunctions (boolean operators) like "or" and "and". If you only have
one word/field there, then it considers that a complete expression and
assumes you simply want to test whether it is true or false.
So you would need to write the WHERE like this in order to achieve your
intention (which in itself was flawed, as described in (1) above, but
for the sake of clarity):
where apparel.title like '%s'
or apparel.description like '%s'
or hats.title like '%s'
or hats.description like '%s'
So, to sum up, you need to do something like this instead:
$compare_str
= mysql_real_escape_string(substr($_POST['tfSearch'], 0, 80));
$queries = array();
foreach (array('hats', 'apparel') as $table)
{
$queries[] = "select '{$table}', itemNumber, thumbnailURL,
title, description, price
from {$table}
where title like '%{$compare_str}%'
or description like '%{$compare_str}%'";
}
$query = join(' union ', $queries);
$stmt = mysql_query($query);
I also think you should spend some time with a book or tutorial about
SQL because I get the sense you haven't yet mastered the concept of
relational databases. Without that, you are going to be stuck doing
simple one-table lookups or running into problems as with your code
above.
miguel
--
Photos from 40 countries on 5 continents: http://travel.u.nu
Latest photos: Malaysia; Thailand; Singapore; Spain; Morocco
Airports of the world: http://airport.u.nu
Navigation:
[Reply to this message]
|