display all images which belongs to one user

[Davidkarate]

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.

My tables structure:

classifieds:
id, title, description, state_id, city_id

state:
id, statename

city:
id
city

img:
id
classified_id


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
  }
  ?>

Thanks in advance

[rlange]

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

Yeah, that's one of the things about SQL; it doesn't return complex objects. It just returns rows.

In your situation, I would probably do two separate queries. One to grab the information about the classified itself (title, description, state, city) and a second one just to grab the images associated with that classified.

<?php

$result = mysqli_query( "SELECT `classifieds`.`title`, `classifieds`.`description`, `state`.`statename`, `city`.`city`
FROM `classifieds`
LEFT JOIN `state` ON `classifieds`.`state_id` = `state`.`id`
LEFT JOIN `city` ON `classified`.`city_id` = `city`.`id`
WHERE `classifieds`.`id` = " . $id . "
AND `authorized` = '1'" );

$classified = mysqli_fetch_assoc( $result );

$result = mysqli_query( "SELECT `image_path` FROM `img` WHERE `classified_id` = " . $id );

$classified['images'] = array();
while( false !== ( $row = mysqli_fetch_assoc( $result ) ) {
 $classified['images'][] = $row;
}

var_dump( $classified );

?>

Obligatory Warnings:

1. Make sure

   $id

   is properly sanitized or you open yourself up to SQL injection attacks. This can't be stressed enough.
   
2. If you're on PHP 5 or above, use either the mysqli_* [us3.php.net] functions, the MySQL PDO [us3.php.net] driver, or Zend Framework's Zend_Db [framework.zend.com] classes.
   

Also, the above code is untested and isn't guaranteed to work, but hopefully it gets the point across. :o)

--
Ryan

[Davidkarate]

Thank you so much, it works perfectly.