SELECT doesn't retrieve updated data from DB after INSERT

Question

On one page I'm sending AJAX-request to itself. After getting this request in success-function with PHP I'm adding new record to table "some_table" in some MySQL database. And after that in the same success-function I'm selecting data from this table and showing it on the page by JQuery. The problem is that all old records are shown but not the record which I've added just:

$.ajax({
    url: 'http://somesite.com', 
    type: "POST",
    data: ({param1: value1, param2: value2, act: "I"}),
    success: function(data){
        <?php $mysqli = new mysqli("some_server", "some_database", "some_login", "some_password"); 
              $query = "INSERT INTO some_table VALUES ("'.$_POST["param1"].'","'.$_POST["param2"].'")";
              $mysqli->query($query);
              $mysqli->close();
        ?>
        alert("New record was added");
        <?php echo '$("#left_region").html(\'\');';
              $mysqli = new mysqli("some_server", "some_database", "some_login", "some_password"); 
              $result = $mysqli->query("SELECT some_column1, some_column2 FROM some_table"); 
              while($obj = $result->fetch_object()) { 
                  echo '$("#left_region").append("<p class=\"some_class\"><a href=\"#\">'.$obj->some_column1.'</a></p>");';
              } 
              $result->close();
              $mysqli->close();
        ?>
    }
});

Could anybody hint how to solve it?

- Using MySQLi, but still code prone to SQL injection. So what debugging have you done to ensure that your new record was added? And why do you thing that browser-side javascript can contain php code? — Mark Baker, Aug 09 '13 at 13:28
More helpfully, [take a look at this Dmitry](http://stackoverflow.com/a/60496/1553081) — ಠ_ಠ, Aug 09 '13 at 13:30
You will be write `success` block query in the `http://somesite.com/index.php` file. — ops, Aug 09 '13 at 13:30
You are mixing php and javascript. Your php only runs when the page is **rendered**, not in the actual success block of the ajax call. — Jason P, Aug 09 '13 at 13:30
@Mark Baker - just looked in DB and saw that record was really added. — Dmitry Novikov, Aug 09 '13 at 13:33
I should place the php code on the server. This code is very vulnerable for database thefts since a hacker can simply see ALL required datanames. — KarelG, Aug 09 '13 at 13:34
I'm impressed if it was added by the ajax call... you've managed what millions of the greatest minds have been trying to achieve for years, PHP being executed by a browser... please share your secret — Mark Baker, Aug 09 '13 at 13:35
@DmitryNovikov Yes, it was added because when the page is created on the server any php code on the page runs, regardless of javascript conditionals/control structures. Both of those sql queries will be run on every page load before the page is even sent to the user's browser regardless of the return of your ajax call. — Bad Wolf, Aug 09 '13 at 13:38
@Bad Wolf Ok, understand. Does exist any correct solution to solve this task - update data in database by AJAX-request and to show updated results on the page. — Dmitry Novikov, Aug 09 '13 at 13:45
Mark no need to be sarcastic. Either help or leave. Please (if you can) put a constructive response. — calexandru, Aug 09 '13 at 13:51

score 3 · Accepted Answer · edited Feb 29 '20 at 15:25

You can't expect for a page to execute the code again if the page has already loaded.

Take this as an example:

Client-side:

$.post("some_url.php",{param1: value1, param2: value2, act: "I"},
      function(response){
         if(isNaN(response)==="true")
             $("#left_region").html(response);
         else
             alert("An error ocurred");
      }
)

Server-side:

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli("some_server", "some_database", "some_login", "some_password");
if ($_POST['act']) {
    $stmt = $mysqli->prepare("INSERT INTO some_table VALUES (?, ?)");
    $stmt->bind_param('ss', $_POST["param1"], $_POST["param2"]);
    $stmt->execute();

    $last_inserted_id = $mysqli->insert_id;
    $stmt = $mysqli->prepare("SELECT some_column1, some_column2 FROM some_table where some_column=?");
    $stmt->bind_param('s', $last_inserted_id);
    $stmt->execute();
    $result = $stmt->get_result();
    echo $result->fetch_assoc();
}

Try something like this(if someone finds an error, please correct it, thanks).

SELECT doesn't retrieve updated data from DB after INSERT

1 Answers1