Jump to content

Archived

This topic is now archived and is closed to further replies.

Duncan Disorderly

Help please retrieving data from two tables (joins)

Recommended Posts

Hi All
I think this falls into the PHP + Mysql Joins issues category

Scenario:
I have two tables: listings and listings_p

listings has the following fields: ID,Type,Area,Value, Desc.

listings_p has :ID, L_ID, Path - "path" being the path to a jpeg file.
Note: a one to many relationship - listings_p.L_ID = listing.ID

2: I have three dropdown boxes that I use to search the listings, these are Type, Area and Value.

Requirement:
I need to display all the data from listings plus any matching records (photos) from listings_p.
where Type=X AND Area=X AND Value BETWEEN X AND X

Issue:
Getting the relevant records from listing is easy but what I can not workout is the Query to get the records from listings AND the matching records from listings_p where listings_p.L_ID = listing.ID.
ie: For every listing record there are many Listing_p records

Also :I need to do the above but this time get only the first matching record from listings_p (ie first photo)
For every listing record get only the first Listing_p record


any advice gratefully received.

Cheers
DD



Share this post


Link to post
Share on other sites
Well, a JOIN will give you your "additional" records, but you'll need to run a separate query on the initial table; I guess you could do a UNION if you really felt the need to pull it back from a single statement.

Share this post


Link to post
Share on other sites
[!--quoteo(post=387095:date=Jun 23 2006, 02:37 AM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Jun 23 2006, 02:37 AM) [snapback]387095[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Well, a JOIN will give you your "additional" records [/quote]
Fenway,
Thank you for your kind reply.
I had thought it would require a join... unfortunately,I do not know which join to use or how to use it..

All
I have asked a few people about this and the general response is .."use a join" which is great if i knew how but I dont. I have read the Mysql manual entries for it and just dont get it.

I suppose what I am really asking is: can some kindly person write the query for me? I can then follow it to see how and why each portion of it goes where in the query, basically learning by example.

I would like to know how to include the join into my query statement, given that I firstly need to get all "listings" records that match the search criteria (type,Val,Area) THEN get each "listing_p" record where listings_p.L_ID matches listings.ID from the "listings" records returned by the inital search criteria.
a one to many relationship (many "listings_p" records for each "listings" record)

This was my original query, which worked great until I decided I need to add X more pics to each listings record - hence the listings_p table to hold all the associated image paths.

<?
.............
$result = mysql_query("SELECT * FROM Listings WHERE Type='$L_Type'AND Area='$L_Area'AND Value='$L_Val'") or die(mysql_error());

while ($row = mysql_fetch_array($result)) {

<img src="<? echo $row['Path'];?>">, <? echo $row['ID'],": ",$row['Description'];?>

}
?>

Having just written the above, it suddenly became apparent that i have another issue:
I do not know how to extract/display the "listings_p" records for each "listings" records from the array, given that "path" is now from the listings_p table and both "ID and Description" are from the listings table.
Any help here would be kindly accepted too!.

Sorry for the confusing mail.

Kind regards to all
DD

Share this post


Link to post
Share on other sites
Something like the following should work (UNTESTED):

[code]SELECT l.*, p.*
FROM listings AS l
LEFT JOIN listings_p AS p ON ( p.L_ID = l.ID )
WHERE Type='$L_Type' AND Area='$L_Area'AND Value BETWEEN '$L_Val' AND '$H_Val'[/code]

To which you'd need to add the "easier" query.

Share this post


Link to post
Share on other sites
[!--quoteo(post=387396:date=Jun 24 2006, 02:48 AM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Jun 24 2006, 02:48 AM) [snapback]387396[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Something like the following should work (UNTESTED):

[code]SELECT l.*, p.*
FROM listings AS l
LEFT JOIN listings_p AS p ON ( p.L_ID = l.ID )
WHERE Type='$L_Type' AND Area='$L_Area'AND Value BETWEEN '$L_Val' AND '$H_Val'[/code]

To which you'd need to add the "easier" query.
[/quote]


Fenway, Once again kind thanks for your response...
I actually worked it out late last night...about 4am!....

<?
$result = mysql_query("Select listings_P.`Path`,listings_P.`ID` ,listings.`Description`, listings.`ID`
FROM `listings_p`
RIGHT JOIN `listings`
ON listings_p.`L_ID`=`listings`.`ID`
WHERE listings.`Type`='$L_Type' AND `listings`.`Area`='$L_Area' AND `listings`.`Value`='$L_Value'")
or die(mysql_error());

while ($row = mysql_fetch_array($result)) {
>?
<img src="<? echo $row['Path'];?>">, <? echo $row['ID'],": ",$row['Description'];?>
<?
}
?>

This grabs all matching records from listings and each associated record (image) from listings_p (One to many) via listings_p.L_ID = listings.ID

Cheers
DD

Share this post


Link to post
Share on other sites
[!--quoteo(post=387401:date=Jun 24 2006, 05:08 AM:name=Duncan Disorderly)--][div class=\'quotetop\']QUOTE(Duncan Disorderly @ Jun 24 2006, 05:08 AM) [snapback]387401[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Fenway, Once again kind thanks for your response...
I actually worked it out late last night...about 4am!....

<?
$result = mysql_query("Select listings_P.`Path`,listings_P.`ID` ,listings.`Description`, listings.`ID`
FROM `listings_p`
RIGHT JOIN `listings`
ON listings_p.`L_ID`=`listings`.`ID`
WHERE listings.`Type`='$L_Type' AND `listings`.`Area`='$L_Area' AND `listings`.`Value`='$L_Value'")
or die(mysql_error());

while ($row = mysql_fetch_array($result)) {
>?
<img src="<? echo $row['Path'];?>">, <? echo $row['ID'],": ",$row['Description'];?>
<?
}
?>

This grabs all matching records from listings and each associated record (image) from listings_p (One to many) via listings_p.L_ID = listings.ID

Cheers
DD
[/quote]
Glad you got it working... but [b]NEVER[/b] use RIGHT JOIN! It's almost never needed, and everyone uses LEFT JOIN by convention; simply switch the order of the tables in your query.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.