Multiple Record List Sorted by Checkboxes - problems
8 posts by 3 authors in: Forums > CMS Builder
Last Post: March 29, 2018 (RSS)
By csdesign - March 27, 2018
Hello!
I won't even admit how long I've been working on this with no success.
I'm creating a Schedule of Events List for an annual 7 day event. Some of the events run across multiple day. My end goal is to have a list that looks like this but the user only has to enter each event once - and then just check the day(s) that event falls on. For example, the user enters all the info for Vendor Shopping once - and it shows under each day that it's available. z
If nothing is checked, the event will be hidden.
________
DAY 1
Training Clinic 9-5... more info (detail page)
Vendor Shopping Available ... more info (detail page)
DAY 2
Training Clinic 9-5... more info (detail page)
Vendor Shopping Available ... more info (detail page)
DAY 3
Mounted Shooting ... more info (detail page)
Vendor Shopping Available ... more info (detail page)
DAY 4
Team Sorting ... more info (detail page)
Vendor Shopping Available ... more info (detail page)
and so on for 7 days.
_________
All of the events are entered and I have setup a list of check boxes (day1, day2, day3, day4, day5, day6, day7) to designate the days for each event. (see screenshot of event schedule list with event "Title" and "Day of Event" showing)
Here is my online test page and as you can see, none of the events are showing for the 3 days that I setup to test.
http://www.codyhorsesale.com/saddlemule/eventtest.php
The test page code is this - for each of the 7 days.
<?php foreach ($event_schedule2Records as $record): ?>
<?php if (@$_REQUEST['day_of_event:labels'] == 'day1'): ?>
Record Number: <?php echo htmlencode($record['num']) ?><br/>
Day of Event (values): <?php echo join(', ', $record['day_of_event:values']); ?><br/>
Day of Event (labels): <?php echo join(', ', $record['day_of_event:labels']); ?><br/>
Calendar Title: <?php echo htmlencode($record['calendar_title']) ?><br/>
Event Dates: <?php echo htmlencode($record['event_dates']) ?><br/>
Event Time: <?php echo htmlencode($record['event_time']) ?><br/>
Event Blurb: <?php echo htmlencode($record['event_blurb']) ?><br/>
Title: <?php echo htmlencode($record['title']) ?><br/>
Event Start: <?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 -->
City: <?php echo htmlencode($record['city']) ?><br/>
State: <?php echo htmlencode($record['state']) ?><br/>
Content: <?php echo $record['content']; ?><br/>
<?php endif ?>
<?php endforeach ?>
I'm assuming part of the issue is 'where'. I have tried multiple variations so I'm just stopping here.
// load records from 'event_schedule2'
list($event_schedule2Records, $event_schedule2MetaData) = getRecords(array(
'tableName' => 'event_schedule2',
'where' => "day_of_event LIKE '%\tday1\t%'",
'loadUploads' => true,
'allowSearch' => false,
));
I'm hoping it's just something super obvious that I'm missing. I'm attaching my php file also because I have to get this up and working by Wednesday.
Thank you for your help!! Tina
By Djulia - March 28, 2018
Hello,
You can try this :
// load records from 'event_schedule2'
list($event_schedule2Records, $event_schedule2MetaData) = getRecords(array(
'tableName' => 'event_schedule2',
'where' => "CONCAT(',', `day_of_event`, ',') LIKE '%,day1,%'",
'loadUploads' => true,
'allowSearch' => false,
));
Djulia
By csdesign - March 28, 2018
Thanks for the reply! I wasn't able to get this to solve it but I did get a little further.
TEST 5:
The result was more than just one event showing per day but not all events are showing. http://www.codyhorsesale.com/saddlemule/eventtest5.php
Number of events per day:
mon - 2 events (# of events actually showing = 1)
tue - 3 events (# of events actually showing = 3)
wed - 3 events (# of events actually showing = 2)
thu - 3 events (# of events actually showing =2)
fri - 3 events (# of events actually showing = 1)
sat - 5 events (# of events actually showing = 1)
sun - 2 events (# of events actually showing = 1)
I tried this - but then none of the events showed up
If I used WHERE (CONCAT(',', `day_of_event`, ',') LIKE '%,fri,%')
repeated this for mon, tues only.
// load records from 'event_schedule2'
list($event_schedule2Records, $event_schedule2MetaData) = getRecords(array(
'tableName' => 'event_schedule2',
'where' => "CONCAT(`day_of_event` LIKE '%\tmon\t%')",
'loadUploads' => true,
'allowSearch' => false,
'debugSql' => true,
));
// load records from 'event_schedule2'
list($event_schedule2Records, $event_schedule2MetaData) = getRecords(array(
'tableName' => 'event_schedule2',
'where' => "CONCAT(`day_of_event` LIKE '%\ttue\t%')",
'loadUploads' => true,
'allowSearch' => false,
'debugSql' => true,
));
Repeated this for each day of the week
<!--/*DAY 1*/-->
<div class="list-title"><h2><?php echo htmlencode($event_schedule_daysRecord['1st_day_of_week']) ?> <?php echo htmlencode($event_schedule_daysRecord['event_month']) ?> <?php echo htmlencode($event_schedule_daysRecord['day_1_date']) ?></h2></div>
<?php foreach ($event_schedule2Records as $record): ?>
<?php if (strpos($record['day_of_event'],"\tmon\t") !== false) : ?>
Day of Event (labels): <?php echo join(', ', $record['day_of_event:labels']); ?><br/>
Title: <?php echo htmlencode($record['title']) ?><br/>
<hr>
<?php endif ?>
<?php endforeach ?>
ETC... for tue, wed, thu, fri, sat, sun
<?php if (!$event_schedule2Records): ?>
No records were found!<br/><br/>
<?php endif ?>
this is the really odd part. On test #7: http://www.codyhorsesale.com/saddlemule/eventtest7.php
The result was 1 event for every day except for the last day which had 2 for sunday, which is correct. I'm SO confused.
Each day is setup as the following: ( mon, tue, wed, thu, fri, sat, sun)
/// load records from 'event_schedule2'
list($event_schedule2Records, $event_schedule2MetaData) = getRecords(array(
'tableName' => 'event_schedule2',
'where' => "(`day_of_event` LIKE '%\tmon\t%')",
'loadUploads' => true,
'allowSearch' => false,
'debugSql' => true,
));
And this is repeated for each day:
<?php foreach ($event_schedule2Records as $record): ?>
<?php if (strpos($record['day_of_event'],"\tmon\t") !== false) : ?>
Day of Event (labels): <?php echo join(', ', $record['day_of_event:labels']); ?><br/>
Title: <?php echo htmlencode($record['title']) ?><br/>
<hr>
<?php endif ?>
<?php endforeach ?>
ETC... for tue, wed, thu, fri, sat, sun
<?php if (!$event_schedule2Records): ?>
No records were found!<br/><br/>
<?php endif ?>
Not seeing your data, it's a bit hard to say. The first thing, I would not check for tab on both sides...just check on one side or the other. If I recall, the data is stored as value1\tvalue2\tvalue3\t, so checking at the end only will be safer.
Can you share one of your day_of_event fields with multiple selections and one with only a single selection so we can see how it's actually setup? You should be able to get this from phpMyAdmin or the Developer Console.
Also, you are going to be using every record in the table at some point, so you might want to avoid going back to the database over and over. Just get all the records and then do your foreach 7 different times selecting the records that match. Something like this:
list($event_schedule2Records, $event_schedule2MetaData) = getRecords(array(
'tableName' => 'event_schedule2',
'loadUploads' => true,
'allowSearch' => false,
'debugSql' => true,
));
for ($i=0; $i<7; $i++) {
<get day of week from date function>
<display the day of week and any other data you wish to display >
foreach ($event_schedule2Records as $record) {
if (strpos($record['day_of_event'],"<day>\t") !== false) {
<do for this record on this day>
}
}
}
Obviously, replace <day> with "mon" or "tue" or whatever.
Keep in mind, I tend to use the braces rather than the : and endforeach or : and endif notation. Do whichever you are more comfortable with.
Gary.
By csdesign - March 29, 2018
Thanks Gary! So, it took a little back and forth, but when I removed the 'where' statement it worked!
Just posting my final results in case anyone else has the same issue:
(partial screenshot included because I will be deleting my test page)
// load records from 'event_schedule2'
list($event_schedule2Records, $event_schedule2MetaData) = getRecords(array(
'tableName' => 'event_schedule2',
'loadUploads' => true,
'allowSearch' => false,
));
repeated for each checkbox (day of the week)
<?php foreach ($event_schedule2Records as $record): ?>
<?php if (strpos($record['day_of_event'],"\tmon\t") !== false) : ?>
Day of Event (labels): <?php echo join(', ', $record['day_of_event:labels']); ?><br/>
Title: <?php echo htmlencode($record['title']) ?><br/>
<hr>
<?php endif ?>
<?php endforeach ?>
Thanks!!
