I am new to php and prepared stamenents. I am building a sql insert from a array of key, values pairs. The code below does not give any errors (it is inside a try/catch block) and not add the data to the database also. What is wrong with it ?
try {
// prepare sql and bind parameters
echo "new device, add it to DB
";
$keys = implode(", ", array_keys($header));
$values = implode(", ", array_values($header));
var_dump($values);
$placeHolders = str_repeat('?,', count($header));
$placeHolders = substr($placeHolders, 0, -1); //remove the last comma
$sql = "INSERT INTO device ($keys) VALUES ($placeHolders);";
echo "sql: $sql
";
$stmt = $conn->prepare($sql);
$stmt->execute($values);
$stmt->debugDumpParams();
} catch(PDOException $e) {
echo "Error adding a new device. debugParams:
";
$stmt->debugDumpParams();
echo "exception: " . $e->getMessage() . "
";
exit;
}
The current output is:
string(130) "608a242b, 7f019a0be51680cb0ecb1b627415c92a, 902b3438d2cf, whatever, 1.0, 1.0, A, 0000681c0ec2, OID_GENERIC, 1563290266, 1563290266"
sql: INSERT INTO device (deviceID, scannerID, scannerMAC1, scannerMAC2, scannerVer, scannerHwRev, scannerModel, upsMAC, snmpSet, firstSeem, lastSeen) VALUES (?,?,?,?,?,?,?,?,?,?,?);
SQL: [176] INSERT INTO device (deviceID, scannerID, scannerMAC1, scannerMAC2, scannerVer, scannerHwRev, scannerModel, upsMAC, snmpSet, firstSeem, lastSeen) VALUES (?,?,?,?,?,?,?,?,?,?,?);
Params: 0
and the device table is empty (no rows) and it looks like this:
+--------------+-------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+-------------------+-----------------------------+
| deviceID | varchar(8) | NO | PRI | NULL | |
| scannerID | varchar(32) | NO | | NULL | |
| scannerMAC1 | varchar(12) | NO | | NULL | |
| scannerMAC2 | varchar(12) | YES | | NULL | |
| scannerVer | varchar(5) | NO | | NULL | |
| scannerHwRev | varchar(5) | NO | | NULL | |
| scannerModel | varchar(5) | NO | | NULL | |
| upsMAC | varchar(12) | NO | | NULL | |
| snmpSet | varchar(30) | NO | | NULL | |
| lastSeen | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| firstSeen | int(11) | YES | | NULL | |
+--------------+-------------+------+-----+-------------------+-----------------------------+
11 rows in set (0.00 sec)
PS: User "Don't panic" warned me I should use an array in execute argument. Using the $header as parameter to execute, now I am getting the following error:
string(130) "608a242b, 7f019a0be51680cb0ecb1b627415c92a, 902b3438d2cf, whatever, 1.0, 1.0, A, 0000681c0ec2, OID_GENERIC, 1563292682, 1563292682"
sql: INSERT INTO device (deviceID, scannerID, scannerMAC1, scannerMAC2, scannerVer, scannerHwRev, scannerModel, upsMAC, snmpSet, firstSeem, lastSeen) VALUES (0);
Error adding a new device. debugParams:
SQL: [156] INSERT INTO device (deviceID, scannerID, scannerMAC1, scannerMAC2, scannerVer, scannerHwRev, scannerModel, upsMAC, snmpSet, firstSeem, lastSeen) VALUES (0);
Params: 11
Key: Name: [9] :deviceID
paramno=-1
name=[9] ":deviceID"
is_param=1
param_type=2
Key: Name: [10] :scannerID
paramno=-1
name=[10] ":scannerID"
is_param=1
param_type=2
Key: Name: [12] :scannerMAC1
paramno=-1
name=[12] ":scannerMAC1"
is_param=1
param_type=2
Key: Name: [12] :scannerMAC2
paramno=-1
name=[12] ":scannerMAC2"
is_param=1
param_type=2
Key: Name: [11] :scannerVer
paramno=-1
name=[11] ":scannerVer"
is_param=1
param_type=2
Key: Name: [13] :scannerHwRev
paramno=-1
name=[13] ":scannerHwRev"
is_param=1
param_type=2
Key: Name: [13] :scannerModel
paramno=-1
name=[13] ":scannerModel"
is_param=1
param_type=2
Key: Name: [7] :upsMAC
paramno=-1
name=[7] ":upsMAC"
is_param=1
param_type=2
Key: Name: [8] :snmpSet
paramno=-1
name=[8] ":snmpSet"
is_param=1
param_type=2
Key: Name: [10] :firstSeem
paramno=-1
name=[10] ":firstSeem"
is_param=1
param_type=2
Key: Name: [9] :lastSeen
paramno=-1
name=[9] ":lastSeen"
is_param=1
param_type=2
exception: SQLSTATE[21S01]: Insert value list does not match column list: 1136 Column count doesn't match value count at row 1