Ajax POST with mysqli database query

Question

I am trying to get Ajax to POST to another.PHP file in the same directory as the main index. The idea is that I want an online diary that people can write into and have it instantly updated in the database.

Currently, my ajax looks like this

    $('#diary').on('input propertychange', function() {
    $.ajax({
        type: "POST",
        url: "updatedatabase.php",
        data: {content: $('#diary').val()},
        success: function(  msg  ) {
             alert('Data Saved: ' + msg);
         },
        dataType: "text"
    });
});

My PHP looks like this, on the updatedatabase.php page

<?php

session_start();

if ($_POST['content']) {

$link = mysqli_connect("location", "username", "password", "databasename");

if (mysqli_connect_error()) {
    echo "Could not connect to database";
    die;
}

$query = "UPDATE `users` SET `Diary` = '".mysqli_real_escape_string($link, $_POST['content'])."' WHERE email = '".mysqli_real_escape_string($link, $_SESSION['email'])."' LIMIT 1";

if (mysqli_query($link, $query)) {
    echo "Success";
} else {
    echo mysqli_error($link);
    echo "Failure";
}

}

?>

Full HTML + PHP at start of main page:

<?php

session_start();

$link = mysqli_connect(databaseinfo);

if (mysqli_connect_error()) {
    echo "Could not connect to database";
    die();
}

if (empty($_COOKIE['userId']) && (empty($_SESSION['email']))) {
    header('Location: index.php');
}


$query = "SELECT email FROM users WHERE email = '".mysqli_real_escape_string($link, $_COOKIE['userId'])."' ";

if (!mysqli_query($link, $query)) {
    header('Location: login.php');
    die();
}

if(isset($_POST['logout'])) {
    unset($_SESSION['email']);
    setcookie('userId', '', time() - 60 * 60);
    header('Location: login.php');
}



?>

<!doctype html>
<html lang="en">
  <head>
    <title>Title</title>
    <!-- Required meta tags -->
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">

    <!-- Bootstrap CSS -->
    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css" integrity="sha384-ggOyR0iXCbMQv3Xipma34MD+dH/1fQ784/j6cY/iJTQUOhcWr7x9JvoRxT2MZw1T" crossorigin="anonymous">

    <style type="text/css">

    body {
        background-image: url("images/landscape.jpg");

    }

    h1 {
        text-align: center;
        margin: 15px auto 30px auto;
    }

    #diary {
        min-height: 40vw;
    }

    </style>

  </head>
  <body>

    <div class="container">
        <div class="container">
        <h1>Secret Diary</h1>   

        <form method="POST">

            <div class="form-group">
            <button type="submit" name="logout" id="logout" class="btn btn-info">Logout</button>   
            </div>

        </form>
        </div>

        <div class="form-group">
            <div class="form-group">
              <textarea class="form-control" name="diary" id="diary" rows="3" placeholder="shhh"></textarea>
            </div>
        </div>

    </div>

    <!-- Optional JavaScript -->
    <!-- jQuery first, then Popper.js, then Bootstrap JS -->
    <script src="https://code.jquery.com/jquery-3.3.1.slim.min.js" integrity="sha384-q8i/X+965DzO0rT7abK41JStQIAqVgRVzpbzo5smXKp4YfRvH+8abtTE1Pi6jizo" crossorigin="anonymous"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.14.7/umd/popper.min.js" integrity="sha384-UO2eT0CpHqdSJQ6hJty5KVphtPhzWj9WO1clHTMGa3JDZwrnQq4sF86dIHNDz0W1" crossorigin="anonymous"></script>
    <script src="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/js/bootstrap.min.js" integrity="sha384-JjSmVgyd0p3pXB1rRibZUAYoIIy6OrQ6VrjIEaFf/nJGzIxFDsf4x0xIM+B07jRM" crossorigin="anonymous"></script>

    <script type="text/javascript"> 

        $('#diary').on('input propertychange', function() {
        $.ajax({
            type: "POST",
            url: "updatedatabase.php",
            data: {content: $('#diary').val()},
            success: function(  msg  ) {
                 alert('Data Saved: ' + msg);
             },
            dataType: "text"
        });
    });

    </script>

  </body>
</html>

I have tried changing from .on to .bind, I've tried different variations of the ajax code and update database code, I've tried looking around StackOverflow.

p.s I understand there are a couple of security errors in the mysqli query injections. This is mainly for my own practice and learning journey but I am struggling to figure this one out.

Many thanks in advance.

