PHP /SQL Query assistance

johannesmoolman · May 6, 2019, 12:08pm

Hi guys
I got a SQL query from Crystal Reports, and would like to use it with my PHP Code, but im used to mysqli and the info i need to access is on SQL server.
Anyone that can make this query PHP friendly as im lost.
I would like to enter the Employee last name and get the query running

SELECT 'EMP.LASTNAME', "EMP"."FIRSTNAME", "READER"."READERDESC", "EMP"."MIDNAME", "EMP"."ID", "READER"."TIMEATT", "EVENTS"."CARDNUM", "READER_OUT"."TIMEATT", "READER_OUT"."READERDESC", "EVENTS"."MACHINE", "EVENTS"."SERIALNUM", "EVENTS"."EVENTTYPE", "EVENTS"."EVENTID", "EVENTS_OUT"."EVENTTYPE", "EVENTS_OUT"."EVENTID", "ACCESSPANE"."PANELID", "EVENTS"."EVENT_TIME_UTC", "EVENTS_OUT"."EVENT_TIME_UTC", "READER_NGP_IN"."DOOR_OPTIONS", "READER"."CTRLTYPE", "READER_NGP_OUT"."DOOR_OPTIONS", "READER_OUT"."CTRLTYPE"
 FROM   (((((("AccessControl"."dbo"."EVENTS" "EVENTS" INNER JOIN "AccessControl"."dbo"."READER" "READER" ON ("EVENTS"."MACHINE"="READER"."PANELID") AND ("EVENTS"."DEVID"="READER"."READERID")) INNER JOIN "AccessControl"."dbo"."EMP" "EMP" ON "EVENTS"."EMPID"="EMP"."ID") INNER JOIN "AccessControl"."dbo"."ACCESSPANE" "ACCESSPANE" ON "EVENTS"."MACHINE"="ACCESSPANE"."PANELID") LEFT OUTER JOIN "AccessControl"."dbo"."EVENTS" "EVENTS_OUT" ON "EMP"."ID"="EVENTS_OUT"."EMPID") LEFT OUTER JOIN "AccessControl"."dbo"."READER" "READER_OUT" ON ("EVENTS_OUT"."MACHINE"="READER_OUT"."PANELID") AND ("EVENTS_OUT"."DEVID"="READER_OUT"."READERID")) LEFT OUTER JOIN "AccessControl"."dbo"."READER_NGP" "READER_NGP_OUT" ON ("READER_OUT"."PANELID"="READER_NGP_OUT"."PANELID") AND ("READER_OUT"."READERID"="READER_NGP_OUT"."READERID")) LEFT OUTER JOIN "AccessControl"."dbo"."READER_NGP" "READER_NGP_IN" ON ("READER"."PANELID"="READER_NGP_IN"."PANELID") AND ("READER"."READERID"="READER_NGP_IN"."READERID")
 WHERE  (("READER_NGP_IN"."DOOR_OPTIONS" IS  NOT  NULL  AND  NOT ("READER"."CTRLTYPE"=64 OR "READER"."CTRLTYPE"=179) AND "READER_NGP_OUT"."DOOR_OPTIONS" IS  NOT  NULL  AND "READER_OUT"."CTRLTYPE"=64) OR ("READER"."TIMEATT"=1 AND "READER_OUT"."TIMEATT"=2)) AND "EVENTS"."EVENT_TIME_UTC"<"EVENTS_OUT"."EVENT_TIME_UTC" AND (("EVENTS"."EVENTTYPE"=0) OR ("EVENTS"."EVENTTYPE"=2 AND ("EVENTS"."EVENTID"=0 OR "EVENTS"."EVENTID"=2)) OR ("EVENTS"."EVENTTYPE"=3 AND ("EVENTS"."EVENTID"=4 OR "EVENTS"."EVENTID"=5))) AND (("EVENTS_OUT"."EVENTTYPE"=0) OR ("EVENTS_OUT"."EVENTTYPE"=2 AND ("EVENTS_OUT"."EVENTID"=0 OR "EVENTS_OUT"."EVENTID"=2)) OR ("EVENTS_OUT"."EVENTTYPE"=3 AND ("EVENTS_OUT"."EVENTID"=4 OR "EVENTS_OUT"."EVENTID"=5)))
 ORDER BY "EMP"."LASTNAME", "EMP"."FIRSTNAME", "EMP"."MIDNAME", "EMP"."ID"

