Fetching MySQLi Data from all rows in a table from a single column. PHP

Question

I have a table which assigns all users an ID number. I am trying to fetch all those ID's and add them into an array for use with a foreach. The method I am using below is not working for me. The array is not being populated with the data from MYSQLi. Can anyone spot any issues?

<?php 
    $servername = "localhost";
    $username = "dasusername";
    $password = "daspassword";
    $dbname = "notadummy";

    $conn = new mysqli($servername, $username, $password, $dbname);

    if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
    } 

    $result = mysql_query("SELECT userid FROM vms_users");

    $data = array();
    while(($row = mysql_fetch_array($result))) {
        $data[] = $row['userid'];
    }

    print_r(array_values($data));

    $conn->close();
?>

have you tried **mysql_fetch_row()** instead of **mysql_fetch_array()** — Joomler, Oct 11 '15 at 00:51
@RishiVishwakarma I gave it a shot however the $data array remains empty via the print_r — Whiskey Simulation, Oct 11 '15 at 00:53
Why are you using mysql instead of mysqli? Mysql has been deprecated for a while now. — frosty, Oct 11 '15 at 00:55
Yup. I'd try writing a Mysqli object and use `mysql_query()` afterwards to. It works like a charm!... — icecub, Oct 11 '15 at 00:56
Thanks for the nudges I rewrote my code and it is now working. — Whiskey Simulation, Oct 11 '15 at 01:01
@fred, I guess OP wasn't trying to mix both, he did that by mistake — Mubin, Oct 11 '15 at 01:23
Yeah it was a mistake. I wasn't trying to mix the two. @fred must have been going through questions quickly and didn't really read what was going on. — Whiskey Simulation, Oct 11 '15 at 01:48

score 2 · Accepted Answer · edited Oct 11 '15 at 01:20

2

I spotted an error for you :) Thanks to @icecub for pointing a typo.

you're connecting using mysqli but you're querying and extracting object using mysql

so change following part

$result = mysql_query("SELECT userid FROM vms_users");
//       ^ here
$data = array();
while(($row = mysql_fetch_array($result))) {
    //       ^ here
    $data[] = $row['userid'];
}

to

$result = $conn->query("SELECT userid FROM vms_users");

$data = array();
while($row = $result->fetch_assoc()) {
    $data[] = $row['userid'];
}

and this should work fine.

edited Oct 11 '15 at 01:20

icecub

8,615
6
41
70

answered Oct 11 '15 at 00:57

Mubin

4,325
5
33
55

Hey thanks for your comment. I noticed that issue as well just before I saw your answer. I did it a bit different but was still able to get the same result. – Whiskey Simulation Oct 11 '15 at 01:02
pleasure buddy :). also, if you've `userid` as `Primary Key` in your `database` then after using `SELECT DISTINCT userid FROM vms_users`, you don't need to use `array_values`. There will be no blank/empty value. IFF you've `userid` a `PK`. – Mubin Oct 11 '15 at 01:06
and if, it's `PK`, you can use this too `SELECT userid FROM vms_users`. this will also yield same results. – Mubin Oct 11 '15 at 01:06
2

If you're going for OOP (which I can only applaud), at least stick to it. So instead use: `while($row = $result->fetch_assoc()) { ... }` – icecub Oct 11 '15 at 01:08
@Mubin Awesome thanks for the information. I will give you Answer credit as we pretty much came up with the same result. Thanks again. – Whiskey Simulation Oct 11 '15 at 01:08
@icecub, thanks man, updated my answer. +1 to you. – Mubin Oct 11 '15 at 01:12
@icecub Great tip! Thanks! I have adjust my code. I just started getting into learning mysqli and I am having a hard time weeding through all the depreciated functions.. – Whiskey Simulation Oct 11 '15 at 01:12
Much better. Now I'd still remove the extra `(...)` around the parameters in the while loop (they're kinda pointless), but that's just me :) – icecub Oct 11 '15 at 01:13
@icecub so, suggest an edit, and I'll accept it, edit the code you way the like :) – Mubin Oct 11 '15 at 01:15
awesome +1 for `$result->fetch_assoc()` ; thanks @icecub – Joomler Oct 11 '15 at 01:19

Fetching MySQLi Data from all rows in a table from a single column. PHP

1 Answers1