Hot answers tagged

12

Put this above your query. $this->EE->db->save_queries = TRUE; EE_Config.php sets the save_queries value dynamically based on debug/profiler settings, ignoring settings in database.php. This is why it worked fine in your local environment.


6

Not sure if it is best way to do this, but something like this: SELECT ct.title AS entry_title, cd.field_id_6 AS custom_field_6, (SELECT ca.cat_name FROM exp_category_posts cp INNER JOIN exp_categories ca ON cp.cat_id = ca.cat_id WHERE entry_id = cd.entry_id AND ca.group_id = 1 LIMIT 1) AS cat_1, (SELECT ca.cat_name FROM exp_category_posts cp INNER ...


6

SQL is the best option to bulk update the entries. But you must need to be careful as wrong execution can stress you up! You can use below query: UPDATE exp_channel_titles SET status = 'created' WHERE status = 'open' AND channel_id = '1' AND entry_date <= '1262332799' Replace the date value with UNIX timestamps of the date and channel_id you want ...


4

This will do it: $this->EE->db->save_queries = TRUE; $query = $this->EE->db->select('foo')->get('bar'); $bob = end($this->EE->db->queries); $this->EE->db->save_queries = FALSE;


4

This should cover it. SELECT field_id_49 as 'First Name', field_id_50 as 'Last Name', field_id_51 as 'Email', field_id_56 as 'Date of Birth', field_id_53 as 'Occupation', field_id_52 as 'Gender', field_id_57 as 'Address', field_id_58 as 'Address 2', field_id_59 as 'City', field_id_60 as 'Postal Code', field_id_54 ...


3

Take a look at the example on the Field display : ex 1: SELECT action_id as option_name, method as option_label, class as optgroup FROM exp_actions ORDER BY class You will see that the query needs to mark the fields option_label and option_value within the query, using the AS operator. Ergo, to replicate your query I have assumed you wish to display ...


3

Reference: EE Database Class User Guide The database class (IE CodeIgniter's ActiveRecord, which really isn't ActiveRecord, reference) does some nicities for you, like escaping your queries automatically. It also allows you to reference your table names without using whatever Expression Engine table-name-prepending that occurs at install (usually something ...


3

Sounds like a poorly coded template... First, you should aim to refactor it using the multi_add_to_cart_form instead of the simple add_to_cart_form: http://cartthrob.com/docs/tags_detail/multi_add_to_cart_form/index.html This will give you a single form for all 6 products so should reduce CT's load by 6X. You should then look into other optimisations and ...


3

This SQL will clean up both exp_channel_titles and exp_channel_data: DELETE exp_channel_titles, exp_channel_data FROM exp_channel_titles INNER JOIN exp_channel_data ON exp_channel_data.entry_id = exp_channel_titles.entry_id WHERE exp_channel_titles.channel_id = 5 AND exp_channel_titles.status = 'your_custom_status' Replace the channel_id and status as ...


3

You can try below query to display entries of last 45 Days. SELECT DISTINCT field_id_19 AS HomeTitle FROM exp_channel_data t1 INNER JOIN exp_channel_titles t2 ON t1.entry_id = t2.entry_id WHERE t1.field_id_19 <> "" AND ((UNIX_TIMESTAMP() - t2.entry_date) < 3888000)


2

Off the top of my head I'm pretty sure this should work for you : SELECT field_id_16 FROM exp_channel_data cd LEFT JOIN exp_category_posts cp ON cd.entry_id = cp.entry_id WHERE cp.cat_id = 1 Change the cp.cat_id = 1 number to whichever category ID you need and I'm fairly certain you should be good to go.


2

You can try this query: {exp:query sql="SELECT count(*) as member_count, m_field_id_6 as state FROM exp_member_data JOIN exp_members ON exp_members.member_id = exp_member_data.member_id WHERE m_field_id_6 != '' AND m_field_id_9 = 'USA' AND exp_members.group_id='5' GROUP BY m_field_id_6 ORDER BY m_field_id_6 ...


2

SELECT ct.entry_id FROM exp_channel_titles AS ct LEFT JOIN exp_channel_data cd ON ct.entry_id = cd.entry_id WHERE ct.author_id = $member_id Although you've requested the entry_id in your question, you don't actually need to include channel_data, as it's in channel_titles. But I've included above in case you want to pull all the entry data (so change ...


2

Using SQL to remove entries means you also have to do extra clean up on relationship tables and categories. You can bypass the input limits by appending the appropriate query string parameters to the address of the edit screen. For example, go to Publis > Edit > Your Channel. Then append the following to the url: &perpage=5&status=closed You ...


2

You can do it like this: <a href="members/index" class="button">Download</a> (place this button anywhere you want) Just create group in your templates called members and add {exp:ajw_export sql="SELECT * FROM exp_member_data" format="csv" filename="members.csv" } to index file.


2

Could you not use native parameters for this? {exp:channel:entries channel="mychannel" show_expired="only" start_on="-12 months"}


2

From the CodeIgniter docs: Beyond simplicity, a major benefit to using the Active Record features is that it allows you to create database independent applications, since the query syntax is generated by each database adapter. It also allows for safer queries, since the values are escaped automatically by the system.


1

You're missing a database column that was added in Matrix version 2.5 for compatibility with Better Workflow. It sounds like you may have updated the files for that add-on but not triggered the update by visiting Add-Ons > Fieldtypes > Matrix to trigger Matrix’s update script.


1

Use this query... SELECT * FROM exp_extensions WHERE hook = 'hook_youre_looking_for'; The class column will indicate what Extension it belongs to, and the method column will tell you what method (function) to look at in that PHP class.


1

look in the exp_extensions table. you should find everything you need there


1

You're getting the total of all related articles because you're not specifying that you need to group the results by entry. Try: SELECT t.entry_id, t.title, COUNT(r.child_entry_id) AS entry_count FROM exp_channel_titles AS t INNER JOIN exp_playa_relationships AS r ON t.entry_id = r.child_entry_id WHERE r.parent_field_id =...


1

The Query is good. Setting issue unique to my setup on the database itself. Once DB issue was taken care of, everything worked out.


1

Turn on template debugging, you should be able to see if the entry id is being passed correctly to the playa tag.


1

After playing around and learning about SQL I have found solution . It works. For thouse who are strangling here it is. Please NOTE the difference from the above. DATE_FORMAT(DATE_ADD(from_unixtime(cd.field_id_454), INTERVAL 6 HOUR),'%h:%i %p') AS 'Start Time',


1

As far as I can tell, you must use use Unix Timestamp (ugh) to get proper results. So: SELECT entry_id, title, expiration_date FROM exp_channel_titles WHERE expiration_date BETWEEN unix_timestamp(date_sub(Now(), INTERVAL 12 MONTH)) AND unix_timestamp(Now());


1

If the site_id is 1: SELECT channel_id, channel_name FROM exp_channels WHERE site_id = 1; Getting the channel_id and ht e channel_name.


1

While we're mentioning add-ons here, Low Search will do the job nicely. http://gotolow.com/addons/low-search


1

I've always used Solspace Super Search for all of my projects and never run into anything it couldn't do search related: http://www.solspace.com/software/detail/super_search/


1

Date math is a pain. Here's query that should give you entries in the future: SELECT entry_id, title FROM exp_channel_titles WHERE UNIX_TIMESTAMP() < entry_date; Double check this to make sure you are getting the 200 results you are expecting. Server Time zone, User time zone, DST settings, server time etc can all throw you off, but in any case it ...


1

{preload_replace:group_to_display="1"} {preload_replace:current_category_id="15"} {exp:query sql=" SELECT DISTINCT `cats`.* FROM `exp_category_posts` AS `posts` INNER JOIN `exp_categories` AS `cats` ON `posts`.`cat_id` = `cats`.`cat_id` WHERE `group_id` = {group_to_display} AND `entry_id` IN ( SELECT DISTINCT `entry_id` ...


Only top voted, non community-wiki answers of a minimum length are eligible