I am trying to execute a while loop to display data regarding flagged posts. I am currently logged in as Conor, who has flagged 2 posts (2 rows in the flagged_posts field). But when I execute the below code, only one div is echo's, whereas two should be echo's as he has flagged two posts.
Here is my approach:
<?php
// getting id of logged in user, need this to see all the flagged posts from logged in user.
$get_uid = mysqli_query ($connect, "SELECT id FROM users WHERE username = '$user' ");
$get_d = mysqli_fetch_assoc($get_uid);
$user_ident = $get_d ['id'];
// using the above id and getting all the flagged posts which have been flagged by the id obtained
$flagged_by2 = mysqli_query ($connect, "SELECT * FROM flagged_posts WHERE flagged_by_id = '$user_ident'");
$num_of_flagged_posts = mysqli_num_rows ($flagged_by2);
while ($getting_d = mysqli_fetch_assoc ($flagged_by2)){
$flagged_thought_id = $getting_d['thought_id'];
}
$query = mysqli_query($connect, "SELECT * FROM user_thoughts WHERE id='$flagged_thought_id' ORDER BY id DESC LIMIT {$start}, {$limit}");
while ($row = mysqli_fetch_array($query)) {
$thought_id = $row['id'];
$message_content = $row['message'];
$date_of_msg = $row['post_details'];
$thoughts_by = $row['added_by'];
$attachent = $row['attachment'];
$shared = $row['shared'];
echo
" <div class='message_wrapper'>
<p> Details would be displayed here </p>
</div>";
} //while closed
Edit:
Pagination (with Marcin Nabiałek approach):
$flagged_by2 = mysqli_query ($connect, "SELECT * FROM flagged_posts WHERE flagged_by_id = '$user_ident'");
$flagged_ids = [];
$num_of_flagged_posts = mysqli_num_rows ($flagged_by2);
while ($getting_d = mysqli_fetch_assoc ($flagged_by2)){
$flagged_ids[] = $getting_d['thought_id'];
}
if (!$flagged_ids) {
$flagged_ids[] = 0;
}
// Pagination - Load more content when bottom of page hit.
$limit = 10;
$page = (int) (!isset($_GET['p'])) ? 1 : $_GET['p'];
# sql query
$query = mysqli_query($connect, "SELECT * FROM user_thoughts WHERE id IN " . implode(', ', $flagged_ids)." ORDER BY id DESC");
# find out query start point
$start = ($page * $limit) - $limit;
# query for page navigation
if(mysqli_num_rows($query) > ($page * $limit) ){
$next = ++$page;
}
//$query = mysqli_query($connect, "SELECT * FROM user_thoughts WHERE id='$flagged_thought_id' ORDER BY id DESC LIMIT {$start}, {$limit}");
$query = mysqli_query($connect, "SELECT * FROM user_thoughts WHERE id IN " . implode(', ', $flagged_ids)." ORDER BY id DESC LIMIT {$start}, {$limit}");
if (mysqli_num_rows($query) < 1) {
header('HTTP/1.0 404 Not Found');
echo "<div class='nothing_to_say'>
$ufirstname has no flagged posts.
</div>";
exit();
}
With the above, I get two of the same errors:
mysqli_num_rows() expects parameter 1 to be mysqli_result - On line:
if(mysqli_num_rows($query) > ($page * $limit) ){
AND
if (mysqli_num_rows($query) < 1) {
If I comment these two lines out, then I get another error: mysqli_fetch_array() expects parameter 1 to be mysqli_result, on line:
while ($row = mysqli_fetch_array($query)) {
The problem here is:
$flagged_by2 = mysqli_query ($connect, "SELECT * FROM flagged_posts WHERE flagged_by_id = '$user_ident'");
$num_of_flagged_posts = mysqli_num_rows ($flagged_by2);
while ($getting_d = mysqli_fetch_assoc ($flagged_by2)){
$flagged_thought_id = $getting_d['thought_id'];
}
$query = mysqli_query($connect, "SELECT * FROM user_thoughts WHERE id='$flagged_thought_id' ORDER BY id DESC LIMIT {$start}, {$limit}");
You set in loop values for same variable and you should have array here. So you should rewrite this code into:
$flagged_by2 = mysqli_query ($connect, "SELECT * FROM flagged_posts WHERE flagged_by_id = '$user_ident'");
$flagged_ids = [];
$num_of_flagged_posts = mysqli_num_rows ($flagged_by2);
while ($getting_d = mysqli_fetch_assoc ($flagged_by2)){
$flagged_ids[] = $getting_d['thought_id'];
}
if (!$flagged_ids) {
$flagged_ids[] = 0;
}
$query = mysqli_query($connect, "SELECT * FROM user_thoughts WHERE id IN " . implode(', ', $flagged_ids)." ORDER BY id DESC LIMIT {$start}, {$limit}");
However you could make only 1 query as showed an comment and in addition you should use PDO with prepared statements - now yor code is vulnerable to SQL injection.