Query works on sql server, does not work php codeigniter

Question

Hopefully this question is not a duplicate.

I have a query that works on sql server. When i try to use it in Codeigniter, i receive an Internal Server Error (500).

Here is the query with php code,

   public function tnaBucketTable($filter = null){
    $sql = "
        SELECT TOP 500 start ,SC.status ,S.shift_id ,SC.call_id,SC.type,SC.win_end,S.job_id
        ,pin = CASE 
                WHEN pin IS NULL THEN pin_agency 
                ELSE pin 
            END
            ,CONVERT(VARCHAR, start, 3)+' '+CONVERT(CHAR(5), start, 108) AS start_time
            ,CONVERT(CHAR(5), [end], 108) AS finish_time
            ,guard_name = CASE guard_info
                WHEN 'Agency' THEN  (SELECT shortname FROM [SecurityPlatform].[dbo].[SP-Guard-Agency] WHERE pin = S.pin_agency)
                ELSE (SELECT shortname FROM [SecurityPlatform].[dbo].[SP-Guard] WHERE pin = S.pin)
            END
            ,guard_number = CASE guard_info
                WHEN 'Agency' THEN  (SELECT phone1 FROM [SecurityPlatform].[dbo].[SP-Guard-Agency] WHERE pin = S.pin_agency)
                ELSE (SELECT phone_number FROM [SecurityPlatform].[dbo].[SP-Guard] WHERE pin = S.pin)
            END
            ,agency_number = CASE S.guard_info
                WHEN 'Agency' THEN (
                    SELECT contact_no_1 FROM [SecurityPlatform].[dbo].[SP-Agency] WHERE agency_id = (
                        SELECT agency_id FROM [SecurityPlatform].[dbo].[SP-Guard-Agency] WHERE pin = S.pin_agency
                    )
                )
                ELSE NULL
            END
            ,C.client_name
            ,ST.site_name
            ,ST.phone_number AS site_phone
            ,ST.site_sin
            FROM [SecurityPlatform].[dbo].[SP-Shift] S
            JOIN [SecurityPlatform].[dbo].[SP-Job] J ON J.job_id = S.job_id
            JOIN [SecurityPlatform].[dbo].[SP-Site] ST ON ST.site_sin = J.[sin]
            JOIN [SecurityPlatform].[dbo].[SP-Client] C ON C.client_id = ST.client_id
            JOIN [SecurityPlatform].[dbo].[SP-ScheduleCall] SC ON SC.shift_id = S.shift_id 
            JOIN [SecurityPlatform].[dbo].[SP-TrackingCall] TC ON TC.call_id = SC.call_id
            WHERE S.[status] = 'Confirmed'
            AND SC.[status] != 'Unconfirmed'
            AND pin > 0 ORDER BY S.shift_id DESC, SC.win_end DESC";

    $query = $this->db->query($sql);
    return $query->result();
}

And the result from Sql Server:

Result from browser:

Anyone have an idea what am i missing?

Please ask if you need more information.

Thanks in advance.

Edit: var_dump($sql) for request

