I was reading up on preventing SQL injection and i tried to convert my code. Before I changed it, when the page was loaded, I would update my sql with whatever was in 'input[name="amount"]'
and change the text of the id "freetexts" to whatever echo json_encode($result);
gave out. Now after I changed it, the value of freetexts keeps getting changed to "null"
here is my php
<?php
$username="XXX";
$password="XXX";
$database="XXX";
$amount = $_POST["amount"];
$conn = new mysqli(localhost, $username, $password, $database);
// Check connection
if(mysqli_connect_errno()) {
echo "Connection Failed: " . mysqli_connect_errno();
exit();
}
/* create a prepared statement */
if ($stmt = $conn->prepare("UPDATE freetexts SET amount = amount - ? WHERE 1")) {
}
/* Bind parameters: s - string, b - blob, i - int, etc */
$stmt -> bind_param("s", $amount);
//$update = "UPDATE freetexts SET amount = amount - '$amount' WHERE 1";
/* Execute it */
$stmt -> execute();
/* Bind results */
$stmt -> bind_result($result);
/* Fetch the value */
$stmt -> fetch();
echo json_encode($result);
/* Close statement */
$stmt -> close();
?>
And here is my javascript
var amount = $('input[name="amount"]').val();
$.ajax({
type: 'POST',
data: {
amount: amount
},
url: 'textlimit.php',
success: function(data) { //Receives the data from the php code
document.getElementById('freetexts').innerHTML = "Current FREE texts left: " + data;
},
error: function(xhr, err) {
console.log("readyState: " + xhr.readyState + "
status: " + xhr.status);
console.log("responseText: " + xhr.responseText);
}
});
Thanks to @tkausl and @Darren, I've realized that no where in my code actually selects the row I want. I also learned that WHERE 1
does literally nothing. This is the revised php and it works perfectly but I also doubt it's optimized. Any ideas on what I could do to make the code better? Such as removing $stmt -> fetch();
if it's unnecessary
<?php
define('DB_SERVER', "localhost");
define('DB_USER', "XXX");
define('DB_PASSWORD', "XXX");
define('DB_DATABASE', "XXX");
$amount = $_POST["amount"];
$mysqli = new mysqli(DB_SERVER, DB_USER, DB_PASSWORD, DB_DATABASE);
// Check connection
if(mysqli_connect_errno()) {
echo "Connection Failed: " . mysqli_connect_errno();
exit();
}
/* create a prepared statement */
if ($stmt = $mysqli->prepare("UPDATE freetexts SET amount = amount - ?")) {
/* Bind parameters: s - string, b - blob, i - int, etc */
$stmt -> bind_param("s", $amount);
/* Execute it */
$stmt -> execute();
/* Bind results */
$stmt -> bind_result($result);
/* Fetch the value */
$stmt -> fetch();
/* Close statement */
$stmt -> close();
}
$query = "SELECT `amount` FROM `freetexts`";
$result = $mysqli->query($query);
if ($result->num_rows > 0) {
// output data of each row
while($row = $result->fetch_assoc()) {
echo json_encode($row["amount"]);
}
} else {
echo "0 results";
}
$mysqli->close();
?>