What happens? Do you get an error? Does nothing happen or what? — bestprogrammerintheworld, Apr 13 '20 at 10:06
why don't you just submit the diary text via a submit button, meaning wrap it inside a form tag/ It doesn't make sense to use `onchange()` etc. I assume users are going to write lots of text in the textarea tag and want to submit. `onchange()` will trigger it before they are done and that's bad UX — Rotimi, Apr 13 '20 at 10:28
I would do that but I am trying to figure out how to properly use the ajax function so that it works correctly. — Dyneskye, Apr 13 '20 at 10:32

Oli · Accepted Answer · 2020-04-13T10:28:29.333

2

Use .on('change') to bind to the input event.

Also, don't use the slim minified jquery library if you're going to use ajax, it's not included. See jQuery 3 slim ajax basic example

EDIT: I got the script working, with this piece of html/js:

<script
        src="https://code.jquery.com/jquery-3.4.1.min.js"
        integrity="sha256-CSXorXvZcTkaix6Yvo6HppcZGetbYMGWSFlBw8HfCJo="
        crossorigin="anonymous"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.14.7/umd/popper.min.js" integrity="sha384-UO2eT0CpHqdSJQ6hJty5KVphtPhzWj9WO1clHTMGa3JDZwrnQq4sF86dIHNDz0W1" crossorigin="anonymous"></script>
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/js/bootstrap.min.js" integrity="sha384-JjSmVgyd0p3pXB1rRibZUAYoIIy6OrQ6VrjIEaFf/nJGzIxFDsf4x0xIM+B07jRM" crossorigin="anonymous"></script>

<script type="text/javascript">

  $('#diary').on('change', function() {
    $.ajax({
      type: "POST",
      url: "updatedatabase.php",
      data: {content: $('#diary').val()},
      success: function(  msg  ) {
        alert('Data Saved: ' + msg);
      },
      dataType: "text"
    });
  });

</script>

edited Apr 13 '20 at 10:28

answered Apr 13 '20 at 09:54

Oli

2,370
2
26
42

Thank you. I have changed the jQuery CDN to uncompressed. I have also changed the jquery code to .on('input', function() instead of .on('input propertychange', function() but it still seems not to be working. I am unable to update the database and I am not receiving the return echo from updatedatabase.php – Dyneskye Apr 13 '20 at 10:01
1

on('input') is the same as on('change','input') ? – bestprogrammerintheworld Apr 13 '20 at 10:03
2

@Dyneskye - just be careful to use input. It would be executed on all elements that is an input. – bestprogrammerintheworld Apr 13 '20 at 10:04
Thank you! it's worked after fiddling around with the jquery cdns. Previously it didn't seem to work after continuous editing it seems it has finally resolved itself. for those of you with the same issue, the problem was as oli said, the library I was using was slim jquery and did not contain the correct ajax code for it to work. – Dyneskye Apr 13 '20 at 10:49

score 1 · Answer 2 · answered Apr 13 '20 at 09:59

1

You should use the change in jQuery like this:

 $('#diary').on('change', function() {
     code...
 });

or

 $(document).on('change', '#diary', function() {
     code...
 });

or if the #diary is not dynamically created (in runtime) you could do:

 $('#diary').change(function() {
     code...
 });

answered Apr 13 '20 at 09:59

bestprogrammerintheworld

5,417
7
43
72

Thank you. I have tried these and it doesn't seem to work. I think the problem is with my ajax code or the updatedatabase.php code? – Dyneskye Apr 13 '20 at 10:06
What does happen? – bestprogrammerintheworld Apr 13 '20 at 10:07
Nothing happens. I can't get a response from my updatedatabase PHP page ( I should be receiving an alert I believe ) and nothing goes into the MySQL database diary table section – Dyneskye Apr 13 '20 at 10:09
Does the actual javascript execute (if you do an alert('x'); in the change-function. Does that dialog appear? – bestprogrammerintheworld Apr 13 '20 at 10:15
Yes the javascript code executes. But the ajax code doesn't seem to, the error is somewhere between the ajax code and the updatedatabase.php I think – Dyneskye Apr 13 '20 at 10:21
1

$_POST['diary'] should be $_POST['content'] because your sending data as content: {value of diary} in your ajax-request. Therefore it's the key content you should use in your PHP. – bestprogrammerintheworld Apr 13 '20 at 10:31
Whoops I thought I removed that code. ``` echo $_POST['diary']; $query = "UPDATE users SET `diary` = '".$_POST['diary']."' WHERE email = '".mysqli_real_escape_string($link, $_COOKIE['userId'])."'"; ``` has been removed now as it isn't relevant to the ajax post towards updatedatabase.php ... – Dyneskye Apr 13 '20 at 10:44

Ajax POST with mysqli database query

2 Answers2