[Mr. Crane]

Okay, I'm stumped. I'm getting this error from the query below: #1060 - Duplicate column name 'event_min'. What am I doing wrong??

Code:  select

Highlight Mode: (MySQL)
SELECT event_id, event_start, event_stop, event_min, event_max
FROM (
 
SELECT a.event_id event_id, b.id date_id, b.res res, a.event_min, a.event_max, b.res + INTERVAL 
a.event_start_imon
MONTH + INTERVAL a.event_start_iday
DAY + INTERVAL a.event_start_ihr HOUR + INTERVAL a.event_start_imin
MINUTE event_start, b.res + INTERVAL a.event_stop_imon
MONTH + INTERVAL a.event_stop_iday
DAY + INTERVAL a.event_stop_ihr HOUR + INTERVAL a.event_stop_imin
MINUTE event_stop, event_min, event_max
FROM trk_calendar a, trk_period b
WHERE a.event_id =90
AND b.id
IN ( 7683, 7684 )
)a
WHERE event_start < '2011-01-31' + INTERVAL 1
DAY AND event_stop > '2011-01-01' - INTERVAL 1
DAY AND event_start >= event_min
AND event_stop <= event_max
 

[UnrealEd]

I think the error is caused by the subquery: in it you select the field a.event_min, but also the field event_min. However, this shouldn't throw an error as the subquery should return a table with an a.event_min column and an event_min column.

The same goes for the event_max field.

If that isn't causing the error, then I have no idea what is

[Max]

Yep, I gave up on it. This was some SQL written by a friend who's no longer around. I went and rewrote the whole mess in about 1/5 as many lines and replaced the whole thing. I have NO idea why he made it so damn complicated.

You should see some of the SQL this guy wrote...it's super odd and convoluted...he does a complex query, then uses that result in a variable as part of another query, and so on. And not a single line is commented. Here's an example, this is one of his simpler functions below. Caution: looking at this may make your eyes bleed.

Code:  select

Highlight Mode: (MySQL)
    $event_start = calculate_date('b.res','a.event_start_imon','a.event_start_iday','a.event_start_ihr','a.event_start_imin');
    $event_stop  = calculate_date('b.res','a.event_stop_imon','a.event_stop_iday','a.event_stop_ihr','a.event_stop_imin');
 
    $qi = 0;
    $qs0 = array();
    foreach ($events as $event_id => $event) {
 
        $start    = $event['start'];
        $stop     = $event['stop'];
        $date_ids = $event['ids'];
 
 
        $date_id_list = join(',',$date_ids);
        $qs0[$qi++] = "
            select a.event_id event_id, b.id date_id,b.res res, a.event_min, a.event_max
            , $event_start event_start
            , $event_stop  event_stop
            , event_min
            , event_max
            from  pdc_calendar a, pdc_period b
            where a.event_id = $event_id
            and b.id in ($date_id_list)
        ";
    }
 
    $qs1 = join("\nunion\n",$qs0);
 
#D print "<pre>\nget_month_events:\n$qs1\n</pre>\n";
 
    $qs2 = "
        select event_id, event_start, event_stop, event_min, event_max from (
            $qs1
        ) a
        where event_start < '$year-$month-31' + interval 1 day 
        and event_stop > '$year-$month-01' - interval 1 day
        and event_start >= event_min 
        and event_stop  <= event_max
    ";
 
    $qr1 = mysql_query($qs2);
 
#D print "<pre>\n$qs2\n</pre>\n";
 
    if (count($qs0) == 0) {
        return $rtn;
    }
 
    # printf("min(%s) max(%s)\n",$start,$stop);
    $i = 0;
    $qa3 = array();
    while ($row = mysql_fetch_assoc($qr1)) {
        $qa3[$i++] = sprintf("
            select a.res, extract(day from a.res) event_day, '%s' event_start, '%s' event_stop
            , b.is_public, b.event_title, b.event_type, b.event_location, b.event_text, b.event_flags
            , b.form_data, b.hr_dates, b.pplan_event 
            , b.event_id, b.event_color
            from pdc_period a, pdc_calendar b
            where a.res between '%s' and '%s'
            and event_id = %d
        ",$row['event_start'],$row['event_stop'],$row['event_start'],$row['event_stop'],$row['event_id']
        );
    }
 
    if (count($qa3) == 0) {
        return $rtn;
    }
 
    $qs3 = sprintf("select * from (%s) a where res between '$year-$month-01' and '$year-$month-31'\n",join("\nunion\n",$qa3));
 
#D print "\n<pre>\n$qs3\n</pre>\n>";
 
    $qr3 = mysql_query($qs3);
 
    while ($row = mysql_fetch_assoc($qr3)) {
        if (! is_array($rtn[$row['event_day']])) {
            $rtn[$row['event_day']] = array();
        }
 

[UnrealEd]

Crazyness!

I totally lost it, when I saw there was more to come after this line:

Code:  select

Highlight Mode: (PHP)
$qs1 = join("\nunion\n",$qs0);
 

I mean, come on, a union of queries like the one Mr. Crane posted. You have to be a complete idiot or a genius (and we all know there's a small border between ) to write such queries

[Max]

I totally lost it, when I saw there was more to come after this line:

Code:  select

Highlight Mode: (PHP)
$qs1 = join("\nunion\n",$qs0);
 

Lol, I asked MC for help on this first (the code he posted above is part of the same thing) and he was baffled too. His actual comment was "Holy ****!" Then we got to the second part and it was Tylenol Time. Convoluted code is one thing, a complete lack of comments is another.

Concatenating a query like that is...creative. But there's "creative" and there's "good", and the two don't always coincide.

FYI, the guy that wrote the original code (both of those blobs of madness) is a long-time perl hacker who, the last we heard, is working at Amazon.