If you want to retrieve every column in every row without having to know each column name, you can count the number of columns in the table.
$queryCols = "SHOW COLUMNS FROM players":
$queryPlayers = "SELECT * FROM players";
$result = mysql_query[$queryPlayers];
$playerColumns = mysql_query[$queryCols];
$colsResult = mysql_fetch_row[$playerCols];
$numFields = count[$colsResult];
while [$row = mysql_fetch_array[$result]] {
for [$i = 0; $i < $numFields; $i++] {
$row[$i]; // Each column in each row
}
}
Here an example of a mysqli select statement which is the successor to the soon to be deprecated mysql_* statements:
$DB_NAME = 'test';
$DB_HOST = 'localhost';
$DB_USER = 'root';
$DB_PASS = '';
$mysqli = new mysqli[$DB_HOST, $DB_USER, $DB_PASS, $DB_NAME];
if [mysqli_connect_errno[]] {
printf['Connection to $s using %s@$s failed: %s\n',
$DB_NAME, $DB_USER, $DB_HOST, mysqli_connect_error[]];
exit[];
}
$query = "SELECT `fname`, `lname`, `team` FROM `players`;";
if [$stmt = $mysqli->prepare[$query]] {
$stmt->execute[];
$stmt->bind_result[$fname, $lname, $team];
$table = close[];
} else {
printf["Prepared Statement Error: %s\n", $mysqli->error];
}
Table
CREATE TABLE IF NOT EXISTS `players` [
`id` int[20] NOT NULL AUTO_INCREMENT,
`fname` varchar[32] NOT NULL,
`lname` varchar[32] NOT NULL,
`team` varchar[64] NOT NULL,
PRIMARY KEY [`id`]
] ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=13 ;
INSERT INTO `players` [`id`, `fname`, `lname`, `team`] VALUES
[1, 'Peyton', 'Manning', 'Denver Broncos'],
[2, 'Matt', 'Ryan', 'Atlanta Falcons'],
[3, 'Tom', 'Brady', 'New England Patriots'],
[4, 'Colin', 'Kaepernick', 'San Francisco 49ers'],
[5, 'Matt', 'Schaub', 'Houston Texans'],
[6, 'Aaron', 'Rodgers', 'Green Bay Packers'],
[7, 'Joe', 'Flacco', 'Baltimore Ravens'],
[8, 'Robert', 'Griffin III', 'Washington Redskins'],
[9, 'Andrew', 'Luck', 'Indianapolis Colts'],
[10, 'Matt', 'Flynn', 'Seattle Seahawks'],
[11, 'Andy', 'Dalton', 'Cincinnati Bengals'],
[12, 'Christian', 'Ponder', 'Minnesota Vikings'];
The Results:
First Name Last Name Team
Peyton Manning Denver Broncos
Matt Ryan Atlanta Falcons
Tom Brady New England Patriots
Colin Kaepernick San Francisco 49ers
Matt Schaub Houston Texans
Aaron Rodgers Green Bay Packers
Joe Flacco Baltimore Ravens
Robert Griffin III Washington Redskins
Andrew Luck Indianapolis Colts
Matt Flynn Seattle Seahawks
Andy Dalton Cincinnati Bengals
Christian Ponder Minnesota Vikings
[PHP 4, PHP 5]
mysql_fetch_array — Fetch a result row as an associative array, a numeric array, or both
Description
mysql_fetch_array[resource $result, int $result_type = MYSQL_BOTH]: array
Parameters
resultThe result resource that is being evaluated. This result comes from a call to mysql_query[].
result_type The type of array that is to be fetched. It's a constant and can take the following values: MYSQL_ASSOC, MYSQL_NUM, and
MYSQL_BOTH.
Return Values
Returns an array of strings that corresponds to the fetched row, or false if there are no more rows. The type of returned array depends on how result_type is defined. By using MYSQL_BOTH [default], you'll get an array with both associative and number indices. Using MYSQL_ASSOC, you only get associative
indices [as mysql_fetch_assoc[] works], using MYSQL_NUM, you only get number indices [as mysql_fetch_row[] works].
If two or more columns of the result have the same field names, the last column will take precedence. To access the other column[s] of the same name, you must use the numeric index of the column or make an alias for the column. For aliased columns, you cannot access the contents with the original column name.
Examples
Example #1 Query with aliased duplicate field names
SELECT table1.field AS foo, table2.field AS bar FROM table1, table2
Example #2 mysql_fetch_array[] with MYSQL_NUM
Example #3 mysql_fetch_array[] with MYSQL_ASSOC
Example #4 mysql_fetch_array[] with MYSQL_BOTH
Notes
Note: Performance
An important thing to note is that using mysql_fetch_array[] is not significantly slower than using mysql_fetch_row[], while it provides a significant added value.
Note: Field names returned by this function are case-sensitive.
Note: This function sets NULL fields to the PHP
nullvalue.
See Also
- mysql_fetch_row[] - Get a result row as an enumerated array
- mysql_fetch_assoc[] - Fetch a result row as an associative array
- mysql_data_seek[] - Move internal result pointer
- mysql_query[] - Send a MySQL query
robjohnson at black-hole dot com ¶
20 years ago
Benchmark on a table with 38567 rows:
mysql_fetch_array
MYSQL_BOTH: 6.01940000057 secs
MYSQL_NUM: 3.22173595428 secs
MYSQL_ASSOC: 3.92950594425 secs
mysql_fetch_row: 2.35096800327 secs
mysql_fetch_assoc: 2.92349803448 secs
As you can see, it's twice as effecient to fetch either an array or a hash, rather than getting both. it's even faster to use fetch_row rather than passing fetch_array MYSQL_NUM, or fetch_assoc rather than fetch_array MYSQL_ASSOC. Don't fetch BOTH unless you really need them, and most of the time you don't.
KingIsulgard ¶
13 years ago
I have found a way to put all results from the select query in an array in one line.
// Read records
$result = mysql_query["SELECT * FROM table;"] or die[mysql_error[]];
// Put them in array
for[$i = 0; $array[$i] = mysql_fetch_assoc[$result]; $i++] ;
// Delete last empty one
array_pop[$array];
You need to delete the last one because this will always be empty.
By this you can easily read the entire table to an array and preserve the keys of the table columns. Very handy.
puzbie at facebookanswers dot co dot uk ¶
10 years ago
Yes, that will generate a dummy array element containing the false of the final mysql_fetch_array. You should either truncate the array or [more sensibly in my mind] check that the result of mysql_fetch_array is not false before adding it to the array.
mehdi dot haresi at gmail dot com ¶
13 years ago
For all of you having problems accessing duplicated field names in queries with their table alias i have implemented the following quick solution:
The usage of this function will be pretty similar to calling mysql_fetch_array:
Lets asume we have 2 tables student and contact each having fID as the index field and want to access both fID fields in php.