Okay guys, the code below works fine. Except that since I have given users up to 4 image downloads, the product preview page should only display 1 of the images like the first one in the array in the product page but it shows all rows if the user uploads like 4 images. how can I limit this? thanks.
<?php
echo 'Your posts:' . '<br>' . '<hr>';
$posts = "SELECT * FROM posts,image_data WHERE userid='$user' AND user_posts.userid = image_data.client_id ORDER BY date_created ASC";
$posts_result = $mysqli->query($posts);
while ($posts_result_rows = $posts_result->fetch_assoc()) {
$its_id = $posts_result_rows["userid"];
$its_title = $posts_result_rows['post_title'];
$its_image = $posts_result_rows['file'];
$its_description = $posts_result_rows['post_description'];
?>
<table border=0 cellpadding=5 cellspacing=0 >
<tr>
<?php echo '<br>' . $its_title . ' <br>' . $its_description . '<br>'; ?>
<img src="<?php echo "users_data/users_posted_data/".$its_image; ?>" alt="<?php echo $its_title; ?>" width="120" height="120"><hr>
</tr>
</table>
<?php
}
?>
try adding LIMIT 1
$posts = "SELECT * FROM posts,image_data WHERE userid='$user' AND user_posts.userid = image_data.client_id ORDER BY date_created ASC LIMIT 1";
$posts_result = $mysqli->query($posts);
The important part is to add limit 1.
SELECT * FROM posts,image_data WHERE userid='$user' AND user_posts.userid = image_data.client_id ORDER BY date_created ASC limit 1
Order by in descending order for getting latest row
$posts = "select * from (SELECT * FROM posts,image_data WHERE userid='$user' AND posts.userid = image_data.client_id ORDER BY date_created DESC)temp group by userid ";
$posts_result = $mysqli->query($posts);
Below mentioned SQL query is working fine: http://sqlfiddle.com/#!9/1a0e9/1
select * from
(SELECT * FROM posts,image_data WHERE userid=1
AND posts.userid = image_data.client_id
ORDER BY date_created DESC)temp group by userid;
<?php
echo 'Your posts:<br><hr>';
$posts = "SELECT * FROM posts,image_data WHERE userid='$user' AND user_posts.userid = image_data.client_id ORDER BY date_created ASC";
$posts_result = $mysqli->query($posts);
while ($posts_result_rows = $posts_result->fetch_assoc()) {
$its_id = $posts_result_rows["userid"];
$its_title = $posts_result_rows['post_title'];
$its_image = $posts_result_rows['file'];
$its_description = $posts_result_rows['post_description'];
echo "<table border=0 cellpadding=5 cellspacing=0 ><tr><td>";
echo '<br>' . $its_title . ' <br>' . $its_description . '<br>';
}
?>
<img src="<?php echo "users_data/users_posted_data/".$its_image; ?>" alt="<?php echo $its_title; ?>" width="120" height="120"><hr>
</td></tr>
</table>
$posts = "SELECT * FROM posts WHERE userid='$user' ORDER BY date_created ASC";
$posts_result = $mysqli->query($posts);
while ($posts_result_rows = $posts_result->fetch_assoc())
{
$posts1 = "SELECT * FROM image_data WHERE client_id='$user' and file != '' ORDER BY image_id DESC";
$posts_result1 = $mysqli->query($posts1);
$posts_result_rows1 = $posts_result1->fetch_assoc()
$its_id = $posts_result_rows["userid"];
$its_title = $posts_result_rows['post_title'];
$its_image = $posts_result_rows1['file'];
$its_description = $posts_result_rows['post_description'];
?>
<table border=0 cellpadding=5 cellspacing=0 >
<tr>
<td>
<?php echo '<br>' . $its_title . ' <br>' . $its_description . '<br>'; ?>
<img src="<?php echo "users_data/users_posted_data/".$its_image; ?>" alt="<?php echo $its_title; ?>" width="120" height="120" />
<hr>
</td>
</tr>
</table>
<?php
}
?>
The second image query write inside while loop for fetching only one image and also check file column is not empty. Idon't know what's your column name put your column name according your table structure.