Sorting Uploads

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

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

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

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