Sorting Uploads

15 posts by 2 authors in: Forums > CMS Builder
Last Post: January 12, 2011 (RSS)

Archived
Reply
Reply

By Jason - January 12, 2011

Hi Phil,

uploads is a special table in CMS Builder and normally isn't accessed this way. You'll need to always have something in orderBy for this query.

The word order has special meaning in MySQL, so you'll also have to have the single quotes around it so MySQL knows you're talking about a field name and not a command.

The field order is the dragSortOrder that you have the images inside CMS Builder. If you change the order in CMS Builder, you should see the order changed.

If this isn't the way you want it to work, which field would you like to sort on?

Hope this helps.

---------------------------------------------------
Jason Sauchuk - Project Manager
interactivetools.com

Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/

Re: [Jason] Sorting Uploads

Archived
Reply
Reply

By pcolvin - January 12, 2011

Jason,

I do want to sort on the dragSortOrder field for the one upload field. I have tried putting the order in single quotes, the page displays but no sorting takes place. See below for the code I'm using.

  //get uploads for the selected record other docs
  list($upload_otherRecords, $upload_otherMetaData)=getRecords(array( 
    'tableName'       =>      'uploads', 
    'allowSearch'     =>      false, 
    'where'			  =>	  "tableName='".mysql_escape(@$_REQUEST['section'])."' AND fieldName='other_docs' AND recordNum='".intval($myRecord['num'])."'",
	'orderBy'		  =>	  "'order'",
	'debugSql' => true,
    ));

Here is the output from the debugSql

SELECT SQL_CALC_FOUND_ROWS `uploads`.* FROM `cms_uploads` as `uploads` WHERE (tableName='exec_comm' AND fieldName='other_docs' AND recordNum='1') ORDER BY 'order'

Now for another question which is probably also a syntax problem.

In the other upload field which is for meeting minutes, I would like to sort them by the info1 field which contains and English date such as November 1, 2010. Is it possible to take the English date and use the strtotime() function to covert it to a number and then sort by the number? See the code below and the SQL ouput:

  //get uploads for the selected record meeting minutes
  list($upload_minutesRecords, $upload_minutesMetaData)=getRecords(array( 
    'tableName'       =>      'uploads', 
    'allowSearch'     =>      false, 
    'where'			  =>	  "tableName='".mysql_escape(@$_REQUEST['section'])."' AND fieldName='meeting_minutes' AND recordNum='".intval($myRecord['num'])."'",
    'orderBy'         =>      "\" strtotime('info1')\"",
	'debugSql' => true,	
    ));

I know what I have in the orderBy will not work since it sees the function inside the quotes. I just don't have my head around the proper PHP syntax just yet to get it right.

SQL output:

SELECT SQL_CALC_FOUND_ROWS `uploads`.* FROM `cms_uploads` as `uploads` WHERE (tableName='exec_comm' AND fieldName='meeting_minutes' AND recordNum='1') ORDER BY " strtotime('info1')"

Sorry to be such a pain with this. Hopefully I'm close enough and will soon have this working.

Thanks

Phil

Archived
Reply
Reply

By Jason - January 12, 2011

Hi Phil,

That's really odd that the first query isn't working. I've tried this locally and it does sort the uploads. I can change the order in the CMS Record and it reflects that change.

For that one, maybe if you could fill out a [url http://www.interactivetools.com/support/]2nd Level Support Request[/url] and let me know the name of the .php file you're working with I can take a look.

As for the second query, if you want to convert a string in info 1 to a date, you can use the MySQL Function STR_TO_DATE().

For example, if you're storing your date in info 1 like this:
January 1, 2011, you can try this:

'orderBy' => "STR_TO_DATE(info1,'%M %d,%Y')",

Here is some more info on STR_To_DATE():
http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_str-to-date

Hope this helps

Re: [Jason] Sorting Uploads

Archived
Reply
Reply

By pcolvin - January 12, 2011

Jason,

I got the second query working perfectly with the date. Thank you.

I'll fill out a second level request for the other one.

Thanks

Phil

Archived
Reply
Reply

By Jason - January 12, 2011

Hi Phil,

I've taken a look into this. The issue was the single quotes around order. I replaced ' with ` around order (`order`) and it works now. The reason was the single quote was making MySQL treat the value in order as a string, not a number. If you change the drag sort order in your CMS record now, you'll see the change reflected on the page.

Hope this helps
Jason