DaveMaxwell · May 6, 2019, 4:07pm

Ugh. I despise the “builder” format of SQL Server because it’s never the most efficient query, and the paranthesis that it adds are confusing (and annoying)

I’ve reformatted the query to make it easier to read, clearing up the WHERE condition, and also changed the LEFT OUTER JOINs to INNER JOINS because as soon as you put a where condition on a field from a left outer joined table, it’s essentially an inner join anyway, so it’s more efficient just to run it as an inner join.

I’ve also added the appropriate WHERE for the last name as you’d use it in a parameterized query.

SELECT EMP.LASTNAME
	 , EMP.FIRSTNAME
	 , READER.READERDESC
	 , EMP.MIDNAME
	 , EMP.ID
	 , READER.TIMEATT
	 , EVENTS.CARDNUM
	 , READER_OUT.TIMEATT
	 , READER_OUT.READERDESC
	 , EVENTS.MACHINE
	 , EVENTS.SERIALNUM
	 , EVENTS.EVENTTYPE
	 , EVENTS.EVENTID
	 , EVENTS_OUT.EVENTTYPE
	 , EVENTS_OUT.EVENTID
	 , ACCESSPANE.PANELID
	 , EVENTS.EVENT_TIME_UTC
	 , EVENTS_OUT.EVENT_TIME_UTC
	 , READER_NGP_IN.DOOR_OPTIONS
	 , READER.CTRLTYPE
	 , READER_NGP_OUT.DOOR_OPTIONS
	 , READER_OUT.CTRLTYPE
  FROM AccessControl.dbo.EVENTS EVENTS 
 INNER JOIN AccessControl.dbo.READER READER ON EVENTS.MACHINE = READER.PANELID AND EVENTS.DEVID = READER.READERID
 INNER JOIN AccessControl.dbo.EMP EMP ON EVENTS.EMPID = EMP.ID 
 INNER JOIN AccessControl.dbo.ACCESSPANE ACCESSPANE ON EVENTS.MACHINE = ACCESSPANE.PANELID) 
 INNER JOIN AccessControl.dbo.EVENTS EVENTS_OUT ON EMP.ID = EVENTS_OUT.EMPID) 
 INNER JOIN AccessControl.dbo.READER READER_OUT ON EVENTS_OUT.MACHINE = READER_OUT.PANELID AND EVENTS_OUT.DEVID = READER_OUT.READERID
 INNER JOIN AccessControl.dbo.READER_NGP READER_NGP_OUT ON READER_OUT.PANELID = READER_NGP_OUT.PANELID AND READER_OUT.READERID = READER_NGP_OUT.READERID
 INNER JOIN AccessControl.dbo.READER_NGP READER_NGP_IN ON READER.PANELID = READER_NGP_IN.PANELID AND READER.READERID = READER_NGP_IN.READERID
 WHERE EMP.LASTNAME = ?
   AND READER_NGP_IN.DOOR_OPTIONS IS NOT NULL  
   AND READER.CTRLTYPE NOT IN (64, 179) 
   AND READER_NGP_OUT.DOOR_OPTIONS IS NOT NULL 
   AND (READER_OUT.CTRLTYPE = 64 OR (READER.TIMEATT = 1 AND READER_OUT.TIMEATT = 2))
   AND EVENTS.EVENT_TIME_UTC < EVENTS_OUT.EVENT_TIME_UTC 
   AND (EVENTS.EVENTTYPE = 0 OR (EVENTS.EVENTTYPE = 2 AND EVENTS.EVENTID IN (0, 2)) OR (EVENTS.EVENTTYPE = 3 AND EVENTS.EVENTID IN (4, 5))) 
   AND (EVENTS_OUT.EVENTTYPE = 0 OR (EVENTS_OUT.EVENTTYPE = 2 AND EVENTS_OUT.EVENTID IN (0, 2)) OR (EVENTS_OUT.EVENTTYPE = 3 AND EVENTS_OUT.EVENTID IN (4, 5)))
 ORDER BY EMP.LASTNAME
		, EMP.FIRSTNAME
		, EMP.MIDNAME
		, EMP.ID

