Tagged Questions

2
votes
1answer
24 views

How to skip first row in a dynamic query?

I've not yet found the right way to use ->range($offset,$limit) in a dynamic query to skip first result, but without limiting the resultset (LIMIT clause). The only workaround I've found is to use an ...
5
votes
4answers
5k views

“OR” condition in db_select

I am studying the new database layer, and I am glad it is changed to an OOP layer. I need to implement a query with db_select(). I found I can add a WHERE statement with $query->condition(), but by ...
0
votes
1answer
37 views

Check for field values to be unique when storing into the DB

I have a content type "Research assignment" which has several fields including the field "field_user" and "field_project". I want to remove any old research assignments having the same user and ...
2
votes
1answer
54 views

Placeholders and the query builder?

I read on the drupal.org site: "Never put a literal value or variable directly into a query fragment, just as literals and variables should never be placed into a static query directly (they can lead ...
3
votes
4answers
2k views

db_query not returning anything (or empty) - drupal 7

I am trying to perform a db_query but am unable to do so. The query I am trying to perform is very simple, but to make things even simpler, the following query does not even work for me: $result = ...
1
vote
3answers
57 views

How can I get count of deleted rows

How can I get count of deleted rows after db_delete? db_delete('node') ->condition('uid', 1) ->execute(); something like $count = db_delete('node') ->condition('uid', 1) ...
4
votes
1answer
3k views

Trying to get COUNT(*) from custom content type with EntityFieldQuery

How can I retrieve the 'count(*)' from a query in drupal 7? The query needs to include a custom content type and a custom field. Notes Custom Content Type: employees Custom Field Name: ...
1
vote
1answer
121 views

Why can't I have hyphens in my db_query()

I am trying to SELECT all the rows in my MySQL where a field matches a variable using the db_query() function. The variable is not a user input, it is retrieved from another database table based on ...
1
vote
3answers
99 views

how to get nid of a node when user is not on a node page

I had made a custom form and want a field in it that would work as a node reference field. Currently iam not on the node page but i want the nid of nodes of a content type to be the options of the ...
0
votes
2answers
115 views

How can I write SelectQuery for SELECT LAST_INSERT_ID()?

Is it possible to run the following query with SelectQuery ? SELECT LAST_INSERT_ID(); I would do this with db_query(), but I'm finding a way to use the query builder. $last_id = db_query("SELECT ...
2
votes
1answer
1k views

db_select() ignores condition() when used with join

