HELP: PHP search query SQL error

[Frankie]

I'm trying to run the following search query:

$query =
sprintf ("SELECT itemNumber, thumbnailURL, title, description, price
FROM apparel,hats
WHERE apparel.title OR apparel.descrip tion OR hats.title OR
hats.descriptio n
LIKE '%s'",
$_POST['tfSearch']);

but it returns this error:

"Column: 'itemNumber' in field list is ambiguous"

Both "apparel" and "hats" tables share the same columns specified in the
SELECT clause.

What I'm I missing?

Frank H.
Austin, TX

[Bob Smith]

Re: HELP: PHP search query SQL error

On 7/28/2006 2:51 PM, Frankie wrote:

I'm trying to run the following search query:
>
$query =
sprintf ("SELECT itemNumber, thumbnailURL, title, description, price
FROM apparel,hats
WHERE apparel.title OR apparel.descrip tion OR hats.title OR
hats.descriptio n
LIKE '%s'",
$_POST['tfSearch']);
>
but it returns this error:
>
"Column: 'itemNumber' in field list is ambiguous"
>
Both "apparel" and "hats" tables share the same columns specified in the
SELECT clause.
>
What I'm I missing?

Indeed it is ambiguous. To avoid this, use apparel.itemNum ber or
hats.itemNumber in the SELECT list. Repeat for the other columns.

Also, to avoid an SQL injection attack, be sure to filter the input in
$_POST[...] using a function appropriate to your SQL implementation.

--
_______________ _______________ ___________
Bob Smith -- bsmith@sudleyde placespam.com

To reply to me directly, delete "despam".

[Frankie]

Re: HELP: PHP search query SQL error

"Bob Smith" <bsmith@sudleyd eplacespam.comw rote in message
news:Iqtyg.6367 $oa1.820@news02 .roc.ny...

>

>$query =
>>sprintf ("SELECT itemNumber, thumbnailURL, title, description, price
> FROM apparel,hats
> WHERE apparel.title OR apparel.descrip tion OR hats.title OR
> hats.descriptio n
> LIKE '%s'",
> $_POST['tfSearch']);

>
Indeed it is ambiguous. To avoid this, use apparel.itemNum ber or
hats.itemNumber in the SELECT list. Repeat for the other columns.
>
Also, to avoid an SQL injection attack, be sure to filter the input in
$_POST[...] using a function appropriate to your SQL implementation.

Thanks for your quick reply!....that did it.

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.

What I'm (humbly) trying to do is query the common columns from 6 different
tables. Using the above method would produce a query string a mile long (and
horribly complex). The above query was a test for only 2 tables.

Any suggestions?

