Asked  7 Months ago    Answers:  5   Viewed   29 times

I want to run mysql queries to insert data from a custom html form. Here I have to insert multiple set of data, some data to one table and some to other. Currently I am using the simple approach to send data to php page using jquery ajax and run multiple mysqli_query() functions one after another. But I guess when there will be large number of users, there will be problems related to speed. So can anyone suggest me a better way to do the same.

There are 5 tables in the database and each table has 7 to 10 columns that need to get different set of data, every time.

I want to run each query only if the previous insert query is successfully completed. That's why I am checking for the result every time and then running the next query, which makes me feel the issue of speed on large user base.

 Answers

16

The problem is, I need to insert data to first table and if its successfully inserted then only run query for the second table.

This means you need a transaction.

A transaction is a set of queries that either all execute ok or if one fails - they all fail. This is to ensure you don't end up with crap data in your tables.

Do not

  • Do not use multiquery.
  • Do not use mysql_* function(s).
  • Do not use bulk inserts.

People telling you to do that just have absolutely no clue what they're doing, ignore them.

Do

  • Use PDO
  • Use prepared statements
  • Prepare the statement(s) ONCE, use them MULTIPLE times

Sample code - do NOT copy paste

$dsn = 'mysql:dbname=testdb;host=127.0.0.1;charset=utf8mb4';
$user = 'dbuser';
$password = 'dbpass';

$pdo = new PDO($dsn, $user, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$insert['first'] = $pdo->prepare("INSERT INTO table SET col1 = :val, col2 = :val2");
$insert['second'] = $pdo->prepare("INSERT INTO another_table SET col1 = :val, col2 = :val2"); 

$pdo->beginTransaction();

$insert['first']->bindValue(':val', 'your value');
$insert['first']->bindValue(':val2', 'anothervalue');
$insert['first']->execute();

$insert['second']->bindValue(':val', 'your value');
$insert['second']->bindValue(':val2', 'anothervalue');
$insert['second']->execute();

$pdo->commit();

The code above will save the data in two tables ONLY if both inserts are successful.

Wednesday, March 31, 2021
 
mario
answered 7 Months ago
41

Use single insert query for three insert operations like this:

<?php
$insert = "INSERT INTO `more_regular` (`product_detail`, `unit`, `quantity`, `price`) VALUES ";
$values = array();
foreach ($type as $a=>$b) {
    $values[] = "('$detail_product[0]', '$unit[0]', '$quantity[0]', '$price[0]')";
    $values[] = " ( '$detail_product[1]', '$unit[1]', '$quantity[1]', '$price[1]')";
    $values[] = " ('$detail_product[2]', '$unit[2]', '$quantity[2]', '$price[2]')";
} // above foreach ends here
if (! empty($values)) {
    $insert .= implode(', ', $values);
}
mysql_query($insert);
?>

Basically, the logic is:

INSERT INTO TABLE (ID, NAME) VALUES
(1,'Andrew'),
(2,'Glenn'),
(3,'Marvel');
Saturday, May 29, 2021
 
Zeth
answered 5 Months ago
71

you can't print the result from mysqli_query, it is mysqli_resource and for dumping the error you need to change mysql_error() to mysqli_error()

$username = "bob";
$db = mysqli_connect("localhost", "username", "password", "user_data");
$sql1 = "select id from user_information where username='$username'";
$result = mysqli_query($db, $sql1) or die(mysqli_error());
while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) { 
    echo $row['id'].'<br>'; 
} 
Saturday, May 29, 2021
 
pamelus
answered 5 Months ago
61

SQLite

This is available using the SQLite last_insert_rowid() function:

The last_insert_rowid() function returns the ROWID of the last row insert from the database connection which invoked the function. The last_insert_rowid() SQL function is a wrapper around the sqlite3_last_insert_rowid() C/C++ interface function.

PHP

The PHP version/binding of this function is sqlite_last_insert_rowid():

Returns the rowid of the row that was most recently inserted into the database dbhandle, if it was created as an auto-increment field.

Friday, June 25, 2021
 
redrom
answered 4 Months ago
24

I don't think it will work this way. When you close the statement (e.g. $menu_stmt->close();) you also deallocate the statement handle. So the second time through the loop you don't have the prepared statements available to work with anymore.

Try closing the statements after the loop has finished executing.

Friday, August 6, 2021
 
NIKHIL
answered 3 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :