Create array from mysql query. Column2 as key Column 1 as value

Question

I have a table that contains

column 1 = state  column 2 = link
Alabama             auburn.alabama.com
Alabama             bham.alabama.com
Alabama             dothan.alabama.com

I need to grab from my database table and put into an array that i can array_walk() through. they need to be accessed like this array.

$arraytable = array(
"auburn.alabama.com"=>"Alabama",
"bham.alabama.com"=>"Alabama",
"dothan.alabama.com"=>"Alabama",
);

I have tried everything but not sure how make this work using php to print the array like such. Any help is greatly appreciated.

Michael Berkowski · Accepted Answer · 2012-07-04 22:51:48Z

3

Note Your question title is inconsistent with your example. In the title you ask for column 1 as the key, but your example uses column 2 as the key. I've used column 2 here...

It isn't clear what MySQL API you are using to fetch, but whichever it is, use the associative fetch method and create new array keys using the pattern $arraytable[$newkey] = $newvalue. This example would be in object-oriented MySQLi:

$arraytable = array();

while($row = $result->fetch_assoc()) {
  // Create a new array key with the 'link' and assign the 'state'
  $arraytable[$row['link']] = $row['state'];
}

answered Jul 4, 2012 at 22:51

Michael Berkowski

271k47 gold badges450 silver badges395 bronze badges

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

1 Comment

d3vo Over a year ago

Excellent answer! works great. was just getting ready to post my solution and seen this answer is much more simple. This is the greatest resource around. Thanks for the fast response.

AcidReign · Accepted Answer · 2015-02-06 01:59:31Z

1

You can use array_column for this, since PHP5.5 (http://php.net/array_column)

Description

array array_column ( array $array , mixed $column_key [, mixed $index_key = null ] )

array_column() returns the values from a single column of the array, identified by the column_key. Optionally, you may provide an index_key to index the values in the returned array by the values from the index_key column in the input array.

For PHP < 5.5: https://github.com/ramsey/array_column/blob/master/src/array_column.php

answered Feb 6, 2015 at 1:59

AcidReign

5043 silver badges6 bronze badges

1 Comment

AcidReign Over a year ago

note: I realize this is an old question, but someone pasted the link into IRC as a cross-reference to their question. So I figured it would be useful to reference the new function here, for future users.

mickmackusa · Accepted Answer · 2022-04-11 22:18:25Z

To implement AcidReign's suggestion, here is the snippet:

Code: (Demo)

$resultset = [
    ['state' => 'Alabama', 'link' => 'auburn.alabama.com'],
    ['state' => 'Alabama', 'link' => 'bham.alabama.com'],
    ['state' => 'Alabama', 'link' => 'dothan.alabama.com']
];

var_export(array_column($resultset, 'state', 'link'));
//                                            ^^^^-- use this column's data for keys
//                                   ^^^^^-- use this column's data for values

Output:

array (
  'auburn.alabama.com' => 'Alabama',
  'bham.alabama.com' => 'Alabama',
  'dothan.alabama.com' => 'Alabama',
)

However, array_column() won't directly work on a result set object, but a foreach() can immediately access the data set using array syntax without any fetching function calls.

Body-less foreach: (Demo)

$result = [];
foreach ($mysqli->query('SELECT * FROM my_table') as ['link' => $link, 'state' => $result[$link]]);
var_export($result);

Or a foreach with a body: (Demo)

$result = [];
foreach ($mysqli->query('SELECT * FROM my_table') as $row) {
    $result[$row['link']] = $row['state'];
}
var_export($result);

All of the above snippets return:

array (
  'auburn.alabama.com' => 'Alabama',
  'bham.alabama.com' => 'Alabama',
  'dothan.alabama.com' => 'Alabama',
)

Collectives™ on Stack Overflow

Create array from mysql query. Column2 as key Column 1 as value

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related