UBB.Developers Forums Community Development Programming PHP and Server-side Scripting Using a loop to group DB data by date

Some background:

I am working on creating a script that users can utilize for organizing a competition schedule. Each day there are six competitions around the country. Each person has to pick which competitions they will be going to.

I have created a database that holds the competition data. Here is a screenie from a portion of it:
[]http://www.drumlines.org/upload/schedule.gif[/]

The form I am creating for members, so far, looks like this:
[]http://www.drumlines.org/upload/form.gif[/]

Each day's dropdown lists those six competition locations from that day, as well as the default "None" which you saw in the screenshot. Here is a snippet of the code I am using to create this:
[]http://www.drumlines.org/upload/script.gif[/]

As you can see, I am having to repeat the section of code while changing the date and making variations of the $complist variable. I have been struggling to create a loop where I would not have to keep repeating the same section of code. I am still learning PHP, so don't point and laugh too hard.

Any heroes out there?

To show you I really AM trying, I did manage to work out a script that lists all Drum Corps in the database:
http://www.drumlines.org/threads/showcorps.php

I'm still tinkering with this. The script has gone through many incarnations so far, but here is the current:
[]http://www.drumlines.org/upload/script2.gif[/]

Along with my template file, this outputs:
[]http://www.drumlines.org/upload/form2.gif[/]

As you can see, the date variable is not getting set the way I want it to be. It seems the dates overwrite the variable as they pass through and thus I end up with the last date.

Any input, or direction to a starter's PHP guide, would be appreciated. I have been reading though the two sites suggested in the forum instructions here.

Can you attach the whole script and describe what result you want to acchieve?

I would like to have a page that displays each day during the season with the choices of competitions from that day in a drop down, similar to the screenshots I have posted. When the user fills out the form and submits it, it will enter their schedule information into the database. As I have it now, I am having to copy lots of sections of code. I have added a $week 'get' variable so that I only have to have 7 copies of the code, but this would require the user to enter each week individually.

Something else new that has come up. There is a 6 corps maximum per competition for division 1 corps and a 4 corps maximum per competition for division 2 corps. I would like to get the script to query for full competitions and remove those selections from the dropdowns, but I cannot seem to get that either. Even if I have to settle for a listing of full competitions, I guess that would be better than nothing. I've been hacking away at this day and night for the past couple of weeks. It's a good thing I have a lot of down-time here at work.

Sorry about rambling. Thanks for the offer to look over the code.

Here's the latest layout I'm using for the database...

Ok, first thing:
Wouldn't it be easier to automatically get the week and day number by using one of the php date functions?
Using some kind of calendar functionality would be best, I suppose.
This way you can simply define the start date and end date of season. Using a iterator, you could render dropdown boxes for every day without the need to supply additional information.

If that sounds to complicated, using a function to build the right query would help to avoid copying the code

function buildQuery ($week = false, $day = false) {
if ($week && $day) {
$query = "
SELECT Date
FROM fdc_Schedule
WHERE Week = '$week'
AND Day = '$day'
";
}
else {
print "error not enough params supplied";
exit;
}

return $query
}


using the function:
$query = buildQuery($week, $day)
$sth = $dbh -> do_query($query);

while (list ($location1) = $dbh -> fetch_array($sth)) {
$loptions1 .= "<option value="$location1">$location1</option>";

Thanks for taking a look at it! I'm going over the code and suggestions you posted trying to figure it all out to see if and where I can use it. Thanks again mate!

I'm in a crunch for time, and I'm not acquainted enough with PHP to figure our what you were suggesting, so I went ahead and went with something based on what I had before. Thanks for looking it over though!