Selecting associative array in mysql

Question

I currently have the following:

 $query='select concat("[",key,"=>",value,"]")
 from table';
 if(isset($query))$r=$mysql->query($query);
 if(isset($r)){
      for($i=1;$i<=$r->num_rows;$i++){
           $r->data_seek($i-1);
           $a[$i-1]=$r->fetch_array(MYSQLI_ASSOC);
           $a[$i-1]=parse_array($a[$i-1]);
      }
 }
 $mysql->close;
 function parse_array($parent){
      foreach($parent as$k=>$val){
           if(strpos($val,']')){
                $array=explode(',',substr($val,1,-1));
                foreach($array as$val){
                     $keypair=explode("=>",$val);
                     $newarray[$keypair[0]]=$keypair[1];
                }
                $parent[$k]=parse_array($newarray);
           }
      }
 }

There has to be a more elegant way of doing this - perhaps built into MySQL? I'm trying to minimize the time this spends running PHP - I would like it to arrive to PHP already in array form, but MySQL kicks Subquery returns more than one result if I attempt a subquery.

Edit: Here's table:

 +----------+----------+
 | value    | key      |
 +----------+----------+
 | img.jpg  | src      |
 +----------+----------+

Output should be:

 [
      'src'=>'img.jpg'
 ]

Is this the way to initialize `$i` in `for($i-1;$i<=$r->num_rows;$i++){`? It should be `for($i=0...` — Nana Partykar, Jul 24 '17 at 11:44
@AlivetoDie its associative. `key1` could be `width`, `value1` could be `100%` — Evan Hendler, Jul 24 '17 at 11:49
I got rid of `key2` and `value2` - they were distracting from the point of the question. — Evan Hendler, Jul 24 '17 at 11:58

score 0 · Answer 1 · answered Jul 24 '17 at 12:07

Just move all of the manipulation over to php. Fetch the query with numeric indexes. Make the assumption that the every even index is a key and every odd index is a value (or vice versa).

$query = 'select key1, value1, key2, value2
from table';
if(isset($query))
    $result = $mysql->query($query);
if(isset($result)) {
    $newResult  = []; // if your version of php doesn't support this syntax to create a new array use `$newResult = array();`
    while($row=$result->fetch_array(MYSQLI_NUMERIC)) {
        $newResult[] = build_assoc_array($row);
    }
}
$mysql->close;
function build_assoc_array($flat_arr) {
    $newArr = [];
    $numCol = count($flat_arr);
    for($colIndex = 0; $colIndex  < $numCol; $colIndex+=2) {
        $newArr[$flat_arr[$colIndex]] = $flat_arr [$colIndex+1];
    }
    return $newArr;
}

Just mysql? Oh, I see that in the question now, my bad. In that case, your OP is possibly a dupe of [this question](https://stackoverflow.com/questions/10925445/mysql-select-dynamic-row-values-as-column-names-another-column-as-value) — , Jul 24 '17 at 12:25

Selecting associative array in mysql

1 Answers1