Execute Multiple Queries in PHP with NULL as an Input Parameter

Question

EDIT (2011-07-23)

Have gotten some very helpful answers, both of which I've tried implementing. But I can't seem to get back the id from my Get_Security statement. I'm pretty sure my problem is that, in my first call statement Get_Security, the last three parameters are set to NULL. Seems like other people have the same problem. Doesn't seem like there's much documentation on having NULL as an input. How does one go about this?

NEW CODE

$stmt = mysqli_stmt_init($link);
$sql = "CALL Get_Security('$symbol', '$tagName', NULL, NULL, NULL)";
if (!mysqli_stmt_prepare($stmt, $sql)){
    $error = 'Failed to prepare statement. Error No: ' . mysqli_errno($link) . ': ' . mysqli_error($link);
    include '../error.html.php';
    exit();
}
mysqli_stmt_execute($stmt);
$result = mysqli_stmt_get_result($stmt);
while ($row = mysqli_fetch_assoc($result)) {
    $id = $row['id'];
}
mysqli_stmt_close($stmt);
mysqli_close($link);

include $_SERVER['DOCUMENT_ROOT'] . 'mypath-to-database-link';  //this gets $link
$stmt = mysqli_stmt_init($link);
$sql = "CALL Add_Active('$id','Research')";
if (!mysqli_stmt_prepare($stmt, $sql)){
    $error = 'Failed to prepare statement Add_Active. Error No: ' . mysqli_errno($link) . ': ' . mysqli_error($link);
    include '../error.html.php';
    exit();
}
mysqli_stmt_execute($stmt);
mysqli_stmt_close($stmt);
mysqli_close($link);

include $_SERVER['DOCUMENT_ROOT'] . 'mypath-to-database-link'; //this gets $link
$sql = "INSERT INTO MyTable SET
        id='$id',
        open_items='$openItems',
        attachments='$attachments'
    ";
$stmt = mysqli_stmt_init($link);
if (!mysqli_stmt_prepare($stmt, $sql)){
    $error = 'Failed to INSERT INTO Research_Security. Error No: ' . mysqli_errno($link) . ': ' . mysqli_error($link);
    include '../error.html.php';
    exit();
}
mysqli_stmt_execute($stmt);
mysqli_stmt_close($stmt);
mysqli_close($link);

ORIGINAL ENTRY

Searched extensively (e.g. PHP Manual, SO questions) but answers are confusing.

I need to execute 3 of SQL statements in a row:

Call stored procedure Get_Security that takes some inputs and returns an array, including the id.
Call another stored procedure Add_Active that takes the returned id from Get_Security as an input.
Insert some variables into my table.

Problem: I'm getting the MySQL Error Number 2014: "Commands out of sync; you can't run this command now".

I know I have to use mysqli_stmt_prepare, mysqli_stmt_execute, and mysqli_stmt_close to resolve this, but it's very confusing how to do this.

Would very much appreciate help in how to translate this using the above functions.

CODE:

$sql = "CALL Get_Security('$symbol', '$tagName', NULL, NULL, NULL)";   
$result = mysqli_query($link, $sql);
if (!$result){
        $error = 'Error calling stored procedure Get_Security.';
        include '../error.html.php';
        exit();
}
while($row = mysqli_fetch_array($result)){
    $tags[] = array('id' => $row['id']);
}
foreach ($tags as $tag){
    $id = $tag['id'];
}


$sql = "CALL Add_Active('$id','Research')";
$result = mysqli_query($link, $sql);
if (!$result){
        $error = 'Error calling stored procedure Add_Active. Error No: ' . mysqli_errno($link) . ': ' . mysqli_error($link);
        include '../error.html.php';
        exit();
}

$sql = "INSERT INTO MyTable SET
            id='$id',
            open_items='$openItems',
            attachments='$attachments'
        ";
if (!mysqli_query($link, $sql)){
        $error = 'Error adding submitted tag into Research_Security. Error No: ' . mysqli_errno($link) . ': ' . mysqli_error($link);
        include '../error.html.php';
        exit();
}

Kevin Peno · Accepted Answer · 2011-07-23 00:07:08Z

1

+50

I hope this helps. From what I can tell you aren't doing anything too fancy, so this should suffice. PDO does also support IN/OUT params to stored procedures as well, but I didn't see you using them.

Please note, PDO handles errors in different ways depending on how it is initialized. So I've skipped error handling here. Please let me know if you have questions.