string(1763) " SELECT TOP 500 start ,SC.status ,S.shift_id ,SC.call_id,SC.type,SC.win_end,S.job_id ,pin = CASE WHEN pin IS NULL THEN pin_agency ELSE pin END ,CONVERT(VARCHAR, start, 3)+' '+CONVERT(CHAR(5), start, 108) AS start_time ,CONVERT(CHAR(5), [end], 108) AS finish_time ,guard_name = CASE guard_info WHEN 'Agency' THEN (SELECT shortname FROM [SecurityPlatform].[dbo].[SP-Guard-Agency] WHERE pin = S.pin_agency) ELSE (SELECT shortname FROM [SecurityPlatform].[dbo].[SP-Guard] WHERE pin = S.pin) END ,guard_number = CASE guard_info WHEN 'Agency' THEN (SELECT phone1 FROM [SecurityPlatform].[dbo].[SP-Guard-Agency] WHERE pin = S.pin_agency) ELSE (SELECT phone_number FROM [SecurityPlatform].[dbo].[SP-Guard] WHERE pin = S.pin) END ,agency_number = CASE S.guard_info WHEN 'Agency' THEN ( SELECT contact_no_1 FROM [SecurityPlatform].[dbo].[SP-Agency] WHERE agency_id = ( SELECT agency_id FROM [SecurityPlatform].[dbo].[SP-Guard-Agency] WHERE pin = S.pin_agency ) ) ELSE NULL END ,C.client_name ,ST.site_name ,ST.phone_number AS site_phone ,ST.site_sin FROM [SecurityPlatform].[dbo].[SP-Shift] S JOIN [SecurityPlatform].[dbo].[SP-Job] J ON J.job_id = S.job_id JOIN [SecurityPlatform].[dbo].[SP-Site] ST ON ST.site_sin = J.[sin] JOIN [SecurityPlatform].[dbo].[SP-Client] C ON C.client_id = ST.client_id JOIN [SecurityPlatform].[dbo].[SP-ScheduleCall] SC ON SC.shift_id = S.shift_id JOIN [SecurityPlatform].[dbo].[SP-TrackingCall] TC ON TC.call_id = SC.call_id WHERE S.[status] = 'Confirmed' AND SC.[status] != 'Unconfirmed' AND pin > 0 ORDER BY S.shift_id DESC, SC.win_end DESC"

The issue is more likely to be on your php code not the query itself — Masivuye Cokile, Jan 17 '19 at 12:04
@MasivuyeCokile then it would return an error. But it does not return anything. — Mücahid Erenler, Jan 17 '19 at 12:05
Enable PHP error reporting: https://stackoverflow.com/questions/1053424/how-do-i-get-php-errors-to-display — Alex K., Jan 17 '19 at 12:06
Are you using Ajax? the error does not just come up you need to enable error reporting @MücahidErenler can you check the server error log for the errros — Masivuye Cokile, Jan 17 '19 at 12:07
So i did check the error.log for your request, and thats what i got, Maximum execution time of 30 seconds exceeded. but it does 0.1 second in sql server — Mücahid Erenler, Jan 17 '19 at 12:12
@SalmanA nope. no loops. pushing the data to contoller and ajax success tries to get it. — Mücahid Erenler, Jan 17 '19 at 12:38
Well if it says 30 seconds then you need to identify where those 30 seconds were spent. An infinite loop usually causes that. It could be SQL server too but you have to check that. Or may be there is just too much data to process (number of rows or number of megabytes). — Salman A, Jan 17 '19 at 12:44
@SalmanA Thank you for your responses so far. Pretty sure it is not infinite loop. I thought that saying TOP 500 will shortened the processed data. There are 16k rows on the table. But still it is quite bizzare that sql gives the result, but i can not recieve it from codeigniter. — Mücahid Erenler, Jan 17 '19 at 12:48
Comment the `$query = $this->db->query($sql);` part and return null on the next line, and see if it still gives a timeout error. If not, then the problem could be with database driver or you could be having some server access issues. — Prahlad Yeri, Jan 17 '19 at 13:41
@PrahladYeri I did what you have said. Now there is no time out error. If i write a simple query and try again, if returns the result. So i don't think i have a problem with db driver. Also i can access the other pages of the server. — Mücahid Erenler, Jan 18 '19 at 11:43
error 500, check the web server logs, you will get some clue — Danyal Sandeelo, Jan 18 '19 at 12:58

score 0 · Answer 1 · answered Jan 17 '19 at 12:15

This

