mysql fetch assoc strangeness!

what the hell? ive done this millions of times before... :-s

[delboy1978uk]

really simple code here guys, no idea whats wrong :-(

$newsq = mysql_query("SELECT * FROM news ");
$rows = mysql_num_rows($newsq);
echo $rows.' rows.<br ?>';
$num = 1;

while($news = mysql_fetch_assoc($newsq));
{
echo 'row '.$num.'<br />';
echo $news['newsdate'].'1<br/>';
echo $news['headline'].'2<br/>';
echo $news['news'].'3<br/>';
}

The query works fine the mysql editor i have, and I get 3 rows of results.
The output, however, is like this:

3 rows.
row 1
1
2
3

it should have been:

3 rows
row1
date1
headline2
news3
row2
date1
headline2
news3
row3
date1
headline2
news3

so its as if mysql_fetch assoc($newsq) isn't working
no data seems to be stored yet it runs through the loop once displaying nothing but the static text!

its bustin' my chops guys, can anyone help?

[delboy1978uk]

now its working! im gonna paste the code in, compare, and see what i did wrong!
$newsq = mysql_query("SELECT * FROM news ");
$rows = mysql_num_rows($newsq);
echo $rows.' rows.<br ?>';
$num = 1;
while($news = mysql_fetch_assoc($newsq))
{
echo 'row '.$num.'<br />';
echo $news['newsdate'].'1<br/>';
echo $news['headline'].'2<br/>';
echo $news['news'].'3<br/>';
$num++;
}

[Frank_Rizzo]

It could be because you have a field 'news' which is the same name as the table.

If not try a print_r($news) instead of the loop dumping each row.

Lastly is it due to lowercase field names?

[Trav]

assuming the column names are correct (you're not getting a mysql error on the output, right?), I'm not seeing an iterator for the $num variable, so it always stays at '1'. Once you have that, I think you need something like:

while....
{
echo 'row '.$num.'<br />';
echo $news[$num]['newsdate'].'1<br/>';
echo $news[$num]['headline'].'2<br/>';
echo $news[$num]['news'].'3<br/>';
}

Or change mysql_fetch_assoc to mysql_fetch_row, so the array you get isn't multi-dimensional.

[delboy1978uk]

lmao there was a semicolon after the while statement before the curly brackets

[delboy1978uk]

and yeah i forgot to add that lol but it still shouldnt have mattered that was just so i could see how many rows it went through
cheers everyone!

[Matthew1980]

Hi all,

In the given (working) example to disambiguate the column/table name 'news', encase it in backticks

//enable error reporting, but remove when releasing/going live :)
error_reporting(E_ALL);
$newsq = mysql_query("SELECT * FROM `news` ") or die(mysql_error());//remove the extra directive when live
echo mysql_num_rows($newsq)."</br>";
$num = "1";

//loop through results
while($news = mysql_fetch_array($newsq)){//This would work just as well
echo "row ".$num."<br />\n\r";
echo $news['newsdate']."1<br/>\n\r";
echo $news['headline']."2<br/>\n\r";
echo $news['news']."3<br/>\n\r";
$num++;
}

In your example I added the error handler (just in case something server side went wrong) and also this line had invalid syntax (guessing a typo):-

$rows = mysql_num_rows($newsq);
echo $rows.' rows.<br ?>';

I changed this to be a bit more streamlined too:-

echo mysql_num_rows($newsq)."</br>";

And yes more than likely, the semi-colon after the closing parenthesis on the while loop would have thrown an error, if you had error_reporting(E_ALL); this would have been flagged up as a warning.

Hope this helps,

Cheers,
MRb