Displaying results after MySQL JOIN query with PHP

Question

$sql = "SELECT messages.text, users.name FROM messages INNER JOIN users ON messages.user_id=users.id ORDER BY messages.ms_id DESC LIMIT 10";
$result = mysql_query($sql);
$rows = array();
    while($row = mysql_fetch_array($result))
    {
            $rows[]=$row;
    }
    echo $rows[0][1].$rows[0][0];
/*         for($i=0;$i<=10;$i++)
                {

                                  echo $rows[i][1].$rows[i][0];

                }
*/

This script is supposed to show the last 10 messages in a chat.What I'm doing is getting the Name of the user from the users table and the text from the message table and I want to display them in my chat window.Right now I have only 4 messages recorded and don't know how this affects the whole script I should implement a check for this too, but the bigger problem is that when i use echo $rows[0][1].$rows[0][0]; the info is displayed correctly, but when I try to make a loop so I can show tha last 10 (I tried the commented one) then nothing is displayed.I thought at least when I use this loop I'll see the 4 recorded messages but what really happen is a blank window.Obvously I have the info recorded in $rows[] and can echo it, but don't understand why this loop don't work at all.I'll appreciate if someone can help me with this and with the check if the messages are less then 10.

Thanks.

Leron P.S Here is the edited script, thanks to all of you, I need the array because otherwise the most recent message is shown at the top which is not an opiton when I use it for diplaying chat masseges.

 for($i=10;$i>=0;$i--)
            {
                      if($rows[$i][1]!="" || $rows[$i][0]!="")
                           {
                              echo $rows[$i][1].' : '.$rows[$i][0];
                              echo "</br>";
                           }
            }

Fosco · Accepted Answer · 2011-06-08 22:29:00Z

2

Your FOR loop was running 11 times even if only 10 records. The second clause should be a < instead of <=. Plus the $ was missing on the i variable.

For example sake, you don't really need to make an array from the rows, and you can refer to the fields by name:

while($row = mysql_fetch_array($result))
{
    echo $row['name'] . ' says: ' . $row['message'] . '<BR>';
}

answered Jun 8, 2011 at 22:29

Fosco

38.6k7 gold badges90 silver badges101 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

BugFinder · Accepted Answer · 2011-06-08 22:28:35Z

1

why not just do

   while($row = mysql_fetch_array($result))
    {
            echo $row[1]." ".$row[0];
    }

Your query, auto limits it to the last 10, this will then show anything from 0 to 10 which get returned.

PS I added a space between username and message for readability

answered Jun 8, 2011 at 22:28

BugFinder

17.9k4 gold badges42 silver badges58 bronze badges

Comments

KOGI · Accepted Answer · 2011-06-08 22:29:49Z

1

You need $ symbols on your i variable:

for($i=0;$i<10;$i++)
{
   echo $rows[$i][1].$rows[$i][0];
}

A more robust solution would be like this:

$sql = "SELECT messages.text, users.name FROM messages INNER JOIN users ON messages.user_id=users.id ORDER BY messages.ms_id DESC LIMIT 10";
$result = mysql_query($sql);

while($row = mysql_fetch_array($result))
{
   echo $row[1].$row[0];
}

answered Jun 8, 2011 at 22:29

KOGI

3,9992 gold badges26 silver badges38 bronze badges

Collectives™ on Stack Overflow

Displaying results after MySQL JOIN query with PHP

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related