By fiasst-1 on

I'm looking to build a list of recently viewed nodes for the current user but need some help creating a fast and efficient query.

Here's what I have so far:

global $user;
$result = db_query("SELECT DISTINCT title, path FROM {accesslog} WHERE uid = '%d' ORDER BY timestamp DESC LIMIT 5", $user->uid);

Trouble is, I only want to return nodes from a 'product' content type.

Any help is much appreciated!

⋅ Categories: Drupal 6.x

Comments

nevets’s picture

nevets commented

You can use views to do that.

fiasst-1’s picture

fiasst-1 commented

So although I've tried using views for this, it won't allow me to style the results in the way I need to. So a SQL query would be great if its possible. I have no idea how to tie the node nid to something in the accesslog table.

tomas.teicher’s picture

tomas.teicher commented

If you insist on your own query, look at the Mysql JOIN statement

fiasst-1’s picture

fiasst-1 commented

I have tried to INNER JOIN the {node} table but the {accesslog} table only has this information in it:

stdClass Object (
  [aid] => 555,
  [sid] => 7rhbkuqqqd0enf0rrhjpfo6aa2,
  [title] => Node Title,
  [path] => node/1619,
  [url] => http://www.site.com/category1,
  [hostname] => 86.148.02.013,
  [uid] => 1,
  [timer] => 1323,
  [timestamp] => 1300194125
)

So I don't know how I can check this information against a node n.type = 'product'?

tomas.teicher’s picture

tomas.teicher commented

I think you can get nid from path and then get content type from node table where nid =$nid (1619 in this case)

fiasst-1’s picture

fiasst-1 commented

this is the only thing I could think to do. But, when you say compare, do you mean return all the paths before stripping out the non-numeric characters ('node/') and then running a second query? Wasn't sure if this was the best/only way to do it?

Thanks for your replies

fiasst-1’s picture

fiasst-1 commented

I've got this so far:

global $user;
$nodes = db_query("SELECT DISTINCT n.nid FROM {accesslog} AS a INNER JOIN {node} AS n WHERE n.nid = LTRIM(RTRIM(REPLACE(a.path, 'node/',''))) AND n.type = 'product' AND a.uid = '%d' ORDER BY a.timestamp DESC LIMIT 5", $user->uid);

But it's not actually returning the latest accesslog product nodes. I'm not sure but it could be to do with LTRIM(RTRIM(REPLACE(a.path, 'node/',''))) and not all a.path columns containing 'node/'.

Any help more than welcome!

tomas.teicher’s picture

tomas.teicher commented

When using JOIN statement, you have to use ON statement, where do you want to join tables. I am not sure but I think you can have function in ON statement. Try something like this:

$nodes = db_query("SELECT DISTINCT n.nid FROM {accesslog} AS a INNER JOIN {node} AS n ON n.nid = LTRIM(RTRIM(REPLACE(a.path, 'node/',''))) WHERE n.type = 'product' AND a.uid = '%d' ORDER BY a.timestamp DESC LIMIT 5", $user->uid);

At first, try it only in phpmyadmin as SQL query with static data to check sql syntax, then in PHP

fiasst-1’s picture

fiasst-1 commented

thanks for the tip. I've included the ON statement however, this still shows the same order of nodes and still not the latest.