sql error problem - my mind is slipping away

[paul2463]

Hi Guys, I am getting an error and for the life in me I cannot figure out why here is the code, well the part of it you need:-
[code]
$query = "SELECT idpurchord, date FROM purchord";
$result = mysql_query($query) or die ('Error in pw query: $query. ' . mysql_error());
if (mysql_num_rows($result) > 0)
{
echo "Inside if statement  ";
echo $result;
while ($row = mysql_fetch_assoc($result))    // this is line 57
        {
          BLAH BLAH BLAH
[/code]

I have put the two echos inside just to check whats happening and what I get when i run this is

[quote]
Inside if statement  Resource id #4
Warning: mysql_fetch_assoc(): 4 is not a valid MySQL result resource in C:\Program Files\xampp\htdocs\****\regular.php on line 57
[/quote]

I cant fisgure out why i get the error, i am pulling data from the database, i know this because it echos "inside if statement" and then the result resource id so why does it error when i try and assign it to $row???

any help gratefully received

Paul

[kenrbnsn]

Try using backticks, " ` ", around the word "date" in your query. Also, use double quotes in the "or die" argument. If you don't, the $variable "$query" will not get expanded.

[code]<?php
        $query = "SELECT idpurchord, `date` FROM purchord";
$result = mysql_query($query) or die ("Error in pw query: $query. " . mysql_error());
if (mysql_num_rows($result) > 0)
{
echo "Inside if statement  ";
echo $result;
while ($row = mysql_fetch_assoc($result))    // this is line 57
        {
          BLAH BLAH BLAH
?>[/code]

Ken

[paul2463]

thanks Ken
i did what you suggested and changed the code a bit to make it easier to read when run
[code]
<?php
$query = "SELECT idpurchord, `date` FROM purchord";
$result = mysql_query($query) or die ("Error in pw query: $query. " . mysql_error());
if (mysql_num_rows($result) > 0)
{
echo mysql_num_rows($result);
echo ("<br>");
echo "Inside if statement  ";
echo ("<br>");
echo $result;
echo ("<br>");
while($row = mysql_fetch_assoc($result)) {
echo "Got in here too";
echo ("<br>");
?>
[/code]

when I run it now I get the following

[quote]
2                              //number of rows returned
Inside if statement      // inside the if statement as more than 0 are returned
Resource id #4            //resource id
Got in here too            //inside while loop, but this should have been printed twice as there are two rows
Warning: mysql_fetch_assoc(): 4 is not a valid MySQL result resource in C:\Program Files\xampp\htdocs\****\regular.php on line 61
[/quote]

then it goes and throws the error again

I even changed the column name from date to dateadded to see if that was causing the problem but to no avail.

[paul2463]

Hi Ken

sorry I sheepishly say, I am stupid and therefore need to be chastised or at least open a bottle of wine and forget that the last hour ever happened

I had a
mysql_free_result($result);

and it was inside one to many closing braces, so it got run at the end of the first pass through and obviuously when it got back to the top again $result no longer existed...

please accept my humblest apologies

Paul

[kenrbnsn]

No problem.

BTW, you don't need to use parenthesizes with echo statements.

Ken