mysql_fetch_array(): supplied argument in not a valid mysql result resource.

[marksie1988]

Hi Guys,

 

I have been working on a recursive select box that will allow sub-categories and keep getting the error:

 

<b>Warning</b>:  mysql_fetch_array(): supplied argument is not a valid MySQL result resource in <b>test.php</b> on line <b>22</b><br /

 

i have a table as follows:

 

CREATE TABLE IF NOT EXISTS `ost_help_topic` (
  `topic_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `isactive` tinyint(1) unsigned NOT NULL DEFAULT '1',
  `noautoresp` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `priority_id` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `dept_id` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `CatParent` bigint(11) unsigned DEFAULT NULL,
  `topic` varchar(32) NOT NULL DEFAULT '',
  `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `updated` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`topic_id`),
  UNIQUE KEY `topic` (`topic`),
  KEY `priority_id` (`priority_id`),
  KEY `dept_id` (`dept_id`)
) 

 

<?php
  
$db_host = "localhost";
$db_un = "user";
$db_pass = "pass";
$Item_DB = "data"; 
$table = "ost_help_topic";

$link = mysql_connect($db_host, $db_un, $db_pass);

$tab = "        "; // this is 8 spaces, which works as a pseudo-tab character inside the <option>s
$tablvl = 1;
function print_kids($pos) { // $pos is the current position inside the hierarchy (curr item's ID)
   global $link;
   global $tab;
   global $tablvl;
   $pos = ($pos?$pos:null);
   $query = "SELECT * from $table WHERE isactive=1 AND CatParent".($pos == null ? " IS NULL" : "=".$pos);
         // NULL parent == top level item.  For 0-parents, replace " IS NULL" with "=0"
   $res = mysql_db_query($Item_DB, $query, $link);
   if (!$res) print(mysql_error());
   while($row = mysql_fetch_array($res)) {
      $has_kids = mysql_fetch_array(mysql_db_query($Item_DB, "SELECT * from $table where isactive=1 AND CatParent=$row[0]", $link)) != null;
      print("<option value=\"$row[0]\">");
      for ($i=0; $i<$tablvl; $i++) print($tab);
      print("$row[6]</option>\n");
      if ($has_kids) {
         $tablvl++;
         print_kids($row[0]); // <span class="posthilit">recursive</span> call
         $tablvl--; 
      }

   } 
}

$numrows = 1;

$res = mysql_db_query($Item_DB, "SELECT * FROM $table", $link);

while (mysql_fetch_array($res)) $numrows++;

// Yes, I'm sure there's a more efficient way to do this <img src="./images/smilies/icon_razz.gif" alt="" title="Razz" />
  
print("<select name=\"hierarchy\" size=\"$numrows\">\n");
print("<option value=\"null\" selected=\"selected\">Root of all items</option>\n");
print_kids(0);
print("</select>");
mysql_close($link);
?>

[AyKay47]

the problem is in the query resource in which mysql_fetch_array() is using. The first step would be to debug your query by ouputting the sql to make sure that it is correct, and use mysql_error

[bigjoe11a]

Hi! Mark, Kay is right and your error is in the query, How ever I wouldn't try and code it like this. Try using a constant and not variables.

 

define('TABLE_NAME','ost_help_topic');

 

Then just use the defined constant. Using global in your code is not all ways a good idea.

 

[AyKay47]

Hi! Mark, Kay is right and your error is in the query, How ever I wouldn't try and code it like this. Try using a constant and not variables.

 

define('TABLE_NAME','ost_help_topic');

 

Then just use the defined constant. Using global in your code is not all ways a good idea.

 

IMO, using global is never a good idea. Whatever variables are needed in the function's scope should be passed via parameter list.

[litebearer]

aside...

5.3.0 This function now throws an E_DEPRECATED notice.

4.0.6 This function is deprecated, do not use this function. Use mysql_select_db() and mysql_query() instead.

http://php.net/manual/en/function.mysql-db-query.php

[Pikachu2000]

And 4.0.6 was released 10¹/₂ years ago . . .