Ordered List of Records

Hi Perch, 

I've done some research and you need to use the FIELD function in your order by string to order the items:

  // load records from 'blog'
  list($blogRecords, $blogMetaData) = getRecords(array(
    'tableName'   => 'items',
    'allowSearch' => false,
    'orderBy'     => "FIELD(num,4,5,6,1,2,3) DESC"
  ));

So the FIELD function orders records by the num field, and will cycle through each value in the array after it. 

You'd have to ensure that the items are entered in comma separated string, or you'll get MySQL errors. You could do this by limiting the values allowed in selectedITems field to numbers and commas.

Let me know if you have any questions

Cheers

Greg

Hi, Greg -

Sounds interesting - but how could I apply this approach to different [category] pages with different number strings?

The idea is that each page would have it's own number string.

:0/

Perch

Hi, Greg -

Thanks for this. It seems to work well. However, could you explain a bit more about how the FIELD() funtion works? In particular I'd like to know more about the num parameter.

'orderBy'     => "FIELD(num,$itemOrder) DESC"

The only minor issue I've found is that the returned values are not limited to the $itemOrder value. To get around this I've added the following lines to the getRecords function:

$itemOrder = $category['selectItems'];

$limit = count(explode(',', $category['selectItems']));

  // load records from 'items'
  list($items, $itemsMetaData) = getRecords(array(
    'tableName'   => 'items',
    'loadUploads' => true,
    'allowSearch' => false,
    'orderBy'     => "FIELD(num,$itemOrder) DESC",
    'limit'    => $limit,

  ));

The count and explode functions seem a bit over-the-top but I'm guessing this is the only way to do it?

:0)

Perch

Hi Perch, 

Here is a way you can get MySQL to do the work for you:

  $itemOrder = $category['selectItems'];

  // load records from 'items'
  list($items, $itemsMetaData) = getRecords(array(
    'tableName'   => 'items',
    'where'       => "num IN($itemOrder)",
    'loadUploads' => true,
    'allowSearch' => false,
    'orderBy'     => "FIELD(num,$itemOrder) DESC",
    'limit'    => $limit,

  ));

So the num in the MySQL field function is the num field in the items section, which is the key value that is automatically created when you add a record. It attempts to sort the returned items in the order of the values in it. You can read more about it here:

http://www.electrictoolbox.com/mysql-order-specific-field-values/

I've added a where statement which will only return values in the num field that are in the IN function.

Let me know if you have any questions.

Cheers

Greg