PHP MySQL insert multiple rows with different IDs

Question

I have the following table:

id | message_id | recevier_id
 1 |      8     |    2
 2 |      9     |    5
 3 |     14     |    4

I am sending data to a PHP file to add to the above table. My data is as follows:

messageid = "2" receiver id = "5,6,34"

I am trying to add multiple rows with different "receiver id", so the outcome of the above query should result in :

id | message_id | recevier_id
 1 |      8     |    2
 2 |      9     |    5
 3 |     14     |    4
 4 |      2     |    5
 5 |      2     |    6
 6 |      2     |   34

My current MySQL query looks like this:

<?php
$inputvalues = $_POST;
$errors = false;
$result = false;
session_start();
include_once '../../../includes/database.php';
$uid = $_SESSION['usr_id'];
$sendername = $_SESSION['firstnames'].' '.$_SESSION['surname'];

    if (mysqli_connect_errno()) {
        printf("Connect failed: %s\n", mysqli_connect_error());
        exit();
    }

    foreach ($inputvalues as $key => $value) {
        if(isset($value) && !empty($value)) {
            $inputvalues[$key] = $mysqli->real_escape_string( $value );
        } else {
            $errors[$key] = 'The field '.$key.' is empty';
        }
    }

    if( !$errors ) {
        $mysqli->query("
            INSERT INTO `message_receiver_map` (`message_id`, `receiver_id`) VALUES ('".$messageid."', '".$inputvalues['receiverid']."');
        ");
        $returnResult = "Success";
    }
    mysqli_close($mysqli);
    echo json_encode(['result' => $returnResult, 'errors' => $errors]);
    exit;
?>

How can I achieve this?

`insert into table (..) values(messageid,reciverid[0]),(messageid,reciverid[1]),(messageid,reciverid[2])` of course as prepared Statements to prevent SQL injection — Jens, Jul 05 '17 at 14:15
Show us all the PHP code you are using to process that input into a query — RiggsFolly, Jul 05 '17 at 14:16
@Jens How can I make it an array? (I am assuming it is). The ids are sent via ajax/jquery as a string, eg(1,2,4) etc. — Bruno, Jul 05 '17 at 14:20
@bruno php has functions like [explode](http://php.net/manual/en/function.explode.php) — Jens, Jul 05 '17 at 14:21
Your script is at risk of [SQL Injection Attack](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) Have a look at what happened to [Little Bobby Tables](http://bobby-tables.com/) Even [if you are escaping inputs, its not safe!](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) Use [prepared parameterized statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) — RiggsFolly, Jul 05 '17 at 14:21
@RiggsFolly Thanks for the input. I am just starting to learn PHP and I am making this for fun and not for a commercial product or anything. Learning how to prevent SQL injections etc are on my to-do list once I learn the basics :) — Bruno, Jul 05 '17 at 14:22
@Jens I won't know how many ids will be passed to my $receiverid variable so would I have to do a loop? — Bruno, Jul 05 '17 at 14:23
@Bruno yes explode your string and for every entry add the values to insert — Jens, Jul 05 '17 at 14:24

score 1 · Answer 1 · edited Jul 05 '17 at 14:42

You can do something like this:

$messageid = "2";         // received value
$receiverids = "5,6,34";  // received value
$receiverid = "";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// prepare and bind
$stmt = $conn->prepare("INSERT INTO `table` (`message_id`, `receiver_id`) VALUES (?, ?)");
$stmt->bind_param("ss", $messageid, $receiverid);

foreach($rid in explode($receiverids)) {
   // set parameters and execute
   $receiverid = $rid;
   $stmt->execute();
}

$stmt->close();

Sorry, edited your question instead of mine. Beg your pardon — RiggsFolly, Jul 05 '17 at 14:43

score 0 · Accepted Answer · edited Jul 09 '17 at 14:52

Assuming 2 field in $_POST exist called

$_POST['messageid'] = "2" 
$_POST['receiver id'] = "5,6,34"

Then the creation of the 3 rows can be done like this

Remember that once a query with parameters has been prepared, it can be reused any number of times, with new parameter values each time it is executed.

<?php
    session_start();
    include_once '../../../includes/database.php';

    // this if should probably be in your database.php script
    if (mysqli_connect_errno()) {
        printf("Connect failed: %s\n", mysqli_connect_error());
        exit();
    }

    if ( isset($_POST['messageid'],$_POST['receiver id']) ){ 
        // proceed to create the new rows

        //create a query
        $sql = "INSERT INTO `message_receiver_map` 
                        (`message_id`, `receiver_id`) 
                VALUES (?,?)";

        // prepare the query (send to server and compile it)
        // now this query can be run multiple times with different 
        // parameter set each time it is executed
        $stmt = $mysqli->prepare($sql);

        // now for each receiver_id run the query
        $rec_ids = explode($_POST['receiver id']);

        foreach ($rec_ids as $rec_id) {
            // bind the 2 parameters from this iteration to the query
            $stmt->bind_value('ii', 
                                $_POST['messageid'],
                                $rec_id);
            // run query with these new params
            $result = $stmt->execute();
        }
    }
}
?>

If you are using a INNODB database you could also wrap a transaction around this so the you get all 3 rows created or none at all if an error occurs somewhere while creating the 3 new rows.

Thank you for taking the time to comment the code, it really helped me understand what was going on, much appreciated sir! — Bruno, Jul 05 '17 at 14:48

PHP MySQL insert multiple rows with different IDs

2 Answers2