Hot answers tagged

8

I haven't heard of any best practices around this. I think it depends on the user, you. If you're looking for some best practices to improve speed, you may want to start with: Ellis Lab's Handling Extreme Traffic Solspace's Website Performance Guidelines (PDF) Matt Perry's How to Make EE Faster .Net's Optimise your ExpressionEngine site Generally speaking, ...


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


5

To get a single value, you want to use the row() method on your $query object: $this->EE->db->select('my_col'); $query = $this->EE->db->get_where( 'fb_photo_settings', array('setting_name' => 'facebook_id') ); $var = $query->row('my_col'); To get an object with a whole row: $query = $this->EE->db->get_where( '...


5

A JOIN requires an ON clause too. Also, it's exp_channel_titles, not exp_channel_entries. Try this on for size as the sql parameter: SELECT COUNT(cp.entry_id) AS post_count FROM exp_category_posts cp JOIN exp_channel_titles t ON cp.entry_id = t.entry_id WHERE t.status IN ('Open', 'open') AND cp.cat_id = '{category_id}' If you want to add more statuses to ...


5

Try this to see if it works for you. I just tested, and it works just fine for me. $this->EE->session->userdata('signature') To see all the values in the session values stored as userdata, just do the following: var_dump($this->EE->session->userdata); exit();


5

Most of required indexes are already there. What I usually do, is if I use custom sorting order with exp:channel:entries I add key on field that I sort by. I.e. if I have {exp:channel:entries orderby="title"} I add index on title in exp_channel_titles table. If I have {exp:channel:entries orderby="my_custom_field"} and my_custom_field is a custom field ...


5

This happens because of the "else". If you use else, the conditional became advanced and it will be parsed after the modules. Try this: {if segment_3 != ''} {exp:query sql="SELECT a.cat_id, a.parent_id, b.cat_name FROM exp_categories a, exp_categories b WHERE a.cat_url_title = '{segment_3}' AND b.cat_url_title = '{segment_2}'"} {/if} {if segment_3 ==''} ...


5

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


4

GROUP_CONCAT is your friend. Instead of returning multiple rows of entry IDs, GROUP_CONCAT lets you return a single row containing a concatenated string of a single field, like entry_id, like in this example: {exp:query sql="SELECT GROUP_CONCAT(DISTINCT entry_id SEPARATOR '|') AS entry_ids FROM exp_channel_titles WHERE channel_id = 1 ...


4

If you turn on the Output Profiler, you will be able to see the list of SQL queries generated for any page.


4

try to add sticky DESC {exp:query sql="SELECT entry_id AS rel_entry_id, title AS rel_title, (SELECT rel_child_id FROM exp_relationships WHERE rel_id = '{select-location}' LIMIT 1) AS rel_child_id FROM exp_channel_titles WHERE channel_id IN (5, 7) AND status='Open' ORDER BY sticky DESC, title ASC "}


3

For custom field columns that will be used in join statements I find its best to set the column type to an int or a varchar(#) and then index it as suggested. You can also optimise the way EE gets its data from the database. When EE queries the database for a set of channel entries it brings back a lot of data that is often not needed. This includes ...


3

You can use one query instead of two using the sql JOIN: $sql = "UPDATE exp_channel_titles ct LEFT JOIN exp_channel_data cd ON cd.entry_id = ct.entry_id SET ct.status = 'Registered', cd.field_id_119 = 'Yes' WHERE ct.channel_id=12 AND cd.field_id_119 != 'Yes' AND ct.entry_id={entry_id} AND ct.status != 'Hidden'"; $this->EE->db->query($sql);


3

Right, you are performing two queries so you need two calls to the db (if you want to use CI). This is the only other way I could think to do reasonably do it, but it's actually more code (but not handwritten SQL). $this->EE->db->where(array( 'channel_id' => 12, 'entry_id' => $entry_id, 'status !=' => 'Hidden' )); $this->...


3

You'll need to use the Allow EE Code plugin, and wrap your field with it on the front-end: {exp:allow_eecode query="y"} {my_field} {/exp:allow_eecode}


3

I'm not aware of any established best practices for adding indexes to DB tables. I'd say the official EllisLab stance is you shouldn't have to add any because they've done this work for you where needed. But every project has different needs and developers have to evaluate this on a case by case basis depending on the project. In my years with EE, I've only ...


3

I was just about to have a crack at a little plugin for this but it looks as though Andrew Weaver beat me to it. By the sound of it his plugin will do what you're after: {exp:ajw_country} to display the 2 letter country code of the current IP


3

Shouldn't it be t.author_id instead of t.author? {exp:query sql=" SELECT count(*) AS num_members FROM exp_members m JOIN exp_channel_titles t on t.author_id = m.member_id"} {num_members} {/exp:query}


3

All results of queries created by the exp:channel:entries module are parsed by the function parse_images on $this->EE->typography. When you use the exp:query module, you need to do this by your own; but, luckily, Rob Sanchez coded an addon for this. {exp:query sql="SELECT field_id_1 AS file FROM exp_channel_data" parse="inward"} {exp:parse_file_paths} ...


3

Its not clear from your question whether you're wanting to show the child categories based on the parent category page you're currently viewing. We needed to do the same thing, so I wrote a quick plugin, as I don't think there's any combination of EE tags that will allow you to achieve this. The scenario we used this in was if I was on domain.com/page/...


3

You may want to check if mod_security is installed on your server and either disable it or change the configuration. My guess is that your issues is that mod_security is seeing the SQL query in your query tag and blocking the submission.


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

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

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

You are correct - when using the User Authors feature, all authorship data is stored in the exp_user_authors table. This script should fill in the gaps in your User Authors table for you. Backup your database, create a new template, enable PHP on it, put this in it (replace $channel_id at the top with your actual channel_id), then load the template i your ...


2

While custom queries using the queries module will provide the most flexibility in designing a report like this, you may also want to take a look at NSM Reports. The goal of NSM Reports is to allow you to create custom reports faster, but store/save reports, and send reports to clients or automatically run a monthly report or so. I've tended towards custom ...


2

Your question asks for a query to change the status of an entry. This is the code you'd use to change the status for a specific entry_id: UPDATE exp_channel_titles t SET t.status = 'pending' WHERE t.status = 'open' AND t.entry_id = "1" I suspect you need something more complicated though. If that's the case, you'll need to update your questions with ...


2

You're running into variable collision. Since both the Query and Channel modules use the {paginate} tags, the outer-most module (Query) is winning the battle. The solution is to embed your Channel Entries loops. {exp:query sql="SELECT status FROM exp_channel_titles WHERE entry_id = '46'"} {if status == 'closed'} {embed="_embeds/loop1"} {if:...


2

You would need to either turn on PHP for the template and then use that to create a query like you did, or go the much easier route an just use the Query Module included with ExpressionEngine (unless you are using Core). With the Query Module your statement would look like {exp:query sql="SELECT col_id_1, col_id_2, col_id_3 FROM exp_matrix_data"} {...


2

You first need to initialize global object like: $this->EE =& get_instance();


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