Complex sort?

10 posts by 3 authors in: Forums > CMS Builder
Last Post: February 16, 2009   (RSS)

Hi Dave,

I have a table (called registration) I'd like to sort using fields I've created in each record. One of the fields is the name of a class (name) and the others, the class presentation dates, actually consists of several similar fields (list1, list2,...list50). Some of the classes have several (up to 50) dates for the same class.

What I'd like to do is list the class by date with the oldest (first) date at the top. So what the user would end up seeing is a "Schedule of Classes" with the date of the class listed on the left and the name of the class on the right. I've already figured out the table and how to list them it's just a matter of getting them to list by date, even though a record could contain more then one date.

The dates (field=list1, list2,...) is in the format "Jan 5 thru 16, 2009" and would only need to see the first date (Jan 5). An example listing would be: "Jan 5 thru 16, 2009 /30 Firearms Instructor" The /30 is the number of 'seats' available and is part of the date field and if that would cause a problem, I can take it out (with some another code I found).

I'm pretty sure the code for the sorting (sql) probably goes in the top portion under the 'where' => but do not know how I would loop through the dates to put them in the proper order order.

You helped me once before looping through the dates:

...
<?php foreach (range(1,50) as $num): ?>
<?php if ([url "mailto:!@$registrationRecord["list$num"]!@$registrationRecord["list$num[/url]"]) { break; } ?>
<?php echo $registrationRecord["list$num"] ?><br/>
<?php endforeach ?>
...

which worked great for listing all the dates by record but now I'm lost. I checked the php web site under 'sort' but just got more confused.

Any suggestions?

TIA!

Eric

Re: [Dave] Complex sort?

Hi Dave,

Geez, that was quick! I guess you live in front of a computer too [:)]

Here's a url to what I've come up with so far: http://alamedacountysheriff.org/_rtc2009/classes/registrationList.php

A result would look something like this: http://alamedacountysheriff.org/_rtc2009/scheduleDetail.php?viewer.php?page=1

I'm trying to get away from having the user enter the information, as in the second example.

All the dates entered under each record are in chronological order (the starting date in some cases) and all are entered as text fields. The first available date would always be "list1". As the user updates with newer dates, they could either add on to the existing dates or delete the oldest and replace with newer; either way, the first date would always be "list1".

If this issue goes into the "custom programming" area, please let me know. I have no problem utilizing and paying for your great talent!

Let me know if you need any other information.

Eric

Re: [eduran582] Complex sort?

By Dave - February 9, 2009

Hi Eric,

Thanks for the link. Are the dates just entered in regular text fields? If so it's hard to sort on them because PHP/MySQL doesn't understand them as dates unless they're in a specific date field.

What about sorting them with "dragSortOrder" and having newly added records added to the top? That way new records always show first and the user can change the order of records if needed by dragging them around?

Or another option, have a date field that is used for sorting. So "Start Dating" date could be stored as a date and we could sort on that.

Would either of those work?
Dave Edis - Senior Developer
interactivetools.com

Re: [Dave] Complex sort?

Hi Dave,

The date/available seats field is a text field. The "seats available" would be changed by the person confirming the registration.

I was thinking the same thing; have a separate 'date' field and use the sort based on that field. I'll figure out something for the "seats available" part. If that were the case (separate date field), when it goes through and creates the "list", would the rows containing the dates and other information end up accending (oldest date on top)?

My goal is to have that "Schedule of Classes" automated based on the information the manager adds to each class. Just getting it in order is the problem. I'll add the date field and see what happens [tongue]

As usual, thanks for all the help!

Eric

Re: [eduran582] Complex sort?

By Dave - February 9, 2009

Hi Eric,

Yes, if you add a date field and then sort by that like this:

'orderBy' => " `myDateField` DESC ",

Then the newest records will show up first. You can also set the sort order under: Admin > Section Editors
Dave Edis - Senior Developer
interactivetools.com

Re: [Dave] Complex sort?

Thanks, Dave.

I think I've run into a snag; there are several dates (at least more then one) for most of the classes. So if Iadd several date fields, I'm not sure it will work. I was going to try Date-1 up to Date-50 (date fields) replacing the existing text fields that contain a date. With that many possible dates for each class, how would I set the 'orderBy' or even the Admin Section editor? Would it be something like 'orderBy' => " Date-1, Date-2, Date-3...Date-50 ", ?

[crazy]One other thought.... maybe I'll just create another (numeric?) field and have the manager convert each date and THEN order it on that field (similar to above)? That way I wouldn't have to change all those dates.

Thanks. Hope you like challenges [cool]

Eric

Re: [eduran582] Complex sort?

By Dave - February 10, 2009

Hi Eric,

Yea, I'd recommend creating one field for sorting. Or using the "dragSortOrder" field, or a single date field and sort on that.

You can set the sorting for the admin under: Admin > Section Editors > Sorting (tab)

Hope that helps!
Dave Edis - Senior Developer
interactivetools.com

Re: [Dave] Complex sort?

By eduran582 - February 13, 2009 - edited: February 16, 2009

Hi Dave,

I'm still working on a way to display the schedule of classes and have done so (using your suggestion) by creating a new Section called "Schedule of Classes" (class_dates). The 'manager' will have to add each class name and date, in date format, as well as a 'display date' of the class (in some cases many times). It was the only way I could get the class dates to list properly. You can see this at:

http://alamedacountysheriff.org/_rtc2009/classes/scheduleList.php

What I need to do now is figure out a way to show the dates, using the 'display date' textfield (display_date) in a box at the bottom of the class discription page. Now that I have all the dates in their own table (class_dates), I figured I could compare the class name (name) in the class table (evoc, in this case) with the name in the class_dates table (also called 'name') and if it is the same, list the date; if not, skip and go to the next record in the table until the end of the table is reached.

This is what I'm using and all it does is give me a list of the same [first] date listed in the class_dates table:


<?php foreach ($class_datesRecords as $record): ?>

<?php if ($record['name'] == $evocRecord['name']) ?>
<?php echo $class_datesRecord['display_date'] ?><br/>

<?php endforeach ?>

You can see that here:

http://alamedacountysheriff.org/_rtc2009/classes/evoc/evocDetail.php?1-Day-EVOC-Basic-Skills-Update-5

It isn't going to the next record to compare 'name' but seems to loop through the entire table. The number of dates listed (all the same) agree with the number of the same name class; just not the correct date for that record. I looked in php.net at 'do-while' and 'while' thinking that would help me loop through the table but I just couldn't get any of the examples to work with what I have.

Any suggestions?

TIA!

Eric

[:)] UPDATE:

