CODEWITHSUNDEEP
phpmysqlmysqli
Suicidalllama
Suicidalllama

Reputation: 53

php insert into database values become 0

I just started writing PHP for a school project and I've stumbled upon a problem. I have an API which I post some data to. The data is the name, ID and class of a sensor. When I try to insert this into my mySQL database it doesn't input the correct data. Instead of inputting the actual data, it inputs 0 into every column except for building. Even the status column, which is a hard coded string. I've tried pretty much everything (that I know of with my limited PHP knowledge) and I am stumped. 

require_once "../meta.php";

$tmpdevice = clean($_POST['label']);
$device = str_replace('¥', ':', $tmpdevice);

$tmpgenericClass = clean($_POST['zwave_class_generic']);
$genericClass = str_replace('¥', ':', $tmpgenericClass);

$tmpuniqueID = clean($_POST['thingUID']);
$uniqueID = str_replace('¥', ':', $tmpuniqueID);

$status = 'Not Accepted';
$building = 303;

if ($uniqueID == "") {
    echo '{"status":"error"}';
    exit;
}
if ($device == "") {
    echo '{"status":"error"}';
    exit;
}
if ($genericClass == "") {
    echo '{"status":"error"}';
    exit;
}

$servername = DB_HOST;
$username = DB_USER;
$password = DB_PASSWORD;
$databasename = DB_NAME;

$conn = new mysqli($servername, $username, $password, $databasename);
if ($conn->connect_error) {
    die("Connection error: " . $conn->connect_error);
    exit;
}

$stmt = $conn->prepare("INSERT IGNORE INTO Actuator (UniqueID, Device, GenericClass, Status, Building) values (?,?,?,?,?)");
$stmt->bind_param("idddd", $uniqueID, $device, $genericClass, $status, $building);

if ($stmt->execute()) { 
    echo '{"status":"ok"}';
} else {
echo '{"status":"error"}';
}

$conn->close();   

?>

This is my database setup

Upvotes: 1

Views: 677

Answers (1)

Shadow
Shadow

Reputation: 34232

The problem is that most of the fields are strings (varchar), but when you bind the parameters, you bind them as numbers (i- integer, d - double).

Use the proper data types for binding (s - string):

$stmt->bind_param("ssssi", $uniqueID, $device, $genericClass, $status, $building);

Upvotes: 2

Related Questions