TalkPHP - View Single Post - Search query multiple tables? (PHP, MySQL)

shoaibmunir · 06-16-2009, 11:49 AM

Quote:

Originally Posted by Wildhoney

If you have conflicting columns then you will, unfortunately, need to specify them individually as in the code example.

To take your code as the example this time, you would have to specify explicitly that you would like the id from the tbl_products table, with your supplied alias, p. You would specify this by adding the following to your SELECT statement:

sql Code:

p.rn AS rn_id

Which, excluding your conditionals at the end of your MySQL statement above, would now be like so:

sql Code:

SELECT
    p.*,
    pd.*,
    p.rn AS rn_id
FROM
    tbl_products p,
    tbl_productsdetail pd

All your data is still returned, but the ID you require is now stored in rn_id, because rn_id is unique, whereas id is not unique to that particular table, and therefore overwritten.

So then to get the value you would do:

php Code:

echo $apshmrecords['rn_id'];

Thank dude its working fine but i'm not getting search query result 100%

Code:

select p.*, pd.*, p.rn as p_rn from tbl_products p, tbl_productsdetail pd where p.rn=pd.pn and p.code like '%2001%' or pd.name like '%a%' or pd.description like '%a%'