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

learn more… | top users | synonyms (1)

-1
votes
0answers
12 views

why this sql_query with FULL OUTER JOIN do not run for me? [on hold]

$sql = 'SELECT * FROM table1 FULL OUTER JOIN table2 ON table1.column1 = table2.column1;'; $result = db_query($sql); These lines of code do not run for me.. has anyone ...
1
vote
0answers
11 views

Adding a Pager to 3 Queries on a Custom Module

I have a multi-page form module that, when submitted, queries the database with some complex logic. Overall it's working fine. However, all the results are displayed on ONE giant page. I'd like the ...
0
votes
1answer
15 views

How to map url_alias table to node table?

I was trying to use some SQL to map the url_alias field to my node field on the url_alias.pid = node.nid field. However, it turns out that these are not apparently related, sometimes they are the same ...
2
votes
2answers
101 views

Using orderBy correctly with hook_query_alter

I have a hook_query_alter function that I'm using to edit my search.module results. It works ok so far. However, I'm having trouble adding an orderBy condition that I need. Here's the SQL I want added ...
0
votes
0answers
17 views

Concat query used in a like condition

I'd like to perform a query where I get the value of various fields(stored in different tables) and check if a string exists on them. This is part of a bigger query. What I did until know is : ...
-1
votes
1answer
38 views

drush has no connection to mysql

i have a problem with drush to enable or disable module. Download Modules works perfect! but i want to use drush in its full range. Yesterday, i see my drush will not connect to the database. i ...
0
votes
0answers
12 views

Batch fails with SQL error “String data, right truncated…” for node field

Batch fails with SQL error "String data, right truncated..." for node field_collection field. But when saving node with the same field value there is no errors and Drupal log has no errors ...
0
votes
2answers
46 views

How to programmatically use the “between” operator in Drupal Views

I have a price field and I want to see if the value of this field is between multiple price segments. E.g.: (price BETWEEN 0 AND 100) OR (price BETWEEN 150 AND 200) So how can I use the "between" ...
0
votes
1answer
19 views

Does Drupal cache db_query results for multiple users?

I am running into something strange and perhaps someone here can help me. I am trying to prevent two things from happening at once because the second attempt might be influenced by the first attempt. ...
1
vote
1answer
54 views

Getting information from database and giving it to javascript

The main thing I want to achieve is that my module makes a google map with markers. For now im doing this: ...
-1
votes
1answer
46 views

SQL Alias to Dynamic Query

I have a complex views SQL query, that I have converted to a dynamic query which works perfectly -- my issue is with custom fields: 'node' AS field_data_field_description_node_entity_type, 'node' AS ...
0
votes
1answer
37 views

How do I write this PHP query the Drupal way (database abstraction)?

I am using the code below to evaluate if a panel pane should display. I've read the docs on database abstraction, but I just don't really understand how to apply it. Of course, the dpm values are for ...
0
votes
0answers
18 views

Debugging/Profiling tools for Drupal [duplicate]

Looking for other debugging or profiling tools/methods for Drupal other than Web Profiler and Devel module. Anyone?
0
votes
1answer
38 views

insert big array into database

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

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
23 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
30 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
61 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
32 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
43 views

SQL Conditional Condition statement from webform submitted data [closed]

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
105 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
32 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
40 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
52 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
618 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
52 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
355 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
75 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
86 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
36 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
51 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
51 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
74 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 ...
3
votes
3answers
3k 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 ...
3
votes
3answers
596 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
73 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
36 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
144 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
38 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
102 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
501 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
61 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
105 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
51 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
265 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
45 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
19 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
60 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
31 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 ...
4
votes
1answer
3k 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()?