Populate Arrays from a Database

One of my earliest questions working with arrays is: How do I get data off a column in a table (or database) into an array? Actually, this is so useful that it's no wonder why so many people ask this same question so often on popular PHP message boards and forums.

Some sample data in a database table

code:
TABLE: tbl_category
===============================
cat_id  |cat_title  |cat_parent
--------+-----------+----------
 1      |graphics   | 0
 2      |web design | 0
 3      |hardware   | 0
 4      |php        | 2
 5      |array      | 4

So this is our sample data and table. Now, for whatever reasons, some of us might want to get the data off one column of our database table and put it inside an array so that we may use it later on in our script.

Let's assume for one minute that we want to create an array called $cat_titles with all the category titles i.e. cat_title from our database where the cat_parent value is 0.

Populating an array using MYSQL_FETCH_ROW()

php:


<?php
//  Using MYSQL_FETCH_ROW()
//  =======================

//  we want to build an array of data from the CAT_TITLE column in
//  our TBL_CATEGORY table.
$sql = "SELECT cat_id, cat_title FROM tbl_category
 WHERE cat_parent=0";
$result = mysql_query( $sql,$conn );
while( $row=mysql_fetch_row($result) )
{
  $cat_titles[] = $row[1];
  //  do stuff with other column
  //  data if we want
}
mysql_free_result( $result );
?>


Populating an array using MYSQL_FETCH_ASSOC()

php:


<?php
//  Using MYSQL_FETCH_ASSOC()
//  =========================

//  we want to build an array of data from the CAT_TITLE column in
//  our TBL_CATEGORY table.
$sql = "SELECT cat_id, cat_title FROM tbl_category
 WHERE cat_parent=0";
$result = mysql_query( $sql,$conn );
while( $row=mysql_fetch_assoc($result) )
{
  $cat_titles[] = $row['cat_title'];
  //  do stuff with other column
  //  data if we want
}
mysql_free_result( $result );
?>


We have ARRAY!

Yes, using either method described above, we get one array named $cat_titles with 3 values: graphics, web design and hardware.

Now, we can use this array any way we choose but most likely, you would use it like this:

php:


<?php
//  Using our $CAT_TITLES array
//  ===========================
echo "<p>\n";
foreach( $cat_titles as $v )
{
  echo $v."<br />\n";
}
echo "</p>\n";
?>


Which would result in HTML being output like this:

code:
<p>
graphics<br />
web design<br />
hardware<br />
</p>

TOP

How old are you?

 Teenager

 20 something

 30 something

 40 something

 Over 50