How to make a php array of a mysql_fetch_array while loop

arrays, loops, mysql, php, while-loop

Solution

All you need to do here is push the array returned by `mysql_fetch_array()` onto a larger array using `[]`:

$MultiDimArray = array();

while($row = mysql_fetch_array($result))
{
  $MultiDimArray[] = array ( 'Husband' => $row['husband'], 'Wife' => $row['wife'] );
}

print_r($MultiDimArray);

Now, a few comments:

- Please don't write new code with the `mysql_*` extension, use PDO or MySQLi instead.

- Use `mysqli_fetch_assoc()` and `mysqli_fetch_row()` instead of `mysqli_fetch_array()` unless you explicitly want numerically indexed keys as well as associative.

- If you name your columns (including capitalisation) the same way as you use the keys in your code, you can simply push the return value of `mysqli_fetch_assoc()` onto the array.

For example:

while ($MultiDimArray[] = mysql_fetch_assoc($result)) continue;

- Don't use `or die(mysql_error());` constructs. You should never show the result of `mysql_error()` (or it's PDO/MySQLi equivalent) in a production environment, and if you must do it for debugging make sure you remove it from your code immediately afterwards.

- Don't use `sizeof($array1)` directly as a loop condition. It is very inefficient, the only time you should ever do it is if the size of your array may change during the loop. You should call `sizeof()` once before the loop, store the result in a variable, and compare `$col` against that variable.

For example:

for ($col = 0, $size = sizeof($array1); $col < $size; $col++)
{
  echo $array1[$col]['Wife']."<br />";
}

// Also, don't forget you could simply do this:
foreach ($array1 as $row)
{
  echo $row['Wife']."<br />";
}

Problem

At the moment I have a large number of php files, each one with an array statement in the format: ``` $array1 = array ( array ( 'Husband' => 'bob', 'Wife' => 'ann' ), array ( 'Husband' => 'fred', 'Wife' => 'donna' ), array ( 'Husband' => 'john', 'Wife' => 'mary' ) ); ``` This of course returns from `print_r ($array1)` the output (A): ``` Array ( [0] => Array ( [Husband] => bob [Wife] => ann ) [1] => Array ( [Husband] => fred [Wife] => donna ) [2] => Array ( [Husband] => john [Wife] => mary ) ) ``` In a separate class file, I access $array1 data, using statements like: ``` for ($col = 0; $col < sizeof($array1); $col++) { echo $array[$col]['Wife']."<br />"; } ``` On the theory that a large number of anything is a database, I am wanting to migrate the data in the multiple php array files array into a MySQL database, and access the MySQL data from my class file. So I set up the following in MySQL: ``` CREATE TABLE IF NOT EXISTS `arrays` ( `id` int(5) NOT NULL auto_increment, `husband` varchar(255) collate utf8_unicode_ci NOT NULL, `wife` varchar(255) collate utf8_unicode_ci NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ; INSERT INTO `arrays` (`id`, `husband`, `wife`) VALUES (1, 'bob', 'ann'), (2, 'fred', 'donna'), (3, 'john', 'mary'); ``` Then I access this MySQL database from PHP: ``` $sql = "SELECT * FROM `database`.`arrays`"; $link = mysql_connect ('localhost', 'username', 'password' ) or die(mysql_error()); mysql_select_db ('database', $link) or die(mysql_error()); $result = mysql_query ($sql) or die(mysql_error()); while($row = mysql_fetch_array($result)) { $array2 = array ( 'Husband' => $row['husband'], 'Wife' => $row['wife'] ); print_r ($array2); } ``` This will produce output (B) like: ``` Array ( [Husband] => bob [Wife] => ann ) Array ( [Husband] => fred [Wife] => donna ) Array ( [Husband] => john [Wife] => mary ) ``` My question is how do I make a php array of the mysql_fetch_array while loop, so that output B looks like output A. I want to be able to continue to access the data in my class file in the manner `$array[$col]['Wife']`, but from the MySQL database.

Original source