$query = "SELECT TOP 500 start,SC.status,S.shift_id,SC.call_id,SC.type,SC.win_end,S.job_id,pin = CASE 
            WHEN pin IS NULL THEN pin_agency 
            ELSE pin 
        END
        ,CONVERT(VARCHAR, start, 3)+' '+CONVERT(CHAR(5), start, 108) AS start_time
        ,CONVERT(CHAR(5), [end], 108) AS finish_time
        ,guard_name = CASE guard_info
            WHEN 'Agency' THEN  (SELECT shortname FROM [SecurityPlatform].[dbo].[SP-Guard-Agency] WHERE pin = S.pin_agency)
            ELSE (SELECT shortname FROM [SecurityPlatform].[dbo].[SP-Guard] WHERE pin = S.pin)
        END
        ,guard_number = CASE guard_info
            WHEN 'Agency' THEN  (SELECT phone1 FROM [SecurityPlatform].[dbo].[SP-Guard-Agency] WHERE pin = S.pin_agency)
            ELSE (SELECT phone_number FROM [SecurityPlatform].[dbo].[SP-Guard] WHERE pin = S.pin)
        END
        ,agency_number = CASE S.guard_info
            WHEN 'Agency' THEN (
                SELECT contact_no_1 FROM [SecurityPlatform].[dbo].[SP-Agency] WHERE agency_id = (
                    SELECT agency_id FROM [SecurityPlatform].[dbo].[SP-Guard-Agency] WHERE pin = S.pin_agency
                )
            )
            ELSE NULL
        END
        ,C.client_name
        ,ST.site_name
        ,ST.phone_number AS site_phone
        ,ST.site_sin
        FROM [SecurityPlatform].[dbo].[SP-Shift] S
        JOIN [SecurityPlatform].[dbo].[SP-Job] J ON J.job_id = S.job_id
        JOIN [SecurityPlatform].[dbo].[SP-Site] ST ON ST.site_sin = J.[sin]
        JOIN [SecurityPlatform].[dbo].[SP-Client] C ON C.client_id = ST.client_id
        JOIN [SecurityPlatform].[dbo].[SP-ScheduleCall] SC ON SC.shift_id = S.shift_id 
        JOIN [SecurityPlatform].[dbo].[SP-TrackingCall] TC ON TC.call_id = SC.call_id
        WHERE S.[status] = 'Confirmed'
        AND SC.[status] != 'Unconfirmed'
        AND pin > 0 ORDER BY S.shift_id DESC, SC.win_end DESC";
        var_dump($query);

outputs this

SELECT TOP 500 start,SC.status,S.shift_id,SC.call_id,SC.type,SC.win_end,S.job_id,pin = CASE WHEN pin IS NULL THEN pin_agency ELSE pin END ,CONVERT(VARCHAR, start, 3)+' '+CONVERT(CHAR(5), start, 108) AS start_time ,CONVERT(CHAR(5), [end], 108) AS finish_time ,guard_name = CASE guard_info WHEN 'Agency' THEN (SELECT shortname FROM [SecurityPlatform].[dbo].[SP-Guard-Agency] WHERE pin = S.pin_agency) ELSE (SELECT shortname FROM [SecurityPlatform].[dbo].[SP-Guard] WHERE pin = S.pin) END ,guard_number = CASE guard_info WHEN 'Agency' THEN (SELECT phone1 FROM [SecurityPlatform].[dbo].[SP-Guard-Agency] WHERE pin = S.pin_agency) ELSE (SELECT phone_number FROM [SecurityPlatform].[dbo].[SP-Guard] WHERE pin = S.pin) END ,agency_number = CASE S.guard_info WHEN 'Agency' THEN ( SELECT contact_no_1 FROM [SecurityPlatform].[dbo].[SP-Agency] WHERE agency_id = ( SELECT agency_id FROM [SecurityPlatform].[dbo].[SP-Guard-Agency] WHERE pin = S.pin_agency ) ) ELSE NULL END ,C.client_name ,ST.site_name ,ST.phone_number AS site_phone ,ST.site_sin FROM [SecurityPlatform].[dbo].[SP-Shift] S JOIN [SecurityPlatform].[dbo].[SP-Job] J ON J.job_id = S.job_id JOIN [SecurityPlatform].[dbo].[SP-Site] ST ON ST.site_sin = J.[sin] JOIN [SecurityPlatform].[dbo].[SP-Client] C ON C.client_id = ST.client_id JOIN [SecurityPlatform].[dbo].[SP-ScheduleCall] SC ON SC.shift_id = S.shift_id JOIN [SecurityPlatform].[dbo].[SP-TrackingCall] TC ON TC.call_id = SC.call_id WHERE S.[status] = 'Confirmed' AND SC.[status] != 'Unconfirmed' AND pin > 0 ORDER BY S.shift_id DESC, SC.win_end DESC

