How to make a php array of a mysql_fetch_array while loop

Question

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.

Solution 1

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

Solution 2

try to change your code this way

$array2 = array();
$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);

I believe that is the result you are looking for

Comments

[Jared]

I guess $array2[] is better if you're only adding one variable at a time.

[user1421347]

Many thanks for both your solution, and your insightful suggestions. I am a better programmer for all of it!