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

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.

phpmysql


Solution 1:[1]

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

Solution 2:[2]

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

Solution 3:[3]

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',
)

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Michael Berkowski
Solution 2 AcidReign
Solution 3

Related Questions