Jump to content


Photo

MySQL query while loop problem


  • Please log in to reply
3 replies to this topic

#1 FamousMortimer

FamousMortimer
  • New Members
  • Pip
  • Newbie
  • 4 posts

Posted 09 July 2006 - 10:18 PM

I am having a similar problem with a while loop.....I am trying to have a table where the rows alternate colors, so it becomes easier to read.

here is my code:
   // Make the query.
   $query = "SELECT last_name, first_name, DATE_FORMAT
     (registration_date, '%M %d, %Y') AS dr, user_id FROM users
     ORDER BY $order_by LIMIT $start, $display";
   $result = mysql_query ($query); // Run the query.

...
   // Fetch and print all the records.
   $bg = '#eeeeee'; // Set the background color.
   while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
      $bg = ($bg=='#eeeeee' ? '#ffffff' : '#eeeeee'); // Switch the background color.   
      echo '<tr bgcolor="' . $bg . '">
         <td align="left"><a href="edit_user.php?id=' .
           $row['user_id'] . '">Edit</a></td>
         <td align="left"><a href="delete_user.php?id=' .
           $row['user_id'] . '">Delete</a></td>
         <td align="left">' . $row['last_name'] . '</td>
         <td align="left">' . $row['first_name'] . '</td>
         <td align="left">' . $row['dr'] . '</td>
      </tr>
      ';
   }

here is the error(warning) I keep getting:
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\Apache2\htdocs\view_users.php on line 107

Does anyone have any ideas as to why result becomes invalid?

#2 kenrbnsn

kenrbnsn
  • Staff Alumni
  • Advanced Member
  • 8,235 posts
  • LocationHillsborough, NJ, USA

Posted 09 July 2006 - 11:47 PM

Change this line:
<?php $result = mysql_query ($query); ?>
to
<?php $result = mysql_query ($query) or die("Problem with the query: $query<br>" . mysql_error());  ?>
and see what the error message tells you.

Ken

#3 toplay

toplay
  • Staff Alumni
  • Advanced Member
  • 973 posts

Posted 10 July 2006 - 01:44 AM

Please don't post your problem in another members topic. I've seen this become very confusing for everyone. As you can see I've split yours into a separate topic.

This is a frequent error and is explained in the FAQ topic pinned at the top of this PHP Help area. Here's a quote from it:

...these messages are usually attributed to wrong SQL syntax in the query (or not connected to the database). What I mean by wrong syntax includes the possibility of using the wrong table or column names, and not just syntactically incorrect formatting of the query. This message can be avoided altogether; it masks/hides the real error at the query and is a by-product of wrong logic. These messages occur because error checking wasn't performed after a query and a subsequent SQL command was issued to work on the query result (when there was already a query error). This goes back to what I was explaining in the MySQL Data Retrieval section above (which has examples of correct MySQL error checking). You must check for errors and when there are errors, don't execute any more SQL commands that retrieve data or require the use of the results table. Handle the error after a query appropriately and your code logic shouldn't get as far as trying to read or examine the data inappropriately, then this warning message won't ever occur again. You need to display the real error that caused the query to fail in order for you to solve the main problem (which is usually bad SQL query syntax). Not handling errors like this is a serious logic flaw in code and I see it all the time. A big part of coding is really error checking. Don't get lazy or forget to check for errors.


Do what kenrbnsn has suggested to help yourself see what the query error is.


#4 FamousMortimer

FamousMortimer
  • New Members
  • Pip
  • Newbie
  • 4 posts

Posted 10 July 2006 - 03:03 AM

Hey thanks for the advice.  I put in the die() function and found out that the problem was in my query, a space in between a the mysql function and the parenthesis {i.e.: 'DATE_FORMAT^(' 'instead of DATE_FORMAT('}

Also, I'm sorry for the "breaking the forum rule", thanks for informing me about the confusing practice, I had no idea it was that big of a deal.  I appologize.

FM




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users