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
}
}