CODEWITHSUNDEEP
phpmysql
Rocks
Rocks

Reputation: 511

display all images which belongs to user

I have four table: classifieds, state, city and img. I want to display all images which belong to a unique id(user).

For ex: A user from Minneapolis(city) Minnesota(state) posts an ad about selling a car that has 5 pictures.

I got everything working with this query, except the result is repeating user information for every image.

<?php
$query  = mysql_query("SELECT * FROM classifieds 
    LEFT JOIN  img ON img.classified_id = classifieds.id     
    LEFT JOIN  state ON classifieds.state_id = state.id 
     LEFT JOIN  city ON classifieds.city_id = city.id WHERE id = '".$id."'  AND  

    authorized = '1' 

   ");
   while($row = mysql_fetch_array($query, MYSQL_ASSOC))
   {   
   ?>      

    <div align="center"><a href=' <?php echo $row['image_path']; ?>' rel='lightbox'  

    title='<?php echo $row['title']; ?>' style='text-decoration:none;'> <img 

   src='<?php echo $row['image_path']; ?>' width='150' border='0' />
     </a>
   </div>


   <div class="show_location" style="width:225px; hight:10px; padding-top:20px;"> 

    <span  class='style55'><?php echo $lang['D_STATE']; ?> <span style="color:#06F"> 

   <?php echo $row['statename']; ?></span> </div>

   <div style="width:225px; hight:10px; padding-top:20px;"> <?php echo 

   $lang['D_CITY']; ?><span style="color:#06F"> <?php echo $row['city']; ?></span> 

   </span> </div>

   <?php
   }
   ?>

I looked up for some answers in this site, and I tried to group by, but it returns only the first image.

Thanks

Upvotes: 0

Views: 184

Answers (1)

Habeeb
Habeeb

Reputation: 166

Your 'img' table has multiple records for same 'classified_id'.. so your query will produce multiple records for the same classifieds id. You have two options here

  1. instead of joinig the img table in the main query, write separate query for fetching images inside the while loop.

  2. Use 'group_concat' function for retrieving images.. but you have to split the concated string to image names.

Try this (first option)

<?php
$query  = mysql_query("SELECT * FROM classifieds 
LEFT JOIN  state ON classifieds.state_id = state.id 
LEFT JOIN  city ON classifieds.city_id = city.id WHERE classifieds.id = '" . $id . "'  AND authorized = '1'");

$row = mysql_fetch_array($query, MYSQL_ASSOC);

$get_images_query = "SELECT * FROM img WHERE classified_id = " . $row['id'];
$exec_get_images_query = mysql_query($get_images_query);
while($res_images = mysql_fetch_array($exec_get_images_query))
{   
    ?>
    <div align="center"><a href=' <?php echo $res_images['image_path']; ?>' rel='lightbox' title='<?php echo $row['title']; ?>' style='text-decoration:none;'> <img src='<?php echo $res_images['image_path']; ?>' width='150' border='0' /></a>
   </div>
   <?php
}
?>
<div class="show_location" style="width:225px; hight:10px; padding-top:20px;"><span  class='style55'><?php echo $lang['D_STATE']; ?> <span style="color:#06F"> <?php echo $row['statename']; ?></span></div>
<div style="width:225px; hight:10px; padding-top:20px;"><?php echo $lang['D_CITY']; ?><span style="color:#06F"> <?php echo $row['city']; ?></span></div>

Upvotes: 1

Related Questions