for me. Can you try the var_dump output directly in your SQL? I have a little clue I guess...

edited the question. You can see the var dump now. Thank you for your interest. — Mücahid Erenler, Jan 17 '19 at 12:19
SELECT TOP is for SQL Databases, if you use your Statement on a MySQL database, you have to use LIMIT. What you also can try is this: https://developer.mimer.com/sql-validator-99/ for SQL Queries... — Kerim Yagmurcu, Jan 17 '19 at 12:39
But i am using SQL not MySQL and if it wasn't valid, sql server would return an error, right? — Mücahid Erenler, Jan 17 '19 at 12:44
Depends... I was reading your question again, 500 Server error... your code/statement should not be the source for this error. Check the permissions of your file and check your .htaccess for misconfigurations.. — Kerim Yagmurcu, Jan 17 '19 at 13:05

score 0 · Accepted Answer · answered Jan 18 '19 at 12:55

I have tried all the recommendations in here, but could not find a result, so what i did is i changed my Sql query to Codeigniter's query builder and now it work without any time error.

Hope this helps anyone else.

$this->db->select("
    start,
    SC.status
    ,S.shift_id
    ,SC.call_id
    ,SC.type
    ,SC.win_end
    ,S.job_id
    ,pin =
    CASE 
        WHEN pin IS NULL THEN pin_agency 
        ELSE pin 
    END 
    ,CONVERT(VARCHAR, start, 3)+' '+CONVERT(CHAR(5), start, 108) AS start_time
    ,CONVERT(CHAR(5), [end], 108) AS finish_time
    ,guard_name = 
    CASE guard_info
        WHEN 'Agency' THEN  (SELECT shortname FROM [SecurityPlatform].[dbo].[SP-Guard-Agency] WHERE pin = S.pin_agency)
        ELSE (SELECT shortname FROM [SecurityPlatform].[dbo].[SP-Guard] WHERE pin = S.pin)
    END
    ,guard_number =
    CASE guard_info
        WHEN 'Agency' THEN  (SELECT phone1 FROM [SecurityPlatform].[dbo].[SP-Guard-Agency] WHERE pin = S.pin_agency)
        ELSE (SELECT phone_number FROM [SecurityPlatform].[dbo].[SP-Guard] WHERE pin = S.pin)
    END
    ,agency_number =
    CASE S.guard_info
        WHEN 'Agency' THEN (
            SELECT contact_no_1 FROM [SecurityPlatform].[dbo].[SP-Agency] WHERE agency_id = (
                SELECT agency_id FROM [SecurityPlatform].[dbo].[SP-Guard-Agency] WHERE pin = S.pin_agency
            )
        )
        ELSE NULL
    END
    ,C.client_name
    ,ST.site_name
    ,ST.phone_number AS site_phone
    ,ST.site_sin ", FALSE);

    $this->db->from('SP-Shift S');
    $this->db->join('SP-Job J', 'J.job_id = S.job_id');
    $this->db->join('SP-Site ST', 'ST.site_sin = J.sin');
    $this->db->join('SP-Client C', 'C.client_id = ST.client_id');
    $this->db->join('SP-ScheduleCall SC', 'SC.shift_id = S.shift_id');
    $this->db->join('SP-TrackingCall TC', 'TC.call_id = SC.call_id');
    $this->db->where('S.status', 'Confirmed');
    $this->db->where('SC.status != ', 'Unconfirmed');
    $this->db->where('pin >= ', 0);

Interesting! If you are still curious, you can try setting the `memory_limit` and `mssql.timeout` ini configurations in your php code as per [this answer](https://stackoverflow.com/a/24344562/849365). These values are different when you run queries through PHP and any other mssql environments. The CodeIgniter's query builder could be changing these configuration values or maybe doing some other optimizations, so it could be working. — Prahlad Yeri, Jan 18 '19 at 13:00

Query works on sql server, does not work php codeigniter

2 Answers2