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
– Glubus, Commented Dec 14, 2017 at 13:44
You can have multiple where and they will be used as AND. Check the docs. — FirstOne
– FirstOne, Commented Dec 14, 2017 at 13:45
You can use $this->db->where('collection_id', $sCollectionId); $this->db->where('latest', 1); — FirstOne
– FirstOne, Commented Dec 14, 2017 at 13:46
@Glubus I tried but I'm getting no output for var_dump instead getting a Server Error 500. — StandardNerd
– StandardNerd, Commented Dec 14, 2017 at 13:51

Jawad A. · Accepted Answer · 2017-12-14 16:48:17Z

1

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

edited Dec 14, 2017 at 16:48

answered Dec 14, 2017 at 13:51

Jawad A.

736 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

StandardNerd Over a year ago

I get "A PHP Error was encountered - Severity: Notice - Message: Undefined property: stdClass::$id"

FirstOne Over a year ago

Not just an array, since you can call where multiple times, as said mentioned in the comments of the question.

Jawad A. Over a year ago

@FirstOne Yes, we can call where clause multiple times and also pass an array of multiple conditions within 1 clause. It depends on our own coding style :) Complete Guide: codeigniter.com/userguide3/database/…

Jawad A. Over a year ago

@StandardNerd, Please use the full code as this:

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';     }   }

Collectives™ on Stack Overflow

SQL Query fails with no error message

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related