Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

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.

share|improve this question
2  
Please dont call your table 'arrays thats bad practice. It should be more descriptive of the objects you are storing – allen213 May 28 '12 at 9:46

3 Answers

up vote 3 down vote accepted

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 />";
}
share|improve this answer
Many thanks for both your solution, and your insightful suggestions. I am a better programmer for all of it! – user1421347 May 28 '12 at 10:05
up vote 1 down vote

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

share|improve this answer
up vote 0 down vote

Maybe try something like:

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

print_r ($array2);
share|improve this answer
I guess $array2[] is better if you're only adding one variable at a time. – Jared May 28 '12 at 9:47

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.