PHP Programming - HELP: PHP search query SQL error

Author

HELP: PHP search query SQL error

Frankie

2006-07-28, 6:57 pm

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.description OR hats.title OR
hats.description
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

2006-07-28, 6:57 pm

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.description OR hats.title OR
> hats.description
> 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.itemNumber 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@sudleydeplacespam.com

To reply to me directly, delete "despam".

Frankie

2006-07-28, 6:57 pm

"Bob Smith" <bsmith@sudleydeplacespam.com> wrote in message
news:Iqtyg.6367$oa1.820@news02.roc.ny...
>
>
> Indeed it is ambiguous. To avoid this, use apparel.itemNumber 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_gpc" is enabled, escaping any quotes (") or apostrophes (').

F.H.

Shelly

2006-07-28, 6:57 pm

"Bob Smith" <bsmith@sudleydeplacespam.com> wrote in message
news:Iqtyg.6367$oa1.820@news02.roc.ny...
> On 7/28/2006 2:51 PM, Frankie wrote:
>
> Indeed it is ambiguous. To avoid this, use apparel.itemNumber 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

2006-07-28, 6:57 pm

Frankie wrote:
> "Bob Smith" <bsmith@sudleydeplacespam.com> wrote in message
> news:Iqtyg.6367$oa1.820@news02.roc.ny...
>
>
> 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_gpc" 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_escape_string() is a good way to go. Using prepared statements is
better.

Grtz,
--
Rik Wasmus

Frankie

2006-07-29, 3:57 am

"Rik" <luiheidsgoeroe@hotmail.com> wrote in message
news:16bca$44caa404$8259c69c$29664@news1
.tudelft.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 w

s 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 "description" 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_escape_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

2006-07-29, 6:57 pm

"Frankie" <frankie66@earthlink.net> wrote:
>
> 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

Frankie

2006-07-30, 3:57 am

"Miguel Cruz" <spam@admin.u.nu> wrote in message
news:spam-203EFF.01593130072006@localhost...
>
> 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.

Sponsored Links