CODEWITHSUNDEEP
phpmysqliprepared-statement
David
David

Reputation: 495

Multiple Prepared statements in PHP with MySQLi

I want to make two prepared statements, one right after the other in PHP with MySQLi. I am a novice at PHP and MySQLi so I don't know whether I should close the statement, close the database connection, put all of the code in a function, or just have code not inside a function.

Basically, I just want to insert a record into one table and then insert the same record into another table using MySQLi.

Upvotes: 8

Views: 26970

Answers (3)

RumpRanger
RumpRanger

Reputation: 654

Directly off the mysqli page: http://php.net/manual/en/mysqli.commit.php

<?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli("localhost", "root", "", "");
$mysqli->set_charset('utf8mb4');

/* set autocommit to off */
$mysqli->autocommit(FALSE);

$stmt1 = $mysqli->prepare("INSERT INTO tbl1 (id, intro) VALUES (?, ?)");
$stmt2 = $mysqli->prepare("INSERT INTO tbl2 (id, name) VALUES (?, ?)");

$str1 = 'abc';
$str2 = 'efg';
$str3 = 'hij';
$str4 = 'klm';

$stmt1->bind_param('ss', $str1, $str2);
$stmt2->bind_param('ss', $str3,$str4);

$stmt1->execute();
$stmt2->execute();

/* commit and set autocommit to on */
$mysqli->autocommit(true);

Upvotes: 12

cazort
cazort

Reputation: 806

There are several different questions in here and I can try to answer all of the remaining ones. The answer by RumpRanger, with the edits from Dharman, shows the details of how to do the prepared statements.

You want to keep the database connection open while you do all your queries. You rarely ever want or need to close a connection; it will close automatically when the script finishes execution. When I code in PHP I only close the connection in scripts where something complex is going to happen after the connection is needed. Even this type of use, however, has become less beneficial over time because newer versions of PHP have built-in optimization and clean up certain variables automatically if they see they are not used later in a script, and destruction of objects associated with a MySQL connection will close the connection automatically.

Whether or not to put the code in a function depends on whether or not you intend to generalize or abstract what you are doing in the query. If you anticipate needing to do the same query but with different parameters, multiple times from multiple places in your code, then put it in a function. If you know this isn't going to be the case, then just write the code out.

Upvotes: 0

Your Common Sense
Your Common Sense

Reputation: 158009

whether I should close the statement

No.

close the database connection

No.

put all of the code in a function

Preferably, if you have an idea what functions are and how to use them.

BTW, inserting the same data twice makes no sense. You have to link data, not double it.

Upvotes: 0

Related Questions