Need Help JOINING two tables

[jeger003]

hello everyone,

i am having some issues calling fields from two different tables. basically i have a classified site and one table has the title of the listing and the other has the url of the image

 

here is what i have....i wasnt able to use the actual JOIN because i have to call values from both tables.........one holds the title the other holds the url of images....and i need to fetch both at once

// JOIN (listings_urls) ON (listings_urls.listing_id = listings.id
$query = mysql_query("SELECT * FROM listings WHERE search_text LIKE '%$value_to_find%' AND live = 1 ") or die(mysql_error());

$ad_id = mysql_fetch_array($query);

$query_images = mysql_query("SELECT * FROM listings_urls WHERE listing_id = '".$ad_id['id']."' ") or die(mysql_error());

$num = mysql_num_rows($query);   

if($num > 0) 
   {
    while ($fetch = mysql_fetch_array($query) && $images = mysql_fetch_array($query_images))
      {

	 echo $fetch['title']."<br>";
         echo $images['image_url']."<br>";        	


      }
   }

else
   {
      echo 'No Items Found!';
   }

 

 

i would appreciate any help.....as you can see im new to it.....i would also appreciate pointers....and tips for future reference

[revraz]

Show us your table structures.

 

There should be no reason you can't do a JOIN unless you have no foreign key, and if you don't, you should re-design your DB.

[jeger003]

well the two tables i need to call values from are...........listings and listings_url

 

the listings table holds

id <-----------------------------

title                                        |

description                              |

location                                  |

                                              | Same numbers...these i have to JOIN

the listings_url hold                    |

id                                          |

listings_id------------------------

listings_url //is the url to the image to specific listing_id (/images/34252.jp)

 

 

 

i can use JOIN like this but........how can i call the and echo the listings_url....thats the only way to display the image specific to each listing

 

<?php


$query = mysql_query("SELECT * FROM listings WHERE search_text LIKE '%$value_to_find%' AND live = 1 JOIN (listings_urls) ON (listings_urls.listing_id = listings.id ") or die(mysql_error());

$num = mysql_num_rows($query);   

if($num > 0) 
   {
    while ($fetch = mysql_fetch_array($query) && $images = mysql_fetch_array($query_images))
      {
echo $fetch['title']."<br>";
echo $fetch['image_url']."<br>";        
      }
   }

else
   {
      echo 'No Items Found!';
   }

?>

[revraz]

Of course, in your SELECT you call the fields you want.  So instead of SELECT *, use

 

SELECT listings.title, listings.image_url, listings_urls.listing_urls

 

And then in your echo, you just do the same

 

echo $fetch['listings_url']."<br>";

 

You can also view the entire $fetch array to see all the results.

[jeger003]

whoa....didnt know that's allowed

 

 

i made the changes and get an error only with JOIN in the query......if i remove JOIN and everything after it.....it works and its able to fetch values from both tables

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'JOIN (listings_urls) ON (listings_urls.listings_id' at line 1

 

 

this is the code that cause the error

<?php

$query = mysql_query("SELECT listings.title,listings.id,listings_urls.image_url,listings_urls.lisitings_id FROM listings,listings_urls WHERE search_text LIKE '%$value_to_find%' AND live = 1 JOIN (listings_urls) ON (listings_urls.listings_id = listings.id)") or die(mysql_error());

$num = mysql_num_rows($query);   

if($num > 0) 
   {
    while ($fetch = mysql_fetch_array($query))
      {

	 echo $fetch['title']."<br>";
         echo $fetch['image_url']."<br>";        	


      }
   }

else
   {
      echo 'No Items Found!';
   }
?>

[jeger003]

ok i am able to join the two tables but when i use the while() to fetch arrays it only displays one listing............anyone know why?

 

 

<?php

$id = 'listings.id';
$query_image = mysql_query("SELECT listings_urls.listings_id,listings_urls.thumb_url,listings.title,listings.image FROM listings_urls,listings WHERE listings_urls.listings_id = $id AND listings.live = 1") or die(mysql_error());

$num = mysql_num_rows($query_image);   

if($num > 0) 
   {
    while ($fetch = mysql_fetch_array($query_image) )
      {


	if($fetch['image']== 1)
	{
	 echo "<img src='/".$fetch['thumb_url']."'><br>";
	 echo "<br>".$fetch['title'];
	}
	else
	{
		echo "no image to display";					
	}


      }
   }

else
   {                                              
      echo "no books found!";
   }
?>

[revraz]

You are not properly joining the tables.

 

I'll post tomorrow if someone doesn't beat me to it.

[ashishag67]

$query=mysql_query("SELECT * FROM listings li, listings_url lurl where li.search_text LIKE '%$value_to_find%' AND li.live='1' AND lurl.listing_id='".$ad_id['id']."'");

 

 

Try this out will work

 

Cheers