Hot answers tagged

8

The problem is that many developers don't know how to write compliant SQL statements - they're software developers who find themselves also filling the role of database developers. This problem crops up in many addons and was recently fixed in EE core code. For Assets v2.6 Change line 615 of ft.assets.php FROM: $sql = "SELECT DISTINCT a.source_type, a....


6

I believe this is what you're looking for: SELECT exp_channel_data.entry_id AS entry_id, exp_channel_titles.title AS title, exp_channel_titles.url_title AS url_title, GROUP_CONCAT(cp.cat_id SEPARATOR '|') AS categories FROM exp_channel_data LEFT JOIN exp_channel_titles ON exp_channel_titles.entry_id = exp_channel_data.entry_id LEFT JOIN exp_category_posts ...


5

UPDATE Since you would probably prefer to truly just move an existing category to a new group (and not create new ones and reassign entries as outlined below), this SQL is all you would need: UPDATE exp_categories SET group_id = X WHERE cat_id = Y OR parent_id = Y X = new category group Y = parent category that you want to move to new group The OR ...


5

My answer assumes you are for example trying to pull channel data manually. I'm not entirely sure of the OOP / Foreign key part of the question. First you need to understand the exp_channel_titles and exp_channel_data tables. I will only go into the information that's relevant for basic database interaction. exp_channel_titles contains the basic entry ...


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


4

You can use the channel fields api to get the field names: ee()->load->library('api'); ee()->api->instantiate('channel_fields'); $fields = ee()->api_channel_fields->fetch_custom_channel_fields(); //change 1 to your site_id if using MSM $quantity_field_id = $fields['custom_channel_fields'][1]['your_quantity_field_name']; $sql .= "picd....


4

