HELP: PHP search query SQL error

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
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
           LIKE '%s'",

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

Re: HELP: PHP search query SQL error

On 7/28/2006 2:51 PM, Frankie wrote:
Quoted text here. Click to load it

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 --

To reply to me directly, delete "despam".

Re: HELP: PHP search query SQL error

Quoted text here. Click to load it

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 (').


Re: HELP: PHP search query SQL error

Frankie wrote:
Quoted text here. Click to load it

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).

Quoted text here. Click to load it

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

Rik Wasmus

Re: HELP: PHP search query SQL error

Quoted text here. Click to load it

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 "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.

Quoted text here. Click to load it

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

Frank H.
Austin, TX

Re: HELP: PHP search query SQL error

Quoted text here. Click to load it

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  

   shirt fedora
   shirt bowler
   socks fedora
   socks bowler
   pants fedora
   pants bowler

When all you really wanted was


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']  

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:

      = mysql_real_escape_string(substr($_POST['tfSearch'], 0, 80));
   $queries = array();
   foreach (array('hats', 'apparel') as $table)
      $queries[] = "select '', itemNumber, thumbnailURL,
            title, description, price
         where title like '%%'
         or description like '%%'";
   $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  

Photos from 40 countries on 5 continents:
Latest photos: Malaysia; Thailand; Singapore; Spain; Morocco
Airports of the world:

Re: HELP: PHP search query SQL error

Quoted text here. Click to load it

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,


Re: HELP: PHP search query SQL error

Quoted text here. Click to load it

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

$query  = sprintf ("SELECT a.itemNumber, thumbnailURL, title, description,  
            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.


Site Timeline