Also note that until you add a DSN (MySQL's for example) this code doesn't care what database type it is, so the DSN can be a config value making your code more portable. I'm sure you could also see how this code could easily be expanded into a class/model structure (specifically the security check SP could become a PHP method)

$db = new PDO(); // http://www.php.net/manual/en/pdo.construct.php for params

// These generate PDO_Statement (see: http://www.php.net/manual/en/class.pdostatement.php)
$securityStmt = $db->prepare("CALL Get_Security( ?, ?, ?, ?, ? )");
$addActiveStmt = $db->prepare("CALL Add_Active( ?, ? )");
$insertStmt = $db->prepare("INSERT INTO MyTable SET id=?, open_items=?, attachments=?");

// Security CALL
$securityStmt->bindParam( 1, $symbol, PDO::PARAM_STR );
$securityStmt->bindParam( 2, $tagName, PDO::PARAM_STR );
$securityStmt->bindParam( 3, NULL, PDO::PARAM_NULL );
$securityStmt->bindParam( 4, NULL, PDO::PARAM_NULL );
$securityStmt->bindParam( 5, NULL, PDO::PARAM_NULL );

$securityStmt->execute();

// Bind the ID to a variable is useful sometimes...
$securityStmt->bindColumn( 'id', $securityId );
$securityStmt->fetch( PDO::FETCH_BOUND );

/*
    Insert + Active call
    These are much simpler because we don't need to set the data types of the input
    (they are all string I hope...you didn't mention what the last 2 were in the insert).
*/

$addActiveStmt->execute(
    array(
        $securityId,
        'Wedge Research'
    )
);

$insertStmt->execute(
    array(
        $securityId,
        $openItems,
        $attachments
    )
);

edited Jul 23, 2011 at 0:07

answered Jul 19, 2011 at 19:19

Kevin Peno

9,2261 gold badge36 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Kamil Sindi Over a year ago

Thanks a lot for your response @Kevin. I have some additional inputs for $insertStmt that are not strings. Is there something else I have to do?

Kamil Sindi Over a year ago

Also I'm not using OOP for PHP. Could I still use this if most of my code is procedural? Can't seem to get the code working and not sure how to error handle.

Kevin Peno Over a year ago

I'd need more info about what is not working. Whether your code is OOP or not doesn't matter. By default PDO is silent on errors (null objects, etc). To see code fail for errors enable exceptions. This will make it easier to troubleshoot issues. (after new PDO(); add $db->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );

Kamil Sindi Over a year ago

I think the problem I'm having is that $securityId is supposed to be the id of the returned value from the $securityStmt call. Is that's what's happening with $stmt->bindColumn( 'id', $securityId ); and $stmt->fetch( PDO::FETCH_BOUND );?

Kevin Peno Over a year ago

@Hydra, yes. $securityId should become populated with the id column of the returned row form the stored procedure. $stmt->fetch( PDO::FETCH_BOUND ); causes the binding to take place (or should in theory).

|

sanmai · Accepted Answer · 2011-07-23 04:24:21Z

0

$stmt = mysqli_stmt_init($link);
mysqli_stmt_prepare($stmt, "CALL SOMETHING()");
mysqli_stmt_execute($stmt);
$result = mysqli_stmt_get_result($stmt);
while ($row = mysqli_fetch_assoc($result)) {
    print_r($row);
}
mysqli_stmt_close($stmt);

answered Jul 23, 2011 at 4:24

sanmai

31.2k12 gold badges72 silver badges76 bronze badges

4 Comments

Kamil Sindi Over a year ago

Hi @sanmai: I'm pretty sure my problem is that in my first call statement Get_Security, the lest three parameters are NULL. Doesn't seem like there's much documentation on having NULL as an input. How does one go about this?

sanmai Over a year ago

Yep, closing a connection every time is a solution, but you shall know that new connections are not free. Have you tried my code though?

Kamil Sindi Over a year ago

Thank you for your comment @sanmai. I have tried your code but it doesn't work. As I remarked, I think the problem is that I have nulls as input parameters.

Kamil Sindi Over a year ago

If you know how to use nulls as inputs, do you mind showing me? I will accept your answer if it works.

Kamil Sindi · Accepted Answer · 2011-07-23 14:49:14Z

So I've figured out how to solve this with my original code by simply closing the link to the database after every query. I would love to do prepared statements instead, but at least this works.

include $_SERVER['DOCUMENT_ROOT'] . 'path-to-connecting-to-db'; //get $link here    
$sql = "CALL Get_Security('$symbol', '$tagName', NULL, NULL, NULL)"; 


$result = mysqli_query($link, $sql);
if (!$result){
        $error = 'Error calling stored procedure Get_Security.';
        include '../error.html.php';
        exit();
}
while($row = mysqli_fetch_array($result)){
    $tags[] = array('id' => $row['id']);
}
foreach ($tags as $tag){
    $id = $tag['id'];
}
mysqli_close($link);
include $_SERVER['DOCUMENT_ROOT'] . 'path-to-connecting-to-db'; //get $link here
$sql = "CALL Add_Active('$id','Research')";
$result = mysqli_query($link, $sql);
if (!$result){
        $error = 'Error calling stored procedure Add_Active. Error No: ' . mysqli_errno($link) . ': ' . mysqli_error($link);
        include '../error.html.php';
        exit();
}
mysqli_close($link);
 include $_SERVER['DOCUMENT_ROOT'] . 'path-to-connecting-to-db'; //get $link here
$sql = "INSERT INTO myTable SET
            id='$id',
            open_items='$openItems',
            attachments='$attachments'
        ";
if (!mysqli_query($link, $sql)){
        $error = 'Error adding submitted tag into Research_Security. Error No: ' . mysqli_errno($link) . ': ' . mysqli_error($link);
        include '../error.html.php';
        exit();
}

Collectives™ on Stack Overflow

Execute Multiple Queries in PHP with NULL as an Input Parameter

3 Answers 3

8 Comments

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

8 Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related