Take the 2-minute tour ×
Drupal Answers is a question and answer site for Drupal developers and administrators. It's 100% free, no registration required.
mysql_query("SELECT nid,title, type,created, v_api.ContentSum
  FROM `node` n
  LEFT JOIN ( SELECT content_id,
                     SUM(CASE WHEN value=1 THEN 1
                         WHEN value=0 THEN -1
                         ELSE 0
                     END) AS ContentSum
                FROM votingapi_vote
               WHERE value_type = 'option'
               GROUP BY content_id) v_api
    ON n.nid = v_api.content_id
where type='provider'
ORDER BY v_api.ContentSum DESC, created DESC LIMIT 0 , 22");

How do I change this query into a Drupal query?

share|improve this question
add comment

1 Answer

up vote 1 down vote accepted

Check out the drupal Database abstraction layer. Without thinking about what you want to achieve with your query I would write it like that:

db_query_range("SELECT nid, title, type, created, v_api.ContentSum
                FROM {node} n
                LEFT JOIN (SELECT content_id,
                                  SUM(CASE
                                       WHEN value=1 THEN 1
                                       WHEN value=0 THEN -1
                                       ELSE 0
                                      END) AS ContentSum
                           FROM {votingapi_vote}
                           WHERE value_type = 'option'
                           GROUP BY content_id) v_api
                ON n.nid = v_api.content_id
                WHERE type='provider'
                ORDER BY v_api.ContentSum DESC, created DESC", 0, 22)

Maybe one of the values (type,value_type,...) could be set via arguments, too.

EDIT:

here again with arguments:

db_query_range("SELECT nid, title, type, created, v_api.ContentSum
                FROM {node} n
                LEFT JOIN (SELECT content_id,
                                  SUM(CASE
                                       WHEN value=1 THEN 1
                                       WHEN value=0 THEN -1
                                       ELSE 0
                                      END) AS ContentSum
                           FROM {votingapi_vote}
                           WHERE value_type = %s
                           GROUP BY content_id) v_api
                ON n.nid = v_api.content_id
                WHERE type= %s
                ORDER BY v_api.ContentSum DESC, created DESC", 'option', 'provider', 0, 22)
share|improve this answer
    
thank you, if i want to use the replace character to in place of the option and provider. how to alter the code? –  enjoylife May 21 '11 at 6:58
    
Basically, in the query, you'd replace option and provider with %s, then add two more parameters to the function call. See the documentation on the db_query() function to see how to use placeholders in SQL queries. –  Garrett Albright May 21 '11 at 7:02
    
but i don't know how to add them.when i add like this, it shows an error.WHERE value_type = '%s' GROUP BY content_id,'%s') WHERE type='%s' ORDER BY v_api.ContentSum DESC, created DESC",'provider', 0, 22) –  enjoylife May 21 '11 at 7:15
    
when i used the with arguments code, it's can't work. but used the above is ok. why? –  enjoylife May 21 '11 at 7:49
2  
Oh, I noted that nonsenz edited their post to add the function call with the arguments, but the arguments were in the wrong order. I edited the post; the changes should show up soon. –  Garrett Albright May 21 '11 at 15:54
show 2 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.