Retrieving multiple rows with duplicate data

[perky416]

Hi Guys,

 

Im writing a message script for my website where users can send each other messages. When it comes to pulling the messages from the database and displaying them in the inbox im having some trouble.

 

When i use the code below i get the following error messages appear as soon as the page loads:

 

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /home/luser/public_html/website.co.uk/messages.php on line 16

 

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/user/public_html/website.co.uk/messages.php on line 21

 

Im assuming that the error messages are appearing as there are multiple rows using the same $username.

 

When i change

WHERE to='$username'

to

WHERE id='1'

it works perfectly.

 

$messages_query = mysql_query("SELECT * FROM messages WHERE to='$username'");
$messages_row = mysql_fetch_assoc($messages_query);

$from = $messages_row['from'];
$subject = $messages_row['subject'];

if (mysql_num_rows($messages_query) > 0)
{
while ($messages_row = mysql_fetch_array($messages_query));
{
	echo $from, $subject;
}
}

 

Does anybody have any ideas as to what im doing wrong?

 

Thanks

[Maq]

'to' is a reserved word in MySQL:

http://dev.mysql.com/doc/refman/5.1/en/reserved-words.html

 

You should try your best not to use them as table names.  You can either change the table name and every query with it or put backtics around `to` wherever it's used.

[perky416]

Thank you Maq,

 

I didnt know about reserved words.

 

I changed 'to' to 'recipient' and 'from' to 'sender' and it works perfectly.

 

Thanks to your advice i now know to look up reserved words if i have a similar problem in the future.

[Maq]

These types of errors usually refer to your query being invalid.  You can also get a specific error message by temporarily changing this line to:

$messages_query = mysql_query("SELECT * FROM messages WHERE to='$username'") or die(mysql_error());

 

You should be handling errors and exceptions properly though.  Have a read through this blog:

http://www.phpfreaks.com/blog/or-die-must-die

[perky416]

Hi Maq,

 

I have just encountered another problem.

 

Using the following code im only getting data from the first row WHERE recipient='$username'. Im trying to get it to display data from all rows WHERE recipient='$username'  Is it a mysql problem or a php problem?

 

$messages_query = mysql_query("SELECT * FROM messages WHERE recipient='$username'");
$messages_row = mysql_fetch_array($messages_query);

$from = $messages_row['sender'];
$subject = $messages_row['subject'];

if (mysql_num_rows($messages_query) > 0)
{
while ($messages_row = mysql_fetch_array($messages_query));
{
	echo $from, $subject;
}
}

 

Thanks

[Maq]

Because you are assigning $from and $subject before the loop so it's always the first row.  I fixed your code up a bit:

$sql = "SELECT * FROM messages WHERE recipient='$username'";

if (!$messages_query = mysql_query($sql)) 
{
   throw new Exception('SQL Error: ' . mysql_error());
}

if (mysql_num_rows($messages_query) > 0)
{
   while ($messages_row = mysql_fetch_array($messages_query))
   {
      echo "From: " . $messages_row['sender'];
      echo "Subject: " . $messages_row['subject'];
   }
}
else
{
   echo "No results.";
}

[perky416]

Thanks mate I appreciate it.

 

Iv noticed that without the code below, the details from the first row are not displayed. Would you happen to know why this is? Im still learning and would like to understand it better.

 

$sql = "SELECT * FROM messages WHERE recipient='$username'";

if (!$messages_query = mysql_query($sql)) 
{
   throw new Exception('SQL Error: ' . mysql_error());
}

 

Thanks

[Maq]

Because you're actually assigning $messages_query to the return value of  mysql_query($sql).  Upon success mysql_query() returns a resource id or false on failure, which is used multiple times later in the code.  Read more in the manual:

http://us2.php.net/mysql_query