Using JQuery AJAX and php to fetch data to a mysql database

Question

Im trying to insert data into my database with AJAX but dont working. I can verify that im connected to the database but when i click it doesnt insert the data. thanks

with a click function i take the 2 parameter that i wanna insert in my database.

$( "#q_answer1" ).click(function () {
      var q_no = $("#q_no").val(); 
      var main_no = $("#total_no").val();

      $.ajax({
         url: "server.php",
         type: "post",
         async: false,
         data: {
            "done": 1,
            "username": q_no,
            "comment": main_no

         }, 
         success: function(){
            $("#q_no").val('');
            $("#total_no").val('');
         }
      });
  });

And here is the php file, first connect to the ddbb and insert the 2 values with the mysql_query.

<?php
include("dbh.php");
if (isset($_POST['done'])) {
   $q_no = mysql_escape_string($_POST['username']);
   $total_no = mysql_escape_string($_POST['comment']);

   mysql_query("INSERT INTO variables(id, names) VALUES('{$q_no}', '{$total_no}')");
   exit();
}
?>

html is like this:

<div id="total_no">1</div>
<div id="q_answer1" class="btn left_b">yes</div>

and please see https://stackoverflow.com/questions/13944956/the-mysql-extension-is-deprecated-and-will-be-removed-in-the-future-use-mysqli — Junius L, Apr 22 '19 at 08:27
PDO tutorial http://wiki.hashphp.org/PDO_Tutorial_for_MySQL_Developers — Junius L, Apr 22 '19 at 08:31

Junius L · Accepted Answer · 2019-04-22T09:11:20.857

I think you should use PDO, to connect to the database instead of the old driver, which PHP no longer supports. with PDO you can use prepared statements to prevent sql injections

PDO tutorial

filter_var() Constants

dbh.php

$servername = "localhost";
$username = "user";
$password = "pass";
$dbname = 'db';

try {

  $db = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
    // set the PDO error mode to exception
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  }
  catch(PDOException $e)
  {
    exit($e->getMessage());
  }

?>

serve.php

<?php

include("dbh.php");
if (isset($_POST['done'])) {

   $q_no = filter_var($_POST['username'], FILTER_SANITIZE_STRING);
   $total_no = filter_var($_POST['comment'], FILTER_SANITIZE_STRING);

try {

  $stmt = $db->prepare("INSERT INTO variables(id, names) VALUES(?, ?)");
  $stmt->execute(array($q_no, $total_no));

  echo json_encode(["message" => "success"]); // sends success response to front-end 

} catch (\Exception $e) {
  echo json_encode(["message" => $e->getMessage() ]); // sends error response to front-end
}


}
 ?>

in your ajax check if the data was inserted or not.

$("#q_answer1").click(function() {
  var q_no = $("#q_no").val();
  var main_no = $("#total_no").val();

  $.ajax({
    url: "file.php",
    type: "post",
    async: false,
    data: {
      "done": 1,
      "username": q_no,
      "comment": main_no

    },
    success: function(data) {

      const respose = JSON.parse(data);

      if (respose.message === 'success') { // data was inserted 

        $("#q_no").val('');
        $("#total_no").val('');

      }else {
        alert(respose.message); // some error has occured
      }
    }
  });
});

@junius-ln i got this error after implementing your code: SQLSTATE[HY000]: General error: 1366 Incorrect integer value: '' for column 'id' at row 1 — Jacofki, Apr 22 '19 at 09:18
@Jacofki it means you tried to insert a string into integer column. https://stackoverflow.com/questions/29462919/error-1366-hy000-incorrect-integer-value-for-column-id-at-row-1 — Junius L, Apr 22 '19 at 09:21

Rakesh Jakhar · Answer 2 · 2019-04-22T07:11:56.917

0

You have typo error in jquery

$qAnswer1.click(function () {

Should be

$('#q_answer1').click(function () {

You can try following to test

$( "#q_answer1" ).click(function() {
  alert( "Handler for .click() called." );
});

Include the jquery at the top of your page

<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>

Full working code

<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<div id="total_no">1</div>
<div id="q_answer1" class="btn left_b">yes</div>
<script type="text/javascript">
  $( "#q_answer1" ).click(function () {
    var q_no = $("#q_no").val(); 
    var main_no = $("#total_no").val();

  $.ajax({
     url: "ajax.php",
     type: "post",
     async: false,
     data: {
        "done": 1,
        "username": q_no,
        "comment": main_no

     }, 
     success: function(){
        $("#q_no").val('');
        $("#total_no").val('');
     }
  });
});
</script>

in your PHP file try to print $_POST to see its working.

edited Apr 22 '19 at 07:11

answered Apr 22 '19 at 06:51

Rakesh Jakhar

6,380
2
11
20

@Jacofki have you included jquery at the top ? – Rakesh Jakhar Apr 22 '19 at 07:03
@its working, if you click on yes , try the handler code – Rakesh Jakhar Apr 22 '19 at 07:07
Thanks @rakesh-jakhar, after that change i get a 500 (Internal Server Error) in the server.php (for you ajax.php). – Jacofki Apr 22 '19 at 08:21
1

make sure the path of server.php – Rakesh Jakhar Apr 22 '19 at 08:42
is correct, and also i get this message: [Deprecation] Synchronous XMLHttpRequest on the main thread is deprecated because of its detrimental effects to the end user's experience. For more help, check https://xhr.spec.whatwg.org/. – Jacofki Apr 22 '19 at 09:09

score 0 · Answer 3 · answered Apr 22 '19 at 07:10

You have to take value of div as mentioned below,

var q_no = $("#q_no").text(); 
var main_no = $("#total_no").text();

Pass data in key-value Pair, After pass first key-value data concate other data with & sign key.

$( "#q_answer1" ).click(function () {
          var q_no = $("#q_no").val(); 
          var main_no = $("#total_no").val();

          $.ajax({
             url: "server.php",
             type: "post",
             async: false,
             data: 'done=' + 1 + '&username=' + q_no + '&comment=' + main_no,
             success: function(){
                $("#q_no").val('');
                $("#total_no").val('');
             }
          });
      });

score 0 · Answer 4 · answered Apr 22 '19 at 07:11

0

You can't use val() on div. Try using text() and then check if your server.php is getting these value.

Thanks

answered Apr 22 '19 at 07:11

Kunal Virk

132
5

Using JQuery AJAX and php to fetch data to a mysql database

4 Answers4