Simple Search Problem

[HotShot99]

Hi,

I am building a simple search engine, well trying.
I will try to keep it simple...

The user (using a PHP form) types in their search criteria (text entry). Then I ensure the text has '%' either side.

What I need to do now is query the table 'myProducts' (it MAY return multiple results) and then take those results 'IDproduct' (values between 1 & 9999)
and query the field 'IDproduct' in 'myStock'. Also ensuring that I return unique 'IDstock' (no dupes).

I can do simple SQL queries, but how do I create and pass this array to the next SQL search?

The two tables:

TABLE NAME: 'myProducts'
FIELD NAME: 'IDproduct'
FIELD NAME: 'productName'

TABLE NAME: 'myStock'
FIELD NAME: 'IDstock'
FIELD NAME: 'IDproduct'
FIELD NAME: 'stockDate'

Thanks for any help,
HotShot

[matto]

hi!

you can do that in one pass:

select mystock.* from mystock,myproducts where
myproducts.productname like '%searchstring%' and
mystock.idproduct=myproducts.idproduct

of course you'll have to replace "searchstring" with the search key entered by your visitor.

hope this helps

[HotShot99]

Thank You.

Sometimes when you look at a problem too much you'll never get the solution...

I sat in the garage for 10 minutes with a cup of coffee this morning and voila - this is one flamin qry!

SELECT
mp.IDproduct AS mp_IDproduct,
mp.productName AS mp_productName,
ms.IDstock AS ms_IDstock,
ms.IDproduct AS ms_IDproduct,
ms.stockDate AS ms_stockDate

FROM myProducts mp, myStock, ms
WHERE
mp.IDproduct=mp.productName
AND mp.productName LIKE '%searchstring%'

Thank you Matto.