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 3 down vote favorite

Alright so I have an innate fear of everything MySQL

Here's my table:

+----+-----------------+------+
| id | name            | age  |
+----+-----------------+------+
|  1 | Timmy Mellowman |   23 |
|  2 | Jeff Johnson    |   18 |
+----+-----------------+------+

Here's my PHP code (outside of connecting to the DB)

$raw = mysql_query("SELECT * FROM example") or die(mysql_error()); 
$row = mysql_fetch_array($raw);
echo "\n\n";
print_r($row);

So here's my output:

Array
(
    [0] => 1
    [id] => 1
    [1] => Timmy Mellowman
    [name] => Timmy Mellowman
    [2] => 23
    [age] => 23
)

Why is this the output? Double the name? Why is the the name set for both [1] and [name]?

And on the side is their a better way to use PHP with MySQL

share|improve this question
1  
Heads up! The next major release of PHP is deprecating the mysql_ family of functions. As you seem to be learning how to use PHP, now would be a great time to switch to PDO or mysqli. –  Charles Dec 23 '12 at 3:41
 
This is defined behavior; strictly speaking, this is a "feature" of PHP, not MySQL. MySQL is returning just one copy of the values; PHP is populating the array with two styles of indexes: by numeric position and by name. You can specify which indexes are to be used (name, numeric, or both) with an additional parameter passed to the mysql_fetch_array function. The default, when you don't specify, is BOTH. –  spencer7593 Dec 23 '12 at 3:41
add comment

2 Answers

up vote 5 down vote accepted

Per the PHP docs for mysql_fetch_array:

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).

So in your line $row = mysql_fetch_array($raw); $row receives an array that has both associative and numeric indices since that's the default. If you want only associative indices, use $row = mysql_fetch_array($raw,MYSQL_ASSOC); (or mysql_fetch_assoc()) or is you want only numeric indices, use $row = mysql_fetch_array($raw,MYSQL_NUM); (or mysql_fetch_row()).

To retrieve all your rows, use something like:

while ($row = mysql_fetch_array($raw)) {
    echo "<p>" . $row['id'] . " - " . $row['name'] . " - " . $row['age'] . "</p>\n";  
}
share|improve this answer
 
Thanks, and great answer as I see what you mean now - but it only shows data for the first person? Shouldn't SELECT * FROM example return ALL the rows? –  Mandatory Programmer Dec 23 '12 at 3:38
1  
It does but you are only retrieving the first record. You would have to loop to retrieve them all. See example #2 from the link in my answer. –  j08691 Dec 23 '12 at 3:38
 
Could you be more specific? Still a bit confused, why do I need to loop and how would I do that? I've seen while statements that do this but I'm just rerunning the same command over and over and it will automatically move down a row each time? –  Mandatory Programmer Dec 23 '12 at 3:41
 
If you look at example #2 at php.net/manual/en/function.mysql-fetch-array.php you'll see the while loop example and what that does is retrieve each row from the query, one row at a time, just as you surmised. –  j08691 Dec 23 '12 at 3:43
 
OH ok, yes I didn't read the link part (looked in your answer for example #2)- thank you! –  Mandatory Programmer Dec 23 '12 at 3:47
add comment
up vote 1 down vote

If return type not specified , Default is mysql_fetch_array ( $raw ,MYSQL_BOTH ) which returns both associative and numeric indices as above.

For numeric indices use

$row = mysql_fetch_array($raw, MYSQL_NUM)

For associative indices use

$row = mysql_fetch_array($result, MYSQL_ASSOC)

For accessing all rows use while loop

while ($row = mysql_fetch_array($row, MYSQL_NUM)) {
    printf("ID: %s  Name: %s Age:%s", $row[0], $row[1],$row[2]);  
}

or if are using associative indices

while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
    printf("ID: %s  Name: %s Age:%s", $row["id"], $row["name"],$row["age"]);
}
share|improve this answer
add comment

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.