Get MySQL column names in PHP array

Question

Can someone please help me how to get my MySQL column names out in my while loop? My code goes like this:

if (mysqli_connect_errno()) {
    echo "MySQL failed: " . mysqli_connect_errno();
}

if (isset($_GET['date'])) {
    $get_date = $_GET['date'];
    $result = mysqli_query($conn, "SELECT * FROM stats WHERE form_date = '$get_date'");
    $col = array();
    while ($row = mysqli_fetch_array($result)) {
        $col[] = $row['year'];
        $col[] = $row['month'];
        $col[] = $row['day'];
        $col[] = $row['weekday'];
        $col[] = $row['form_date'];
        $col[] = $row['name'];
        $col[] = $row['count'];
        $col[] = $row['lang'];
    }
    echo "<pre>";
    print_r($col);
    echo "</pre>";
    mysqli_close($conn);
} else {
    echo "Please set a date value in URL parameter ?date=[YYYY-MM-DD]";
}

My returned array then looks like this:

Array
(
    [0] => 2014
    [1] => 01
    [2] => 01
    [3] => Wednesday
    [4] => 2014-01-01
    [5] => Michael K
    [6] => 41
    [7] => SE
    [8] => 2014
    [9] => 01
    [10] => 01
    [11] => Wednesday
    [12] => 2014-01-01
    [13] => Nicklas S
    [14] => 40
    [15] => DK
    [16] => 2014
    [17] => 01
    [18] => 01
    [19] => Wednesday
    [20] => 2014-01-01
    [21] => Jesper S
    [22] => 5
    [23] => SE
)

However I would like my array to get returned like this:

Array
(
    [year] => 2014
    [day] => 01
    [month] => 01
    [weekday] => Wednesday
    [form_date] => 2014-01-01
    [name] => Michael K
    [count] => 41
    [lang] => SE
    [year] => 2014
    [month] => 01
    [day] => 01
    [weekday] => Wednesday
    [form_date] => 2014-01-01
    [name] => Nicklas S
    [count] => 40
    [lang] => DK
    [year] => 2014
    [month] => 01
    [day] => 01
    [weekday] => Wednesday
    [form_date] => 2014-01-01
    [name] => Jesper S
    [count] => 5
    [lang] => SE
)

So the keys in my array matches the column names I have in my database. I just can't seem to wrap my head around how I do this. Can someone help? :-)

@RocketHazmat Yes you can, that's an associative array in PHP — mituw16
– mituw16, Commented May 27, 2014 at 18:43
@mituw16 associative arrays are fine, but not with duplicate keys. — Sterling Archer
– Sterling Archer, Commented May 27, 2014 at 18:43
Fair point, I didn't notice that. Was just commenting that associative arrays are possible in PHP — mituw16
– mituw16, Commented May 27, 2014 at 18:44

Robert · Accepted Answer · 2014-05-27 18:50:38Z

2

Change

$col = array();
while ($row = mysqli_fetch_array($result)) {
    $col[] = $row['year'];
    $col[] = $row['month'];
    $col[] = $row['day'];
    $col[] = $row['weekday'];
    $col[] = $row['form_date'];
    $col[] = $row['name'];
    $col[] = $row['count'];
    $col[] = $row['lang'];
}

to

$col = array();
while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) {
    $col[] = $row;
}

print_r($col);

mysql_fetch_array() has second argument. This optional parameter is a constant indicating what type of array should be produced from the current row data. The possible values for this parameter are the constants MYSQLI_ASSOC, MYSQLI_NUM, or MYSQLI_BOTH.

edited May 27, 2014 at 18:50

answered May 27, 2014 at 18:43

Robert

20.3k6 gold badges58 silver badges85 bronze badges

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

4 Comments

madsobel Over a year ago

Thanks to everybody who reacted this quickly! This was just what I was looking for.

Robert Over a year ago

Check this solution, it won't produce duplicate keys as others suggested because it will be fetched as assoc array. and stored in $col array.

Sterling Archer Over a year ago

@Robert not that I care about a checkmark on a question like this, but comments like "accept this answer" come off as very 'rep-farm-ish'. A good answer will earn the mark without advertising it

Robert Over a year ago

I don't advertise my answer I just write him to check it because I think it will fit his needs. I don't need checkmark I don't write answers to get one I write ones to help people.

mathielo · Accepted Answer · 2014-05-27 18:42:55Z

0

You are looking for mysqli_fetch_assoc() which fetches results as an associative array (column => value).

Then try this:

while ($row = mysqli_fetch_assoc($result)) {
    echo "<pre>";
    print_r($row);
    echo "</pre>";
}

answered May 27, 2014 at 18:42

mathielo

6,7757 gold badges53 silver badges64 bronze badges

Comments

Sterling Archer · Accepted Answer · 2014-05-27 18:44:28Z

0

You can not use an associative array like this because it will have duplicate key indices, which is not allowed. I suggest you work with a restructured array to have each "group" of keys as a nested array.

answered May 27, 2014 at 18:44

Sterling Archer

22.5k19 gold badges86 silver badges121 bronze badges

Comments

GôTô · Accepted Answer · 2014-05-27 19:10:26Z

-1

Use mysql_assoc_array() instead of mysq_fetch_array()

edited May 27, 2014 at 19:10

GôTô

8,0733 gold badges37 silver badges45 bronze badges

answered May 27, 2014 at 18:42

Dmitry Borovkov

2894 silver badges16 bronze badges

Collectives™ on Stack Overflow

Get MySQL column names in PHP array

4 Answers 4

4 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related