SamuelCalifornia · May 6, 2019, 6:35pm

Does SQL Server have non-standard syntactical requirements? I have not heard of such things.

Most languages can be expressed in a compact format or in a more readable format for people to understand. C++, C#, PHP. JavaScript (including JSON) and XML all are like that. Just search for prettyprint and you will find an abundance of utilities (for various languages) to do reformatting such as you did.

DaveMaxwell · May 6, 2019, 6:39pm

No, but the visual builder creates non-sensical stuff like the example query. I’ve not figured out the method to their “madness”

johannesmoolman · May 7, 2019, 7:56am

Thank you very Much Dave for the Query assistance it is not giving syntax etc errors any more, i am still struggling though if you could possibly assist.

<?php  
$serverName = "WIN-1CAQBP5SVP8"; 
$uid = "LENEL";   
$pwd = "MULTIMEDIA";  
$databaseName = "AccessControl"; 

$connectionInfo = array( "UID"=>$uid,                            
                         "PWD"=>$pwd,                            
                         "Database"=>$databaseName); 

/* Connect using SQL Server Authentication. */  
$conn = sqlsrv_connect( $serverName, $connectionInfo);  
if( $conn ) {
     echo "Connection established.<br />";
}else{
     echo "Connection could not be established.<br />";
     die( print_r( sqlsrv_errors(), true));
}

  $sql= "SELECT EMP.LASTNAME
	 , EMP.FIRSTNAME
	 , READER.READERDESC
	 , EMP.MIDNAME
	 , EMP.ID
	 , READER.TIMEATT
	 , EVENTS.CARDNUM
	 , READER_OUT.TIMEATT
	 , READER_OUT.READERDESC
	 , EVENTS.MACHINE
	 , EVENTS.SERIALNUM
	 , EVENTS.EVENTTYPE
	 , EVENTS.EVENTID
	 , EVENTS_OUT.EVENTTYPE
	 , EVENTS_OUT.EVENTID
	 , ACCESSPANE.PANELID
	 , EVENTS.EVENT_TIME_UTC
	 , EVENTS_OUT.EVENT_TIME_UTC
	 , READER_NGP_IN.DOOR_OPTIONS
	 , READER.CTRLTYPE
	 , READER_NGP_OUT.DOOR_OPTIONS
	 , READER_OUT.CTRLTYPE
  FROM AccessControl.dbo.EVENTS EVENTS 
 INNER JOIN AccessControl.dbo.READER READER ON EVENTS.MACHINE = READER.PANELID AND EVENTS.DEVID = READER.READERID
 INNER JOIN AccessControl.dbo.EMP EMP ON EVENTS.EMPID = EMP.ID 
 INNER JOIN AccessControl.dbo.ACCESSPANE ACCESSPANE ON EVENTS.MACHINE = ACCESSPANE.PANELID) 
 INNER JOIN AccessControl.dbo.EVENTS EVENTS_OUT ON EMP.ID = EVENTS_OUT.EMPID) 
 INNER JOIN AccessControl.dbo.READER READER_OUT ON EVENTS_OUT.MACHINE = READER_OUT.PANELID AND EVENTS_OUT.DEVID = READER_OUT.READERID
 INNER JOIN AccessControl.dbo.READER_NGP READER_NGP_OUT ON READER_OUT.PANELID = READER_NGP_OUT.PANELID AND READER_OUT.READERID = READER_NGP_OUT.READERID
 INNER JOIN AccessControl.dbo.READER_NGP READER_NGP_IN ON READER.PANELID = READER_NGP_IN.PANELID AND READER.READERID = READER_NGP_IN.READERID
 WHERE EMP.LASTNAME = ?
   AND READER_NGP_IN.DOOR_OPTIONS IS NOT NULL  
   AND READER.CTRLTYPE NOT IN (64, 179) 
   AND READER_NGP_OUT.DOOR_OPTIONS IS NOT NULL 
   AND (READER_OUT.CTRLTYPE = 64 OR (READER.TIMEATT = 1 AND READER_OUT.TIMEATT = 2))
   AND EVENTS.EVENT_TIME_UTC < EVENTS_OUT.EVENT_TIME_UTC 
   AND (EVENTS.EVENTTYPE = 0 OR (EVENTS.EVENTTYPE = 2 AND EVENTS.EVENTID IN (0, 2)) OR (EVENTS.EVENTTYPE = 3 AND EVENTS.EVENTID IN (4, 5))) 
   AND (EVENTS_OUT.EVENTTYPE = 0 OR (EVENTS_OUT.EVENTTYPE = 2 AND EVENTS_OUT.EVENTID IN (0, 2)) OR (EVENTS_OUT.EVENTTYPE = 3 AND EVENTS_OUT.EVENTID IN (4, 5)))
 ORDER BY EMP.LASTNAME
		, EMP.FIRSTNAME
		, EMP.MIDNAME
		, EMP.ID";




