Php Ajax how insert data into two tables same time

Question

I'm trying to insert data into two different tables. My problem is that it only inserts into one table the "pm_imbox",I am not familiar with ajax so is there anyway that i can do this using ajax ?

Here is my code

        <?php

    require_once"db.php";
    $to_username = $_POST['to_username'];
    $title = $_POST['title'];
    $message = $_POST['message'];
    $to_userid = $_POST['to_userid'];
    $userid = $_POST['userid'];
    $request_id = $_POST['request_id'];
    $from_username = $_POST['from_username'];
    $senddate = $_POST['senddate'];

    $stmt = $DBcon->prepare("INSERT INTO pm_outbox(userid,username,to_userid,to_username,title,content,ReqID,senddate)VALUES('$userid','$from_username','$to_userid','$to_username','$title','$message','$request_id','$senddate')");

    $stmt = $DBcon->prepare("INSERT INTO pm_imbox(userid,username,from_id,from_username,title,content,ReqID,recieve_date)VALUES('$to_userid','to_username','$userid','$from_username','$title','$message','$request_id','$senddate')");

    $stmt->bindparam(':to_username', $to_username);
    $stmt->bindparam(':title', $title);
    $stmt->bindparam(':message', $message);
    $stmt->bindparam(':to_userid', $to_userid);
    $stmt->bindparam(':userid', $userid);
    $stmt->bindparam(':request_id', $request_id);
    $stmt->bindparam(':from_username', $from_username);
    $stmt->bindparam(':senddate', $senddate);

    if($stmt->execute())
    {
      $res="Data Inserted Successfully:";
      echo json_encode($res);
    }
    else {
      $error="Not Inserted,Some Probelm occur.";
      echo json_encode($error);
    }



     ?>

I am guessing it might be the $stmt->bindparam, I do not know how to config them to link with the query This is what i have done so far with my ajax JavaScript file.

        <script type="text/javascript">

      function insertData() {
        var to_username=$("#to_username").val();
        var title=$("#title").val();
        var message=$("#message").val();
        var to_userid=$("#to_userid").val();
        var userid=$("#userid").val();
        var request_id=$("#request_id").val();
        var from_username=$("#from_username").val();
        var senddate=$("#senddate").val();


    // AJAX code to send data to php file.
            $.ajax({
                type: "POST",
                url: "reply_process.php",
                data: {to_username:to_username,title:title,message:message,to_userid:to_userid,userid:userid,request_id:request_id,from_username:from_username,senddate:senddate},
                dataType: "JSON",
                success: function(data) {
                 $("#message").html(data);
                $("p").addClass("alert alert-success");
                },
                error: function(err) {
                alert(err);
                }
            });

    }

      </script>

Ajax is a way of sending a request to your server. From the perspective of this PHP script, the fact that it's called through Ajax is of zero imporance. It just gets a chunk of (post) data and has to deal with it. In fact, in many cases it's easier to test a script like this using a plain old HTML form, or using a testing tool like [Postman](https://www.getpostman.com/). — GolezTrol, May 18 '18 at 12:07
You prepare the first statement, and then prepare the second statement and store it in the same variable, `$stmt`. That variable from that point on only holds the seconds statement. The assignment doesn't magically transform $stmt into a list of statements to execute. Store them in separate variables, `$stmt1` and `$stmt2` (or more descriptive names) and call `->execute()` for each of them. You may want to read a little about transactions too, to make sure that if the second fails, the first is rolled back too (atomic operation). — GolezTrol, May 18 '18 at 12:09

score 0 · Answer 1 · answered May 18 '18 at 12:11

Your code has one variable $stmt which holds the query. There are two lines which assign query string to the same variable $stmt and so the final query $stmt variable will be having is pm_imbox because it will replace the 1st assigned query for pm_outbox

Correct it and you will be good to go:

$stmt1 = $DBcon->prepare("INSERT INTO pm_outbox(userid,username,to_userid,to_username,title,content,ReqID,senddate)VALUES('$userid','$from_username','$to_userid','$to_username','$title','$message','$request_id','$senddate')");

$stmt1->bindparam(':to_username', $to_username);
$stmt1->bindparam(':title', $title);
$stmt1->bindparam(':message', $message);
$stmt1->bindparam(':to_userid', $to_userid);
$stmt1->bindparam(':userid', $userid);
$stmt1->bindparam(':request_id', $request_id);
$stmt1->bindparam(':from_username', $from_username);
$stmt1->bindparam(':senddate', $senddate);

$stmt = $DBcon->prepare("INSERT INTO pm_imbox(userid,username,from_id,from_username,title,content,ReqID,recieve_date)VALUES('$to_userid','to_username','$userid','$from_username','$title','$message','$request_id','$senddate')");

$stmt->bindparam(':to_username', $to_username);
$stmt->bindparam(':title', $title);
$stmt->bindparam(':message', $message);
$stmt->bindparam(':to_userid', $to_userid);
$stmt->bindparam(':userid', $userid);
$stmt->bindparam(':request_id', $request_id);
$stmt->bindparam(':from_username', $from_username);
$stmt->bindparam(':senddate', $senddate);

if($stmt1->execute() && $stmt->execute())  // This will execute both the queries.
{
    ....
    //further code
    ....

So if `$stmt->execute()` fails, you would display 'Not inserted'.. But `$stmt1` did execute earlier, so that was inserted.. — GolezTrol, May 18 '18 at 12:15
Actually the main issue is, using the same `$stmt` variable. The `if` condition should be placed according to the need. — Himanshu Upadhyay, May 18 '18 at 12:16
It's true that this solves the immediate problem of using the same variable But I mean that your code assumes that the statements either both succeed or both fail, which is not necessarily the case in a real life scenario, which is why you typically want to go a step further and apply a transaction here — GolezTrol, May 18 '18 at 12:17

Kawaljeet Singh · Answer 2 · 2018-05-18T12:17:57.950

The second statement below overrides the first one, that's why only second one executes :

 $stmt = $DBcon->prepare("INSERT INTO pm_outbox(userid,username,to_userid,to_username,title,content,ReqID,senddate)VALUES('$userid','$from_username','$to_userid','$to_username','$title','$message','$request_id','$senddate')");

 $stmt = $DBcon->prepare("INSERT INTO pm_imbox(userid,username,from_id,from_username,title,content,ReqID,recieve_date)VALUES('$to_userid','to_username','$userid','$from_username','$title','$message','$request_id','$senddate')");

So, You if you need to execute both queries in one go, then change the logic as follows.

$stmt1 = $DBcon->prepare("INSERT INTO pm_outbox(userid,username,to_userid,to_username,title,content,ReqID,senddate)VALUES('$userid','$from_username','$to_userid','$to_username','$title','$message','$request_id','$senddate')");

$stmt2 = $DBcon->prepare("INSERT INTO pm_imbox(userid,username,from_id,from_username,title,content,ReqID,recieve_date)VALUES('$to_userid','to_username','$userid','$from_username','$title','$message','$request_id','$senddate')");

if($stmt1->execute() && $stmt1->execute()){
// do you stuff
}

Php Ajax how insert data into two tables same time

2 Answers2