php

mysql

mysqli

How to run multiple insert query in SQL using PHP in one go?

Viewed 67 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

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.

Monday, September 19, 2022

psabbate

<?php
$sql = new mysqli('127.0.0.1','root','Qwert12345','plot_io_db');
//echo $sql->query('Select * From players');
?>

It will work. Just remove port from localhost (127.0.0.1)

Tuesday, August 30, 2022

noel_yap

There are several ways to achieve this, but if you prefer the query builder way

$results = Yii::app()->db->createCommand()->
          select('id, filename, uploadDate')->
          from('files')->
          order('uploadDate DESC')->
          limit(5)->
          queryAll();

var_dump($results);

Read this documentation for more detail : http://www.yiiframework.com/doc/guide/1.1/en/database.query-builder

Friday, October 28, 2022

nitin_dominic

mysqli_connect("","username" ,"password","databasename");//Server name cannot be NULL

use loaclhost for server name(In Loacl)

<?php
    $con = mysqli_connect("localhost","username" ,"password","databasename");

    if (mysqli_connect_errno())
    {
        echo "Failed to connect to MySQL: " . mysqli_connect_error();
    }
?>

Or can use MySQLi Procedural

<?php
    $servername = "localhost";
    $username = "username";
    $password = "password";

    // Create connection
    $con = mysqli_connect($servername, $username, $password);

    // Check connection
    if (!$con) {
        die("Connection failed: " . mysqli_connect_error());
    }
    echo "Connected successfully";
?>

EDIT 01

$servername = "localhost";
$username = "root";
$password = "";

Monday, October 10, 2022

arkadiusz_nowak

to run two commands use &. Both commands will be executed:

dir file.txt & echo done

Use && to execute the second command only if the first command was successful:

dir existentfile.txt && echo done

Use || to run the second command only if the first command failed:

dir nonexistentfile.txt || echo not found

You can combine:

dir questionablefile.txt && (echo file exists) || (echo file doesn't exist)

Monday, September 12, 2022

puneet

Only authorized users can answer the search term. Please sign in first, or register a free account.

Not the answer you're looking for? Browse other questions tagged :

php