PDO fetchAll array to one dimensional

Question

this may be a simple question but am struggling to understand how to solve it. I have a form which allows the user to select either "custom" or "all" staff" to assign to a job.

If custom is selected the user selects staff by clicking each checkbox, I then insert these into a jobs table. This produces the array below (3, 1, 10 are the staff IDs)

Array
(
    [0] => 3
    [1] => 1
    [2] => 10
)

If "all staff" is selected, I first query a select statement to get all the staff ID's from the staff table, and then insert these into the job table the same as above. However this produces the array :

Array
(
    [0] => Array
        (
            [staffID] => 1
            [0] => 1
        )

    [1] => Array
        (
            [staffID] => 23
            [0] => 23
        )

    [2] => Array
        (
            [staffID] => 26
            [0] => 26
        )

    [3] => Array
        (
            [staffID] => 29
            [0] => 29
        )
)

How can I convert the array above to the first array shown?

I'm using the code below to query the database to get all the staff ID's and then inserting them.

    $select = $db->prepare("SELECT staffID FROM staff");
    if($select->execute())
    {
       $staff = $select->fetchAll();
    }

    for($i = 0; $i<count($staff); $i++)
    {
    $staffStmt = $db->prepare("INSERT INTO staffJobs (jobID, userID) VALUES (:jobID, :staffID)");
    $staffStmt->bindParam(':jobID', $jobID, PDO::PARAM_INT);
    $staffStmt->bindParam(':staffID', $staff[$i], PDO::PARAM_INT);

    $staffStmt->execute();              

}

The first array inserts fine, however the last array inserts zeros for the staffID.

Any suggestions?

Thanks =).

Solution 1

Take a look at example 2 in the manual. In your first query you can use:

$staff = $select->fetchAll(PDO::FETCH_COLUMN, 0);

And your second array will have the same form as the first array.

Solution 2

If you print_r($staff[$i]) inside the for you would probably get

    Array
    (
        [staffID] => 1
        [0] => 1
    )

which means you should use $staff[$i]['staffID'] instead.

---

The other alternative, which should work with your current code, is to use PDOStatement::fetchColumn() instead of fetchAll().

Solution 3

You need to give fetchAll a fetch style

$staff = $select->fetchAll(PDO::FETCH_NUM);

From this link

Comments

[Elliott]

Thanks but wouldnt this require me to use two different methods? Such as the first array doesn't contain "staffID", is it not possible to remove the "staffID" completely and just reference it by number?

[Doug]

@Elliot: I see what you mean now, fetchColumn() should work in both cases.

[Nico]

Thanks! Your link helped me find how to fetch a staffID-indexed array: $select->fetchAll(PDO::FETCH_COLUMN|PDO::FETCH_GROUP). Pretty cool stuff