Last call to make your voice heard! Our 2022 Developer Survey closes in less than a week. Take survey.

Hot answers tagged

6 votes
Accepted

SQL Query to get entries with their categories

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 ...
user avatar
  • 4,002
4 votes
Accepted

Custom query ignoring sticky entries

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 ...
user avatar
  • 5,781
3 votes
Accepted

MX Select Plus CE - problems with SQL Query

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 ...
user avatar
  • 2,418
3 votes
Accepted

How to use an SQL query to display entries from last 45 days

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....
user avatar
3 votes
Accepted

What's wrong with this SQL query?

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 ...
user avatar
3 votes
Accepted

Can't save templates containing Query tags

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 ...
user avatar
2 votes

Pass a UNIX Timestamp as a URL Segment to display entries edited since that date

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 ...
user avatar
  • 6,108
2 votes

Get all entries where expiration date is within last 12 months

Could you not use native parameters for this? {exp:channel:entries channel="mychannel" show_expired="only" start_on="-12 months"}
user avatar
  • 1,577
2 votes

Create a list of entries where parents status is open

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 ...
user avatar
  • 5,781
2 votes
Accepted

Total entry views for all entries in a given category

You should be able to simply add: SUM(t.view_count_one) as view_count ... to your SELECT statement.
user avatar
  • 17.4k
2 votes
Accepted

Create an entry from an extension?

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->...
user avatar
2 votes
Accepted

Querying for a relationship field

https://docs.expressionengine.com/latest/fieldtypes/relationships.html#accessing-children To access the child entry to your Servers channel, just loop over the field like so: {exp:channel:entries ...
user avatar
  • 5,900
1 vote
Accepted

Model service filtering by date

$data = ee('Model')->get('ChannelEntry')->filter('field_id_103', '>' , '1536677819'); This code works for me. Where field_id_103 is my field ID and 2nd parameter is operator (> , <, >=, &...
user avatar
1 vote
Accepted

How do i search for a specific username by id

It would seem you want to do this with a php approach over using the exp:query because of some php variable you want to use with it? If so, check out the Database Class Docs over in the EE ...
user avatar
1 vote

Create a list of entries where parents status is open

Unless I'm missing something this is really straightforward. The channel:entries tag assumes the open status so you don't even need to specify it. Assuming you have a relationship field in the ...
user avatar
1 vote

ExpressionEngine 2.7.3 How do I directly execute a query against the database?

Enable the Profiler (Admin > System Administration > Output and Debugging) so you can see what the query actually being executed is. If you aren't getting results from the second query, I would ...
user avatar
  • 2,255
1 vote
Accepted

Need query that filters entries by category

You have defined p as alias for the posts table but you have not defined t as alias for the titles table. FROM exp_channel_titles t You'll also probably want to limit by status and by date (not ...
user avatar
  • 1,079
1 vote
Accepted

SQL Query to Get count of entries of a certain status inside channel

So... I finally got it working, though not with SQL which I imagine is more efficient. Behold my mess. Note this is only because I'm having to nest it, and also match to url_title because of how we ...
user avatar
1 vote

SQL Query to Get count of entries of a certain status inside channel

It might work out easier to turn this into a short plugin, but would a query like this work? You'd need to pass in the id of the channel you're looking for. {exp:query sql="SELECT count(*) AS ...
user avatar
1 vote
Accepted

Dynamic Template Name in URL

Are the separate templates really different enough to warrant being separate? Usually you could code in the logic for slightly different views within a single template - much simpler and more ...
user avatar
  • 4,885
1 vote
Accepted

Passing a GET variable back in a query while using mysqli_real_escape_string()

Use ee()->db->escape_str($event) - it's the native way to escape potentially-unsafe data in EE.
user avatar
  • 17.4k
1 vote

Removing duplicate entries using an SQL query

I've now solved this one by using an SQL Query as follows: <select id="locationList" name="fieldName"> <option value="">All Locations</option> {exp:query sql=' ...
user avatar
1 vote

Removing duplicate entries using an SQL query

I would have solved that with storing everything in a exp:stash:set_list, and then looping out it with a exp:stash:get_list. Read more on how to do that here: https://github.com/croxton/Stash/wiki/%...
user avatar
1 vote

Low Search and match 3 fields and then return data

Looking at your example code, you've got several filter parameters in your Form tag. And as stated: these parameters can be applied in two ways: as input fields in a Form, or as hard-coded ...
user avatar
  • 11.3k
1 vote

Forbidden: You do not have permission to access this document - When attempting to save a Template

The issue was that the Hosting Provider had enabled mod_security in the middle of the day (right when I had taken a slight break) without notifying me of this ahead of time. They disabled ...
user avatar
1 vote

Can you query a grid field to return a list of distinct fields?

I preface the answer by saying that I do not recommend using SQL to get at your entry data this way, as it's what the Channel Entries tag and field variables are designed to do for you. That said, ...
user avatar
  • 2,255
1 vote

URL segmentation GoDaddy

I think I've heard of this issue before, where the host forces query strings. http://ee2docs.com/urls/remove_index.php.html#exceptions Look under that reference and scroll down to the area ...
user avatar
  • 5,900
1 vote

List authors by number of posts in specific channel

After reading up a bit on mySQL, I've found an answer, which seems to work. If anyone sees that this is horribly inefficient feel free to disagree! {exp:query sql="SELECT exp_members.screen_name, ...
user avatar
1 vote

Playa related entries count

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 ...
user avatar
1 vote

How can I retrieve the site name from a channel entry as a query?

Here's the query to get the site name and site label for each channel: SELECT site_name, site_label, c.site_id, channel_name, channel_id FROM exp_channels c LEFT JOIN exp_sites s ...
user avatar
  • 1,079

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