Search query multiple tables? (PHP, MySQL)

shoaibmunir · 06-15-2009, 04:06 PM

I'm working on a php project. i have a create a search product page. its contain multi table and working fine but exact problem is both table have some same column names i can't get the first table column value

Database Structure //I can't change must i've to create a script for getting result

tbl_products:-
rn //i want this value because i'm using as a product id
imagename

tbl_productsdetail

rn //i don't wana this value
pn //value coming from tbl_products rn column
name
description

Code:

$apshmquery="select p.*, pd.* from tbl_products p, tbl_productsdetail pd where p.code like '%" . $apshmreqcode .  "%' or pd.name like '%" . $apshmreqq .  "%' or pd.description like '%" . $apshmreqq .  "%' and p.rn=pd.pn ";


 $apshmsql = mysql_query($apshmquery);

$apshmrows = mysql_num_rows($apshmsql);

while ($apshmrecords = mysql_fetch_array($apshmsql)) {

echo ($apshmrecords["rn"]."</br>");//i want here tbl_products column value

echo ($apshmrecords["name"]."</br>");//its coming from productsdetail working fine

echo ($apshmrecords["description"]."</br>");//its coming from productsdetail working fine

}

have any good solution??

Thank's in advance :)

Wildhoney · 06-15-2009, 04:17 PM

You can use the AS keyword for that.

sql Code:

SELECT
    myTable1.id AS myTable1_Id,
    myTable2.id AS myTable2_Id
FROM
    ...

Please let me know if that makes sense. If not, I will explain much better

!

shoaibmunir · 06-15-2009, 04:20 PM

Quote:

Originally Posted by Wildhoney

You can use the AS keyword for that.

sql Code:

SELECT
    myTable1.id AS myTable1_Id,
    myTable2.id AS myTable2_Id
FROM
    ...

Please let me know if that makes sense. If not, I will explain much better

!

thank's for your reply but you have to explain me because i have already try with same query

Wildhoney · 06-15-2009, 05:44 PM

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

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

Wildhoney · 06-16-2009, 11:55 AM

What do you mean by not 100%? What appears to not be working?

shoaibmunir · 06-16-2009, 01:47 PM

Thank's

Its Working Fine Now :)