Php output differnt than MSSQL

[tiptap]

Hey, I have a really confusing problem here.

I'm using MSSQL & PHP. I have created a querry in MSSQL that basically shows a list of events and shows them in date order. I have written a bit of code into the mssql statement that says 'If the current date is the friday before the event date, and the event date is on a saturday or sunday, then dont show that event'

when I run that code in mssql it returns the correct results - when its firday the next event it shows is a monday (as it should).

However when i then run this querry in php it does not show the desired results.

For example when php ran this on saturday it showed events for saturday & sunday (when it should show monday onwards). and when running it today it shows events for tuesday (when it should be showing events for monday as well)

My select statement is quite massive so appologies if its confusing

SELECT DISTINCT 
                      dbo.events.showPrice, dbo.events.eventID, dbo.events.categoryID, dbo.events.eventName, dbo.events.eventDetails, dbo.events.eventLive, 
                      dbo.eventCategories.categoryName, eventCategories_1.categoryName AS categoryParent, dbo.events.enabled, tickets_1.ticketStartDate, 
                      MIN(tickets_1.ticketPrice) AS priceFrom, dbo.venues.venueID, dbo.venues.venueName, dbo.venues.venueAddress1, dbo.venues.venueCity, 
                      dbo.venues.venueCounty, dbo.texts.text, '1' AS test,
                          (SELECT     MIN(ticketStartDate) AS minStart
                            FROM          dbo.tickets
                            WHERE      (eventID = 184) AND (ticketStartDate > CASE WHEN Datepart(dw, getdate()) <= 6 AND ticketEndDate > getdate() AND 
                                                   ticketEndDate < DATEADD(DAY, 2, GETDATE()) THEN getDate() ELSE DATEADD(DAY, 2, GETDATE()) END)) AS minStart,
                          (SELECT     MAX(ticketStartDate) AS maxEnd
                            FROM          dbo.tickets AS tickets_2
                            WHERE      (eventID = 184) AND (ticketStartDate > GETDATE())) AS maxEnd
FROM         dbo.events INNER JOIN
                      dbo.eventCategories ON dbo.events.categoryID = dbo.eventCategories.categoryID LEFT OUTER JOIN
                      dbo.eventCategories AS eventCategories_1 ON dbo.eventCategories.categoryParent = eventCategories_1.categoryID INNER JOIN
                      dbo.tickets AS tickets_1 ON dbo.events.eventID = tickets_1.eventID LEFT OUTER JOIN
                      dbo.venues ON tickets_1.venueID = dbo.venues.venueID LEFT OUTER JOIN
                      dbo.texts ON tickets_1.textsID = dbo.texts.textsID
WHERE     (tickets_1.ticketEndDate > CASE WHEN Datepart(dw, getdate()) <= 6 AND tickets_1.ticketEndDate > getdate() AND 
                      tickets_1.ticketEndDate < DATEADD(DAY, 2, GETDATE()) THEN getDate() ELSE DATEADD(DAY, 2, GETDATE()) END)
GROUP BY dbo.events.eventID, dbo.events.categoryID, dbo.events.eventName, dbo.events.eventDetails, dbo.events.eventLive, dbo.events.showPrice, 
                      dbo.eventCategories.categoryName, eventCategories_1.categoryName, tickets_1.ticketEndDate, dbo.events.enabled, tickets_1.ticketStartDate, 
                      dbo.venues.venueName, dbo.venues.venueID, dbo.venues.venueAddress1, dbo.venues.venueCity, dbo.venues.venueCounty, dbo.texts.text
HAVING      (dbo.events.eventID = 184) AND (dbo.events.enabled = 1) AND (MONTH(tickets_1.ticketStartDate) = MONTH(GETDATE())) AND 
                      (YEAR(tickets_1.ticketStartDate) = YEAR(GETDATE()))
 

My initial thinking is maybe the date is wrong in php? iis? or mssql?

What do you think?

Thanks so much in advance

[Atli]

Hi.

I'm sorry to say that I can't really spot a problem with that.
The query you posted contains no variables passed into it by PHP, which pretty much eliminates PHP as the problem.

It doesn't sound likely that your HTTP server (IIS I assume?) is the reason either, as it shouldn't have anything to do with the database connection.

Which just leaves the database connection itself.

Could you show us the code you use to connect to the database and execute the query?
Perhaps we can spot something out of the ordinary.

[code green]

Hi Atli. This had me stumped also and 75% of my DB stuff is mssql.
The only time I have had a similar effect is across different servers and timezones without realising a few hours difference round midnight was changing the date

[tiptap]

Thanks for the help so far.

Below is the code i run;

$eventsDetail = $detail->eventsGetTheatreDates($itemID,0,0);
 
print_r($eventsDetail);
 
 

which calls

public function eventsGetTheatreDates($itemID,$month,$year){
 
            global $db;
 
            $rs = $db->databaseQuery('exec eventsGetTheatreDates '.$itemID.','.$month.','.$year);
            $row = $db->databaseResult($rs);
 
            $db->databaseDisconnect();
 
            return $row;
    }    
 

Which calls

public function databaseQuery($query)
    {        
        $results = odbc_exec($this->conn,$query);
}
 
public function databaseResult($rs)
    {
        $i = 0;
        $j = 0;
        $arrayTemp = "";
        $arrayItem = "";
        $arrayReturn = "";
        $array = "";
        $arrayReturn["oneRow"] = TRUE;
 
        while(odbc_fetch_row($rs))
        {            
 
            for ($j = 1; $j <= odbc_num_fields($rs); $j++)
            {       
                 $field_name = odbc_field_name($rs, $j);
                 $arrayItem[$field_name] = odbc_result($rs, $field_name);
                 $arrayReturn[$field_name] = odbc_result($rs, $field_name);
 
            }
 
            $array[$i] = $arrayItem;
            $i++;
        }
 
        if(sizeof($array)>1){
            $arrayReturn = $array;
 
        };
 
        return $arrayReturn;       
    }