I am trying to get a query to work in the 'database abstraction layer' However I have different documentation pages tell me how to do things differently, but none of them work when I write them in the query. As a normal query without the d.a.l. the query works fine. So now I am trying to write it in d.a.l. language, however I am not succeeding.
These are the pages I read:
API 'Database abstraction layer': http://api.drupal.org/api/group/database/6
API 'db_query': http://api.drupal.org/api/function/db_query/6
API 'db_query_range': http://api.drupal.org/api/function/db_query_range/6
Documentation 'Database access': http://drupal.org/node/101496
Documentation 'Writing secure code': http://drupal.org/writing-secure-code
Documentation 'Handle text in a secure fashion': http://drupal.org/node/28984
The one page is showing me to use a single quote in the query while another uses double quotes for the query statement. If I use %s I get a sql error, but using %d gives me an empty result.
According from what I read this should be the proper version:
if ( arg(0) == 'node' && is_numeric(arg(1)) && ! arg(2) ) {
$node = node_load(arg(1));
$node = ($node->nid);
}
$result = db_query_range(db_rewrite_sql("SELECT node.nid, node.title, content_type_publication.nid, content_type_publication.field_type_publication_value, content_type_publication.field_date_publication_value, content_type_publication.field_source_value, content_type_publication.field_source_url_value, content_type_publication.field_author_value , content_type_publication.field_isbn_value, content_type_publication.field_link_to_publication_url_value, content_type_publication.field_synapsis_value FROM {node} INNER JOIN content_type_publication ON node.nid = content_type_publication.nid WHERE node.nid = %d", $node));
while ($node = db_fetch_object($result)) {
What am I doing wrong or overlooking?
Comments
Line 3: <?php $node =
Line 3:
should be
then later in the query string use the $nid variable instead of the $node variable.
Try that.
Since arg(1) is the nid,
Since arg(1) is the nid, unless you need $node elsewhere, you could replace the node_load() and following line with
$nid = arg(1);Also you probably want to use db_query() instead of db_query_range() (You do not have arguments that actually limit the query which could be a problem).
And you probably what the query and while loop inside the 'if' or there is a possibility $nid is not set.
Thank you for your replies.
Thank you for your replies. Incorporated changes.
In addition I had to leave out the db_rewrite_sql as well in order to get it to work. From what I read in the documentation page (http://drupal.org/node/93737) db_rewrite_sql should almost always be used. In this case it should be used if I understand it correctly?
Update: according to this post, db_rewrite_sql should go in front of db_query, instead of behind it as stated here.
So:
That works. But is that correct?
Shouldn't the table name
Shouldn't the table name content_type_publication be in { }. Also would it not be better to join node and content_type_publication on vid?
This $result =
This
is incorrect and I am surprised it even works. Note your referenced post says to run db_rewrite_sql beforedb_query which is different that in front of, before means it should process the SQL before it is passed to db_query. db_rewrite_sql() takes and SQL statement (generally a select) and is used most often to ensure you are not selecting nodes that a user is not supposed to be able to view. It should work as
$result = db_query(db_rewrite_sq(l"SELECT etc..", $nid));Shouldn't the table name
Would make sense. I implemented it, and it appears to work. That is not to say it is correct. Wouldn't know on what to search to figure that out.
Yes, it appears you are correct (see also: http://drupal.org/node/105011 and http://drupal.org/node/246407). I read vid as vocabulary id, but it appears this is only the case in the taxonomy table(?). In the node and the CCK table the vid is the version id. In the node table the nid is the primary key, and in the CCK table vid is the primary key. Are there any problems to be expected there?
Suggestion in general if I may: Would it be clearer to name the similar named id's for example 'vocid' for taxonomy vocabulary id and 'verid' for version id instead?
I'm glad I checked. Thank you for pointing that out. Is 'db_rewrite_sq(l' a spelling mistake? It didn't work.
After also reading Lesson #8 Class Notes wiki -- db_rewrite_sql() and the Drupal 5's node_access system and this post db_rewrite_sql tid ambiguos it appears that the query would have to be written like this in order to work:
The key bit in this case appearing to be that the ',$vid' has to be after the end of the first set of parentheses and before the last parenthesis of the second set of parentheses.
Can anyone please confirm if this is the correct way to write a query like this in the database abstraction layer?
Edit: Although I just noticed that $vid might not be the same as arg(1) in all cases?
Yes, that was a typo on my
Yes, that was a typo on my part ('db_rewrite_sq(l')
And yes, you have the correct usage in your last code snippet.
O.k. thank you
O.k. thank you. I still don't get the nid/vid part in relation to arg(1) part. Is arg(1) the nid? in the url? The url nid doesn't change does it? How does this work with pretty url's?
Sorry to ask so many questions on this, I just can't make sense of it sometimes.
given a path like node/{nid}
given a path like node/{nid} (ex: node/123) the {nid} part is always the node id (nid) of the node being viewed.
The arg() function returns the parts of the unaliased path. In this case arg(0) is 'node' and arg(1) will be the nid, this is even true if the path is aliased.
Gotcha. :-) Thanks for
Gotcha. :-) Thanks for clearing that up.
O.k. so to get the revision part figured out what I did was create a couple of revisions of nodes. What I want is for the code to get the active revision automatically. Or even if there are no revisions get the node based on the url. Bit of trial and error, and it may be that the code can be improved, but it works, and seems solid on getting the correct revision based on the node url your on (with or without path aliases (using clean urls or not and/ or the module pathauto).
After checking in the database I noticed that the CCK and node vid's equal each other, and 'keep track of each other' for lack of better words. So in the query I changed the code to select the vid in both the node and CCK tables.
The code below works, however is subject to review: