Welcome to WebmasterWorld Guest from 54.161.110.186

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

display all images which belongs to one user

     
5:46 pm on Jun 11, 2012 (gmt 0)



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
8:28 pm on Jun 11, 2012 (gmt 0)



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
4:13 pm on Jun 12, 2012 (gmt 0)



Thank you so much, it works perfectly.
 

Featured Threads

Hot Threads This Week

Hot Threads This Month