finding nearest month

bpgillett

hi. i'm building a concert calendar. the user selects a month from a dynamic drop down menu; the concerts from that month are then displayed. currently, the selected item in the menu upon loading the url is the first month (ordered by date) found in the table for a defined year. i would prefer the selected item to rather be the present month...if the present month is not found in table, then the selected item would be the next chronological month found in the table. i'm having trouble writing this loop. the idea would be something like:

//define $q_month as current month
$present_month = getdate();
$q_month = $present_month["month"];

continued on next thread...

bpgillett

//then query table for $q_month
$result = mysql_query("SELECT DISTINCT date_format(date,'%M') AS month_formatted FROM concert_calendar WHERE date_format(date,'%M')='$q_month'",$db);

//if $q_month is found then set $month
if ($myrow = mysql_fetch_array($result)) {
$month = $myrow["month_formatted"];
}

continued on next thread...

bpgillett

if not found then start loop

set a $counter = 1

LOOP:
check table for current month plus $counter
if found, set $month and exit loop
otherwise, increment counter by 1
return to brginning of loop

thanks,

brian

jmh

Hi Brian

The problem is you are trying to do arithmetic with strings, which is pretty hard. You could try this instead:

Use an array of month names (e.g. $months = array('', 'January', 'February', ...)).

The user submits the form which returns the month (as above). You look through your array to find out the month number, then look through MySQL for that month.

If you find it, great your script will end. If not, loop through decrementing the month each time until it reaches 0 or you find the month with any entries.

$inmonth = array_keys($months, $_POST["month"]);  //could be date("F")
$inmonth = $inmonth[0];
$sql = "SELECT DISTINCT date_format(enter,'%m') AS month_formatted ";
$sql .= "FROM dates ";
$sql .= "WHERE date_format(enter,'%m') = ".sprintf("%02d", $inmonth);

//if $q_month is found then set $month
list($mymonth) = mysql_fetch_row(mysql_query($sql));
while (($mymonth != $inmonth) && ($inmonth > 0)) {
  $inmonth --;
  $sql = "SELECT DISTINCT date_format(enter,'%m') AS month_formatted ";
  $sql .= "FROM dates ";
  $sql .= "WHERE date_format(enter,'%m') = ".sprintf("%02d", $inmonth);
  list($mymonth) = mysql_fetch_row(mysql_query($sql));
}
if ($mymonth == $inmonth) {
  echo "Date found in $mymonth";
} else {
  echo 'There were no dates on or before '.$_POST["month"];
}

Hope that helps. 😃

Jon

bpgillett

thanks for the solution. it's a big help.

--brian