Questions related to SQL queries, used in a module to retrieve information in a database table.
0
votes
1answer
18 views
GROUP BY in hook_views_query_alter() doesn't work
I'm using Views 7.x-3.6 and I tried altering the GROUP BY clause with hook_views_query_alter()
function mymodule_views_query_alter(&$view, &$query) {
if ($view->name == "view_name"){
...
0
votes
0answers
56 views
Add a JOIN with a subquery into a views query
Here's my SQL, which I am trying to include in hook_views_query_alter() to modify the uc_catalog view (from Ubercart 3):
JOIN (
SELECT uc_products.nid, MIN(uc_products.sell_price) AS min_price
...
0
votes
0answers
22 views
Ubercart adjustments query hangs up
Recently found a bug in my system which uses ubercart, such that whenever I click on adjustments tab of specific product edit page, the cpu utilization for mysql shoots up to 100% for some reason.
I ...
2
votes
1answer
23 views
Is it possible to divide two columns using the the query builder?
Is it possible to divide two columns using the the query builder?
Like: $query -> fields('A', array('valsA') / 'B', array('valsB'));
2
votes
2answers
52 views
General error: 2006 MySQL server has gone away
This is the error I am suddenly getting.
Additional uncaught exception thrown while handling exception.
Original
PDOException: SQLSTATE[70100]: Unknown error: 1317 Query execution was ...
0
votes
3answers
49 views
Pulling data into a content type
I'm hoping to get some opinions on the best way to proceed. We are migrating our website from a Microsoft Environment to Drupal 7 and I appear to be having trouble wrapping my head around how to do a ...
0
votes
1answer
23 views
View/query to show latest node related to each term in a taxonomy vocabulary
I have a node type (stories) which is related to a taxonomy vocabulary (countries).
Each story is related to one country only.
I want to create a view of the latest story from each country, ordered ...
0
votes
1answer
35 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
35 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 ...
2
votes
1answer
39 views
Database Query to test new users
In my Drupal 7 site, I inserted values into the 'surname' and 'date of birth' fields of my database tables. I want to prevent new users whose username and date of birth do not match those on my ...
0
votes
1answer
160 views
PDOException: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'fmotors_fms.block_node_type' doesn't exist:
This was working fine last night but when i login to my computer again I got this error now. What is this table {block_node_type} I cant find it in the database but it is in the drupal code in ...
2
votes
1answer
34 views
how to groupby with rollup in drupal 7
how to get booking records from booking table .how to get group by function
with ROLLUP
SELECT * FROM booking
GROUP BY startBooking WITH ROLLUP;
how to convert above query in drupal 7?
...
1
vote
1answer
17 views
EntityFieldQuery for getting the count of a user blog
EntityFieldQuery for getting the count of a user blog. Blog is a content type.
1
vote
0answers
32 views
Performance Implications when Views JOIN added twice?
I have a view with a flag (relationship, only show flagged nodes). I have modified it a few times and now I see in the query output that views has the join added twice:
INNER JOIN {flag_content} ...
0
votes
1answer
73 views
Join multiple tables in D7 Views
I'm trying to create a View Block to display related information on a node.
I'm creating an image gallery where each image node is tagged with one/more taxonomy terms, and a block on each of those ...
1
vote
3answers
49 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)
...
0
votes
1answer
49 views
How to remove and add joins to a view?
Because of some buggy handlers I need to write a hook that will:
Get location_instance table's alias from the original query
Remove location_instance table join
Remove that alias join
Remove a ...
0
votes
0answers
165 views
Drupal 7, How to get Taxonomy Parents and Children in View
My Taxonomy Hierarchy:
-Council Minutes
-Reports to Council
-Auditor
-Treasurer
-Tax Commissioner
-Proposed Ordinances
-First Readings
-Second Readings
-Third Readings
I attached files ...
1
vote
1answer
58 views
building a query with the database api and groupby multiple fields in drupal 7
first of all I simplified my query so it might be more useable to other users on this site.
I'm looking for a solution to use group by on multiple fields.
This query selects the values of the ...
0
votes
1answer
67 views
Making fields, condition and join dynamic in db_select query
What is the Drupal way of creating queries which are dynamic in nature ?
What I mean is for example- In my module I am asking the user to select some content types and the fields associated with each ...
1
vote
1answer
74 views
How do you use hook_query_alter to get inside a query's creation?
I'm getting no love from hook_query_alter. I have a simple query to which I want to add a tag, while also adding a node_access tag. Thus the whole query looks like this:
$the_nodes = ...
0
votes
1answer
40 views
How do I get the result from a database and show it?
I have a database table with the following fields: name, company_name, geo_loction. I want to get those fields using the following query.
$zip_code = db_query("SELECT * FROM data_set WHERE id = 1", ...
0
votes
0answers
22 views
Database insert goes wrong [closed]
I have the following form:
function review_m_form($node){
$form = array();
$form['mark'] = array(
'#type' => 'textfield',
'#title' => 'Give a mark to the content type(1 ...
0
votes
1answer
49 views
How to add table column aliases
I am trying to perform a query on a database that has the following info (in Drupal 7.21):
nid | sid | cid | data
----------------------
32 | 2 | 1 | date
32 | 2 | 2 | name
32 | 3 | 1 ...
2
votes
1answer
39 views
Is it possible to add to different aliases to the same table in a dynamic query?
I would like to use a example query for retrieving a full tree of the nested set model in a dynamic query.
SELECT node.name
FROM nested_category AS node,
nested_category AS parent
WHERE ...
0
votes
0answers
63 views
PDOException on Article page
In my Drupal site, when I tried to save an article page I'm getting the following error:
PDOException: SQLSTATE[42S02]: Base table or view not found: 1146
Table ...
1
vote
1answer
103 views
db_query() is not returning results from a valid query
In short: db_query of a SELECT statement is returning no results. However, running the SELECT statement in phpMyAdmin vs the database returns thousands of results. I'm using Drupal 6.
What I'm ...
1
vote
2answers
96 views
Fatal error: Call to a member function fields() on a non-object
I have the following code that is throwing an error:
/**
* Custom content function
*
* Get system status from the helpdesk database
*
* @return
* An key => value pairing of systems and ...
2
votes
1answer
57 views
Writing to a drupal table form javascript as fast as possible
I have a table in my Drupal database this is simply a name value pair for each entry. I need to write to this table from javascript and get back as soon as possible. My first attempt was to create a ...
0
votes
2answers
127 views
When to use Views vs. a hardcoded query? [closed]
I recently took over maintainership of a little module called Suggested Terms and received a feature request to replace the build-in queries with views.
I'm trying to weigh the pros and cons of using ...
0
votes
0answers
58 views
Taxonomy menu items count, alter query to filter not only by content type but by another term also
I need help altering the query for
function _taxonomy_menu_term_count($tid) {
return db_result(db_query(db_rewrite_sql('SELECT COUNT(n.nid) AS c FROM {term_node} t INNER JOIN {node} n ON t.vid = ...
1
vote
1answer
99 views
Delete all nodes from database except from user1 and admin
I have got thousands of junk entries in my Drupal 6 website. Is there a way I can Delete all nodes from database accept from user1 and admin.
I also want to delete all users accept user1 and admin.
0
votes
1answer
110 views
PDOException: SQLSTATE[HY000]: General error: 1 too many SQL variables
I keep on running into the following error in my Drupal 7 installation:
PDOException: SQLSTATE[HY000]: General error: 1 too many SQL variables:
SELECT menu_links.link_path AS link_path,
...
1
vote
1answer
60 views
Incredibly slow speed in admin pages
I've been using my 4 year old Mac Book Pro for a development environment for the past few years, and with memory (8 GB) and hard drive upgrades, speed was pretty decent. I just got a new iMac with a ...
0
votes
1answer
26 views
DB query for tabular results of all a db table's info
I'm really having a hard time getting my head around the DB abstraction layer.
I've got a simple 2 column table and I'd like to output the values in the at table to a template.
I've seen plain ...
1
vote
3answers
74 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
98 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 ...
1
vote
0answers
25 views
Getting weird ambiguous column error message in where clause when accessing database
I am getting an error that one of the columns I am accessing is ambiguous in my where clause. While I understand what that message means, I believe that my code is set up correctly to avoid such a ...
1
vote
2answers
139 views
Update same table using dynamic query in drupal 7
HiAll,
I want to execute below sql query using Drupal 7 PDO, But could not find a way to do,
SQL:
"UPDATE `books` AS a
JOIN `books` b ON a.pid = b.pid
SET a. `status` = 'INACTIVE'
...
-1
votes
2answers
175 views
Drupal 7 How to find Nodes, tagged with Broken Taxonomies?
In Drupal 7, some Taxonomy Terms were deleted (by force from direct DB Query, for some reason). So some Nodes left remained by still tagging this deleted Taxo Terms. Then the problem is arising at the ...
1
vote
1answer
105 views
Which node table in Drupal 6 stores the node published date, created date timestamps?
I need to write a query to retrieve all nodes and their published dates for a Drupal 6 website and I would like to know which database table holds the nids and the timestamps. I am aware that there ...
1
vote
1answer
111 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
0answers
15 views
How can I print the number of the current pager in Views template?
I have a node View with pager configured Paged output, full pager, Items per page：30
So the view lists only 30 nodes per page.
Now I would like to print the number of the current page and the total ...
2
votes
2answers
120 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
1answer
99 views
Computed code db query issue — working in PHPmyadmin but not field
On my site, I have venues, and reviews of the venues, related with an entity reference field. I'm creating a computed field to store the UID of the first person to create a review for each venue. To ...
3
votes
1answer
201 views
How do I get computed column using db_select?
I require sum(points)? Any help on what to add in the sample code given below?
$query = db_select('digitaliq_question_points','n')
->fields('n',array('points'))
->condition("node_id", ...
2
votes
2answers
261 views
Changing an inner join to a left join in views 3.3?
Using views 3.3, I have a view in Drupal 7 where I am showing two content types. I have a filter criteria based on the value of a field that is attached only to one of the content types. So long as I ...
2
votes
1answer
133 views
Writing a news ticker module. It works, but it's sloppy as hell. Is there a better way to do this query?
I've got three pre-existing websites that use the story module, the path module, and CCK to produce customized news stories in Drupal 6. I wanted to create a news ticker module, to consolidate the ...
2
votes
2answers
1k 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 ...
0
votes
1answer
61 views
Changing exposed filter doesn't rebuild SQL query
I created 5 fields for filters. 3 of them are exposed to site visitor. Problem is that if I even change this exposed filter values while editing View it doesn't rebuild SQL query.
Views version: ...
