2

I've a query where I want to filter query results based on today, yesterday and week. I've written queries within Drupal 7 and it is as follows:-

$result = db_query("
    SELECT
  n.title, n.nid,
  COUNT(*) AS times
FROM
  {node_view_count} a, {node} n where a.nid = n.nid AND a.uid = :uid AND n.nid <> 2120 AND DATE(from_unixtime(:mydate)) = CURRENT_DATE
GROUP BY
  n.title
ORDER BY
  times desc",
array(':uid'=>$_GET['uid'], ':mydate'=> date('Y-m-d H:i:s', "a.timestamp") )) -> fetchAll();

My question is, that could I pass two variables in the array as a parameter? Also if I could why is this query not working when I pass two parameters in it. The problem is that I need to convert the timestamp to date before using it and I am using a query Variable in the parameters.

Do you think you could assist in this?

Thanks.

  • 1
    Probably because you're using FROM_UNIXTIME on a parameter that you've converted into a formatted date string. Just pass the timestamp integer for :mydate and test again – Clive Jun 19 '13 at 18:48
1

I don't have a copy of D7 handy, but this seems to work on D6 for me. There's at least two problems with your code. I'm going to pare your query into a short bit of SQL, then transform it into drupal code.

Use JOINs

Whenever you're using multiple tables in SQL, you'll almost always have to use the JOIN ... ON ... syntax. Using JOIN tells MySQL that you want to connect certain rows on the two tables if a given field from those tables matches. This is a very basic SQL concept, so you'll need to master this if you want to write your own SQL or troubleshoot it. Try W3Schools tutorial for a better explanation.

Here's a simplified, pure SQL query that works for me using a JOIN:

SELECT
  n.title, n.nid, COUNT(*) as times
FROM
  node n 
JOIN
  node_view_count a
ON
    a.nid = n.nid
GROUP BY
    n.title
ORDER BY times DESC;

In my D6 installation, I have a table called node_counter, but you probably have node_view_count instead on D7. JOIN node_view_count tells MySQl that we're connecting the node table to the node_view_count table. The ON a.nid = n.nid tells MySQL to only connect rows from these two tables when the nid field matches.

WHERE Clause

Next, let's add in a simplified WHERE clause. On my D6 database, the node_counter table's timestamp field is an unix timestamp (an integer). We can do the conversion from unix timestamp to datetime using pure SQL as well (no need to use a parameter to db_query()):

...
ON
    a.nid = n.nid
WHERE
    DATE(from_unixtime(a.timestamp)) = "2012-12-21"
GROUP BY
...

Add a Paramater to db_query()

We can change "2012-12-21" to whatever date we want to search on. So, next let's make this changeable part of our query into a parameter that gets passed to db_query():

<?php
$query = <<<SQL
SELECT
  n.title, n.nid, COUNT(*) as times
FROM
  {node} n 
JOIN
  {node_view_count} a
ON
    a.nid = n.nid
WHERE
    DATE(from_unixtime(a.timestamp)) = :mydate
GROUP BY
    n.title
ORDER BY times DESC
SQL;

I've put { and } around the table names so that you use this query inside db_query(). By adding :mydate, you can now add in parameter called ':mydate' with a date string to your db_query() call:

$result = db_query($query, array(':mydate' => '2012-12-21'));

Hopefully, this gets you 90% of the way there.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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