Reputation: 4272
I want to iterate through all the records of mysql table. Here's how my table looks like
So I want to print out the name of the user and the subject of the post they posted. I have another user table. Here's my php code.
require_once('config.php');
// build SQL query
$sql ="SELECT user_id, subject FROM post";
$result = mysqli_query($conn, $sql);
$row = mysqli_fetch_assoc($result);
foreach ($row as $k => $v) {
if($k == 'user_id'){
$uid = $v;
$sql2 ="SELECT '$db', username FROM users WHERE user_id='$uid'";
$result2 = mysqli_query($conn, $sql2);
$row2 = mysqli_fetch_row($result2);
echo $row2[1]. " posted ";
}
if ($k == 'subject') {
echo "<a href='#'> $v </a>";
}
}
I am using the user_id to find the user in my other table. Pretty much a noob here. However I only get one output.
However there should have been 2 outputs. because my post table contains 2 posts made by 2 different users. I only get the first post as an output. can someone please point me to right direction. Also how do I iterate a SQL table for all the records.
Upvotes: 0
Views: 66
Reputation: 20469
If you expect your query to return more than 1 result, you should call mysqli_fetch_assoc
in a while loop.
Also, you can use a JOIN
query to return all the data in a single query:
$sql = "SELECT post.subject, users.username
FROM post JOIN users
ON post.user_id = users.user_id";
$result = mysqli_query($conn, $sql);
while($row = mysqli_fetch_assoc($result)){
echo sprintf("<p>%s posted <a href='#'>%s</a></p>", $row['username'], $row['subject']);
}
Upvotes: 1
Reputation: 148
I can't see where the "$db" variable is coming from, but you seem to be using it in the space where you need to define the columns you want to retrieve.
Can I suggest that you perform a join on the post table instead of performing a MySQL query on every iteration.
SELECT
`post`.`user_id` AS `post_user_id`,
`post`.`subject` AS `post_subject`,
`users`.*
FROM
`post`
LEFT JOIN
`users` ON `post`.`user_id`=`users`.`id`
This will get you all posts, and join on the user data associated with the post if the id exists in the user table. I have just guessed the column name for the user id in the users table, but this should work.
Upvotes: 0