I am trying to put a 2D array into a database. My code is as follows (this is PHP):
public function addspot($linearray,$data){
$dbname=$data['dbname'];
try {
/* Create a connections with the supplied values */
$pdo = new PDO("mysql:host=" . Config::read('hostname') . ";dbname=" . Config::read('database'). "", Config::read('username'), Config::read('password'), array(PDO::ATTR_PERSISTENT => true));
} catch(PDOException $e) {
/* If any errors echo the out and kill the script */
return 'Database conncetion fail in assets/garage.class.php!Make sure your database information is correct';
}
foreach ($linearray as $lines) {
$spot="INSERT INTO `$dbname`(`floor`, `spot`, `status`, `uid`, `type`, `time`) VALUES ('$lines[0]', '$lines[1]', '$lines[2]', '$lines[3]', '$lines[4]', CURRENT_TIMESTAMP);";
$statement = $pdo->prepare($spot);
if($statement->execute()){
//silent
} else {
return 'Spot not added!';
}
}
}
The config values are being read correctly, as well as the statement to add a spot is correct. I know this because when I run the function it correctly adds 1 "spot" but not the rest of the rows in my 2D array.
My array is as follows:
array (size=16)
0 =>
array (size=5)
0 => string '1' (length=1)
1 => string '1' (length=1)
2 => string '1' (length=1)
3 => string '0' (length=1)
4 => string '1' (length=1)
1 =>
array (size=5)
0 => string '1' (length=1)
1 => string '2' (length=1)
2 => string '1' (length=1)
3 => string '0' (length=1)
4 => string '1' (length=1)
(and onwards)
My issue is that the function I have written only writes the first line (line[0]) to the database and the other ones do not get written.
Update Output (using print_r) of statements: Placed after the prepare
PDOStatement Object
(
[queryString] => INSERT INTO `Garage2`(`floor`, `spot`, `status`, `uid`, `type`, `time`) VALUES ('1', '1', '1', '0', '1', CURRENT_TIMESTAMP);
)
PDOStatement Object
(
[queryString] => INSERT INTO `Garage2`(`floor`, `spot`, `status`, `uid`, `type`, `time`) VALUES ('1', '2', '1', '0', '1', CURRENT_TIMESTAMP);
)
print_r($pdo->errorInfo()); output Placed in the else (fail) part of the execute statement
Array
(
[0] => 00000
[1] =>
[2] =>
)
Finally got it. For those who run into this problem in the future, be sure to check that if you have a primary key on your DB, you are not using the same value twice. My first cell was my primary ID, and thus when the first and second statements both had 1 as the value, it failed.
To find this, I added this right after my prepare()\
$pdo->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING );
Try replacing your line
return 'Spot not added!';
With:
var_dump($lines);
die('Error applying line to DB');
This will let us see what the value for $lines is when the query fails to apply.
this should do the trick using prepared statements, see if it works and drop me a message then i`ll format the answer better
$spot="INSERT INTO `$dbname`(`floor`, `spot`, `status`, `uid`, `type`, `time`) VALUES (?, ?, ?, ?, ?, CURRENT_TIMESTAMP)";
$statement = $pdo->prepare($spot);
foreach ($linearray as $lines) {
$values = array($lines[0],$lines[1],$lines[2],$lines[3],$lines[4])
if($statement->execute($values)){
//silent
}
else {
return 'Spot not added!';
}
For numeric primary IDs it's better to use auto increment. Primary IDs do have to be unique. However, you can use Indexes or composed indexes that don't need to be unique.
To answer your question regarding sorting your results, try this query:
-- Ordering by floor and spot
SELECT * FROM myTable ORDER BY floor, spot;
-- Ordering by floor only
SELECT * FROM myTable ORDER BY floor;
Here's a
note: The lines prepended by -- (double dash) are comments. You can remove them