CODEWITHSUNDEEP
phpsqlcodeigniter
sanjaypareth
sanjaypareth

Reputation: 21

Writing SQL Queries in Codeigniter 2.0

I have the following function that does not work and I'm having the hardest time trying to figure it out. I'm 12 and just learning, so forgive me: 

function get_answer() {
$answer = $this->db->query("SELECT COUNT(questions) FROM possible_quest WHERE questions='something'");              

return $answer;
}

When I run the following SQL query in phpmyadmin, it returns the expected result

SELECT COUNT(questions) FROM possible_quest WHERE questions='something'

How do I get this working in CodeIgniter using my function above?

The PHP error I get is

A PHP Error was encountered
Severity: 4096
Message: Object of class CI_DB_mysql_result could not be converted to string

Upvotes: 1

Views: 15585

Answers (6)

Armen Markossyan
Armen Markossyan

Reputation: 1214

I recommend you to use an Active Record with method chaining when possible:

public function getAnswer() {
    return 
        $this->db->
        select('id')->
        where('questions', 'something')->
        get('possible_quest')->row()->count
    ;
}

or

public function getAnswer() {
    return 
        $this->db->
        select('id')->
        from('possible_quest')->
        where('questions', 'something')->
        get()->row()->count
    ;
}

It's secure, easy to use, easy to understand and read. Don't listen to people saying that a single-line code is something good because a good code should be readable.

Upvotes: 0

minboost
minboost

Reputation: 2563

CodeIgniter has functions for building queries and returning the count:

function get_answer() {
  $this->db->from("possible_quest");
  $this->db->where("questions", "something");
  return $this->db->count_all_results();
}

NOTE: The name of the function 'get_answer' doesn't match what you're actually doing. It looks like you're getting a count of questions, not an answer, so you should name it to something that makes more sense, like 'get_question_count'.

Upvotes: 0

Chris C
Chris C

Reputation: 2023

The error you are getting is related to the fact that $this->db->query returns a result object, so you cannot use $answer directly as a string.

I suggest that you use print_r($answer) to see what could be going wrong with your conversion of objects to strings, if you have such a function in your model.

Upvotes: 0

stealthyninja
stealthyninja

Reputation: 10371

You're getting that error because

return $answer;

should be 

return $answer->result();

Upvotes: 0

Sean H Jenkins
Sean H Jenkins

Reputation: 1780

You need to setup to the count.

Heres what you need to do is 

$answer = $this->db->query("SELECT COUNT(questions) as count FROM possible_quest WHERE questions='something'")->first_row()->count;

//$answer is now setup to be count

One line. Thats the beauty of CI

Upvotes: 1

Damien Pirsy
Damien Pirsy

Reputation: 25435

Could be:

function get_answer() 
{
$query = $this->db->query("SELECT COUNT(questions) AS count FROM possible_quest WHERE questions='something'");

$count =  $query->row(); // returns an object of the first row
return $count->count;
// OR
$count =  $query->row_array(); // returns an asociative array of the result
return $count['count'];
}

Another thing: if you want to pass 'something' as a variable, you can use parametrized query, like

 $sql = "SELECT COUNT(questions) AS count FROM possible_quest WHERE questions = ?";
 $query = $this->db->query($sql, array($something));

which has the benefit of escaping automatically your variable, so you don't worry about sql injections.

Upvotes: 4

Related Questions