$result =sqlsrv_query($conn,$sql);
var_dump($result);

?>

the var_dump() is giving boolean False meaning its not getting anything through the query ? any suggestions

again thanks for the assistance

johannesmoolman · May 7, 2019, 8:29am

Ive changed the way my SQlsrv looks like from some googling but also the same result

 <html>
<table>

<?php  
$serverName = "WIN-1CAQBP5SVP8"; 
$uid = "LENEL";   
$pwd = "MULTIMEDIA";  
$databaseName = "AccessControl"; 

$connectionInfo = array( "UID"=>$uid,                            
                         "PWD"=>$pwd,                            
                         "Database"=>$databaseName); 

/* Connect using SQL Server Authentication. */  
$conn = sqlsrv_connect( $serverName, $connectionInfo);  
if( $conn ) {
     echo "Connection established.<br />";
}else{
     echo "Connection could not be established.<br />";
     die( print_r( sqlsrv_errors(), true));
}
$lastname = "moolman";
  $sql= sqlsrv_query($conn,"SELECT EMP.LASTNAME
	 , EMP.FIRSTNAME
	 , READER.READERDESC
	 , EMP.MIDNAME
	 , EMP.ID
	 , READER.TIMEATT
	 , EVENTS.CARDNUM
	 , READER_OUT.TIMEATT
	 , READER_OUT.READERDESC
	 , EVENTS.MACHINE
	 , EVENTS.SERIALNUM
	 , EVENTS.EVENTTYPE
	 , EVENTS.EVENTID
	 , EVENTS_OUT.EVENTTYPE
	 , EVENTS_OUT.EVENTID
	 , ACCESSPANE.PANELID
	 , EVENTS.EVENT_TIME_UTC
	 , EVENTS_OUT.EVENT_TIME_UTC
	 , READER_NGP_IN.DOOR_OPTIONS
	 , READER.CTRLTYPE
	 , READER_NGP_OUT.DOOR_OPTIONS
	 , READER_OUT.CTRLTYPE
  FROM AccessControl.dbo.EVENTS EVENTS 
 INNER JOIN AccessControl.dbo.READER READER ON EVENTS.MACHINE = READER.PANELID AND EVENTS.DEVID = READER.READERID
 INNER JOIN AccessControl.dbo.EMP EMP ON EVENTS.EMPID = EMP.ID 
 INNER JOIN AccessControl.dbo.ACCESSPANE ACCESSPANE ON EVENTS.MACHINE = ACCESSPANE.PANELID) 
 INNER JOIN AccessControl.dbo.EVENTS EVENTS_OUT ON EMP.ID = EVENTS_OUT.EMPID) 
 INNER JOIN AccessControl.dbo.READER READER_OUT ON EVENTS_OUT.MACHINE = READER_OUT.PANELID AND EVENTS_OUT.DEVID = READER_OUT.READERID
 INNER JOIN AccessControl.dbo.READER_NGP READER_NGP_OUT ON READER_OUT.PANELID = READER_NGP_OUT.PANELID AND READER_OUT.READERID = READER_NGP_OUT.READERID
 INNER JOIN AccessControl.dbo.READER_NGP READER_NGP_IN ON READER.PANELID = READER_NGP_IN.PANELID AND READER.READERID = READER_NGP_IN.READERID
 WHERE EMP.LASTNAME = '$lastname'
   AND READER_NGP_IN.DOOR_OPTIONS IS NOT NULL  
   AND READER.CTRLTYPE NOT IN (64, 179) 
   AND READER_NGP_OUT.DOOR_OPTIONS IS NOT NULL 
   AND (READER_OUT.CTRLTYPE = 64 OR (READER.TIMEATT = 1 AND READER_OUT.TIMEATT = 2))
   AND EVENTS.EVENT_TIME_UTC < EVENTS_OUT.EVENT_TIME_UTC 
   AND (EVENTS.EVENTTYPE = 0 OR (EVENTS.EVENTTYPE = 2 AND EVENTS.EVENTID IN (0, 2)) OR (EVENTS.EVENTTYPE = 3 AND EVENTS.EVENTID IN (4, 5))) 
   AND (EVENTS_OUT.EVENTTYPE = 0 OR (EVENTS_OUT.EVENTTYPE = 2 AND EVENTS_OUT.EVENTID IN (0, 2)) OR (EVENTS_OUT.EVENTTYPE = 3 AND EVENTS_OUT.EVENTID IN (4, 5)))
 ORDER BY EMP.LASTNAME
		, EMP.FIRSTNAME
		, EMP.MIDNAME
		, EMP.ID");

