Passing a parameter to MySql Procedure Issue

[LarryH]

Hi -

 

I have been battling for days trying to pass a parameter to a functioning procedure.

 

I have a PHP app that works perfectly, which calls a Procedure in our database. As soon as I add a parameter to the Procedure, I get zero result.

The objective is to be able to pass a parameter (accountID) to the called Procedure. This parameter is to be used to control the WHERE clause in the procedure

 

Please can someone look at tell me what am I doing wrong.

 

Thanks in advance (and I'm sorry to have copied all the code, but thought it would help)

 

Larry

The PHP (getallroutesformap.php ) Application code

<?php

 

    include 'dbconnect.php';

               

    switch ($dbType) {

        case DB_MYSQL:

        $stmt = $pdo->prepare('CALL prcGetAllRoutesForMap();');

                                $acID='10091';

                                $stmt->bindParam(':accID', $acID, PDO::PARAM_STR);   // always calls the procedure

///                           $stmt->bindParam(':accID', $acID);  /// does not work

///                           $stmt->bindParam(':accID', 10091);  /// does not work

                                $acID='10091';

            break;

        case DB_POSTGRESQL:

        case DB_SQLITE3:

      }

 

    $stmt->execute();

    $json = '{ "locations": [';

 

    foreach ($stmt as $row) {

        $json .= $row['json'];

        $json .= ',';

    }

 

    $json = rtrim($json, ",");

    $json .= '] }';

 

    header('Content-Type: application/json');

    echo $json;

 

?>

Database Procedure (getallroutesformap) that works.

Note: accountID=’10091’, but I need it to be a variable/parameter

 

BEGIN

SELECT sessionId, gpsTime, CONCAT('{ "latitude":"', CAST(latitude AS CHAR),'", "longitude":"', CAST(longitude AS CHAR), '", "speed":"', CAST(speed AS CHAR), '", "direction":"', CAST(direction AS CHAR), '", "distance":"', CAST(distance AS CHAR), '", "locationMethod":"', locationMethod, '", "gpsTime":"', DATE_FORMAT(gpsTime, '%b %e %Y %h:%i%p'), '", "userName":"', userName, '", "phoneNumber":"', phoneNumber, '", "sessionID":"', CAST(sessionID AS CHAR), '", "accuracy":"', CAST(accuracy AS CHAR), '", "extraInfo":"', extraInfo, '" }') json

FROM (SELECT MAX(GPSLocationID) ID

      FROM gpslocations

      WHERE accountID = '10091' && sessionID != '0' && CHAR_LENGTH(sessionID) != 0 && gpstime != '0000-00-00 00:00:00'

      GROUP BY sessionID) AS MaxID

JOIN gpslocations ON gpslocations.GPSLocationID = MaxID.ID

ORDER BY gpsTime;

END

 

Database Procedure that gives Zero Results

Here the Procedure has a parameter assigned to it called accID . which is setup as an IN, VARCHAR, 15

 

This immediately gives me a zero result, even if I change accountID= accID or leave it as it was before.

 

BEGIN

SELECT sessionId, gpsTime, CONCAT('{ "latitude":"', CAST(latitude AS CHAR),'", "longitude":"', CAST(longitude AS CHAR), '", "speed":"', CAST(speed AS CHAR), '", "direction":"', CAST(direction AS CHAR), '", "distance":"', CAST(distance AS CHAR), '", "locationMethod":"', locationMethod, '", "gpsTime":"', DATE_FORMAT(gpsTime, '%b %e %Y %h:%i%p'), '", "userName":"', userName, '", "phoneNumber":"', phoneNumber, '", "sessionID":"', CAST(sessionID AS CHAR), '", "accuracy":"', CAST(accuracy AS CHAR), '", "extraInfo":"', extraInfo, '" }') json

FROM (SELECT MAX(GPSLocationID) ID

      FROM gpslocations

      WHERE accountID = accID && sessionID != '0' && CHAR_LENGTH(sessionID) != 0 && gpstime != '0000-00-00 00:00:00'

      GROUP BY sessionID) AS MaxID

JOIN gpslocations ON gpslocations.GPSLocationID = MaxID.ID

ORDER BY gpsTime;

END

[Muddy_Funster]

When building SQL statement strings to use bound variables you either need to set the parameter marker by name using a preface of a colon or numerically using question marks as placeholders.

 

e.g.

named.

$sql = <<<QUERY_STRING
SELECT col1, col2, col3 col4 
FROM table1
WHERE param1 = :var
QUERY_STRING;
...
$stmt->bindParam(':var', $variable, PDO::PARAM_INT); // change third part to suit parameter type

 

numerical.

$sql = <<<MY_QUERY
SELECT col1, col2, col3, col4
FROM table1
WHERE param1 = ?
MY_QUERY;
...
$stmt->bindParam(1, $var, PDO::PARAM_INT); // again, change third part to suit parameter type.