How to loop through an array return from the Query of Mysql

Question

This might be easy for you guys but i could't get it.

I have a php class that query the database and return the query result. I assign the result to an array and wants to use it on my main.php script. I have tried to use echo $var[0] or echo $var[1] but the output are 'array' instead of my value. Anyone can help me about this issue? Thanks a lot!

My php class

<?php 
class teamQuery {

            function teamQuery(){


            }
            function getAllTeam(){
                $connection = mysql_connect(DB_SERVER,DB_USER,DB_PASS);
                    if (!$connection) {
                        die("Database connection failed: " . mysql_error());
                    }

                    $db_select = mysql_select_db(DB_NAME,$connection);
                    if (!$db_select) {
                        die("Database selection failed: " . mysql_error());
                    }

                  $teamQuery=mysql_query("SELECT * FROM team", $connection);

                    if (!$teamQuery){
                        die("database has errors: ".mysql_error());
                          }
                    $ret = array();   

                    while($row=mysql_fetch_array($teamQuery)){

                    $ret[]=$row;
                    }     
                    mysql_free_result($teamQuery); 

                    return $ret;
                    }


            }


?>

My php on the main.php

$getTeam=new teamQuery();
$team=$getTeam->getAllTeam();

   //echo $team[0] or team[1] output 'array' string!
  // while($team){
  // do something }  can't work either

  // How to loop through the values??

Thanks!

Answer 1

Try this (and variations thereof):

foreach ($team as $member) {
    echo $member[0];
    echo $member[1];
    //etc.
}

Answer 2

That's because each of the elements in your array is also an array. print_r the array to see it with your own eyes.

For instance, if you want to output the names for each team, you'll have to first loop through the outer array, and access the individual fields inside the loop.

foreach ($team as $t) {
    echo $t["name"] . "<br />"; // hopefully it's "name", the * in your query isn't really self-documenting
}

Answer 3

foreach ($team as $t)
    echo $t[0];

Answer 4

You get this output because $team[0] and $team[1] are indeed arrays. They are the rows of your database table and every row consists of several fields, e.g. id, name etc.
You have 2 dimensional array, that might look like this:

Array
(
    [0] => Array
        (
            [id] => 1
            [name] => foo
        )

    [1] => Array
        (
            [id] => 2
            [name] => bar
        )
)

To loop through the data, use for or foreach:

foreach($team as $member) {
    foreach($member as $k=>$v) {
        echo $k . ': ' . $v;
    }
}

---

Also the documentation about arrays might be worth reading for you.

Answer 5

First advise: use the print_r function to display arrays. This is very handy to see how data is stored within a variable. Wrap it in the HTML tag <pre> so it's not all on one line. Example:

echo '<pre>'; print_r($variable); echo '</pre>';

Second: you probably don't want to save the output like that. Display your $ret variable with print_r and see how the data is being stored. What's more useful is to use mysql_fetch_assoc. Try this:

while ($row = mysql_fetch_assoc($teamQuery)) {
  echo '<pre>'; print_r($row); echo '</pre>'
}

You can see that each row now contains a key and a value, e.g. "name" and "John Doe", "age" and "35", etc. This way you can display a certain value like this:

echo $row['name']; // John Doe

When you retrieve multiple items from the database, and you want to store them in an array, you need a unique identifier for each of those entries. Usually this is the unique ID number from the table in which the data is stored. That way you can group all these keys and values together, like so:

while ($row = mysql_fetch_assoc($teamQuery)) {
  $results[$row['id']]['name'] = $row['name'];
  // etc
}

You can do this manually for each field that you retrieved. If you need all the fields (what you usually do when selecting *), this is easier:

while ($row = mysql_fetch_assoc($teamQuery)) {
  foreach ($row as $key => $value) {
    $results[$row['id']][$key] = $value;
  }
}

The $results array now contains something like this:

Array
(
    [1] => Array
        (
            [id] => 1
            [name] => John Doe
        )

    [2] => Array
      (
          [id] => 2
          [name] => Jane Doe
      )
)

... and all the other values. Now you can use the already mentioned foreach function again to display each of these entries. You don't have to use $key and $value; it's mostly easier to choose something more meaningful. E.g.: instead of $results (or your $ret) use $team or $members, because that's what you're retrieving. Then display each of those members like this:

foreach ($team as $memberId => $member) {
  echo $member['name']; // John Doe, Jane Doe, etc.
}