Page 1 of 1

MySQL only returning one row

Posted: Fri Feb 27, 2009 10:08 am
by besly98
Hi all... this is my code and the sql is only pulling out 1 row... im baffled as to why. any ideas anyone?

<?


$sql3 = "SELECT * FROM wall WHERE id='".$userUNid."' ORDER BY orderid DESC LIMIT 3";
$results = mysql_query($sql3);
$arr = mysql_fetch_row($results);
$message = $arr[2];
$postid = $arr[3];


$sql2 = mysql_query("SELECT * FROM members WHERE id='".$postid."' ") or die ('Error: '.mysql_error ());
while($row = mysql_fetch_array($sql2))
{


echo "<h2 style='padding: 0px; margin: 15px 0px 7px 0px;'>Dougout</h2><div class='dogoutimg'><img src='http://www.jdbesly.co.uk/test/home/upload/";
echo $row['image'] ;
echo "' style='width: 60px;' /></div><div class='dougout' style='float: right;'>";
echo $message;
echo "</div>";
}

mysql_close($con);
?>

Re: MySQL only returning one row

Posted: Fri Feb 27, 2009 1:14 pm
by Randwulf
I don't know a whole lot about SQL so there's probably a more elegant way to solve this problem, but SQL normally only returns one row for whatever reason.

To get all of the rows you could do:

Code: Select all

 
while ($arr = mysql_fetch_row($results))
{
     //do some stuff
}
 
And each time the while loop iterates it grabs the next row.

P.S. It would be nice if you could put your code in code tags :)

Re: MySQL only returning one row

Posted: Fri Feb 27, 2009 1:17 pm
by besly98
sorry how do i put them in tags :-(

so do i place this around both sql queries?

Re: MySQL only returning one row

Posted: Fri Feb 27, 2009 2:09 pm
by Randwulf
besly98 wrote:sorry how do i put them in tags :-(

so do i place this around both sql queries?
To use code tags just put your code between [-code=php] and [-/code] (remove the dashes for it to work).

And yes, sort of. I think I'm confused as to what the problem is. Is it the first or the second query that isn't delivering all of the data you want?

Re: MySQL only returning one row

Posted: Fri Feb 27, 2009 2:14 pm
by besly98
its both. basicaly i have 2 tables. they both have id's i want the user who posts a message on someones profile to display ther epicture on the persons profile with that message... bit liek the facebook wall i guess.

Re: MySQL only returning one row

Posted: Fri Feb 27, 2009 2:23 pm
by John Cartwright
You need to iterate the first query like you did with the second one. The solution was even already posted, however I would stick to mysql_fetch_assoc() versus it's siblings.

Re: MySQL only returning one row

Posted: Fri Feb 27, 2009 2:29 pm
by besly98
im not sure i understand... :-( can you post an example?

Re: MySQL only returning one row

Posted: Sat Feb 28, 2009 11:38 am
by John Cartwright
By placing $arr = mysql_fetch_row($results) in a while() loop, you will iterate the result set return by the query. By only calling it once, $arr will only contain the data for the first returned row.

What I meant was:

Code: Select all

$sql3 = "SELECT * FROM wall WHERE id='".$userUNid."' ORDER BY orderid DESC LIMIT 3";
$results = mysql_query($sql3);
 
while ($arr = mysql_fetch_row($results)) { 
   $message = $arr[2];
   $postid = $arr[3];
 
   $sql2 = mysql_query("SELECT * FROM members WHERE id='".$postid."' ") or die ('Error: '.mysql_error ());
   while($row = mysql_fetch_array($sql2))
   {
      //do stuff
   }
}