Using mysql_query and mysql_fetch_array

Question

I am trying to find out which is the proper way to fetch data from my database. Either way works, but what's the difference; an in-depth explanation?

$sql = mysql_query("SELECT * FROM _$setprofile");
while($row = mysql_fetch_array($sql)) {
    $username = $row['user'];
    $password = $row['pass'];
    echo "$username:$password";
}

versus the function below...

$sql = mysql_query("SELECT user,pass FROM _$setprofile");
while($row = mysql_fetch_row($sql)) {
    echo "$row[0]:$row[1]";
}

This is something I've always wanted to know.

Answer 1

The difference is that fetch_array extracts an array containing BOTH numerical and associative indexes (unless you provide an extra option to tell it otherwise), while fetch_row only gets numerical indexes and fetch_assoc only gets associative indexes. Usually, you don't want both.

Use fetch_assoc instead of fetch_array - that ONLY gets you an array with associative indexes. That means it'll run a bit faster (it has to do less work), but the code will be just as clear.

From a functional perspective, the difference is minimal. However, the former has the problem that you're fetching more from the database than you need (SELECT *). It's generally recommended not to select more than you actually need.

Answer 2

The difference is you're re-assigning the variables in the first example. But you could just say:

while(list($username, $password) = mysql_fetch_array($sql)) {
    echo "$username:$password";
}

Or you could pull out a hash

while($row = mysql_fetch_assoc($sql)) {
    echo "{$row['username']}:{$row['password']}";
}

The right way depends on the application or your preference, I personally avoid the numeric indexed arrays unless I specifically need them. Who wants to try to keep a mental tab of what data is in which index?

Answer 3

There's no much difference internally. Both ordinal positions and column names are available in the result set metadata within the MySQL client API, regardless.

Regarding usage, both can be handy in different circumstances. Referencing columns by name is more mnemonic, results in (semi-) self-documenting code, allows you to change the position or number of columns in the query without breaking your code, etc.

But fetching by ordinal is hand too sometimes. For example:

SELECT u.name, d.name FROM user u JOIN department d USING (dept_id)

Now you have two columns with the same name in the result set. If you fetch an associative array, one overwrites the other because an assoc array can only have one value per key. So $row["name"] is one of the names, and you don't necessarily know which it's going to be.

SELECT d.name, COUNT(*) FROM user u JOIN department d USING (dept_id) GROUP BY dept_id

Now you have a column that has no alias, and depending on the RDBMS brand you use, it could invent a funny-looking alias automatically, or else just use the whole expression as the key for the assoc array. So it's nice to be able to use ordinal position instead of column name in this case.

(It's funny how my writing style becomes more informal and chatty when I'm listening to the StackOverflow podcast while I'm writing.)