PHP/MySQL : Returning unknown columns

Question

I have this script returning an array containing a row of values in a MySQL database table :

$player_login_request = $db->query('SELECT * FROM Players');
while ($player_login = $player_login_request->fetch())
{
    print_r($player_login);
}

Here is the output of this script :

Array ( [id] => 2 [0] => 2 [name] => tom [1] => tom [experience] => 20 [2] => 20 [strength] => 30 [3] => 30 [damages] => 10 [4] => 10 )

It seems that the row of entries is doubled with some other columns named with some numbers. If you look at the structure of my table, the columns [0], [1], ..., [4] contain the same value than the previous columns (id, name, etc) but they shouldn't be there.

Here is the structure of the table if you are interested :

+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| id         | int(11)     | NO   | PRI | NULL    | auto_increment |
| name       | varchar(50) | YES  |     | NULL    |                |
| experience | int(11)     | NO   |     | NULL    |                |
| strength   | int(11)     | NO   |     | NULL    |                |
| damages    | int(11)     | NO   |     | NULL    |                |
+------------+-------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)

As you can see, when I do a SELECT * FROM Players in the command line, the columns are not displayed !

I don't understand the logic behind it. How do I get rid of it ?
I hope someone is here to help :P

Thank you in advance !

fusion3k · Accepted Answer · 2016-02-25 22:51:09Z

2

->fetch() returns a “mixed” array with numeric and associative keys.

In MySQLi, to return only associative array you have to use ->fetch_assoc(), to return only numeric array you have to use ->fetch_row()

In PDO, to return only associative array you have to use ->fetch(PDO::FETCH_ASSOC), to return only numeric array you have to use ->fetch(PDO::FETCH_NUM)

Edit:

In PDO, you can set a default fetch mode in this way:

$connection = new PDO( ... );
$connection->setAttribute( PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC );

Searching around, I have not found the possibility to permanent set the default fetch mode.

I don't know if MySQLi allow to set a default fetch mode.

edited Feb 25, 2016 at 22:51

answered Feb 25, 2016 at 0:56

fusion3k

11.7k4 gold badges27 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

tomfl Over a year ago

Ok, thanks. I have reinstalled PHP recently and I used to goof around doing some programming stuff like that before (just came back to it), but I don't remember having to do what you told me to do. I'm sure your answer is right, but would there be any way to return an associative array by default ?

fusion3k Over a year ago

@tomfl Which driver do you use? PDO or MySQLi?

tomfl Over a year ago

Hello again, I'm using PDO.

tomfl Over a year ago

Oh, that's interesting. I'll look into it. Thank you very much !

Collectives™ on Stack Overflow

PHP/MySQL : Returning unknown columns

1 Answer 1

Edit:

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Edit:

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related