Logic + MySQL query

scrupul0us

heres what im trying todo... i have a db table setup:

[id][month][min][max][title][found][www][img]

id - primary key
month-2 digit representation of the events month
min - start day of the event
max - end day of the event
title - name of the event
found - association tied to event
www - event website
img - img data (if any)

an example recordset would be:

1-02-01-28-Event 1-Event 1 Foundation-http://www.event1.org-BINARY DATA

in this case, Event 1 runs the entire month of february

now, heres my struggle... i have some events that run just a single week and some that are just a single day... somehow i need to pull from the DB with some logic like this:

-based on todays month, find all records
-if there is ONE event that exactly matches todays date, return that record
-if there are MORE THAN ONE events that matches todays date, return ONE random selection of those events
-if there is ONE event in which todays date falls within a WEEK span event, return that record
-if there are MORE THAN ONE events in which todays date falls within a WEEK span event, return ONE random selection of those events
-if NONE of the above are true, pick one random event that span the ENTIRE month

NOW, what would be the best way todo this? Just build a large IF structure with multiple queries or IS there a more efficient way?

I dont really see that working... if i SELECT all where month = thismonth, how would i continue processing the results? (i havent played much with mysql recordsets other than just looping them out)

any/all feedback is welcome!

leatherback

hm..

Just a thought..:

Use a date difference calculation between start date and end date to get the number of days or so?

scrupul0us

i guess im not following... perhaps an example

scrupul0us

anyone else with a good idea?

the only think i can think of doing is querying all results by the current month and then building a multi-dim array and then futz around... but if i have alot of images stored thats really gunna blow...

there has to be a somewhat graceful way to accomplish this

NogDog

Maybe....

$mo = (int) date('m');
$day = (int) date('d');
$sql = <<<EOD
SELECT *, CAST(max AS INT) - CAST(min AS INT) AS length, RAND() AS sort_num
FROM table_name
WHERE CAST(month AS INT) = $mo AND $day BETWEEN CAST(min AS INT) AND CAST(max AS INT)
ORDER BY length ASC, sort_num
LIMIT 1
EOD;
$result = mysql_query($sql) or die("Query failed ($sql): " . mysql_error());

This would give precedence to the event with the fewest number of days that overlaps today's date, and randomly select one if there is more than one event with the same number of days.

scrupul0us

thats nuts!... ill give that a try when i get home tonight and let you know how it works

seriously, thats one crazy sql statement 🙂

NogDog

Let me know if it works, I'll be curious to find out. 🙂

greenie2600

scrupul0us -

That's nothing 🙂 I would have agreed about the craziness of this SQL query before I started my current job, but I now routinely deal with queries that are pages long. You have to carefully indent and format them just to be able to read them.

This isn't entirely by choice - we've inherited a pretty messy database - but I've found that you can greatly simplify and speed up a lot of PHP scripts if you put some of the logic in the query. SQL definitely isn't as fun to write as PHP, but it's worthwhile to spend some time with the MySQL manual to learn about the various functions and control structures that are available.