• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Is it possible to make this query using Query Builder, if not how can I make it safe?

#1
Here is an example of the query just as a string that I pass to the query function. This works as I expected.

Code:
$query_string = "SELECT SUM(author = '$author' AND email = '$email' AND approved = 1) AS email_author_approved, " .
        "SUM(author = '$author' AND email = '$email' AND spam = 1) AS email_author_spam, " .
        "SUM(author = '$author' AND spam = 1) AS author_spam, " .
        "SUM(email = '$email' AND spam = 1) AS email_spam " .
        "FROM comments " .
        "WHERE author = '$author' OR email = '$email'";
        
        $query = $this->db->query($query_string);
        $result = $query->result_array()[0];

I would like to use the Query Builder class instead. In the user guide I have seen the select_sum function, but no examples that uses multiple sums together after each other. Can this be done? If not possible to use query builder class how can I make this safe.

I'll now explain what this code does in case it's unclear. It's basically my idea of a very simple spam filter.

It uses the sum function to count how many times this email/name combination has had comments approved and displays the results in a column called "email_author_approved.

It uses the sum function to count how many times this email/name combination has had comments marked as spam and displays results in a column called "email_author_spam".

Then does the same thing separately for the email and the author. If i understand correctly the information I pass to the sum function will result in either true or false which is the same as 1 or 0. So i can get a count. The where is to stop it counting all the rows.
Reply


Messages In This Thread
Is it possible to make this query using Query Builder, if not how can I make it safe? - by DreamOfSleeping - 11-30-2015, 04:18 AM

Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2019 MyBB Group.