A problem with mysql query

PHP
1

Hi there
I am using a query to find what user made a comment. The problem I am having is I get the wrong user from the query. Everything seems fine in the database and I just can’t seem to track the problem

		$query = "SELECT * FROM messages";
		$result=mysql_query($query) or die("Invalid Query : ".mysql_error());
			while ($row = mysql_fetch_assoc($result)){
				$pid=($row["pid"]);
				$id=($row["id"]);
				$query = "SELECT * FROM users WHERE id='$pid'";
				$rresult=mysql_query($query) or die("Invalid Query : ".mysql_error());
				while ($row = mysql_fetch_assoc($rresult)){
				$user=($row["user"]);
				}
                       }

The query is working in reverse I.E when I search for what messages are from the user. Can anyone see my problem. :confused:

2

Perhaps more like this…


$query = "SELECT * FROM users ORDER BY id";
$result = mysql_query($query);
while($row = mysql_fetch_array($result)){
	$user = $row['user'];
	$id = $row['id'];
	$query2 = "SELECT * FROM messages WHERE pid = '$id' ORDER BY pid";
	$result2 = mysql_query($query2);
	echo $user . "<br/>";
	while($row2 = mysql_fetch_array($result2)){
		echo $row['message'] . "<p>";
	}
	echo "<hr>";
}
3

My mistake it was in the order I was calling the queries.:goof:
Thanks all anyway!

4

firehawk777, assuming your after a list of messages, from the table and want to display the username you should be able to do it with one query.

$sql="
    SELECT
          messages.*
        , users.name
    FROM
        messages
    INNER JOIN
        users
    ON
        messages.pid = users.id
";

$result=mysql_query($sql) or die("Invalid Query : ".mysql_error());

while ( $row = mysql_fetch_assoc($result) ) {
    $messages[] = $row;
}

The name field in the users table may be different, just change the field name to suit. If you need just the messages belonging to a single user, just add a WHERE clause to suit.

btw if you’ve not used joins before, when referring to a field in a join query which doesn’t have an alias, the syntax is:

table_name.field_name

5

Thanks Spacephoenix.
I will try this.