mysql_fetch_array() problem

Question

So I have 3 DB tables that are all identical in every way (data is different) except the name of the table. I did this so I could use one piece of code with a switch like so:

function disp_bestof($atts) {
 extract(shortcode_atts(array(
  'topic' => ''
 ), $atts)); 
 $connect = mysql_connect("localhost","foo","bar");
  if (!$connect) { die('Could not connect: ' . mysql_error()); }
 switch ($topic) {
  case "attorneys":
   $bestof_query = "SELECT * FROM attorneys p JOIN (awards a, categories c, awardLevels l) ON (a.id = p.id AND c.id = a.category AND l.id = a.level) ORDER BY a.category, a.level ASC";
   $category_query = "SELECT * FROM categories";
   $db = mysql_select_db('roanoke_BestOf_TopAttorneys');
   $query = mysql_query($bestof_query);
   $categoryQuery = mysql_query($category_query);
  break;
  case "physicians":
   $bestof_query = "SELECT * FROM physicians p JOIN (awards a, categories c, awardLevels l) ON (a.id = p.id AND c.id = a.category AND l.id = a.level) ORDER BY a.category, a.level ASC";
   $category_query = "SELECT * FROM categories";
   $db = mysql_select_db('roanoke_BestOf_TopDocs');
   $query = mysql_query($bestof_query);
   $categoryQuery = mysql_query($category_query);
  break;
  case "dining":
   $bestof_query = "SELECT * FROM restaurants p JOIN (awards a, categories c, awardLevels l) ON (a.id = p.id AND c.id = a.category AND l.id = a.level) ORDER BY a.category, a.level ASC";
   $category_query = "SELECT * FROM categories";
   $db = mysql_select_db('roanoke_BestOf_DiningAwards');
   $query = mysql_query($bestof_query);
   $categoryQuery = mysql_query($category_query);
  break;
  default:
   $bestof_query = "switch on $best did not match required case(s)";
  break;
 }

 $category = '';
 while( $result = mysql_fetch_array($query) ) {
  if( $result['category'] != $category ) {
   $category = $result['category'];
   //echo "<div class\"category\">";
   $bestof_content .= "<h2>".$category."</h2>\n";
   //echo "<ul>";

Now, this whole thing works PERFECT for the first two cases, but the third one "dining" breaks with this error:

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource ... on line 78

Line 78 is the while() at the bottom. I have checked and double checked and can't figure what the problem is. Here's the DB structure for 'restaurants':

CREATE TABLE `restaurants` (
  `id` int(10) NOT NULL auto_increment,
  `restaurant` varchar(255) default NULL,
  `address1` varchar(255) default NULL,
  `address2` varchar(255) default NULL,
  `city` varchar(255) default NULL,
  `state` varchar(255) default NULL,
  `zip` double default NULL,
  `phone` double default NULL,
  `URI` varchar(255) default NULL,
  `neighborhood` varchar(255) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=249 DEFAULT CHARSET=utf8

Does anyone see what I'm doing wrong here? I'm passing "dining" to the function and as I said before, the first two cases in the switch work fine.

I'm sure it's something stupid...

Answer 1

Look at your query code - you run $bestof_query regardless of whether it has been set to valid SQL. My first guess is that you're misspelling 'dining' somewhere and getting the default case.

Also, double check that your database names are correct (they are fairly complicated) and that all databases have the same permissions. Are you checking whether $db is true?

Answer 2

You're getting a sql error on that query. You should echo your mysql error and review it to fix your query. The warning you're getting is because you're passing a boolean false to mysql_fetch_assoc() which is expecting a result set. mysql_query() returns false if there is an error.

Answer 3

You should always initialize the variable you use to some (null) value and then check for it before using it. My guess is that your third case (dining) never gets executed because of some misspelled identifier or something. This causes default: to run, after which your while() will execute anyway. However, $query is not set to anything useful.

Therefore, you should throw an exception or otherwise break execution in the default: handler. Or, you may initialize $query = null; before the switch() and only do the while() loop when $query !== null.

---

On a related note: you might code more efficient when you instead use the following (note the exception handler):

$db_name = null;
$table = null;
switch ($topic) {
  case "attorneys":
    $db_name = 'roanoke_BestOf_TopAttorneys';
    $table = 'attorneys'
  break;
  case "physicians":
   $db_name = 'roanoke_BestOf_TopDocs';
   $table = 'physicians'
  break;
  case "dining":
   $db_name = 'roanoke_BestOf_DiningAwards';
   $table = 'restaurants'
  break;
  default:
   throw new Exception("Unknown topic.");
  break;
 }

$bestof_query = "SELECT * FROM $table p JOIN (awards a, categories c, awardLevels l) ON (a.id = p.id AND c.id = a.category AND l.id = a.level) ORDER BY a.category, a.level ASC";
$category_query = "SELECT * FROM categories";
$db = mysql_select_db($db_name);
$query = mysql_query($bestof_query);
$categoryQuery = mysql_query($category_query);