I finally figured out how to get the dates to list the correct way (link #2 above)! It was a case sensitive issue; some of the 'name' was all caps so I used the strtoupper function:

<?php if (strtoupper($record['name']) == strtoupper($evocRecord['name'])): ?>
<?php echo $record['display_date'] ?>&nbsp;/<?php echo $record['seats'] ?><br/>
<?php endif ?>


Now if I can figure out how to use a value ('name') to loop through the records of a table to retrieve only those records that equal the 'name' value. The record taken from the table contains the 'name' value and I want to use that value to loop through the rest of the records (from the top) and get only those records that equal the 'name' value. I tried initializing it first ($staticrec = $class_datesRecord['name'];) at the top and then used the following to loop through the table:

<?php foreach ($class_datesRecords as $staticrec => $record): ?>
<?php echo $record['display_date'] ?><br/>
<?php endforeach ?>

But it only showed one date and there are several for the same 'name'.

Any suggestions?

Eric


[:)] LAST UPDATE:

Got it working! It was (I think) a matter of changing from a multi to a category type section (don't know why I chose multi to start with). If you click on the links above you'll see the dates for that particular class are now listed properly.

Re: [eduran582] Complex sort?

By ross - February 16, 2009

Hi Eric

That's great! I am glad you got that working. I'll make sure Dave knows :). Feel free to post anymore questions you have.
-----------------------------------------------------------
Cheers,
Ross Fairbairn - Consulting
consulting@interactivetools.com

Hire me! Save time by getting our experts to help with your project.
Template changes, advanced features, full integration, whatever you
need. Whether you need one hour or fifty, get it done fast with
Priority Consulting: http://www.interactivetools.com/consulting/