[PHP] Only one result returned out of many..

[+dave164 Subscriber¹]

Hey,

I have this SQL query

$sql = mysql_query("SELECT n.*, r.`newspic` FROM `news` as n, `regiments` as r WHERE n.`regID` = r.`regID` AND n.`memberonly` = 0 ORDER BY n.`dateposted` DESC");

However when i run it in PHP under a while loop, only one column is outputted, dispite the fact that running this query in phpmyadmin returns all the columns.

All my other loops work fine, its just this one...

Any surgestions?

Thanks,

Garnett

[AnthonySterling]

Pop up your PHP code, your SQL looks OK.

SilverB.

[Andrew Lyle Global Moderator]

Wow that looks messy.

I suggest trimming all the extras off and seeing what returns. If you still only get 1 result, it might be an old problem with PHP, where having two of the same queries only returns a single result..

But try taking all that extra lines off first

[+dave164 Subscriber¹]

Wow that looks messy.

I suggest trimming all the extras off and seeing what returns. If you still only get 1 result, it might be an old problem with PHP, where having two of the same queries only returns a single result..

But try taking all that extra lines off first

Yup i tried it with just SELECT * FROM news, not working.

The PHP:

(this is a right state, but meh)

<?php
$sql = mysql_query("SELECT n.*, r.`newspic` FROM `news` as n, `regiments` as r WHERE n.`regID` = r.`regID` AND n.`memberonly` = 0 ORDER BY n.`newsid` DESC");
while ($result = mysql_fetch_array($sql)) {
 echo '<div id="newsback"><div style="margin: 0 10 0 10; height: 276px;">';
 echo '<div style="width: 97%; margin: 0 0 10 5;"><div style="padding: 10 0 0 5; text-align: left;"><a href="news.php?id='.$result['newsid'].'" style="color: #666633; font-size: 12pt;">' . $result['title'] . '</a>';
 if (isset($admin) && $admin == 1) {
  echo '<div style="font-size: 8pt; text-align: right; margin: -15 0 0 0;"><a href="submitnews.php">Submit news</a> | <a href="managenews.php?action=delete&id='.$result['newsid'].'">Delete</a> | <a href="managenews.php?action=edit&id='.$result['newsid'].'">Edit</a></div>';
 }
 echo '</div></div>';
 echo '<div style="text-align: left; width: auto; overflow: hidden; font-size: 8pt; margin: -10 0 0 2px;"><center><img src="'.$result['newspic'].'" style="width: 97%; height: 100px; margin: 10 0 -10 0px;"></center><p>';
 if (strlen(stripslashes($result['content']))>200) {
  echo substr_replace($result['content'], "  <a href='news.php?id={$result['newsid']}'>...more</a>", 200); 
 }  
 else {
  echo $result['content'];
 }
 echo '</div></div>';
 $sql = mysql_query("SELECT count(newsid) as nocomments FROM newscomments WHERE newsid = '{$result['newsid']}' GROUP BY newsid LIMIT 1");
 if (mysql_num_rows($sql)!=0) $numcomments = mysql_fetch_object($sql)->nocomments;
 else $numcomments = 0;  
 echo '<div style="float: left; font-size: 8pt; margin: -30 0 10 20px; color: #6f6f6f;">' . $result['about'];
 echo ' | <a href="news.php?id='.$result['newsid'].'" style="color: #666633;">Comment ('.$numcomments.')</a></div></b>';
 echo '</div>';
}
?>

[AoM_Scott]

You are using the $sql variable within the while statement?

You need to use a different variable for the second query for it to work.

[+dave164 Subscriber¹]

BRILLIANT

That'd be why! Thanks!

[Andrew Lyle Global Moderator]

So the problem is fixed now?

[+dave164 Subscriber¹]

Yeah cheers thats done it =]