If or where statement from another table

Sorted out the date issue, but not the other part...

// load records from 'calendar' that have a date greater than 1 second before midnight today.
list($calendarRecords, $calendarMetaData) = getRecords(array(
'tableName' => 'calendar',
'where' => "'date' > '$today'",
'loadUploads' => true,
'allowSearch' => false,
));

Hi,

From your code example so far, I can see the fields in the Calendar section editor. Is the other section editor just for the course names?

What is the end result you are after? The course dates grouped under the course name?

Let me know and then I can help put together some code examples.

Thanks!

Hi Damon

I have another list called Calendar, which just lists all of the courses and their specific date.  Course A might have 3 different dates, Course B 6 different dates, etc.  And new dates will be added as the calendar year progresses.  What I want to do is list these dates on the Course A or Course B, etc detailed pages, and have the flexibility to order by first forthcoming date, next sequential date.  An example of a course details (eg. Course A) is http://cp124.ezyreg.com/~coco6119/codelime_courses2.php?1 

// load records from 'calendar'
list($calendarRecords, $calendarMetaData) = getRecords(array(
'tableName' => 'calendar',
'loadUploads' => true,
'allowSearch' => false,
));

and the two fields are 

Record Number: <?php echo htmlencode($record['num']) ?><br/>
Course (value): <?php echo $record['title'] ?><br/>
Course (label): <?php echo $record['title:label'] ?><br/>
Date: <?php echo date("D, M jS, Y g:i:s a", strtotime($record['date'])) ?><br/><!-- For date formatting codes see: http://www.php.net/date -->

It's sort of a fix in the absence of a calendar type plugin.  The calendar they have at https://www.phpjabbers.com/event-booking-calendar/ is a great piece of calendar software, but it doesn't plug in with CMS Builder and I can't extract just a string of single course dates for a specific course.  So this is the compromise, combined with www.machform.com as the form/payment editor.

Any help you could provide with the where/if statement would be wonderful.

Thanks in advance.

Carole

You probably have, in your calendar table, a field that cross-references the course table.  For example, maybe you have a "coursenum" field that uses the "num" from the course table as it's value.

Assuming you have your current course record in an array called $courseRecord, and assuming I understand your question properly, you can add where => "coursenum={$courseRecord['num']}" to your getRecords command.

Also, regarding your dates, you can add publishDate and removeDate fields to your section and then the getRecords command will not pull records that haven't been published yet or records that have expired (past their remove date) so you don't have manually expire them.  However, you can also use the way you are doing it.  Either way works fine.

Thanks, but that doesn't work.  It's giving me a 500 Server Error if I put any variation of that code in the line.

Without knowing the exact setup of your tables, it's hard to say, but I use this type of thing all the time, so I'm guessing I just don't know how your tables are setup.

Sorry I didn't understand your environment correctly.  If you share the schema files in your post, it may assist in giving you an answer that will work.

Hi Gary

The tables are set up in 2 different sections.  I hope this is the schema that is required?

1.  COURSE DETAIL - it's on this page where I would like to list the dates

<h1>Courses - Detail Page Viewer</h1>
Record Number: <?php echo htmlencode($coursesRecord['num']) ?><br/>
Course Name: <?php echo htmlencode($coursesRecord['title']) ?><br/>
Duration: <?php echo htmlencode($coursesRecord['duration']) ?><br/>
Location: <?php echo htmlencode($coursesRecord['location']) ?><br/>
Cost: <?php echo htmlencode($coursesRecord['cost']) ?><br/>
_link : <a href="<?php echo $coursesRecord['_link'] ?>"><?php echo $coursesRecord['_link'] ?></a><br/>

2.  CALENDAR LIST - this table has the dates for all of the courses.  The Course value below is a drop down pulled from Courses table above, using the title as the idenfier

<?php foreach ($calendarRecords as $record): ?>
Record Number: <?php echo htmlencode($record['num']) ?><br/>
Course (value): <?php echo $record['title'] ?><br/>
Course (label): <?php echo $record['title:label'] ?><br/>
Date: <?php echo date("D, M jS, Y g:i:s a", strtotime($record['date'])) ?><br/><!-- For date formatting codes see: http://www.php.net/date -->
_link : <a href="<?php echo $record['_link'] ?>"><?php echo $record['_link'] ?></a><br/>
<hr/>
<?php endforeach ?>

<?php if (!$calendarRecords): ?>
No records were found!<br/><br/>
<?php endif ?>

Thanks so much.

Carole

I was looking for the actual schema files, not the HTML files, but...

It looks like your course in CALENDAR LIST is the field 'title' which is probably an string value, not a number, so you'd need to do something with 'title' in order to get the course info.

Possibly, your where clause could have (sometimes, to make things a bit more readable, I'll create the "wherevalue" variable separately BEFORE the getRecords statement):

$whereValue = "title='{$courseRecord['title']}'";

Note: I like to use the bracket notation - I find it a bit more readable.  This is equivalent to:

$whereValue = "title='" . $courseRecord['title'] . "'";

Then inside the getRecords array...

'where' => $whereValue,

So, my question is in looking at your code below, is there any difference between

Course (value): <?php echo $record['title'] ?><br/>
Course (label): <?php echo $record['title:label'] ?><br/>

or are they displaying the same value?

If they are different values, your "where" clause needs to reference whatever is displayed by $record['title'] from the $courseRecord array.

If you are getting 500 errors, it means you have an error in your syntax and your server can't go on.

Since you are also checking against the date, you need to have a compound where clause.  In your existing code, you show:

list($calendarRecords, $calendarMetaData) = getRecords(array(
'tableName' => 'calendar',
'where' => "'date' > '$today'", 
'loadUploads' => true,
'allowSearch' => false,
));

by adding "AND" to the where clause with the title, you can filter on title as well.

Assuming you get $today by some means such as:

$today = date("Y-m-d H:i:s");

You could add the title clause to the where statement

$filterTitle = "`title`='" . $courseRecord['title'] . "'";
$today = date("Y-m-d H:i:s");
$whereValue = "`date` > '$today' AND $filterTitle;

list($calendarRecords, $calendarMetaData) = getRecords(array(
 'tableName' => 'calendar',
 'where' => $whereClause, 
 'loadUploads' => true,
 'allowSearch' => false,
 ));


Again, keep in mind this is speculation without seeing the actual schema. 

Hi Gary

Thanks for providing some further insight.  I finally got the code to work using:

// load records from 'calendar'
$whereValue = "title='{$coursesRecord['title']}'";

list($calendarRecords, $calendarMetaData) = getRecords(array(

'tableName' => 'calendar',
'where' => $whereValue,
'loadUploads' => true,
'allowSearch' => false,
));

The date / today field code doesn't work as it just lists everything, so I'll do this part manually.

Thanks again for your help.

Regards

Carole