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 Perch,

So each category record will have a detail page, and the Items will be in a different order on it?

If you had a getRecords function that was retrieving a specific category, you could use its selectItems field to order the data in the items section like this:

  // load record from 'category'
  list($categories, $categoryMetaData) = getRecords(array(
    'tableName'   => 'category',
    'where'       => whereRecordNumberInUrl(0),
    'allowSearch' => false,
    'limit'       => '1',
  ));
  $category= @$categories[0]; // get first record
  if (!$category) { dieWith404("Record not found!"); } // show error message if no record found


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


This is just example code, so you'll have to make a few changes to get it working with your site.

So a category is being loaded from a category section and the record is being set against the $category variable. Then the selectItems field variable is set to the $itemOrder variable, and this is used in the orderBy key in the getRecords for the items section. 

Cheers!

Greg

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