[SOLVED] mysql_num_rows warning - Does NOT make any sense...

[szym9341]

Hi!  I have a little class that generates a SQL statement, passes the statement to function 'getNumRows' and returns a value.  The class works fine, but flags an error when I have more than two elements in the array.

 

example: This flags the warning "mysql_num_rows(): supplied argument is not a valid MySQL result resource"

 

$si['additional'][0] = "WHERE t1.project_id = '$project_id'";
$si['additional'][1] = "AND t1.todo_cat_id = '$todo_cat_id'";
$si['additional'][2] = "ORDER BY t1.todo ASC";
$data = new getTodos($si);
$anyrows = $data->getNumRows();

 

HOWEVER, this works fine... no warning

 

$si['additional'][0] = "WHERE t1.project_id = '$project_id'";
$si['additional'][1] = "AND t1.todo_cat_id = '$todo_cat_id' ORDER BY t1.todo ASC";
$data = new getTodos($si);
$anyrows = $data->getNumRows();

 

The class:

 

class getTodos {
  public $sql;

  function __construct($si = "") {

  $this->sql = "SELECT $select
                      FROM todos AS t1
                      INNER JOIN todo_cats AS t2 ON t1.todo_cat_id = t2.todo_cat_id
                    ";

  if ((isset($si['additional']) && ($si['additional'] != NULL))) {
    foreach ($si['additional'] as $i) {
      $this->sql .= " $i";
    }
  }
  }

  function getSql() {
    return $this->sql;
  }

  function getNumRows() {
    $dbConnector = new dbConnector('main');
$query = $dbConnector->query($this->sql);
$num = $dbConnector->getNumRows($query);
    return $num;
  }	  

}

 

Either way, the output SQL statement is exactly the same... one way works and the other way does not... PLEASE HELP...

[revraz]

Well apparently the output SQL is not the same or it wouldn't error.  The SQL is not valid so you get a bad Query return.

[szym9341]

Thanks.  I'd love to understand why.  The class has a getSql function that returns the value of $this-sql.

 

This output does not work:

SELECT * FROM todos AS t1 INNER JOIN todo_cats AS t2 ON t1.todo_cat_id = t2.todo_cat_id WHERE t1.project_id = '365' AND t1.todo_cat_id = '5' ORDER BY t1.todo ASC

 

And this output works:

SELECT * FROM todos AS t1 INNER JOIN todo_cats AS t2 ON t1.todo_cat_id = t2.todo_cat_id WHERE t1.project_id = '365' AND t1.todo_cat_id = '5' ORDER BY t1.todo ASC

[Mchl]

What is dbConnector class?

[szym9341]

Here is the dbConnector class: (systemComponent just sends login info)

 

<?php
require_once ('../../systemComponent.php');

class dbConnector extends systemComponent {

  public $theQuery;
  public $link;

  function __construct($which_db) {

    // Load settings from parent class
    $settings = systemComponent::getSettings();
    
if ($which_db == 'main') {
  // array of zero = main database; add else if for another connection
      $host = $settings[0]['dbhost'];
      $db = $settings[0]['dbname'];
      $user = $settings[0]['dbusername'];
      $pass = $settings[0]['dbpassword'];
}

    // Connect to the database
    $this->link = mysql_connect($host, $user, $pass);
    mysql_select_db($db);

  }
  
  function __destruct() {
    //mysql_close($this->link);  
  }

  //*** Function: query, Purpose: Execute a database query ***
  function query($query) {
    $this->theQuery = $query;
    return mysql_query($query, $this->link);
  }

  //*** Function: getNumRows, Purpose: Return row count, MySQL version ***
  function getNumRows($result){
    return mysql_num_rows($result);
  }

  //*** Function: fetchArray, Purpose: Get array of query results ***
  function getArray($result) {
    return mysql_fetch_array($result);
  }


}
?>

[Mchl]

Try displaying mysql_error after running this query.

[szym9341]

