mysql_num_rows() expects parameter 1 to be resource, boolean in joomla component

Question

When I execute the function below query execute successfully but I get a warning above the result the query:

mysql_num_rows() expects parameter 1 to be resource, boolean

How do I fix this?

public function retrieve()
{
    $id=JRequest::getVar('id');
    $db =JFactory::getDBO();
    $sql="select * 
          from 
            #__npco_car,#__npco_namayeshgah,#__npco_agahi
          where 
            #__npco_car.car_id='$id' and
            #__npco_namayeshgah.id_namayeshgah=#__npco_agahi.id_namayeshgah and 
            #__npco_car.car_id=#__npco_agahi.car_id
     ";
    $db->setQuery($sql);

    $db->query();
    $row = $db->getNumRows();

    if($row == 1) {
        return $db->loadAssocList();   
    } else {
        $db = JFactory::getDBO();
        $sql="select * 
              from 
                 #__npco_car,#__npco_useragahi,#__npco_user
              where 
                 #__npco_car.car_id='$id' and 
                 #__npco_user.id_user=#__npco_useragahi.id_user and 
                 #__npco_car.car_id=#__npco_useragahi.car_id
        ";
        $db->setQuery($sql);
        return $db->loadAssocList();
    }
}

Try adding `$query = $db->getQuery(true);` after `$db =JFactory::getDBO();`. Also, I assume you are using Joomla 2.5 or 3.x so when writing SQL queries, I suggest you stick to the correct coding standards ;) — Lodder, Aug 08 '13 at 05:20
[Check This](http://stackoverflow.com/questions/7766418/mysql-num-rows-expects-parameter-1-to-be-resource-boolean-given-in#answer-7766477) — rynhe, Aug 08 '13 at 05:24
Look to the right hand side of this page. See how many previous questions have been asked for exactly this same issue. It means that you've run an invalid query, but not checked for errors before trying to use the results. Fix your query and add some error handling. — Spudley, Aug 08 '13 at 14:00

score 1 · Answer 1 · answered Aug 08 '13 at 13:00

Your code has several issues.

Never use unchecked/unvalidated request values, not even in examples!
Use the query builder.
Reduce coupling by a) setting the database in the constructor, which is done already in models, and b) retrieve the id in the controller.
You try to get all fields (*) from multiple tables, which have some column names in common. That will not work.
Have a look at JOINs.

This will work:

public function retrieve($id)
{
    $query = $this->_db->getQuery(true);
    $query->select('#__npco_car.*')->from(array('#__npco_car', '#__npco_namayeshgah', '#__npco_agahi'));
    $query->where('#__npco_car.car_id = ' . (int) $id);
    $query->where('#__npco_namayeshgah.id_namayeshgah = #__npco_agahi.id_namayeshgah');
    $query->where('#__npco_car.car_id = #__npco_agahi.car_id');

    $this->_db->setQuery($sql);
    $rows = $this->_db->loadAssocList();

    if (empty($rows))
    {    
        $query = $this->_db->getQuery(true);
        $query->select('#__npco_car.*')->from(array('#__npco_car, #__npco_useragahi, #__npco_user'));
        $query->where('#__npco_car.car_id = ' . (int) $id);
        $query->where('#__npco_user.id_user = #__npco_useragahi.id_user');
        $query->where('#__npco_car.car_id = #__npco_useragahi.car_id');
        $db->setQuery($sql);
        $this->_db->setQuery($sql);
        $rows = $this->_db->loadAssocList();
    }

    return $rows;
}

score 0 · Answer 2 · answered Aug 08 '13 at 05:35

may be this is your issue..

Change your query as following

 $sql="select * 
          from 
            #__npco_car,#__npco_namayeshgah,#__npco_agahi
          where 
            #__npco_car.car_id='".$id."' and
            #__npco_namayeshgah.id_namayeshgah=#__npco_agahi.id_namayeshgah and 
            #__npco_car.car_id=#__npco_agahi.car_id
     ";


 $sql="select * 
              from 
                 #__npco_car,#__npco_useragahi,#__npco_user
              where 
                 #__npco_car.car_id='".$id."' and 
                 #__npco_user.id_user=#__npco_useragahi.id_user and 
                 #__npco_car.car_id=#__npco_useragahi.car_id
        ";

mysql_num_rows() expects parameter 1 to be resource, boolean in joomla component

2 Answers2