Not working properly. Grabbing name.

[oz11]

OK so guys and gals, I'm writing a chat program and I want it to display a username in the user online box. So, i reached this code (which works)..

		$stmt = $pdo->prepare("SELECT * FROM `joined_chats` WHERE room_id = ? AND CURRENT_TIMESTAMP() <= date_expire GROUP BY user_id;");
		$stmt->execute([$_GET['id']]); 
		while ($row = $stmt->fetch()) {
   			 echo "<li>".$row['user_id']."</li>";
		}

So, this shows the user id's of all users.. but I want it to be usernames, so i changed it to...

		$stmt = $pdo->prepare("SELECT * FROM `joined_chats` WHERE room_id = ? AND CURRENT_TIMESTAMP() <= date_expire GROUP BY user_id;");
		$stmt->execute([$_GET['id']]); 
		while ($row = $stmt->fetch()) {
			$stmt = $pdo->prepare("SELECT * FROM users WHERE user_id=?");
			$stmt->execute([$row['user_id']]); 
			$user = $stmt->fetch();
   			 echo "<li>".$user['name']."</li>";
		}

However now It only displays one user...

What am I doing wrong guys and gals? Been a few months since i last coded php.

Edited October 27, 2022 by oz11

[Barand]

Overwriting your first $stmt object with the second one perhaps?

A couple of DON'TS for you...

• Don't use SELECT * - specify just the columns you need. That makes it more efficient and people like us can see what the query is doing.
• Don't run queries inside loops like that. Use a single query with a JOIN

SELECT jc.user_id
     , u.name
FROM joined_chats jc
     JOIN users u ON jc.user_id = u.user_id
WHERE jc.room_id = ?
      AND NOW() <= jc.date_expire
ORDER BY user_id

 

[oz11]

Thanks. Got criticised for using WHERE joins, eg:

WHERE joined_chats.user_id = users.user_id 

But always worked out for me. Is it a bad thing? It was the way i was taught at Uni.

[Barand]

I'd criticise you for those too.

Yes, you could have used "... FROM joined_chats jc, users u WHERE jc.user_id = u.user_id ..." but I dislike that method for several reasons.

• it confuses the row selection conditions with join conditions and obfuscates the structure of the query and its table relationships.
• That syntax can't be used for LEFT/RIGHT OUTER JOINS. Even if you want a cartesian join with no conditions, use tableA CROSS JOIN tableB to be explicit about what is required and you haven't just forgotten the WHERE bits.
• I have found queries using WHERE JOINS run slower than the same query using explicit JOIN .. ON syntax.