I want to select all rows from 'ha' table, which access column are >= 0 and node type of that nid is equal $this->ctype. protected function queryAlter() { if(isset($this->ctype)) { ...
4
votes
2answers
2k views

Print the query which is built using db_select()

I want to print the query which is built using db_select() in a programmatical way. Is there any API function provided by Drupal Abstraction Layer? It is similar to query output in Views, but I want ...
2
votes
2answers
158 views

How do I add COUNT(foo.bar) to a dynamic query?

I have a query like the following: SELECT t.tid, t.name, t.description, COUNT(i.nid) FROM taxonomy_term_data t LEFT JOIN taxonomy_index AS i ON i.tid = t.tid GROUP BY i.nid LIMIT 60 I ...
1
vote
3answers
2k views

SelectQuery, db_query() and pager

I have a long query with sub-queries using db_query(). So far I know SelectQuery of D7 comes with Extenders. Example extenders are PagerDefault (replaces Drupal 6 pager_query()). I need my query with ...
3
votes
2answers
1k views

Using db_query() or db_select() to connect to multiple databases

How can I execute a query that connects to different databases? I have the following query, and I need to rewrite it for Drupal. SELECT a.empid, b.empname FROM db1..empidmaster a, db2..emplist b ...
2
votes
2answers
2k views

Using SQL functions in conditionals in Drupal 7 db_select()

I am trying to write a condition into a SQL WHERE clause that forces the column comparison to a variable to be compared in lowercase. However the addExpression function isn't accomplishing this (since ...
1
vote
1answer
288 views

Drupal 7: can't get SQL query to work that works outside of Drupal

I have a SQL query that works just fine on it's own as follows: SELECT timestamp FROM accesslog WHERE uid = 1 AND path = 'mypath' AND timestamp > UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 4 ...
0
votes
1answer
354 views

Using wildcards in custom module outputting sql query

It has become obvious to me that SQL syntax is a little different within drupal. After some fiddling around I am getting the hang of it, but I haven't been able to use wildcards succesfully. $sql = ...
1
vote
1answer
198 views

DB Query replaces %s inside LIKE operator

I have one query like : db_query("SELECT * FROM {foo} WHERE bar LIKE '%supply%' LIMIT %d,%d", 0, 10) Now if I use db_query API it will replace %s with the argument supplied and resulting query will ...
2
votes
2answers
643 views

db_query() PDOException (no parameters were bound)

I have a relatively simple query that is executed via db_query()->execute; For some reason this causes a PDO Error, which I assume has to do with the way drupal handles the input. Any ideas on what ...
0
votes
1answer
110 views

Convert Haversine mysql query to D7 code [closed]

I'm stuck at converting the haversine query to drupal D7 code. Basically I am writing a custom block module that should display the closest city to the entry nodes geo points. Cities are different ...
0
votes
3answers
574 views

PDOException: SQLSTATE[HY093]: Invalid parameter number: parameter was not defined:

I get "PDOException: SQLSTATE[HY093]: Invalid parameter number: parameter was not defined" using the below db_query() call. db_query("SELECT image.:field1_fid as fid, image.:field2_data as file_data, ...
3
votes
2answers
1k views

Is it possible to join dynamic tables (subqueries) using db_select()?

I've got a complex query I'm trying to convert to db_select(). SELECT c.cid, c.pid, c.thread, count.replies, SUBSTRING(c.thread, 1, (LENGTH(c.thread) - 1)) as torder FROM comment c JOIN( SELECT ...
1
vote
2answers
349 views

Database Abstraction Layer and 'LOAD DATA INFILE' Statements

I realize that using the database abstraction layer comes with a few caveats such as not being able to run queries that are not available in one way or another across all versions of the databases ...
1
vote
2answers
518 views

db_query not returning expected results - updated

I'm using raw SQL in a custom module (please don't tell me to use the query builder; this is a single static query that will never need to take params.) If I run the query directly in phpMyAdmin, it ...
1
vote
1answer
415 views

Does db_query and db_insert escape input values? prevent sql injection?

I've been reading through the documentation, but I feel like it's always a little bit vague on this, perhaps I'm missing something. function foomodule_my_hook_menu_callback($unescapedUserInputString) ...
3
votes
1answer
2k views

D7 bug with db_select, 'addExpression' and 'having' conditions?

As I asked in the drupal forum http://drupal.org/node/1393748 (no answers..) I have an issue with a db_select query. It seems that addExpression "GROUP_CONCAT" and "HAVING" is causing an issue. ...
3
votes
1answer
67 views

Injecting database values with a non-Drupal application

I have a D7 site which I would like to allow a non-drupal application to be able to inject values into the database. Specifically, I would like to upload a file to a specific field and then associated ...
1
vote
1answer
199 views

Help with a D7 database query and update

I am not familiar with the Drupal 7 database API. I want to alphabetized a menu. Can anyone help me write a database query to retrieve all the menu item links of a certain parent (plid = 228) and ...
1
vote
2answers
321 views

db_query() function in a foreach cycle - only the first query is getting executed (D6)

I have a query, which I have to run in a foreach cycle. I run the query with db_query() function. My code looks similar to this one (of course, this is just an example code with example values): ...
2
votes
3answers
715 views

How do I use “IGNORE” in a database query?

I would like to run a query similar to INSERT IGNORE INTO the_table (nid, language) VALUES (8, 'Chinese'), but I cannot find any documentation on how the "IGNORE" statement works with the Drupal DB ...
0
votes
1answer
160 views

How to dynamically populate the condition method argument in a Dynamic Query

I am writing a query that will retrieve nodes that match a set of criteria. Though the number of cirteria is unknown. I was hopping i could pass to the condition() method an array of criteria with a ...
0
votes
1answer
114 views

What are the interfaces implemented in the object passed to hook_query_alter()?

Looking at the documentation for hook_query_alter(), I see that the parameter is reported to implement QueryAlterableInterface; looking at the code of _node_query_node_access_alter(), called by an ...
8
votes
2answers
5k views

How do you make sortable tables with a pager with data from a custom table?

For Drupal 6 you could do something like this: $header = array( array('data' => t('Order id'), 'field' => 'order_id'), ... array('data' => t('Transaction time'), 'field' => ...
-3
votes
1answer
1k views

How do I change a MySQL query into a Drupal query?

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 ...