PHP foreach loop to insert data into mysql with PDO only inserts first row

Question

I've read every question/tutorial I could find on inserting things via foreach & PDO, but am still left scratching my head.

I am basically trying to do a batch insert/update of a table based on who's paid dues in my business fraternity (hence the 'brother'/'bro).

The fields are all being retrieved from POST, field 1 is the dues amount ($amount), field 2 is number of people who have paid ($num_paid) and field 3 is an array of user ids for those who have paid ($paid) which is created via check-boxes created by a foreach loop in another file.

The best way I could think to check to make sure things were entered correctly was to compare count($paid) to $num_paid, and proceed only if they matched. I'm open to suggestions on doing that better, but it seems to work the way it is.

I'm trying to loop through the array from field 3 ($paid) using 'foreach' to insert new rows in the table as a 'credit' for each person who has paid, but for some reason it only inserts in the first loop, then somehow inserts empty rows for the rest.

The table is set up with columns: user_id(manually input), transaction_id(AI), date, credit, debit, info.

Using: "INSERT INTO finance VALUES(?,NULL,now(),?,?,?)"; If I insert 5 users, it will insert the first with transaction_id x, then run up the auto-increment count 4 more, but insert nothing. So the next time I try inserting, the transaction_id is 5 more than the first.

e.g. -- Try 1 inserts a row with transaction_id 43. Try 2 inserts one row, with transaction_id 48, but there's nothing in between them.

I've tried passing the array into the function, then looping through it with 'foreach' that way, and also tried using a 'foreach' loop which calls the function and passes it a string each time to insert. Both seem to work the same. I'm pretty lost, because it seems like if the auto incrementing column is counting up, it should be inserting data?

I think this is all the relevant code...(PHP tags were added to make it more readable.)

Processing of form data which calls the function

<?php
if (isset($_POST['amount']) && 
    isset($_POST['num_paid']) && isset($_POST['paid'])) {           

        $amount = saniString($_POST['amount']);
        $num_paid = saniString($_POST['num_paid']);
        $paid = $_POST['paid']; #this is an array of user id's for 
        #who has paid.  

        echo "<br /><br />";
        var_dump($paid);
        echo "<br /><br />";
        var_dump($num_paid);
        echo "<br /><br />";
        var_dump($amount);
        echo "<br /><br />";

        if (count($paid) != $num_paid) {
            echo "<p><b>You did not check the correct number of
                brothers. Please check your entries and try again.</b></p>";
        }
        elseif (count($paid) == $num_paid) {
            $match = true;
            echo "<p><b>Congrats. The numbers match.</b></p>";
        }

        if ($match === true) {
            if ($amount > 0) {
                $credit = $amount;
                $debit = 0;
            }
            elseif ($amount < 0) {
                $credit = 0;
                $debit = $amount;
            }
        else { 
            $credit = 0;
            $debit = 0;
        }           
        foreach ($paid as $dirtyId) {
            $result = updateFin($dirtyId,$broid,$credit,$debit,$dbh);
            var_dump($result);
        }                       
    }           
}
?>

Function to prepare/bind/insert

<?php
function updateFin($dirtyId,$broid,$credit,$debit,$dbh) {
    $sql = "INSERT INTO finance VALUES(?,NULL,now(),?,?,?)";
    $query = $dbh->prepare($sql);

    #foreach ($paid as $dirtyId) { // please notice this is commented out

    $bro_paid = saniString($dirtyId);
    $info = "Dues from bro-ID: " . $bro_paid;

    $query->bindValue(1, $broid);
    $query->bindValue(2, $credit);
    $query->bindValue(3, $debit);
    $query->bindValue(4, $info);

    try {
        $result = $query->execute();                
    }
    catch (PDOException $e) {
        echo "<br />Insert of $credit, with info $info failed.";
        echo $e->getMessage();
        $result = false;
    }
    return $result;
    #}
}
?> 

var_dumping the values I'm trying to pass in gives me what I'd expect - $amount is a string (50.00), $num_paid is also a string (5), and $paid is an array full of the IDs I'm trying to insert...

The 'saniString' function uses strip_tags, htmlentities, and stripslashes, if that's relevant...

If I var_dump($result) it returns bool(true),bool(false),bool(false),bool(false),bool(false). Which is what I'd expect given my results, except I have no idea why the queries are failing and I'm not getting any error messages from PDO?

Answer 1

The issue is caused by the unique on date. The subsequent inserts have a date the same down to the second and so they're ignore (while the autoincrement is incremented). So

alter table finance drop index date

[EDITED] The setting $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); should have caused the duplicate inserts to throw an exception with SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry (It does when I test it) But this attribute isn't set by default when the PDO-object is created.

When in doubt, you can always fall back to checking the error info, even if there isn't an exception. $err = $query->errorInfo();, for a duplicate entry you'll get $err[1] == 1062

Answer 2

In this line if (count($paid) != $num_paid) { you are not defining $match and if this statement gets true than this line will raise an error of undefined variable $match if ($match === true) {

Replace this code block

if (count($paid) != $num_paid) {
    echo "<p><b>You did not check the correct number of
        brothers. Please check your entries and try again.</b></p>";
    $match = false;
}
elseif (count($paid) == $num_paid) {
    $match = true;
    echo "<p><b>Congrats. The numbers match.</b></p>";
}