homepage Welcome to WebmasterWorld Guest from 23.22.194.120
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
display all images which belongs to one user
Davidkarate




msg:4464052
 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

 

rlange




msg:4464112
 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

Davidkarate




msg:4464466
 4:13 pm on Jun 12, 2012 (gmt 0)

Thank you so much, it works perfectly.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved