SQL Query fails with no error message

Question

I use CodeIgniter 3 and I'm trying to change a SQL Query from

SELECT id, shelfmark from documents where collection_id='ubb'

to

SELECT document_id, shelfmark from documents_revisions where collection_id='ubb' and latest='1'

The first SQL Query is generated with:

 public function getDocumentsForCollection($sCollectionId) {
    $this->db->select('id, shelfmark');
    $this->db->where('collection_id', $sCollectionId);
    $query = $this->db->get('documents');
    if($query->num_rows() > 0) {
      return $query->result();
    }
    else {
      return '';
    }
  }

In order to match the second SQL Query I updated this method to

  public function getDocumentsForCollection($sCollectionId) {
    $this->db->select('document_id, shelfmark');
    $where = "collection_id=" . $sCollectionId . " AND latest=1";
    $this->db->where($where);
    $query = $this->db->get('documents_revisions');
    if($query->num_rows() > 0) {
      return $query->result();
    }
    else {
      return '';
    }
  }

When I open the page where the function is executed I just see a blank page.

How can I debug in CodeIgniter to see the generated SQL query of my PHP function?

What's wrong with my updated PHP function?

I updated the method to:

  public function getDocumentsForCollection($sCollectionId) {
    $this->db->select('document_id, shelfmark');
    $where = array("collection_id"=>$sCollectionId,"latest"=>1);
    $this->db->where($where);
    $query = $this->db->get('documents_revisions');
    var_dump($query); exit;
    if($query->num_rows() > 0) {
      return $query->result();
    }
    else {
      return 'foo';
    }
  }

With var_dump($query) right before the if conditional I get:

object(CI_DB_mysqli_result)#65 (8) { ["conn_id"]=> object(mysqli)#16 (19) { ["affected_rows"]=> int(160) ["client_info"]=> string(79) "mysqlnd 5.0.12-dev - 20150407 - $Id: b5c5906d452ec590732a93b051f3827e02749b83 $" ["client_version"]=> int(50012) ["connect_errno"]=> int(0) ["connect_error"]=> NULL ["errno"]=> int(0) ["error"]=> string(0) "" ["error_list"]=> array(0) { } ["field_count"]=> int(2) ["host_info"]=> string(25) "Localhost via UNIX socket" ["info"]=> NULL ["insert_id"]=> int(0) ["server_info"]=> string(23) "5.7.18-0ubuntu0.16.04.1" ["server_version"]=> int(50718) ["stat"]=> string(139) "Uptime: 252001 Threads: 3 Questions: 36434 Slow queries: 0 Opens: 501 Flush tables: 1 Open tables: 313 Queries per second avg: 0.144" ["sqlstate"]=> string(5) "00000" ["protocol_version"]=> int(10) ["thread_id"]=> int(2507) ["warning_count"]=> int(0) } ["result_id"]=> object(mysqli_result)#38 (5) { ["current_field"]=> int(0) ["field_count"]=> int(2) ["lengths"]=> NULL ["num_rows"]=> int(160) ["type"]=> int(0) } ["result_array"]=> array(0) { } ["result_object"]=> array(0) { } ["custom_result_object"]=> array(0) { } ["current_row"]=> int(0) ["num_rows"]=> NULL ["row_data"]=> NULL }

The affected_rows represents the amount of documents I get if I run the SQL query directly in MySQL: ["affected_rows"]=> int(160)

try var_dump($query) before the if statement. Also, verify that you show all errors (e.g. E_ALL) in php.ini under error reporting — Glubus
You can have multiple where and they will be used as AND. Check the docs. — FirstOne
You can use $this->db->where('collection_id', $sCollectionId); $this->db->where('latest', 1); — FirstOne
@Glubus I tried but I'm getting no output for var_dump instead getting a Server Error 500. — StandardNerd

Jawad A. Jawad A. · Accepted Answer · 2017-12-14T13:51:40

You must pass an array in case of multiple conditions in db->where() clause

$where = array("collection_id"=>$sCollectionId,"latest"=>1);

FULL CODE:

public function getDocumentsForCollection($sCollectionId) {
$this->db->select('document_id, shelfmark');
$where = array("collection_id"=>$sCollectionId,"latest"=>1);
$this->db->where($where);
$query = $this->db->get('documents_revisions');
if($query->num_rows() > 0) {
  return $query->result();
}
else {
  return 'foo';
}

}

will generate the result as follows:

SELECT `document_id`, `shelfmark` FROM (`documents_revisions`) WHERE `collection_id` = 'ubb' AND `latest` = 1

Using Where Clause Complete Guide: https://www.codeigniter.com/userguide3/database/query_builder.html#looking-for-specific-data

SQL Query fails with no error message

1 Answers