Only Adding 1 Line to DB Then Skips the Rest

Question

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] => 
)

*font*, *small*, *b*, *i* ?? the 90's called and wants its html back - please learn css and modern html — , Dec 04 '12 at 20:15
@Dagon Thats XDebug...for those who use PHP in the 21st century :P I'll remove it's formatting — chriscct7, Dec 04 '12 at 20:17
Why are you using `prepare` when you are not using bound parameters? That would also get rid of the multiple unneeded prepares that are happening (not automatically though). Note `PDO` is not something magic you can use to replace your `mysql_*` code as is. — PeeHaa, Dec 04 '12 at 20:23
@PeeHaa If I do not I get Call to a member function execute() on a non-object — chriscct7, Dec 04 '12 at 20:27
@PeeHaa I don't use mysql. Never have. Its been deprecated, so I never even bothered with it. — chriscct7, Dec 04 '12 at 20:28
mate, try using PDO::errorInfo and add the output to your question — Tivie, Dec 04 '12 at 20:28
@PeeHaa Here, have a sarcastic cookie. Obviously I thought OP meant the RDBMS itself... Anyway, chriscct7 did you try using $pdo->errorInfo? — Tivie, Dec 04 '12 at 20:34

score 0 · Accepted Answer · answered Dec 04 '12 at 20:56

0

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 );

answered Dec 04 '12 at 20:56

chriscct7

527
5
11

That's a table design smell. 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. – Tivie Dec 05 '12 at 02:59
@Tivie What I want is that I know the first column will either contain the number 1-9, as will the second column. Trying to find a way to make the database automatically sort by lowest first, and lowest second. – chriscct7 Dec 05 '12 at 03:01
PrimaryID won't serve that purpose. You can't "sort a database", but you can sort the results obtained from a query to a database. – Tivie Dec 05 '12 at 03:04

score 0 · Answer 2 · answered Dec 04 '12 at 20:58

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!';
    }

Solved already (primary key issue: see below). Thanks for the help though. — chriscct7, Dec 04 '12 at 21:28

score 0 · Answer 3 · answered Dec 05 '12 at 03:20

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

SQLFiddle

note: The lines prepended by -- (double dash) are comments. You can remove them

Only Adding 1 Line to DB Then Skips the Rest

3 Answers3