PHP mysql. Select users from table1 but select the users row infos from table2?

Question

I have 2 tables in my databes. Users and online_users.

in the online_user table the user get added to the table when he login and he gets deleted from the table when he logout. The table stores loginTIME and username.

I need help listing the members who are online.

I want to ONLY list the members who are in the online_user table(obv) but i want to select the rows from the table users becasue all the info of the users is stored in the table users.

How can i do this?

Answer 1

It would be far simpler to add an extra column to table one which states logged in yes or no. When a user logs in it can update the table with simple yes when they log out update it to say no.



In which case when the log in check is successful then another query happens which goes along the lines of



$SQL = "UPDATE ".$TB1." SET loggedin='yes' WHERE userid='username'";

Answer 2

If both tables have a username that's guaranteed to be unique, then you can do this:



SELECT users.username, users.WhateverOtherInfoYouWantHere FROM users

JOIN online_users ON online_users.username = users.username



I'll agree with everyone else that if there's only going to be one row per user in this table anyway, you're better off sticking the online_users information in the users table (unless there's some reason you can't.) I'm going to disagree with JR though; there's nothing wrong with using joins.

Answer 3

You would need a join but a far better way to design this is to have just a single table. A Bool column in your users table gets set to 1 or 0 when they are on or off line and a timestamp field updates when it is updated. That will preserve your data better too.

Answer 4

Politically correct has the proper solution! Just add a field in your users table called "online" and set it true when user logs-in, and false when he logs-off!

But if you can't change your table:

Use "join" (http://www. sitepoint.com / understanding-sql-joins -mysql-database/) ( remove spaces)



=> select * from `online_users` join `users` where xxx would be the way, but I do not know your table format.



Personnally, I NEVER use "join": it is a source of horrible optimisation problems and source of bugs, and it means that my tables are badly designed!!! :-)



I would do:

ASSUMING you have a field in users_online AND users called "userid"...

$link = dbconnect(); // your function to connect

$sql1 = "select `userid` from `online_users` where 1"; // to select all users that are on-line.

$res1 = mysqli_query($link, $sql1); // the list of online users

while ($row1 = mysql_fetch_array($res1))

{

$sql2 = "select * from `users` where `userid`='".$row['userid']."'";

$res2 = mysqli_query($link, $sql2);

$row2 = mysqli_fetch_array($res2); // you should only have ONE entry, so, no "while"

mysqli_free_result($row2);

// now you have userid from online_users, = to userid in `users` + details of that user: organise your output...

}

mysqli_free_result($res1);

...

Answer 5

Just use left join(or whichever)! You need to learn how to perform joins anyway and they are fairly simple.

Answer 6

Just out of curiosity, why aren't you using $_SESSION?