Sort order issue in expressionengine 1.x

You might noticed the issue for sorting stuff in expressionengine cms to show entries if you’ve worked with this, that is if you’ve a custom field which stores number value and later you want sort the entries by that field – the entries sorted in wrong order! If you didn’t noticed that yet, explaining…

Let you have a custom field named “display_order”. This field stores integer value. Later you’ve tag like bellow to show entries in specific display order by this field –


{exp:weblog:entries  channel="channel_name"  orderby="display_order" sort="asc"  limit="15"}
content
{/exp:weblog:entries}

Above code pulls data in wrong order. Because Expressionengine creates varchar type field in table for every custom field. So if an entry has value 2 for display_order and 10, the entry with value 10 will comes first as mysql query treats them as string.

For this, you can do a quick trick. First find which field is created for the custom field you created, as per above example find out the field created for custom field “display_order”. Lets say table field for this custom field is field_id_20. Alter this field as integer type. So prepare a query like below and execute –

ALTER TABLE exp_weblog_data CHANGE `field_id_20` `field_id_20` INT(11) DEFAULT NULL

So, now see – issue is fixed 😉

Advertisements

Why should we use PDO(PHP Data Object)?

PDO, The PHP Data Object extension is a data-access abstraction layer supports multiple databases(Mysql, SqlLite, PostGreSql). PDO is shipped with PHP 5.1

If you uses framework, you don’t have to do that, you use the database class,methods to handle this. If you are building application from scratch, you may build database class to interact with database with a list of methods. Thus PDO is perfact for you because

This extension has all the methods to get/set value with databases.
Excellent error trapping methods (following code is to connect with mysql database). See below –

<?php
$dsn = 'mysql:host=localhost;dbname=world;';
$user = 'user';
$password = 'secret';
try
{
$dbh = new PDO($dsn, $user, $password);
}
catch (PDOException $e)
{
echo 'Connection failed: ' . $e->getMessage();
}
?>

Mysql errors trapping methods.
Method to protect SQL injection attack.
Today’s popular E-Commerce tool Magento uses PDO. It’s excellent php extension so far.