Hot answers tagged

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

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

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 ==''} ...


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

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

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

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 first need to initialize global object like: $this->EE =& get_instance();


2

You can use this simple query with Query module: {exp:query sql="SELECT exp_categories.cat_id, exp_categories.cat_name, exp_categories.cat_url_title FROM exp_categories LEFT JOIN exp_category_field_data ON exp_category_field_data.cat_id=exp_categories.cat_id WHERE exp_categories.group_id=2 AND exp_category_field_data.field_id_1='Yes'"} <p>{cat_name}...


2

It occurred to me this would be a great idea for a plugin, since it's something most people have to do often when building even moderately complicated sites, and currently requires you to understand both MySQL GROUP_CONCAT and the intricacies of the EE parse order. So without further ado, I present the Channel Query plugin. Use it like so: {exp:...


2

This sounds like a parse order issue. It's the sort of thing you could turn into a custom plugin really easily though, and it would avoid creating any SQL injection security issues at the same time. Create a new file third_party/my_plugin/pi.my_plugin.php <?php class My_plugin { public function __construct() { $this->EE = get_instance(...


2

You can probably do this with Stash. I haven't tested this code, but it should work. First, output/stash the entries by date. {exp:channel:entries orderby="date" ...} {exp:stash:set_list name="entries"} {stash:title}{title}{/stash:title} {stash:is_recent}{if count < 11}true{/if}{/stash:is_recent} {/exp:stash:set_list} {/exp:channel:entries} ...


2

Try to use embed to set and pass ahead a variable: {embed="template_group/.entries-list" eleventh="{exp:channel:entries offset='10'}{edit_date format='%U'}{exp:channel:entries}"} On the embed template: {exp:channel:entries} {if {edit_date format="%U"} < {embed:eleventh} } <custom markup> {/if} default markup (...) {if {...


2

You have a conflict of names of variables. "count" is a reserved name. On all modules of EECMS, the current item number of a loop is parsed on the variable {count}, starting on 1. If you code a query that returns, let's say, 5 rows, your result would be "12345". Your query returns just one row. The loop will be executed once and returns "1". Of course, you ...


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

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

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

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


2

I'm not entirely familiar with Channel Ratings, but you should be able to do what you're looking for with the query. Try this: {exp:query sql="SELECT exp_channel_ratings.*, exp_members.screen_name as like_screen_name FROM exp_channel_ratings_likes, exp_members WHERE entry_id = '{entry_id}' and exp_channel_ratings.like_author_id = exp_members....


2

I believe, your options are: to change the format of date you're using on the segment_2, which isn't a good idea because EllisLab can some day finally normalize all date fields; to reformat the date to match the way its used on edit_date database column; To change the date format, I suggest you the Low Nice Date plugin: {exp:low_nice_date date="{segment_2}...


2

If I understand you needs correctly, and you need art list w/o grouping by artists (for list for last artworks as example) : {exp:query sql='SELECT GROUP_CONCAT(child_id SEPARATOR ',') as art_ids FROM exp_relationships LEFT JOIN exp_channel_titles ON exp_relationships.parent_id=exp_channel_titles.entry_id WHERE exp_channel_titles.status = "open" AND ...


2

You should be able to simply add: SUM(t.view_count_one) as view_count ... to your SELECT statement.


2

Yes, obviously it is possible to use the model to create a new entry. To create a new entry using model you should follow below syntax. $entry = ee('Model')->make('ChannelEntry'); $entry->Channel = $channel; $entry->site_id = ee()->config->item('site_id'); $entry->author_id = ee()->session->userdata('member_id'); $entry->...


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