$result =sqlsrv_query($conn,$sql);
while($row=sqlsrv_fetch_array($result)){
    var_dump($row);
    printf($row);
    printf($result);
}
    

?>

droopsnoot · May 7, 2019, 8:52am

This bit doesn’t look correct:

  $sql= sqlsrv_query($conn,"SELECT EMP.LASTNAME
... etc. etc.

followed by

$result =sqlsrv_query($conn,$sql);
while (...

In this case isn’t $sql already a results object? If so you could either try to retrieve the rows from $sql instead of $result and drop the second sqlsrv_query() call, or simply change the first statement to just be a string assignment. I’m not familiar with SQL Server, so I’m guessing a bit, but it does look as if you’re trying to run two queries there.

I think the reason you had trouble with the query while it still had the ? in place was because that’s a parameter for a prepared statement, which means you would need to prepare the query, then supply the parameter, then execute the prepared statement. It’s worth looking at if your SQL Server library supports it, as it has several advantages.

I think here

    printf($row);
    printf($result);

you probably should use either print_r() or var_dump(), printf is OK for formatted printing but will it work without a format string?

johannesmoolman · May 7, 2019, 11:14am

I am almost sure its the ? part of making a prepared statement, i Have not gotten so far to learn that… any pointers ???

droopsnoot · May 7, 2019, 11:23am

Some information here: https://www.php.net/manual/en/function.sqlsrv-prepare.php

I’m used to using PDO with MySQL, but as one of the advantages of PDO over mysqli is that it can be used to connect to databases other than MySQL, maybe you could use PDO instead of your sqlsrv_ function library.

johannesmoolman · May 7, 2019, 1:18pm

 <html>
<table>

<?php 
  
  error_reporting(E_ALL);
ini_set('display_errors', 1);  
    
$serverName = "WIN-1CAQBP5SVP8"; 
$uid = "LENEL";   
$pwd = "MULTIMEDIA";  
$databaseName = "AccessControl"; 

$connectionInfo = array( "UID"=>$uid,                            
                         "PWD"=>$pwd,                            
                         "Database"=>$databaseName); 


$conn = sqlsrv_connect( $serverName, $connectionInfo);  
if( $conn ) {
     echo "Connection established.<br />";
}else{
     echo "Connection could not be established.<br />";
     die( print_r( sqlsrv_errors(), true));
}
 $lastname ="moolman";
$sql= "SELECT EMP.LASTNAME
	 , EMP.FIRSTNAME
	 , READER.READERDESC
	 , EMP.MIDNAME
	 , EMP.ID
	 , READER.TIMEATT
	 , EVENTS.CARDNUM
	 , READER_OUT.TIMEATT
	 , READER_OUT.READERDESC
	 , EVENTS.MACHINE
	 , EVENTS.SERIALNUM
	 , EVENTS.EVENTTYPE
	 , EVENTS.EVENTID
	 , EVENTS_OUT.EVENTTYPE
	 , EVENTS_OUT.EVENTID
	 , ACCESSPANE.PANELID
	 , EVENTS.EVENT_TIME_UTC
	 , EVENTS_OUT.EVENT_TIME_UTC
	 , READER_NGP_IN.DOOR_OPTIONS
	 , READER.CTRLTYPE
	 , READER_NGP_OUT.DOOR_OPTIONS
	 , READER_OUT.CTRLTYPE
  FROM AccessControl.dbo.EVENTS EVENTS 
 INNER JOIN AccessControl.dbo.READER READER ON EVENTS.MACHINE = READER.PANELID AND EVENTS.DEVID = READER.READERID
 INNER JOIN AccessControl.dbo.EMP EMP ON EVENTS.EMPID = EMP.ID 
 INNER JOIN AccessControl.dbo.ACCESSPANE ACCESSPANE ON EVENTS.MACHINE = ACCESSPANE.PANELID 
 INNER JOIN AccessControl.dbo.EVENTS EVENTS_OUT ON EMP.ID = EVENTS_OUT.EMPID 
 INNER JOIN AccessControl.dbo.READER READER_OUT ON EVENTS_OUT.MACHINE = READER_OUT.PANELID AND EVENTS_OUT.DEVID = READER_OUT.READERID
 INNER JOIN AccessControl.dbo.READER_NGP READER_NGP_OUT ON READER_OUT.PANELID = READER_NGP_OUT.PANELID AND READER_OUT.READERID = READER_NGP_OUT.READERID
 INNER JOIN AccessControl.dbo.READER_NGP READER_NGP_IN ON READER.PANELID = READER_NGP_IN.PANELID AND READER.READERID = READER_NGP_IN.READERID
 WHERE EMP.LASTNAME = ?
   AND READER_NGP_IN.DOOR_OPTIONS IS NOT NULL  
   AND READER.CTRLTYPE NOT IN (64, 179) 
   AND READER_NGP_OUT.DOOR_OPTIONS IS NOT NULL 
   AND (READER_OUT.CTRLTYPE = 64 OR (READER.TIMEATT = 1 AND READER_OUT.TIMEATT = 2))
   AND EVENTS.EVENT_TIME_UTC < EVENTS_OUT.EVENT_TIME_UTC 
   AND (EVENTS.EVENTTYPE = 0 OR (EVENTS.EVENTTYPE = 2 AND EVENTS.EVENTID IN (0, 2)) OR (EVENTS.EVENTTYPE = 3 AND EVENTS.EVENTID IN (4, 5))) 
   AND (EVENTS_OUT.EVENTTYPE = 0 OR (EVENTS_OUT.EVENTTYPE = 2 AND EVENTS_OUT.EVENTID IN (0, 2)) OR (EVENTS_OUT.EVENTTYPE = 3 AND EVENTS_OUT.EVENTID IN (4, 5)))
 ORDER BY EMP.LASTNAME
		, EMP.FIRSTNAME
		, EMP.MIDNAME
		, EMP.ID";
  $query = $conn->prepare($sql); 
$query->bind_param('s', $lastname);
$query->execute();
    
    

if( $sql === false ) {
    echo "Error in executing query.</br>";
    die( print_r( sqlsrv_errors(), true));
}

while ($obj = sqlsrv_fetch_array($sql, SQLSRV_FETCH_ASSOC)) {
    echo $obj['lastname'];
}

    ?>

Am i going in the right direction ? the query is still failing but im trying to figure out the ? placeholder using prepared statement

DaveMaxwell · May 7, 2019, 4:47pm

This is wrong…

if( $sql === false ) {

I haven’t done PHP for a while, but I believe this will work…

$query->execute();
$results = $query->fetchAll();

foreach($results as $result){  
    echo $result['lastname'];
}

droopsnoot · May 7, 2019, 5:38pm

This bit

$query = $conn->prepare($sql); 
$query->bind_param('s', $lastname);
$query->execute();

seems to be mysqli code that you’re trying to run, and I don’t think it’ll work. Did you have a look at that link to the documentation I posted above? The sample code in that page doesn’t look anything like the above.

$stmt = sqlsrv_prepare($conn, $sql, array(&$lastname));
if (sqlsrv_execute($stmt)) { 
   // query executed, get the results

looks a bit more like it, but I might be wrong about the way a parameter is passed.

system · August 7, 2019, 6:12pm

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.