Jump to content

Not working properly. Grabbing name.


oz11
 Share

Go to solution Solved by Barand,

Recommended Posts

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 by oz11
Link to comment
Share on other sites

  • oz11 changed the title to Not working properly. Grabbing name.
  • Solution

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

 

  • Great Answer 1
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.
  • Thanks 1
Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.