Ordering of Channel Entries not working
Development and Programming
Hi. I am displaying a list of entries from a particular channel however the order function does not seem to work.. the “limit” works ok however the “orderby” and “sort” don’t. Code is below.. anything obvious as to why this would not work? Appreciate any help that can be given.. I’ve been going crazy over the last few days 😉
<?php
$sql = "";
if (isset($_GET['season']) && $_GET['season'] != "") {
$sql .= " AND field_id_131 LIKE '%" . $_GET['season'] . "%'";
}
if (isset($_GET['event']) && $_GET['event'] != "") {
$sql .= " AND field_id_132 LIKE '%[" . $_GET['event'] . "]%'";
}
if (isset($_GET['team']) && $_GET['team'] != "") {
$sql .= " AND field_id_133 LIKE '%[" . $_GET['team'] . "]%'";
}
if (isset($_GET['driver']) && $_GET['driver'] != "") {
$sql .= " AND field_id_134 LIKE '%[" . $_GET['driver'] . "]%'";
}
?>
{exp:query sql="SELECT * FROM exp_channel_data WHERE channel_id = '13' AND site_id = '1'<?php print $sql; ?>" limit="30" orderby="date" sort="desc"}
{exp:channel:entries channel="gallery" dynamic="no" entry_id="{entry_id}"}
<a href="http://{image:normal}" rel="gallery" title="{title} | {credit}" class="fancybox"></a>
{if no_results}
Sorry, no results found! Please redefine your search criteria.
{/if}
{paginate}
<div class="gallerypages">
<div class="pages">
{pagination_links}
</div>
</div>
{/paginate}
{/exp:channel:entries}
{/exp:query}
the query module executes SQL, and the entries module has the features for “order by” and “sort”…
The solution is to update your SQL to sort in your query. But because the entry_date is stored in your exp_channel_titles table, we’ll have to include that in the query as well.
Additionally, you should try to employ the “input” class whenever possible to filter against some possible exploits and SQL injections. Not required… but keeps you a bit more protected.
(I’ve included that update in here as well, although you can take and pick what you want to use. 😊
<?
$sql = "";
$season = $this->EE->input->get('season');
$event = $this->EE->input->get('event');
$team = $this->EE->input->get('team');
$driver = $this->EE->input->get('driver');
if ($season != "") {
$sql .= " AND field_id_131 LIKE '%" . $season . "%'";
}
if ($event != "") {
$sql .= " AND field_id_132 LIKE '%[" . $event . "]%'";
}
if ($team != "") {
$sql .= " AND field_id_133 LIKE '%[" . $team . "]%'";
}
if ($driver != "") {
$sql .= " AND field_id_134 LIKE '%[" . $driver . "]%'";
}
?>
{exp:query sql=" SELECT
exp_channel_data.entry_id
FROM
exp_channel_data,
exp_channel_titles
WHERE
exp_channel_data.entry_id = exp_channel_titles.entry_id
AND
exp_channel_titles.channel_id = '13'
AND
exp_channel_titles.site_id = '1'
AND
exp_channel_titles.status != 'closed'
<?php print $sql; ?>
ORDER BY
exp_channel_titles.entry_date DESC
LIMIT 30"}
{exp:channel:entries
channel="gallery"
dynamic="no"
entry_id="{entry_id}"
}
<a href="http://{image:normal}" rel="gallery" title="{title} | {credit}" class="fancybox"></a>
{if no_results}
Sorry, no results found! Please redefine your search criteria.
{/if}
<!-- oh crap... this will not work... -->
{paginate}
<div class="gallerypages">
<div class="pages">
{pagination_links}
</div>
</div>
{/paginate}
<!-- /oh crap -->
{/exp:channel:entries}
{/exp:query}….BUT…. now that I’ve written that out… I see that your pagination won’t work (the entries module only will ever see “1” result)
here’s another solution:
<?
$sql = "";
$season = $this->EE->input->get('season');
$event = $this->EE->input->get('event');
$team = $this->EE->input->get('team');
$driver = $this->EE->input->get('driver');
$search_season = '';
$search_event = '';
$search_team = '';
$search_driver = '';
if ($season != "") {
$search_season = " search:season='" . $season . "'";
}
if ($event != "") {
$search_event = " search:event='" . $event . "'";
}
if ($team != "") {
$search_team = " search:team='" . $team . "'";
}
if ($driver != "") {
$search_driver = " search:driver='" . $driver . "'";
}
?>
{exp:channel:entries
channel="gallery"
dynamic="no"
entry_id="{entry_id}"
<?= $search_season.$search_event.$search_team.$search_driver; ?>
orderby="date"
sort="desc"
}
<a href="http://{image:normal}" rel="gallery" title="{title} | {credit}" class="fancybox"></a>
{if no_results}
Sorry, no results found! Please redefine your search criteria.
{/if}
<!-- okay... this should work now -->
{paginate}
<div class="gallerypages">
<div class="pages">
{pagination_links}
</div>
</div>
{/paginate}
{/exp:channel:entries}(this code makes an assumption about the short name of your custom fields… you will want to update those to be the actual names of your custom fields)
I didn’t actually test any of the above code… but it should be close (if you run into any typos, please let me know and I’ll tweak the above to be correct.)
I hope this helps
Many thanks for looking at this.. at present I’m getting no results with the above code.. Having gone through what I submitted again I noticed that I missed some comments at the start
{!--
field_id_131 = season
field_id_132 = playa:event
field_id_133 = playa:team
field_id_134 = playa:driver
--}It’s using playa for the last 3 fields.. will this make a difference? Many Thanks again
Perhaps… but, I don’t use Playa… so I have no idea the implications.
for the moment, try reducing the complexity until you get it working (try removing the three playa fields from the code):
...
{exp:channel:entries
channel="gallery"
dynamic="no"
entry_id="{entry_id}"
<?= $search_season; ?>
orderby="date"
sort="desc"
}
...and see if you can get this working… (if that doesn’t work… back off on the search_season for a moment and see if that is breaking it)
then start adding one thing back at a time until it breaks again, then fix that part - until it’s all working.
sorry I can’t be much help with Playa… hopefully this will lead you in the right direction or help someone in the future.
Thanks again.. The first code does work however as you mention Pagination does not work.. I dropped everything and still no results.. Why would the first option stop the pagination from working? might try and combine the 2 😉
Well…
My first code uses the query module to do a lookup of the right entry_id (via a little loop). For each instance of the loop, the entries module looks up a single record and displays it. The entries module is what controls the pagination tags… and the entries module only is dealing with a single record lookup - so your pagination will never work in that example out of the box.
The second section of code uses the features of the entries module to do the lookup itself… and not use the query module at all.
I would recommend the second example, and see if we can get this working… it will be the most versatile for you down the road.
Ah Ha!
Try removing the
entry_id='{entry_id}'line from the second section of code…
……
(the more complex, least preferable option would be to handle pagination on your own using the query module. It would take some work, and not be as flexible…)
Hi millszola,
Awesome to see the Community knock this one out!
Why would the first option stop the pagination from working? might try and combine the 2 😉
In order you paginate, you have to specify the limit= parameter in the exp:query tag.
{exp:query limit="5" sql="... }That is separate from any LIMIT you specify in your SQL statement and is used expressly for pagination.
Cheers,
Hi millszola,
In order you paginate, you have to specify the limit= parameter in the exp:query tag.
anybody can do it the easy way… 😉
glad to have learned something new about the query module. Thanks!
Thanks Guys.. amazing.. so the ordering is now working and the display of the images etc.. just the pagination.. this is displaying but not filtering.. I think it will be because of where i’ve placed the limit maybe? The limit is in the “{exp:channel:entries… limit="35"}” which not what Dan has mentioned.. but there’s no “{exp:query limit="5" sql="… }” to place it into. or am I being blind!?
{!--
field_id_131 = season
field_id_132 = playa:event
field_id_133 = playa:team
field_id_134 = playa:driver
--}
<?php
$sql = "";
$season = $this->EE->input->get('season');
$event = $this->EE->input->get('event');
$team = $this->EE->input->get('team');
$driver = $this->EE->input->get('driver');
$search_season = '';
$search_event = '';
$search_team = '';
$search_driver = '';
if ($season != "") {
$search_season = " search:season='" . $season . "'";
}
if ($event != "") {
$search_event = " search:event='" . $event . "'";
}
if ($team != "") {
$search_team = " search:team='" . $team . "'";
}
if ($driver != "") {
$search_driver = " search:driver='" . $driver . "'";
}
?>
{exp:channel:entries channel="gallery" dynamic="no" <?php= $search_season.$search_event.$search_team.$search_driver; ?> orderby="date" sort="desc" limit="35"}
<a href="http://{image:normal}" rel="gallery" title="{title} | {credit}" class="fancybox"></a>
{if no_results}
Sorry, no results found! Please redefine your search criteria.
{/if}
<!-- okay... this should work now -->
{paginate}
<div class="gallerypages">
<div class="pages">
{pagination_links}
</div>
</div>
{/paginate}
{/exp:channel:entries}Nope, you’re not blind. Dan was referring to my first example… which is still an option.
If you go with the above code (my option #2… and my personal preference, although either should be able to work) - then we’ll be using the exp:channel:entries pagination:
http://ellislab.com/expressionengine/user-guide/modules/channel/pagination_page.html
try something like this:
{exp:channel:entries
channel="gallery"
dynamic="no"
<?php= $search_season.$search_event.$search_team.$search_driver; ?>
orderby="date"
sort="desc"
limit="35"
paginate="bottom"
}
... (other stuff from above) ...
{paginate}
Page {current_page} of {total_pages} pages {pagination_links}
{/paginate}
{/exp:channel:entries}Hi Mark Thanks so much for your help with this.. however I’m still struggling.. the ordering works as does the pagination.. yey! but we also have a “filter/search” function and now this doesn’t work.. The filter is using a form to create the URL http://localhost/pages/gallery?season=&event=11&team;=&driver;= This would work on the old style but not on this new one that you have kindly created.. Any ideas?
<form class="galleryfilter" action="{path='pages/gallery'}" method="get">
<div class="label">Filter</div>
<div>
<select name="season">
<option value="">All Seasons</option>
<option value="2012" <?php print (isset($_GET['season']) && $_GET['season'] == '2012') ? 'selected="selected"' : ''; ?>>2012</option>
<option value="2011" <?php print (isset($_GET['season']) && $_GET['season'] == '2011') ? 'selected="selected"' : ''; ?>>2011</option>
</select>
<select name="event">
<option value="">All Races</option>
{exp:channel:entries channel="calendar" dynamic="no" orderby="date" sort="asc" status="open|closed"}
<option value="{entry_id}" <?php print ( (isset($_GET['event']) && '{entry_id}' == '{gallery_default_event}') ) ? 'selected="selected"' : ''; ?>>{title}</option>
{/exp:channel:entries}
</select>
<select name="team">
<option value="">All Teams</option>
{exp:channel:entries channel="team" dynamic="no" orderby="title" sort="asc"}
<option value="{entry_id}" <?php print (isset($_GET['team']) && $_GET['team'] == '{entry_id}') ? 'selected="selected"' : ''; ?>>{title}</option>
{/exp:channel:entries}
</select>
<select name="driver">
<option value="">All Drivers</option>
{exp:channel:entries channel="driver" dynamic="no" orderby="title" sort="asc"}
<option value="{entry_id}" <?php print (isset($_GET['driver']) && $_GET['driver'] == '{entry_id}') ? 'selected="selected"' : ''; ?>>{title}</option>
{/exp:channel:entries}
</select>
{!--
<select name="more" class="last-child">
<option value="">All</option>
<option value="Art">Art</option>
</select>
--}
</div>
<input type="submit" value="Submit" />
</form>Quick question. Is there an area where I can post work for Expression Engine freelancers..? I feel guilty about asking people to fix my problems like this and will no doubt have more… Thanks
Is there an area where I can post work for Expression Engine freelancers..?
There’s a job board in the forum where you can post jobs (even freelance, I presume): http://ellislab.com/forums/viewforum/100/
I feel guilty about asking people to fix my problems like this and will no doubt have more…
For me, it’s helpful to solve these issues and keep active in the community:
- I stay aware of security or technical issues with EE
- I’m constantly improving my skills at development
- Karma
So… there’s no need for guilt if you are getting the help that you need. If you’re not getting the timeliness and support you require… then finding a freelancer will be your best route. I would highly recommend to become active in the forums and help me and others out as we need. These same benefits above can be yours.
….
Now back to the code….
When you say the filter search function doesn’t work… what do you mean?
Are the select boxes listing the desired items? Are the specific items being selected automatically? When you submit the form, what is the result?
OK.. so
- The drop down boxes are showing a full list of the desire items
- The submission is working
- However, the results displayed are not being filtered. Just have a full list