You can do this by developing your add-on as a module rather than a plugin since only modules can use EE actions. (See the EE docs for an explanation of the difference between the types of add-on.) More info on EE actions here. Essentially, you need to develop a module to perform your desired action, then call the associated ACT URL (e.g. http://example....


4

You can make a module - like Steve advice. Use Yeoman ExpressionEngine Add-On generator to simplify developing process. On server you will need to setup cron with wget: execute cronjob every day at 3am 00 03 * * * wget http://example.com/index.php?ACT=XX&task_id=1 Or if you want something really quick and simple: Create a special template with some ...


3

Can you define what you mean by "a modern CMS"? Any database driven system will face similar problems when using Git source control. This isn't specifically an ExpressionEngine problem. The only systems that wouldn't suffer this difficulty are those using flat file architecture. I often use Git with ExpressionEngine. The only possible sticking point being, ...


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

Your best bet would be to attempt to repair the table via a database management utility outside of the EE control panel. Does your web host offer anything like a PHPMyAdmin interface, or do you have any experience with standalone MySQL clients like Sequel Pro? Those utilities should expose table repair functionality. I'd start there first. Before you ...


3

You should never have to reinstall addons locally if you are pulling files/DB from production properly. If you are having this problem, the issue is most likely related to how you are doing the migration. I'd suggest your errors are caused by your exported SQL not including DROP TABLE and you aren't manually dropping the tables in the DB before your import. ...


3

To get a total of all occurrences across all comments: SELECT SUM(LENGTH(comment) - LENGTH(REPLACE(comment, '?', ''))) AS occurrences FROM exp_comments To get the count for each comment in a query result: SELECT LENGTH(comment) - LENGTH(REPLACE(comment, '?', '')) AS occurrences FROM exp_comments (MySQL doesn't have a specific function for this, but I ...


3

I ran it and I'm getting results just fine. Are you outputting the results correctly? $query = ee()->db->query('SELECT cat_url_title, cat_name, ch.channel_name FROM exp_categories c LEFT JOIN exp_channels ch ON ch.cat_group = c.group_id WHERE c.cat_name LIKE "%'.ee()->db->escape_str($keywords).'%"'); foreach($query->...


3

There's a similar post here But you could just look in the template debugging info. Another option would be to add something to each template that shows its name (either output to screen or as an HTML comment). Of course, you would then need to traverse the whole site and note which templates were used, and which ones weren't. Which would possibly be ...


2

Yes, you can prepare an array which would contain the custom field name and id like: $fields = array(); $query = $ee()->db->query("SELECT exp_channel_fields.field_id, exp_channel_fields.field_name FROM exp_channel_fields LEFT JOIN exp_channels ON exp_channel_fields.group_id=exp_channels.field_group WHERE exp_channels.channel_name='[CHANNEL NAME]'"); ...


2

It seems that no member group is selected as "Include Members in PUBLISH page multi-author list?". Go to Control Panel -> Members -> Member Groups. Click on "Edit Group" next to Super Admin. Now make sure the "Include Members in PUBLISH page multi-author list?" is "Yes".


2

Perform DB backup incase this goes wrong. Execute the follow statement UPDATE exp_category_posts SET cat_id = 4 WHERE cat_id = 2


2

Fixed this recurring error which was caused by huge traffic spikes ourselves by doubling the RAM from 2GB to 4GB (which spiked to 400% during heavy memory pressure) allocated to the VM.


2

Depending how many entries need updating you can always bulk assign a category to multiple entries from the main edit page.


2

Ok. I changed the storage engine of EE database from MyISAM to InnoDB. And now the database is replicating the primary key columns of the exp_templates and exp_template_groups tables correctly. It may have something to do with Percona Clusters' limitations: The main Percona site (Percona XtraDB Cluster Limitations) states: Currently replication works ...


2

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


2

You can store your field id's as config vars, so you can change them per environment. Nice thing about this is you don't incur an extra query to get the field names. In your main config.php $config['your_addon_quantity_field'] = 3; In your addon: $quantity_field = ee()->config->item('your_addon_quantity_field'); $sql .= "picd.field_id_{$...


2

For this particular quandary, this worked: Add this line to the top of the SQL file: SET FOREIGN_KEY_CHECKS = 0; Source: http://devot-ee.com/add-ons/support/stash/viewthread/5700 If you see this problem and the above solution doesn't work, check this thread as well: http://devot-ee.com/add-ons/support/stash/viewthread/10311


2

I think either method would work. The hook is going to be the more nerdy (read "hard") way to do it. I use the store_order_complete_end hook to trigger code that notifies other systems of the order. Depend ing on how AJW Export is written, you may be able to fire it off from your code in the extension. The easier approach would be to do it in the template. ...


2

I recently made an extension to do this, so I've put it up on GitHub for others to learn from. https://github.com/expressodev/store_order_export Generally, you want to use the store_order_complete_end hook, which runs when an order is marked as "complete" (when payment is successful). From there it is easy to write an extension which generates any XML (or ...


2

You need to specify the channel parameter, and you can leave out dynamic="no" because that's not a parameter for this tag. If specifying the channel parameter does not work,try adding the following parameters: status="not closed" show_future_entries="yes" show_expired="yes" {exp:channel:categories category_group="1" status="not closed" ...


2

How big is the database? Something like the below (untested) should do it with PHP $sql = "SELECT * FROM exp_channel_titles"; $results = //execute query $urls = array(); foreach($results as $result) { $count = 1; $url = $result['url_title']; $entry_id = $result['entry_id']; while(isInArray($url, $urls)) { $url = $result['...


2

AFIAK there is no quick/easy/simple way. You're on the hook for trying to generate every page within the site and reverse-engineering it to see what templates were involved. With Templates Routes now possible this chore will get even harder. I have lobbied in the past for an addon that would not only do this, but find all the cruft in a legacy install - ...


2

UPDATE exp_category_posts LEFT JOIN exp_channel_data ON exp_channel_data.entry_id = exp_category_posts.entry_id AND exp_category_posts.entry_id IS NULL AND exp_channel_data.channel_id = 5 SET exp_category_posts.cat_id = 123; Please backup those two tables before running any update queries, just in case. Also make sure you know how to ...


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