Search multiple tables at one time?

[ajs83]

I use a search script and it searches the db like this

$query = "select * from names where product like \"%$trimmed%\"

what I would like to do is not only have it search a table called "names" but also "stores" as well for the "product" field.

I don't have server access at the moment to test it but would it just have to be something like

$query = "select * from names,stores where product like \"%$trimmed%\"

or is something else needed?

[badone]

$query = "SELECT *
FROM names AS a,
stores AS b
WHERE
a.product LIKE '%$trimmed%'
OR
b.product LIKE '%$trimmed%'";

HTH,
BAD

[ajs83]

Ok, tried that, but got a

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource error for the bottom line

$query = "SELECT *
FROM name AS a,
stores AS b
WHERE
a.product LIKE \"%$trimmed%\"
OR
b.product LIKE \"%$trimmed%\"
order by product";

$numresults=mysql_query($query);
$numrows=mysql_num_rows($numresults);

[coopster]

Your query is failing. Probably due to the ambiguity in the product column of your ORDER BY clause. Try specifying a table name qualifier...

order by a.product";

[ajs83]

Tried that and got the same thing.

[badone]

Run the query from the MySQL command line. I would hope that would give you better feedback on what the actual error is.

I use PostgreSQL primarily and, I believe, that query is syntactically correct for Postgres.

I noticed you replaced my ' with ", try it with '.

The basic syntax of the query is sound.

Cheers,
BAD

[ajs83]

I originally tried yours and got that error so I changed it to the "" but got the same problem.

[badone]

test=# create table stores(
test(# product text
test(# )
test-# ;
CREATE TABLE
test=# \d stores
Table "public.stores"
Column ¦ Type ¦ Modifiers
---------+------+-----------
product ¦ text ¦

test=# create table name(
test(# product text
test(# );
CREATE TABLE
test=# insert into stores (product) values ('widget');
INSERT 17627 1
test=# insert into name (product) values ('big blue widget');
INSERT 17628 1
test=# select * from name AS a, stores AS b where a.product like '%widget%' or b.product like '%widget%' order by product;
ERROR: ORDER BY "product" is ambiguous
test=# select * from name AS a, stores AS b where a.product like '%widget%' or b.product like '%widget%' order by a.product;
product ¦ product
-----------------+---------
big blue widget ¦ widget
(1 row)

Quick test works here, sorry.

BAD