Why doesn't my ajax work with prepared mysqli statement

Question

I have a small problem, which is that when I add my prepared statement into the PHP file, the Ajax stops working and gives me a 500- error, but when I remove the statement, it works like a charm.

This is my PHP file:

<?php 
include ('db_connect.php');
include ('functions.php');

$datad = $_POST['superstr'];
$id = 1;

    $stmt = $mysqli->prepare("UPDATE `song` SET `lyrtext`=? WHERE `id`=?");
    $stmt->bind_param("si", $datad, $id);
    $status = $stmt->execute();

echo $datad;
?>

and my Ajax looks like this:

$.ajax({
          url: 'includes/sendlyrics.php',
          type: 'POST',
          data: {superstr: 'pelle'},
          success: function(data) {
            //called when successful
            var hello = data;
            //prompt(data);
            console.log("The data is:");
            console.log(data);
            console.log("The variable which should keep the data has this content:");
            console.log(hello);
          },
          error: function(e) {
            //called when there is an error
            console.log(e.message);
            prompt(e.message);
            //alert(e.message);
          }
        });

What's the problem?

have you initiated mysqli like `$mysqli = new mysqli('localhost', 'my_user', 'my_password', 'world');`? — Guns, Apr 14 '14 at 17:35
Have you looked at the console to see what errors are being reported? Does the PHP work by itself, when not called by AJAX? — Jay Blanchard, Apr 14 '14 at 17:36
This is what it said in the console: `POST http://superaudio.web/AudioThingy/includes/sendlyrics.php 500 (Internal Server Error) ` and yes, here is my connection: `$mysqli = new mysqli(HOST, USER, PASSWORD, DATABASE); mysqli_set_charset($mysqli,'utf8'); if ($mysqli->connect_error) { header("Location: ../error.php?err=Unable to connect to MySQL"); exit(); }` — Niklas, Apr 14 '14 at 17:38
You're getting a 500 error which means that PHP at the server level is failing. Have a look at the server error logs to see if there is any clue as to why it is failing, the AJAX appears to be working OK. Does the PHP work by itself? — Jay Blanchard, Apr 14 '14 at 17:40
Please see this post to learn how to properly debug AJAX calls and please feel free to upvote. http://stackoverflow.com/a/21617685/2191572 — MonkeyZeus, Apr 14 '14 at 17:55
This is the error I get: `[14-Apr-2014 19:55:23 Europe/Stockholm] PHP Fatal error: Call to a member function bind_param() on a non-object in /Users/FusionSausage/Documents/Web/superaudio/AudioThingy/includes/sendlyrics.php on line 15` — Niklas, Apr 14 '14 at 17:56
Just to comment as I discussed with @Jay Blanchard, I learn that is a good pratice, and you can use the double quotes instead of back-ticks (which is the ANSI SQL Standard). Just need enabling: [SET sql_mode = 'ANSI_QUOTES';](http://dev.mysql.com/doc/refman/5.6/en/sql-mode.html#sqlmode_ansi_quotes) — Vitor Tyburski, Apr 14 '14 at 18:10

dsaa · Accepted Answer · 2014-04-14T17:57:58.140

$stmt = $mysqli->prepare("UPDATE `song` SET `lyrtext`=? WHERE `id`=?");
$stmt->bind_param("si", $datad, $id);
$status = $stmt->execute();

Is wrong. Amend this to:

$stmt = $mysqli->prepare("UPDATE `song` SET `lyrtext`=? WHERE `id`=?");
$stmt->bind_param("si", $datad, $id);
$stmt->execute();

If you are looking for confirmation of the query executing successfully you can test this by:

if($stmt->execute()){
    //returned true - statement executed successfully
} else{
    //returned false
}

Also you should modify your Ajax call to something similar to:

$.ajax({
url     : "your/url/here.php"
type        : "POST",
data        : {superstr: 'pelle'},
dataType    : "json",
success     : function(data){
        //do something with the response
},
error           : function(jqXHR, textStatus, errorThrown){
        //handle the error
}
});

By adding a dataType of json, you should then ammend the echo statement of your .php file to read:

$response = Array();
array_push($response, $datad);
echo json_encode($response);

I'm sure you've read the documentation online already, but Ajax can be found here, and prepared statements here.

This is all written with the caveat that your DB connection is valid...which it would appear to be given the wording of your question, and your previous attempts at solving the issue.

Why doesn't my ajax work with prepared mysqli statement

1 Answers1