Questions related to SQL queries, used in a module to retrieve information in a database table.

learn more… | top users | synonyms (1)

0
votes
1answer
23 views

insert big array into database

$array = Array ( [a001] => Array ( [0] => Array ( [id] => 1789823 [start] => 2014-09-29 ...
1
vote
1answer
88 views
+50

Clone SelectQuery doesn't seem to work

I want to change the sorting of a view with something very specific which needs 2 subqueries. So far, I tried to do something like this: $sorted = false; function mymodule_query_alter(&$query) ...
0
votes
1answer
16 views

Nested select with Drupals DataBase interface

I have the following SQL query and need to translate it to the drupal API: SELECT *, (SELECT COUNT(*) FROM newsletter WHERE email = pupils.email ) AS newsletter_flag FROM ...
1
vote
1answer
16 views

Is there any way to use SQL Views in Drupal Views

I have some very tricky reports to develop (with multiple subqueries) and it occurred to me that the Data module would be perfect: I can create my complex queries as SQL Views (I know how to do this), ...
0
votes
1answer
16 views

Retrieve a list of values from a lookup table using db_select

I have a "jobs" table where each job can have multiple specialties. So I have a specialties table with a list of all available specialties and I have a lookup table that just has a job_number and a ...
0
votes
1answer
24 views

How can I write a query to get a list of users who have registered in the last 24 hours?

I'm trying to use PHP to generate a query that fetches the number of users who have registered in the last 24 hours. Based on the code for the "who's online" block: $authenticated_count = ...
1
vote
0answers
24 views

SQL Conditional Condition statement from webform submitted data

I would like to retrieve webform submitted data from data base with keywords in multiple fields. The table structure is as below: sid nid cid data 50   7   5  ee 50 ...
0
votes
1answer
51 views

How to query the last nodes of multiple content types?

I want to query last 5 nodes of multiple content types, say, typeA, typeB, typeC, typeD. It was originally only one content type, and I did it using EntityFieldQuery: ...
0
votes
0answers
25 views

total user count and online count

I achieve this with custom block and sql-queries the user table to get Total Users and Online Now. But i am curious is this possible with Drupal Views module ?
0
votes
1answer
31 views

Loading a large node body sequentially for better performance

I have a few very large nodes on my Drupal 7 site. In some cases the body field contains about 2MB of raw HTML (about 250,000 words). Despite the site performing well as a whole, the sheer size of ...
0
votes
2answers
37 views

Using db_select to only get the latest nodes with a certain field value

I have extending a query function for a D2D migration. The nodes have a field on them for a certain type of ID. In the old D6 site the the field did not have to be unique, but in the new D7 site the ...
7
votes
3answers
379 views

Site performance , cache not working properly

I am using the Performance Logging Module. Above screenshot, one strange thing I noticed that Insert Cache_bootstrap on every page. When you go to any page (both admin theme and frontend theme) ...
1
vote
2answers
36 views

select nodes whose created date in hours is equal or larger than 48 hours

I make a PHP routine in which all node is selected and check created date and delete those nodes which is created in last 48 hours or greater. $result = db_query('SELECT n.nid, n.title, ...
1
vote
2answers
258 views

views_query_alter change to operator OR for contextual filters and normal filters

My question is rather simple and I have searched quite a bit for the answer with no result. My view produces below SQL (simplified): SELECT stuff FROM tableA LEFT JOIN tableB LEFT JOIN tableC ON ...
1
vote
1answer
58 views

Possible to use PDO inside a custom module in Drupal 6?

I'm about to build some new functionality on an old Drupal 6 site which will be upgraded to D7 or D8 within a year or so. This new module is heavy on the SQL and I'm building many complex SQL queries. ...
0
votes
1answer
56 views

how to get the response in JSON format?

I am using Drupal 7 Services to expose an API, The following is the code I am using to get some of the datails and It is giving the response interms of Arrays, But I don't want the response to be in ...
0
votes
0answers
23 views

Slow SQL query in Views relationship from Content Profile module

I have a slow SQL query in a view using a relationship from the Content Profile module (D6). My problem is more on the SQL side. Once I have the proper query I should be able to change it. The query ...
0
votes
2answers
32 views

querying content type via drupal database API

Using Drupal 7 I have created a content type called 'fauna'. I simply want to filter by an attribute (eg: 'area') using Drupal database API. As far as I have seen around, most of people follow ...
0
votes
0answers
39 views

Get a node and fields with SQL?

Is there any way in Drupal 7 to load a node and its fields without the Entity Query API? I need to get a field value based on another field in an external script, and don't want to bootstrap Drupal.
0
votes
0answers
49 views

Using addExpression and GROUP_CONCAT gives null result

This has been bothering me for a few days now. Using Migrate module, I am using a GROUP_CONCAT in an expression and the prepareRow always has a null value for the expression. For example, heres how ...
2
votes
3answers
1k views

How to db_select DISTINCT on a particular field in Drupal 7?

I understand that you can specify ->distinct() on the db_select statement so that it only returns distinct values when looking at ALL fields. But what I want is to return distinct values by only ...
2
votes
3answers
366 views

Migrate: What's the difference between prepare() and prepareRow()?

Using the Migrate module: I understand that prepareRow() runs a filter on the row and should return TRUE or FALSE based on some conditions, which allows a row to be migrated or not, but could someone ...
0
votes
0answers
44 views

Migrate using MigrateSourceSQL and MigrateDestinationTable returns incorrect rows

I'm using MigrateSourceSQL source and MigrateDestinationTable for the destination . In the migrate UI where you have the Query ; this query returns the correct amount of rows I wish. Also in the ...
0
votes
1answer
32 views

Help me with my page, when open this page shows me this:

PDOException: SQLSTATE[HY000]: General error: 145 Table './mapibrasil/watchdog' is marked as crashed and should be repaired: INSERT INTO {watchdog} (uid, type, message, variables, severity, link, ...
0
votes
1answer
99 views

How to deal with duplicate table name with db_select left join

Basically I have a db_select that has two left joins. And will be calling up two table fields with the same name. The fields have different data though, so I need to be able to have access to both ...
0
votes
0answers
35 views

Intermittent slow Views query

The Drupal 7 site we've produced is intermittently experiencing slowdown on page load. Using Devel I've pinpointed the slow down to queries generated by two similar Views and the SQL generated. The ...
0
votes
2answers
86 views

What is the proper way to drop database references for an uninstalled drupal module?

I've uninstalled the Commerce Coupon 7.x-2.0-beta3 through the admin ui and removed the corresponding folder from the sites/all/modules directory. Now when I install the previous version of Commerce ...
0
votes
3answers
250 views

Customizing recent blog posts block

I'm including the recent blog posts block in the sidebar of my Drupal site which pulls out the 10 most recent posts in a list with the title of the post linking to the post itself. I'd like to pull ...
0
votes
0answers
57 views

What am I doing wrong? Trying to join two fields from two tables, Drupal 7

Based on this tutorial I created a module to be able to find first name or lastname from user fields. Everything is working fine, but I notice that the search only looks either for name and last name ...
0
votes
0answers
62 views

Drupal Product Display List view- selecting image from variation with lowest delta

I am currently trying to set up a catalog listing for commerce product display nodes. Each display has several selectable product variations referenced. Using a T-Shirt example, think all shirts ...
1
vote
1answer
50 views

Find users who have not uploaded certain contents in last 30 days [closed]

I need to find users who have not uploaded certain contents in last 30 days. I have tried this query it is not working. Can anyone help me? select uid, max(created) as max_created from node where ...
1
vote
2answers
152 views

How to add UNION with pagination in drupal 7 query?

My query is like : $query_club = db_select('club_members','cm'); $query_club->fields('cm',array('first_name','last_name')); $query_club->group_by('cm.uid'); $query_network = ...
0
votes
1answer
36 views

How to add multiple havingCondition with OR operator?

I want to add havingCondition with OR operator In normal sql query my having conditions are like: group by n.nid having comment_count!=0 OR viewed_count!=0 OR likes_count!=0; With adding multiple ...
0
votes
1answer
15 views

How to get the values ​​of an array in the options list (hook_rules_condition_info ()) instead of array index?

'example_condition_user' => array( 'label' => t('User Condition'), 'arguments' => array( ), 'parameter' => array( 'username' => array( 'type' => 'text', ...
0
votes
0answers
43 views

node_save throwing PDOException: “Duplicate entry '' for key 'uri'”

I'm working on a script to update some content (no file management) - but when I call node_save($node), it throws this error: [errorInfo] => Array ( [0] => 23000 [1] => ...
2
votes
1answer
30 views

How to do a db api join on “has no value”?

I've got two tables, Foo and Bar. Foo and Bar can be joined by matching Foo's "nid" column to Bar's "col" column. In use, some rows in Foo will have a corresponding entry in Bar, but some won't. I ...
1
vote
1answer
1k views

How to use LIKE condition in db_select() query?

How do you build up a LIKE condition in a db_select() query? I know how to do this with a db_query() API call, but is there a special syntax / method for doing this with db_select()?
0
votes
1answer
19 views

What is the SQL query to filter og Admin Accounts?

I currently have the following SQL statement that gives me a list of all of my og nodes and their associated owner. I am having trouble nesting my JOIN statements to add the name of the owner from the ...
0
votes
1answer
104 views

get all nodes created in a specific month

I have a custom search form that I posts a year + month, such as 2014-04 I would like to get all nodes created that month, when I create the timestmap I use: "SELECT ....... AND changed >= ...
0
votes
0answers
63 views

db_query complete Content Type with all fields automatically

Can I do this automatically in my module without hard coding the list of content type fields: $query = db_select('node', 'n'); $query->join(ALL FIELDS OF THIS CONTENT TYPE, FIELD ALIASES, ONs); ...
0
votes
0answers
35 views

Query to Database

I want make a query for get some products related with the identified user. The type contain has a taxonomy field called 'operating system' and the main profile has also it. For example: If a user has ...
2
votes
0answers
69 views

Extend db query in CKEditor Link User module

The CKEditor Link User module has essentially the functionality I need, but it requires editors to know users by their 'name' field in the users table. I wanted to extend the query to join with the ...
0
votes
0answers
266 views

unknown column base.enabled in 'field list' commerce kickstart entity module?

After moving some modules into my sites/all/modules directory I suddenly get the following error: PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'base.enabled' in 'field ...
1
vote
1answer
347 views

How to Display Unique Values from database using db_select in Drupal

i tried with following code but i cant get the attached output $query = db_select('webform_submitted_data', 'u'); $query->fields('u', array('sid','nid','data')); ...
0
votes
1answer
56 views

drupal 7, db_select(), strange behavior : malformed query

I have a table: mantenimiento_extras. I want a query like this: select * from mantenimiento_extras where id in (7, 9). (this case assumes passing array(7, 9), but it could be variable, even ...
1
vote
0answers
71 views

Why does views add the sort field as a SELECT expression?

The Context I have a content type with a Date field on it, field_dates. The field is configured to have a single value, allow end dates, and also allow recurrences. For those who don't know how ...
0
votes
1answer
100 views

SQL queries in theme functions - is it a bad practise? [closed]

I know, that calling SQL queries in template files is bad practise. But is it bad practise also for theme functions? For example when my form is rendering with my custom theme function: <?php ...
0
votes
3answers
59 views

Limit the results returned by 'published'

I am new to the Database API and SQL queries in general. I needed to create a custom module that could be given a list of fields and would output the average value of each of these fields. I am ...
1
vote
1answer
84 views

PDOException error - Google AdWords Conversion module

My Drupal site is having a serious issue. Whenever I try to add content, such as a Basic Page, I receive this error: PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column ...
0
votes
1answer
79 views

Displaying and selecting data from a database in a module

I have a database table (gpx_api) with the following fields: runID userID score distance pace speed gpx_path missionID startTime duration I want to display all of these fields ...