A problem with mysql query from php

[firehawk777]

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.

[kenrbnsn]

You're overwriting the variables $row & $user in the nested loops. Having a query inside a loop is very inefficient. You should be able to write a single query to do the work. I usually get the query correct after a lot of trial and error in phpmyadmin.

 

Something like

<?php
$q = "select users.user from users, messages where users.id = messages.pid";
?>

is a good starting point.

 

Ken