PHP storing a two dimension array from MySQL query

Question

I have a simple question. I have an array that has two columns (id, name) that is a result of a MySQL query. I want to store the result of the query into a array variable so i can access each element when i need to.

I am able to store a one dimension array like the following:

    $array = array();
    while($row = mysqli_fetch_assoc($result))
    {
        $array[] = $row['name'];
    }

How can I store and access a two dimensional array? Also is it best to use a for loop or while loop to store these?

the below answers are fine, but sometimes you may want $array[$row['id']] = $row; if you want to pick thinks out of the array based on your index\valuie in mysql — user8011997
– user8011997, Commented Sep 20, 2017 at 21:59
Note: The object-oriented interface to mysqli is significantly less verbose, making code easier to read and audit, and is not easily confused with the obsolete mysql_query interface. Before you get too invested in the procedural style it’s worth switching over. Example: $db = new mysqli(…) and $db->prepare("…”) The procedural interface is an artifact from the PHP 4 era when mysqli API was introduced and should not be used in new code. — tadman
– tadman, Commented Sep 20, 2017 at 22:05
Why has this been down voted? It might be an entry level php question, but we all have different experience levels. — Henry
– Henry, Commented Sep 20, 2017 at 22:10

Henry · Accepted Answer · 2017-09-20 22:03:10Z

1

Simply do this:

$array = array();
while($row = mysqli_fetch_assoc($result))
{
    $array[] = $row;
}

To access your results you can do this:

$firstResultRow = $array[0];
$firstResultName = $array[0]['id'];
$firstResultName = $array[0]['name'];

This will tell you if a particular row exists:

if(isset($array[$x])) {
    $aRow = $row[$x];
    // do stuff with $aRow;
}

This will give you a row count for your array:

$rowCount = count($array);

This will set up a loop through your array:

foreach($array as $index => $row) {
    $id = $row['id']
    $name = $row['name'];
    // $index will have the array index of the current row. 0 -> $rowCount - 1
}

edited Sep 20, 2017 at 22:03

answered Sep 20, 2017 at 21:57

Henry

7,9213 gold badges40 silver badges40 bronze badges

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

3 Comments

tiger_groove Over a year ago

Thank you, and how can I access each index?

Henry Over a year ago

Added some more details @kkmoslehpour

tiger_groove Over a year ago

I have tried to use the foreach to echo the values, however, i am getting " Illegal string offset 'name' " have you seen this before?

kyle · Accepted Answer · 2017-09-20 22:07:20Z

1

Don't specifically store the index of $row but rather store the whole row.

$array = array();
while($row = mysqli_fetch_assoc($result))
{
    $array[] = $row;
}

Then $array will have the following structure:

$array = [
    [
       'id'=> ...,
       'name' => ...,
    ],
    ...
];

To initially access all of the results from [mysqli_fetch_assoc][1] you will want to use the while loop like you are, mysqli_fetch_assoc will continue to output results until it doesn't have any more data. Then at that point mysqli_fetch_assoc will return NULL. This will signal to your while loop to stop iterating.

Then to access variables inside of your $array, I recommend using foreach.

You could then do:

foreach ($array as $row) {
  do something with $row['name'] or $row['id']
}

You could also use a for loop, but it takes more work IMO. Compare the above with this alternative:

for ($i = 0; $i < count($array); $i++) {
   $row = $array[$i];
   do something with $row['name'] or $row['id']
}

edited Sep 20, 2017 at 22:07

answered Sep 20, 2017 at 21:55

kyle

2,6586 gold badges26 silver badges37 bronze badges

2 Comments

tiger_groove Over a year ago

Oh okay, and how can I access each index?

kyle Over a year ago

@kkmoslehpour Hey I updated my answer to provide a more complete explanation and also provided some options as far as accessing the values.

Collectives™ on Stack Overflow

PHP storing a two dimension array from MySQL query

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related