Using PHP to lay out database rows in an array?
I'm just wondering how I could code the execution of the SQL query and then put each row into a new array, for example, you could say that the table looks like this:
$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 can I create a multidimensional array that works like this, the first column data of the first row can be accessed using $ people [0] [1], and the third column of the fifth rows can be accessed using $ people [4] [2].
How can I build an array like this?
Sorry if this is a weird question, this is just that I am new to PHP + SQL and would like to know how to access data directly. Performance and speed are not an issue as I just write small test scripts to get the hang of the language.
a source to share
Can you use a DB module like PEAR :: DB ? If so, check out this Paul Dubois article on Scripting with the DB Pear DB Module. The module has been superseded , but it will show you the basics of more advanced (and more conventional) database practices.
As for your actual question, you can iterate over all the lines and populate the 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 that implements the ArrayAccess interface , asking for a specific string when accessing that index. (This code may be completely wrong, but here's my attempt)
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.
a source to share