title: fetch 2 columns from table in 2 array php mysql
body: <p>I have a table with 6 columns </p>
  
  <p>Sort like:</p>
  
  <p>Database -> kids</p>
  
  <pre><code>|CANDY   | COLOR  |  DRINK  | PET  | SONG  | TOY   |
  ---------------------------------------------------
  |cookie  | blue   | juice   | dog  | if    | ball  |
  |cake    | red    | coke    | cat  | ask   | doll  |
  </code></pre>
  
  <p>I want to store </p>
  
  <ul>
  <li>all the candies in one <code>Array</code> called <code>Candy[];</code></li>
  <li>all the colors on <code>color[];</code></li>
  <li>all the drinks on <code>drink[];</code> etc....</li>
  </ul>
  
  <p>I managed to create the arrays with the columns names with a <code>FORLOOP</code> with these lines, which works fine:</p>
  
  <blockquote>
    <p>$fieldName = mysqli_fetch_field_direct($result, $i)->name;
    ${$fieldName} = array();</p>
  </blockquote>
  
  <p>But when the code gets to the WHILE part, that is also inside the loop, to add the columns items inside the arrays[], it adds only the first element and returns to the FORLOOP, goes and adds one element to the next array[] and jumps to the next array[].... </p>
  
  <pre><code>while($row = mysqli_fetch_array($result)){
    //inserts itens in the "$array" with columns name
    ${$fieldName}[] = $row[$i];
  }
  </code></pre>
  
  <p>If I try to <code>"echo"</code> the <code>arrays[]</code> and don´t put a <code>"BREAK"</code> at the end of the <code>WHILE</code>, It returns an error </p>
  
  <blockquote>
    <p>"Undefined offset: 0 in line..."</p>
  </blockquote>
  
  <p>And when I put the "<code>BREAK</code>", it works for:</p>
  
  <pre><code> echo candy[0];   = cookie
  </code></pre>
  
  <p>but doesn't works for:</p>
  
  <pre><code>echo candy[1];   = Undefined offset: 1 in line...
  </code></pre>
  
  <p>Here is the whole code:</p>
  
  <pre><code>$sql="SELECT candy, color, drink, pet, song, toy FROM kids";
  $result = mysqli_query($con,$sql);
  
  $colNumber = mysqli_num_fields($result);
  
      for($i=0;$i&lt;=$colNumber -1;$i++){
  
          $fieldName = mysqli_fetch_field_direct($result, $i)-&gt;name;
          echo . $fieldName . "&lt;br /&gt;&lt;br /&gt;";
  
          //Creates an Array with Coll Name from DB 
          //with dynamic-variable ${$fieldname}
          ${$fieldName} = array(); 
  
          while($row = mysqli_fetch_array($result, MYSQLI_NUM)){
  
              //inserts the received itens into the array ${$fieldName} 
              ${$fieldName}[] = $row[$i];
  
               printf ("%s (%s)\n", $row[$i], $row[1]); 
               }
  
         echo ${$fieldName}[0];
         echo candy[0];
  
         echo ${$fieldName}[1];
         echo candy[1];
         echo "&lt;hr /&gt;";
  }
  </code></pre>
  
  <p>The WHILE code works when it´s not inside a <code>FORLOOP</code> and if I make a <code>query()</code> like:</p>
  
  <pre><code>SELECT candy FROM kids.
  </code></pre>
  
  <p>But then, like that, I´d need like 600 lines of repeated code to get what I want and copy/paste again and again for each new coll on the DB table.</p>
  
  <p>Any ideas?</p>
  
  <p>I need it to put the arrays inside HTML 
  <code>&lt;SELECT&gt;&lt;OPTION&gt;&lt;/SELECT&gt;</code> , then use <code>mt_rand()</code> to shuffle and get different "<code>profiles</code>". This won´t be used with kid stuff, that was just an example. 
  It will be a virtual crime creator that will shuffle the variables to create different crime hypothesis for law school students work on.</p>
  
  <p>I already spent 3 days reading documentation on <a href="http://php.net/manual/pt_BR/mysqli-result.fetch-array.php" rel="nofollow">http://php.net/manual/pt_BR/mysqli-result.fetch-array.php</a></p>
  
  <p>and "googling" it but couldn't find any answer.</p>