Insert multiple values sent from AJAX into sql database

Question

hi have this code which works perfectly with just value:

    <script type="text/javascript">
    $(document).ready(function() {
    $("input[type=button]").click(function () {
    var textarea_content = $('textarea#wall').val(); // get the content of what user typed    (in textarea) 
    if (textarea_content != '') { // if textarea is not empty 
    var sender =  "<?php echo $_SESSION['user_id'];?>";

     $.ajax({
            type: "POST",
            url: "send_message_function.php",
            data : "action=send&from_id="+sender+"&to_user="+$(this).siblings("input[type=text]").val()+"&message="+textarea_content,
            dataType: "json",
            success: function (data) {
               var  LastID = data["cid"];
               alert("toUser: " + $(this).siblings("input[type=text]").val()+"and text area " + textarea_content + " message id: " + LastID);
                                     }
        });//end success function

        //do something else

        } else {
        alert('Enter some text ! '); // just in case somebody click on share witout writing anything :)
    }
    });//end click function
    });//end ready function
    </script>

and send_message_function.php :

<?php
 require 'core/functions/init.php';

 $action  = $_POST['action'];
 if($action=="send"){
 $from_id = mysql_real_escape_string($_POST['from_id']);
 $to_id = mysql_real_escape_string($_POST['to_user']);
 $message = strip_tags($_POST['message']);

 $sql = mysql_query("INSERT INTO chat (from_id, to_id, message,   dt)VALUES('$from_id','$to_id','$message',NOW())") or die("0");

 echo json_encode(array("cid"=>(mysql_insert_id()),
                               "from_id"=>''.$message_id.''));
}
    ?>

the problem is when I try to send the message to multiple users. The sql query try to insert all the users IDs in the same row. I know I should do some loop but I can't think how run the query for every users I want to send the message to.

$(this).siblings("input[type=text]").val()

returns multiple users id like this: 113,143,234

You don't need to do a loop: electrictoolbox.com/mysql-insert-multiple-records — Peon
– Peon, Commented Sep 28, 2012 at 14:53

Teena Thomas · Accepted Answer · 2012-09-28 15:06:25Z

1

you do not need a loop, you can do multiple inserts with just one query, like this:

 $to_id = explode(',', mysql_real_escape_string($_POST['to_user'])); //split the string to an array containing each id

 $sql = mysql_query("INSERT INTO chat (from_id, to_id, message, dt) VALUES('$from_id','$to_id[0]','$message',NOW()), ('$from_id','$to_id[1]','$message',NOW()), ('$from_id','$to_id[2]','$message',NOW()) ") or die("0");

Note: $from_id shouldn't be a primary key, else you would get a duplicate key error.

Edit: If you do not know the no. of users,

   $to_id = explode(',', mysql_real_escape_string($_POST['to_user']));

   foreach ($to_id as $v)
     $sql = mysql_query("INSERT INTO chat (from_id, to_id, message, dt) VALUES('$from_id','$v','$message',NOW()) ") or die("0");

edited Sep 28, 2012 at 15:06

answered Sep 28, 2012 at 14:56

Teena Thomas

5,2091 gold badge15 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Mat Over a year ago

but, in this example is the query limited to just 3 users? what about if they are less (or more)? I don't know how many users the message will be sent to. it can be 1 or 10.

Teena Thomas Over a year ago

you can send as many as you want, since your eg. contained 3 (113,143,234) i only mentioned 3. if you do not know the no. of users, you would prolly need a loop, to check if that value isset, in that case, you cannot use only 1 query. you would have to do single inserts multiple times.

Teena Thomas Over a year ago

i edited my answer, in case you do not know the no. of users.

Renan Cunha · Accepted Answer · 2012-09-28 14:54:43Z

1

You need to split the users ids string in the comma character, then execute a query for each value founded.

answered Sep 28, 2012 at 14:54

Renan Cunha

3091 gold badge3 silver badges9 bronze badges

1 Comment

Ian Over a year ago

True, but you can do it in one query, like the other answer provided. Maybe show an example for the OP?

Paul Tregoing · Accepted Answer · 2012-10-02 14:21:07Z

1

To insert multiple rows in one SQL query, do this

INSERT INTO foo(a, b) VALUES('c', 'd'), ('e', 'f'), ('h', 'i');

You'll need to loop to build each VALUES set, but you can do the insert in a single query.

So something like

<?php
    $to_id = explode(',', mysql_real_escape_string($_POST['to_user']));

    $sql = 'INSERT INTO chat (from_id, to_id, message) VALUES';
    foreach ($to_id as $id)
    {
        $sql .="('$from_id', '$id', '$message'),";
    }
    rtrim($sql, ','); //strip the final comma
?>

That should form a query along the lines of

INSERT INTO chat(from_id, to_id, message) VALUES('1', '2', 'hello from php'), ('1', '3', 'hello from php'), ('1', '4', 'hello from php')

Doing it this way means only a single query is sent: therefore there's less overhead on communicating with the database which will speed up your script.

edited Oct 2, 2012 at 14:21

answered Sep 28, 2012 at 14:55

Paul Tregoing

1111 silver badge5 bronze badges

1 Comment

Paul Tregoing Over a year ago

Sorry to comment on my own answer but I wanted to point this out again: the other answers - including the one accepted as "best" - are inefficient: they propose sending a query to the DB for each new record. The method I outline above gets all the work done in a single statement.

Alaa Gamal · Accepted Answer · 2012-09-28 14:59:20Z

0

$toIDS = explode(',', mysql_real_escape_string($_POST['to_user']));
foreach($toIDS as $ID){
    $query=mysql_query("INSERT INTO foo(a, b) VALUES('c', 'd')");
    echo (mysql_affected_rows()>0)?$ID .'INSERTED':$ID.' NOT INSERTED';
}

answered Sep 28, 2012 at 14:59

Alaa Gamal

1,1357 gold badges18 silver badges28 bronze badges

1 Comment

Mat Over a year ago

something like this: $toIDS = explode(',', mysql_real_escape_string($_POST['to_user'])); foreach($toIDS as $ID){ $query=mysql_query("INSERT INTO chat (from_id, to_id, message, dt)VALUES('$from_id','$ID','$message',NOW())") or die("0"); echo (mysql_affected_rows()>0)?$ID .'INSERTED':$ID.' NOT INSERTED'; } ??

Collectives™ on Stack Overflow

Insert multiple values sent from AJAX into sql database

4 Answers 4

3 Comments

1 Comment

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

1 Comment

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest