Sorting Uploads

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

By pcolvin - January 7, 2011

I have seen a couple of threads that touches on this and I'm sure it can be done. I have several sections on a site that uses the uploads to post dated documents. I need to list the most recently uploaded document at top of the list of upload that is displayed on the page.

I'm guessing the best way to do this is to use one of the Info fields to do the sorting. Would it be possible to use the PHP Date function to insert the date the document was uploaded into one of the Info fields. If so, what would be a good way to sort the uploaded documents using this field.

If I'm off track with the Info field direction, are there any other solutions to sorting the uploads by the date of upload.

Thanks

P Colvin

Re: [pcolvin] Sorting Uploads

By Jason - January 10, 2011

Hi,

All uploads are stored in a separate section called uploads. We access them through the tableName, fieldName, and recordNum of the record we're getting the uploads for. In the uploads section is a field called createdTime, which we can use to do the sorting you want.

What we'll do is select a single record from our section, then do a separate select to get the uploads for that record, ordering them by createdTime.

In this example we're going to use a section called 'listings' which has an upload field called images:

First we select the first record from 'listings':
list($listingsRecords,$listingsMetaData)=getRecords(array(
'tableName' => 'listings',
'allowSearch' => false,

));

$record = $listingsRecords[0]; //get the first record

We now use the record num stored in $record to get the uploads for that record:

list($uploadRecords,$uploadMetaData)=getRecords(array(
'tableName' => 'uploads',
'allowSearch' => false,
'where' => "tableName='listings' AND fieldName = 'image' AND recordNum='".intval($record['num'])."'",
'orderBy' => 'createdTime DESC',

));

The variable $uploadRecords now contains all the uploads for the listings record we selected, ordered by createdTime (most recent first).

Give this a try and let me know if you run into any issues.

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

By pcolvin - January 10, 2011

Jason,

Thank you, I'm sure I can get this to work for me.

Phil

Re: [Jason] Sorting Uploads

By pcolvin - January 11, 2011

Jason,

I may need some guidance getting this solution to work on the site I'm currently building.

I select the records for the page dynamically depending with the code below:

// load records for the page called.
list($myRecords, $myMetaData) = getRecords(array(
'tableName' => $_REQUEST['section'],
'where' => mysql_escapef('num = ?', $_REQUEST['num']),
'limit' => '1',
));
$myRecord = @$myRecords[0]; // get first record

I have tried a couple of variants to get the code working to load the records, but can't quite figure it out. Any ideas?

Thanks

Phil

Re: [pcolvin] Sorting Uploads

By Jason - January 11, 2011

Hi Phil,

At this stage, you just trying to get the record, not the uploads, right? What is the result of your code? Are yo getting an error, or is nothing happening?

If you could attach your entire .php page, I can take a closer look at what's happening.

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: [pcolvin] Sorting Uploads

By pcolvin - January 11, 2011

One other thing that I forgot to include in the last post. The field name that I'm using is meeting_minutes. In your example it was images.

Instead of sorting on the createdTime, I was hoping I could use the info1 field which I then enter a date such as November 1, 2010. This way if the files are not loaded in the proper sequence, they can be sorted correctly. I'm sure I would have to use the strtotime() function to change the data in the field to something that can be sorted.

Thanks

Phil

Re: [pcolvin] Sorting Uploads

By Jason - January 11, 2011

Hi Phil,

You're passing the name of your section and your record number in the URL string, but you're also going to have to pass the fieldname of the upload field you want. In this example, we're using a request variable called fieldName, but you can change that to whatever you want.

This code first select 1 record from the requested section, then gets all the uploads stored in the requested upload field for that record:

// load records for the page called.
list($myRecords, $myMetaData) = getRecords(array(
'tableName' => "'".mysql_escape($_REQUEST['section'])."'",
'where' => "num = '".intval(@$_REQUEST['num'])."'",
'loadUploads' => false,
'allowSearch' => false,
'limit' => '1',
));
$myRecord = @$myRecords[0]; // get first record

//get uploads for the selected record
list($uploadRecords,$uploadMetaData)=getRecords(array(
'tableName' => 'uploads',
'allowSearch' => false,
'where' => "tableName='".mysql_escape(@$_REQUEST['section'])."' AND fieldName='".mysql_escape(@$_REQUEST['fieldName'])."' AND recordNum='".intval($myRecord['num'])."'",
'orderBy' => "createdTime DESC",
));

So in the end, $myRecord is your record, and $uploadRecords are all the uploads associated with that 1 record and fieldName.

I also noticed in your file that you are select record from 13 other tables, but that those records are not being used anywhere in your page. If you don't need those selections in this page, I would suggest removing them.

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

By pcolvin - January 11, 2011

Jason,

I think I may be getting closer but I'm still having some problems. If I use the example code to load the records, I get the following error.

loadSchema: tableName ''exec_comm'' contains invalid characters!


If I change the line

'tableName' => "'".mysql_escape($_REQUEST['section'])."'",


back to

'tableName' => $_REQUEST['section'],


the error goes away and the page is displayed. However, when I do get the page to display, the upload fields, which I should have mentioned I have more than one, but only need to sort one of those fields, errors as an Undefined Index. I have tried replacing the fieldName with the actual name of the upload field, but that did not seem to correct that problem.

Thanks

Phil

Re: [pcolvin] Sorting Uploads

By pcolvin - January 12, 2011

Jason,

I have pretty much everything working they way I need it to except one small thing. I have two upload fields in the section. One for meeting minutes and one for other documents. The other documents field I would like to sort ascending by the "order" field, or the drag sort order, but when I try to use that field in the orderBy line, I get the following error:

MySQL Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'order' at line 4


If I put the field name "order" inside single quotes ("'order'") the error goes away, but the uploads are not sorted. They appear in the order they are in the table.

If I take out the orderBy line all together, I then get the following error appear on the page:

Notice: Undefined index: listPageOrder in /path/to/htdocs/cmsAdmin/lib/viewer_functions.php on line 67


I'm hoping there is an easy fix to this and I'm just not seeing it. Below is the code that I'm using to load the upload records and sort them:

//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",
));

Thanks

Phil