I added mysql_error() after the query is executed in dbConnector and a new error pops up NOTICE: Undefined offset: 0

[Maq]

did you add it like this?

 

    return mysql_query($query, $this->link) or die(mysql_error());

 

That should be afer mysql_query & mysql_num_rows.

[szym9341]

Okay, I added those lines in the dbConnector class... the error, in total is:

 

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /directories/dbConnector.php on line 40

 

  //*** Function: query, Purpose: Execute a database query ***
  function query($query) {
    $this->theQuery = $query;
     return mysql_query($query, $this->link) or die(mysql_error());
  }

  //*** Function: getNumRows, Purpose: Return row count, MySQL version ***
  function getNumRows($result){
    return mysql_num_rows($result) or die(mysql_error());
  }

[PFMaBiSmAd]

If the query did not fail, giving the or die(...) output, then it is more likely that getNumRows is being called somewhere else (perhaps after an INSERT or UPDATE query) or in a loop that overwrites the $result. What is your whole code that produces the error?

[szym9341]

The script stopped when I added the 'die' in the query.  Here is everything:

 

The error:

<b>Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/szym9341/public_html/cms/_library/dbConnector.php on line 41</b>

 

The script displaying the error:

<?php
ini_set('display_errors', '1');
error_reporting(E_ALL);

require_once('../_library/getTodoCats.php');

$si = array();
$si['additional'][0] = "WHERE t1.project_id = '365'";
$si['additional'][1] = "AND t1.todo_cat_id = '1'";
$si['additional'][2] = "ORDER BY t1.todo ASC";
$data = new getTodos($si);
$anyrows = $data->getNumRows();
echo $anyrows;
?>

 

The class:

<?php
require_once('dbConnector.php');

class getTodoCats {
  public $sql;

  function __construct($si = "") {

    // limit select, if needed
if ((isset($si['select']) && ($si['select'] != NULL))) {
  $select = $si['select'];
} else {
  $select = '*';
}

// foundational SQL
    $this->sql = "SELECT $select
                  FROM todo_cats AS t1
			  ";
    
    // additional SQL, if needed	
    if ($si['additional'] != NULL) {
      foreach ($si['additional'] as $i) {
      $this->sql .= " $i";
      }
    }
  }

  function getSql() {
    return $this->sql;
  }

  function getNumRows() {
    $dbConnector = new dbConnector('main');
$query = $dbConnector->query($this->sql);
$num = $dbConnector->getNumRows($query);
    return $num;
  }	  
  
}  
?>

 

dbConnector:

 

<?php
require_once ('../systemComponent.php');

class dbConnector extends systemComponent {

  public $theQuery;
  public $link;

  function __construct($which_db) {

    // Load settings from parent class
    $settings = systemComponent::getSettings();
    
if ($which_db == 'main') {
  // array of zero = main database; add else if for another connection
      $host = $settings[0]['dbhost'];
      $db = $settings[0]['dbname'];
      $user = $settings[0]['dbusername'];
      $pass = $settings[0]['dbpassword'];
}

    // Connect to the database
    $this->link = mysql_connect($host, $user, $pass);
    mysql_select_db($db);

  }
  
  function __destruct() {
    //mysql_close($this->link);  
  }

  //*** Function: query, Purpose: Execute a database query ***
  function query($query) {
    $this->theQuery = $query;
     return mysql_query($query, $this->link) or die(mysql_error());
  }

  //*** Function: getNumRows, Purpose: Return row count, MySQL version ***
  function getNumRows($result){
    return mysql_num_rows($result) or die(mysql_error());
  }

}
?>

[szym9341]

They should have a 'slap me' button in forums...  After reading PFMaBiSmAd's reply I started stripping everything out and found later a query was being created and the $si array wasn't cleared... so, the statement was bringing in the $si['additional'][2] and causing an error down on that line... the error reporting never flagged this line number.

 

Ohh well.  Thanks everyone for getting me thinking.