Create associative array from 2 columns in PHP and MySQL

Question

Can I make an associative array from two columns? I want column A as key and column B as value.

-------------
| id | name  |
-------------
| 1  | sky   |
-------------
| 2  | space |

I want a function that make result like this:

$ary=array('1'=>'sky','2'=>'space', ... );

Is any php function exist about this matter?

I'm using php, mysql and codeigniter.

It'll come out of your mysql query as an array/object that you loop through using a while statement. If you then need to further put it into an array just insert it inside your while. But in a dataset as simple as yours I can see no reason to array it over just looping — Dave
– Dave, Commented Jul 16, 2013 at 8:46
There is no single function that connects to a database, retrieves data and formats it into an array with one column as a key and another as a value; you'll need to write it yourself.... have you tried? which part are you having problems with? — Mark Baker
– Mark Baker, Commented Jul 16, 2013 at 8:47

Barmar · Accepted Answer · 2017-07-28 14:09:53Z

12

$ary = array();
while ($row = $stmt->fetch_assoc()) {
    $ary[$row['id']] = $row['name'];
}

edited Jul 28, 2017 at 14:09

answered Jul 16, 2013 at 8:46

Barmar

789k57 gold badges554 silver badges669 bronze badges

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

Comments

kojow7 · Accepted Answer · 2017-06-29 07:02:46Z

1

Even though you are using codeigniter, you are not required to use codeigniter's libraries. If you are willing to implement a PDO implementation you can do something like this in native PHP:

$stmt = $db->query("select id, name from mytable");
$results  = $stmt->fetchAll(PDO::FETCH_KEY_PAIR);

There would be less overhead this way than with some other implementations, but it depends if you want to use PDO.

edited Jun 29, 2017 at 7:02

answered Jun 29, 2017 at 6:23

kojow7

11.6k21 gold badges94 silver badges148 bronze badges

Comments

mickmackusa · Accepted Answer · 2023-11-05 22:34:40Z

Since you are using Codeigniter, use its result set helper method, then call array_column() to convert the two columns to a flat, associative array.

var_export(
    array_column(
        $this->query($sql)->result(),
        'name',
        'id'
    )
);

If you were just using PHP's native mysqli:

procedural style:

$result = mysqli_query($conn, $sql);
var_export(
    array_column(
        mysqli_fetch_all($result, MYSQLI_ASSOC),
        'name',
        'id'
    )
);

object-oriented style:

$result = $conn->query($sql);
var_export(
    array_column(
        $conn->fetch_all($result, MYSQLI_ASSOC),
        'name',
        'id'
    )
);

You can immediately iterate the result set object: (Demo)

$result = [];
foreach ($mysqli->query($sql) as $row) {
    $result[$row['id']] = $row['name'];
}
var_export($result);

You can even use array destructuring in a body-less loop.

$result = [];
foreach ($mysqli->query($sql) as ['id' => $id, 'name' => $result[$id]]);
var_export($result);

liding · Accepted Answer · 2013-07-16 08:48:26Z

0

foreach($rows as $key => $value){
    $arr[$key] = $value;
}

answered Jul 16, 2013 at 8:48

liding

1161 silver badge6 bronze badges

1 Comment

feeela Over a year ago

For large datasets, it would result in better performance to fetch each single row instead of fetching them all and iterate over the result set.

Collectives™ on Stack Overflow

Create associative array from 2 columns in PHP and MySQL

4 Answers 4

Comments

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related