Create single array from MySQL with PHP/PDO

Question

I am trying to create a single array from a db table that has few rows like:

--------------
id   |   name
--------------
1    | value 1
2    | value 2
3    | value 3
--------------

I want the created array to be as simple as possible like value1,value2,value3

I am using this function

function getAll()
    {
        //select all data
        $sql = "SELECT name FROM " . $this->table_name . "  ORDER BY id";

        $prep_state = $this->db_conn->prepare($sql);
        $prep_state->execute();

        $row = $prep_state->fetch(PDO::FETCH_ASSOC);
        $this->name = $row['name'];
    }

Is there a simple way to do it, perhaps with another PDO Mode like for example is done with PDO::FETCH_UNIQUE, or a should take a different approach?

UPDATE - This way it worked (thanks to @u_mulder)

function getAll()
{
    $sql = "SELECT name FROM " . $this->table_name . "  ORDER BY id";
    $prep_state = $this->db_conn->prepare($sql);
    $prep_state->execute();

    $names = $prep_state->fetchAll(PDO::FETCH_COLUMN, 0); 
    return(implode(",",$names));

}

Possible duplicate of How to use PDO to fetch results array in PHP? — AmmoPT
– AmmoPT, Commented Aug 27, 2018 at 16:12
Why not use fetchAll instead of fetch since you want to return all rows? — kidA
– kidA, Commented Aug 27, 2018 at 16:15

u_mulder · Accepted Answer · 2018-08-27 16:16:17Z

3

What you need is PDO::FETCH_COLUMN mode:

$sql = "SELECT name FROM " . $this->table_name . "  ORDER BY id";
$prep_state = $this->db_conn->prepare($sql);
$prep_state->execute();

// 0 indicates first column of result
$names = $prep_state->fetchAll(PDO::FETCH_COLUMN, 0); 
print_r($names);
// then do what you want.

answered Aug 27, 2018 at 16:16

u_mulder

54.8k5 gold badges54 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

lStoilov Over a year ago

Thanks a lot. It worked. instead of print_r i just added return(implode(",",$names));

ScaisEdge · Accepted Answer · 2018-08-27 16:13:10Z

1

if youe want all the name in an array you could try group_concat and explode

function getAll()
{
    //select all data
    $sql = "SELECT group_cancat(name) name FROM " . $this->table_name . "  ORDER BY id";

    $prep_state = $this->db_conn->prepare($sql);
    $prep_state->execute();

    $row = $prep_state->fetch(PDO::FETCH_ASSOC);

    // build an array from the concatenated  string pf names 
    $myArray = explode(',',  $row['name']);

    return $myArray;
}

answered Aug 27, 2018 at 16:13

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

Comments

tim · Accepted Answer · 2018-08-27 17:03:07Z

1

Use the fetchAll method which returns an array, and then just return your array in a variable that you can use.

function getAll()
    {
        //select all data
        $sql = "SELECT name FROM " . $this->table_name . "  ORDER BY id";

        $prep_state = $this->db_conn->prepare($sql);
        $prep_state->execute();

        $row = $prep_state->fetchAll(); //changed this
        $return $row; //and this
    }

Then to get the values out of this array.

foreach($row as $key=>$value) {
//echo your results
}

The key will be the id and the value will be the name.

answered Aug 27, 2018 at 17:03

tim

7471 gold badge10 silver badges12 bronze badges

Collectives™ on Stack Overflow

Create single array from MySQL with PHP/PDO

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related