• 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?

#4
(11-30-2015, 04:18 AM)DreamOfSleeping Wrote: 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.

Similar response with more query buider.

I would make this query like following:


PHP Code:
$author $this->db->escape($author);
$email $this->db->escape($email);

$select "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";

$query $this->db->select($selectfalse)
       ->from('comments')
       ->where('author', $author)
       ->or_where('email', $email);
       
       $result 
$query->row(); 
Greetings.
Reply


Messages In This Thread
RE: Is it possible to make this query using Query Builder, if not how can I make it safe? - by rtorralba - 11-30-2015, 01:41 PM

Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


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