AJAX PHP Call to localhost database returning error

Question

I'm making a web-application library with a button that, when clicked, calls a checkout() function. This function uses jQuery to post data to a PHP script. this PHP then connects to a database on localhost (for now) and sends a query.

When executed inside MySQL Workbench, the query shown in the PHP below works perfectly. My problem arises when I try to execute it from the web page. It connects just fine with the db user and password, but the query itself fails.

The HTML

<div class="col-xs-4 btn btn-default button" onclick="checkout();">
    Checkout
</div>

The JS

function checkout() {
    let result=$.ajax({
        type: "POST",
        url: "php/checkout.php",
        dataType: "text",
        //@TODO: add scanner functionality to populate isbn,
        //  add functionality to populate user
        data:
        {
            isbn: 1,
            user: 1
        },
        success: function(result) {
            //change #button-text to house result (for now)
            // @TODO: delete errorcheck when done
            $('#button-text').html(result);
            console.log(result);
        },
        error: function(xhr, status, error) {
            alert(status + ' ||| ' + error);
        }
    });
}

The PHP

    $dbconn = connectToDB($serverName, $dbName, $dbUser, $dbPass);
    // set is_available to false, update checkout and due
    // date, update current owner of book, set due_date to
    // current date + 14 days
    $query = "UPDATE books
                    SET is_available = false,
                        checkout_date = CURDATE(),
                        due_date = DATE_ADD(CURDATE(),
                        INTERVAL 14 DAY)
                    WHERE isbn = $isbn;

                    INSERT INTO currently_checked_out_books
                    (user_id, book_isbn, due_date)
                    VALUES (
                        $user,
                        $isbn,
                        DATE_ADD(CURDATE(), INTERVAL 14 DAY)
                    );";
    // $query = "show tables;"; // returns what you'd expect
    $result = $dbconn->query($query);

What I've Tried:

I double and triple checked my query and my database permissions - the db user I have setup has DB Manager privileges, meaning it should be able to do whatever it wants with the database. As previously stated, I also tested my query directly in MySQL Workbench, and it worked properly. The only thing I can think of is that my db user isn't setup properly, but again, if anything it has too much freedom.

Additional information and clarification provided upon request - if I missed any important needed info, please let me know.

you're attempting to do a multi query but not using the right function for it. — Funk Forty Niner, Jan 14 '18 at 03:31
What is the error message you get back? I cannot see where you assign $user,$isbn from $_POST['user'] and $_POST['isbn']. Plus the multi query as already noted by FunkFortyNiner. — TimBrownlaw, Jan 14 '18 at 03:33

score 2 · Accepted Answer · answered Jan 14 '18 at 03:43

I think your problem is that you have more than one statement in $query but you're using $dbconn->query($query) which doesn't support multiple statements as also reported in official documentation:

Multiple statements or multi queries must be executed with mysqli_multi_query(). The individual statements of the statement string are separated by semicolon. Then, all result sets returned by the executed statements must be fetched.

Therefore you need to split your statements or leave $query as it is and use $result = $dbconn->multi_query($query);.

AJAX PHP Call to localhost database returning error

1 Answers1