Multiple Prepared statements in PHP with MySQLi

Question

I want to do 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.

Thanks!

Solution

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", "my_user", "my_password", "world");
$mysqli->set_charset('utf8mb4');

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

/* Insert some values */
$mysqli->query("INSERT INTO table1 VALUES ('DEU', 'Bavarian', 'F', 11.2)");
$mysqli->query("INSERT INTO table2 VALUES ('DEU', 'Bavarian', 'F', 11.2)");

/* commit transaction */
$mysqli->commit();

/* close connection */
$mysqli->close();

*Edit with prepared statements for "non-sane" action:

<?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);

Comments

[Your Common Sense]

I see no prepared statements here

[Admin]

so right after I execute my first prepared statement can I go about preparing and executing my second one?

[Your Common Sense]

No. Is I said above, it makes no sense. You already inserted your data, no need to insert it again

[Admin]

yea I can see how to do it without prepared statements, but I don't know how to deal with the over head of prepared statements, but thanks anyways :)

[Admin]

it's not the exact same data, so I do need to insert it again

[Your Common Sense]

How come it is not exact the same if you need to insert it again?

[Admin]

i just want to do an insert after another insert

[RumpRanger]

Why shouldnt he? An action may require inserts to two different tables.

[Your Common Sense]

Nope, no sane action would require that

[RumpRanger]

Without being provocative, how would you suggest handling a form where one might insert an entity and its relationship as selected from a multi select?

[Your Common Sense]

@CharlesWeiss as is: nsert an entity and its relationship, NOT entity twice