RE: Yes, I'm using strip_tags() and trim() on the POST value.
"magic_quotes_g pc" is enabled, escaping any quotes (") or apostrophes (').

F.H.

[Shelly]

Re: HELP: PHP search query SQL error


"Bob Smith" <bsmith@sudleyd eplacespam.comw rote in message
news:Iqtyg.6367 $oa1.820@news02 .roc.ny...

On 7/28/2006 2:51 PM, Frankie wrote:

>I'm trying to run the following search query:
>>
>$query =
>sprintf ("SELECT itemNumber, thumbnailURL, title, description, price
> FROM apparel,hats
> WHERE apparel.title OR apparel.descrip tion OR hats.title OR
> hats.descriptio n
> LIKE '%s'",
> $_POST['tfSearch']);
>>
>but it returns this error:
>>
>"Column: 'itemNumber' in field list is ambiguous"
>>
>Both "apparel" and "hats" tables share the same columns specified in the
>SELECT clause.
>>
>What I'm I missing?

>
Indeed it is ambiguous. To avoid this, use apparel.itemNum ber or
hats.itemNumber in the SELECT list. Repeat for the other columns.
>
Also, to avoid an SQL injection attack, be sure to filter the input in
$_POST[...] using a function appropriate to your SQL implementation.

If you have a lot of fields to select, another way is to write it as:

$query = sprintf ("SELECT a.itemNumber, thumbnailURL, title, description,
price
FROM apparel a, hats h
WHERE a.title OR a.description OR h.title OR h.description
LIKE '%s'", $_POST['tfSearch']);

or use h.itemNumber if that is the one you wanted.

Shelly

[Rik]

Re: HELP: PHP search query SQL error

Frankie wrote:

"Bob Smith" <bsmith@sudleyd eplacespam.comw rote in message
news:Iqtyg.6367 $oa1.820@news02 .roc.ny...

>>

>>$query =
>>sprintf ("SELECT itemNumber, thumbnailURL, title, description, price
>> FROM apparel,hats
>> WHERE apparel.title OR apparel.descrip tion OR hats.title
>> OR hats.descriptio n
>> LIKE '%s'",
>> $_POST['tfSearch']);

>>
>Indeed it is ambiguous. To avoid this, use apparel.itemNum ber or
>hats.itemNumbe r in the SELECT list. Repeat for the other columns.
>>
>Also, to avoid an SQL injection attack, be sure to filter the input
>in $_POST[...] using a function appropriate to your SQL
>implementation .

>
>
Thanks for your quick reply!....that did it.
>
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.
>
What I'm (humbly) trying to do is query the common columns from 6
different tables. Using the above method would produce a query string
a mile long (and horribly complex). The above query was a test for
only 2 tables.
>
Any suggestions?

Clarifiy your exact needs and correlation between tables. Without it, it's
hard to come up with a suitable solution. Telling us what kind of database
you're using is also a biog plus (and a question like this is usually more
appropriate in a newsgroup about that partivular kind of DB).

RE: Yes, I'm using strip_tags() and trim() on the POST value.
"magic_quotes_g pc" is enabled, escaping any quotes (") or apostrophes
(').

Magic_quotes are unreliable at best (and IMHO a f*cking nuisance when coding
correctly). If using mysql (which people usually are),
mysql_real_esca pe_string() is a good way to go. Using prepared statements is
better.

Grtz,
--
Rik Wasmus

[Frankie]

Re: HELP: PHP search query SQL error

"Rik" <luiheidsgoeroe @hotmail.comwro te in message
news:16bca$44ca a404$8259c69c$2 9664@news1.tude lft.nl...

>
Clarifiy your exact needs and correlation between tables. Without it, it's
hard to come up with a suitable solution. Telling us what kind of database
you're using is also a biog plus (and a question like this is usually more
appropriate in a newsgroup about that partivular kind of DB).

Thanks for your response, Rik. You responded to another one of my questions
a couple weeks ago and were very helpful...a real asset to this group.

You're right, I'll take my question to the mySQL group.

Just FYI: it's a mySQL database and I'm trying to query one database which
holds 6 different tables for an entered search term which matches the common
"title" or "descriptio n" field of each table, and then display the results.
I might just simplify the query by requiring the user to enter which table
(category) to search in addition to the actual search term.

Magic_quotes are unreliable at best (and IMHO a f*cking nuisance when

coding

correctly). If using mysql (which people usually are),
mysql_real_esca pe_string() is a good way to go. Using prepared statements

is

better.

Again, thanks for your most helpful suggestion! I'll incorporate this
function into my repertoire.

Frank H.
Austin, TX

[Miguel Cruz]

Re: HELP: PHP search query SQL error

"Frankie" <frankie66@eart hlink.netwrote:

>>$query =
>>sprintf ("SELECT itemNumber, thumbnailURL, title, description, price
>> FROM apparel,hats
>> WHERE apparel.title OR apparel.descrip tion OR hats.title OR
>> hats.descriptio n
>> 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.descrip tion or hats.title or hats.descriptio n
like '%s'", the only thing that is actually getting compared to
$_POST['tfSearch'] is hats.descriptio n. For apparel,title,
apparel.descrip tion, 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.descrip tion like '%s'
or hats.title like '%s'
or hats.descriptio n like '%s'

So, to sum up, you need to do something like this instead:

$compare_str
= mysql_real_esca pe_string(subst r($_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($qu ery);

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

[Frankie]

Re: HELP: PHP search query SQL error

"Miguel Cruz" <spam@admin.u.n uwrote in message
news:spam-203EFF.01593130 072006@localhos t...

>
There are two fundamental problems with your SQL....
>
><snip>

You guys are awesome!...a wonderfully helpful and detailed response.

Up until now I have only worked with single tables, as you noticed, and most
of my queries have been simple. I'll have to find a good reference book for
PHP/SQL queries...

Thanks again,

F.H.