Using PHP to place database rows into an array?

Question

I was just wondering how i would be able to code perform an SQL query and then place each row into a new array, for example, lets say a table looked like the following:

$people= mysql_query("SELECT * FROM friends")    

Output:

| ID | Name | Age |   
--1----tom----32   
--2----dan----22   
--3----pat----52   
--4----nik----32   
--5----dre----65

How could i create a multidimensional array that works in the following way, the first rows second column data could be accessed using $people[0][1] and fifth rows third column could be accessed using $people[4][2].

How would i go about constructing this type of array?

Sorry if this is a strange question, its just that i am new to PHP+SQL and would like to know how to directly access data. Performance and speed is not a issue as i am just writing small test scripts to get to grips with the language.

Answer 1

$rows = array();
while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
    $rows[] = $row; 
}

Answer 2

$array = array();
$sql = "SELECT * FROM friends";
$res = mysql_query($sql) or trigger_error(mysql_error().$sql);
while($row = mysql_fetch_assoc($res)) $array[]=$row;

Answer 3

Are you open to using a DB module, like the PEAR::DB module? If so, check out this article by Paul Dubois on Writing Scripts with PHP's Pear DB Module. The Module has been superseded, but it will show you the basics of some more advanced (and more commonplace) DB practices.

As for your actual question, you could iterate over all the rows and populate an array...

$dsn = "mysqli://testuser:testpass@localhost/test";
$conn =& DB::connect ($dsn);
if (DB::isError ($conn)) { /* ... */ }

$result =& $conn->query ("SELECT * FROM friends");
if (DB::isError ($result)){ /* ... */ }

while ($row =& $result->fetchRow()) {
   $people[] = $row;
}
$result->free ();

Or you could write an object which implements the ArrayAccess interface, requesting a particular row when you refer to that index. (This code could be completely wrong but here's my try)

class FriendsTable implements ArrayAccess {
    function offsetGet($key) {  
        $result =& $conn->query ("SELECT * FROM friends LIMIT $key, 1",); // careful; this is vulnerable to injection...
        if (DB::isError ($result)){ die ("SELECT failed: " . $result->getMessage () . "\n"); }
        $people = null;
        if ($row =& $result->fetchRow ()) {
           $people = $row;
        }
        $result->free ();
        return $people;
    }  

    function offsetSet($key, $value) {  
        /*...*/ 
    }  

    function offsetUnset($key) {  
        /*...*/ 
    }  

    function offsetExists($offset) {  
        /*...*/ 
    }
}

$people = new FriendsTable();
$person = $people[2]; // will theoretically return row #2, as an array

... or something.