While loop + mysql_query question

[Lsmjudoka]

I have a script that runs a static chatroom(User has to refresh the page manually). When a user submits a post, the user's data(Username, title, user level, etc..) is stored in the DB along with the post.

The script for getting the post looks something like this:

$result = mysql_query("SELECT * FROM Chatroom ORDER by messageid DESC LIMIT 0, 10");

while($message = mysql_fetch_array($result)) {
// Code that takes message and user data like username, rank, etc, and displays it
}

Someone suggested that instead of storing all that data in the social table, I should split it up like this:

$postresult = mysql_query("SELECT * FROM Chatroom ORDER by messageid DESC LIMIT 0, 10");

while($message = mysql_fetch_array($postresult); {

$user_result = mysql_query("SELECT * FROM Members WHERE userid='{$message['userid']}' ");

// code that gets user data and message data from arrays and prints it

}

The second one looks horribly inefficient, because it appears to make 11 queries to the DB. Am I really ignorant about the inner workings of mysql queries, or is this guy messing with me?

[james.newell]

Hi Lsmjudoka.

This technique of separating tables is called normalization and used to prevent data redundancy.

Whether it is more efficient really depends on your situation and how often the data is accessed. In this particular script, separating out the tables will definately be slower because of the extra queries (although you could do a join) but the separation may cause another script that only prints out user data and not chatroom data to be more efficient because it is only pulling user data and not the chatroom data as well. It should also make your database size smaller.

Hope that helps.

[Lsmjudoka]

I figured something along those lines, but he said that the while loop would somehow only execute one query O.o Which didn't at all make sense to me.

As for the redundancy, I would expect to do something more like this:

SELECT 
Members.info1,
Members.info2,
Chatroom.message
WHERE Members.user_id = Chatroom.user_id

Instead of adding on a separate query.