CODEWITHSUNDEEP
phparrayspdo
lStoilov
lStoilov

Reputation: 1339

Create single array from MySQL with PHP/PDO

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

}

Upvotes: 1

Views: 986

Answers (3)

tim
tim

Reputation: 727

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.

Upvotes: 1

u_mulder
u_mulder

Reputation: 54796

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.

Upvotes: 3

ScaisEdge
ScaisEdge

Reputation: 133400

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

Upvotes: 1

Related Questions