fetch 2 columns from table in 2 array php mysql

Question

I have a table with 6 columns

Sort like:

Database -> kids

|CANDY   | COLOR  |  DRINK  | PET  | SONG  | TOY   |
---------------------------------------------------
|cookie  | blue   | juice   | dog  | if    | ball  |
|cake    | red    | coke    | cat  | ask   | doll  |

I want to store

all the candies in one Array called Candy[];
all the colors on color[];
all the drinks on drink[]; etc....

I managed to create the arrays with the columns names with a FORLOOP with these lines, which works fine:

$fieldName = mysqli_fetch_field_direct($result, $i)->name; ${$fieldName} = array();

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[]....

while($row = mysqli_fetch_array($result)){
  //inserts itens in the "$array" with columns name
  ${$fieldName}[] = $row[$i];
}

If I try to "echo" the arrays[] and don´t put a "BREAK" at the end of the WHILE, It returns an error

"Undefined offset: 0 in line..."

And when I put the "BREAK", it works for:

 echo candy[0];   = cookie

but doesn't works for:

echo candy[1];   = Undefined offset: 1 in line...

Here is the whole 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<=$colNumber -1;$i++){

        $fieldName = mysqli_fetch_field_direct($result, $i)->name;
        echo . $fieldName . "<br /><br />";

        //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 "<hr />";
}

The WHILE code works when it´s not inside a FORLOOP and if I make a query() like:

SELECT candy FROM kids.

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.

Any ideas?

I need it to put the arrays inside HTML <SELECT><OPTION></SELECT> , then use mt_rand() to shuffle and get different "profiles". 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.

I already spent 3 days reading documentation on http://php.net/manual/pt_BR/mysqli-result.fetch-array.php

and "googling" it but couldn't find any answer.

Because I don´t know how to make it work. I tried it already but all I got was errors. Any code suggestion would be appreciated. — Vinícius Rezendrix
– Vinícius Rezendrix, Commented Oct 16, 2014 at 3:32
dear i deleted my answer. And yah i do know now what you want. Sorr i cant help you in this one. — arif_suhail_123
– arif_suhail_123, Commented Oct 16, 2014 at 3:46
Arif, I´ll try this one tomorrow and let you know. Cos it´s 1:24am and I gotta work tomorrow. Since now, thanks! — Vinícius Rezendrix
– Vinícius Rezendrix, Commented Oct 16, 2014 at 4:23
I did what you said and got this, Arif: mysql_fetch_assoc() expects parameter 1 to be resource, boolean given in... Dunno if it´s related, but I use mysqli() for Connection — Vinícius Rezendrix
– Vinícius Rezendrix, Commented Oct 17, 2014 at 2:26

marc_s · Accepted Answer · 2016-02-13 14:18:44Z

Found a way to do it. I translated the code to English so others can have it. If there is any variable name wrong, fix it using the comments and enjoy!

$sql = "SELECT parquet, defendant, action, modusOperandi, victim, crime FROM crime_elements";

//runs '$sql' query to get database data
$result = mysqli_query($con,$sql);

$collNumber = mysqli_num_fields($result);

echo "ColL Number :" . $collNumber;

// Creates $data Array[]
$data = array(); 

while($row = mysqli_fetch_array($result)){
    //inserts received data into "$data" and makes available by array[]index
    $data[] = $row;
}

for($i=0;$i<=$collNumber -1;$i++){
    // Gets the column name from the query()
    $fieldName = mysqli_fetch_field_direct($result, $i)->name;
    echo "</br><b>Coll Name:</b> " . $fieldName . "<br /><br />";

        ${$fieldName} = array_column($data, $i);
    print_r(${$fieldName});
    echo "<br /><b>Specified item choice[]:</b> ". ${$fieldName}[2];
    echo "<hr />";
}

Collectives™ on Stack Overflow

fetch 2 columns from table in 2 array php mysql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related