Echo all results from mysql_query [duplicate]

Question

I am trying to create a function that will query a database and output all the results from the array. For some reason it is only displaying one result.

here is the function

function reguser_list() {
$query = mysql_query("SELECT `user_name` FROM `users` WHERE `active` = 1");
$reguser_names = mysql_fetch_assoc($query);
$list_names = implode(', ', $reguser_names);

echo $reguser_names;
}

there are currently 2 users in the database that are active but this only display one name how can I get it to display both?

Answer 1

You need to iterate the result of mysql_query, eg.

while ($row = mysql_fetch_assoc($result)) 
{
    echo $row['user_name'];
}

---

since i see that you want to concatenate the result by separating it with comma, you can directly do it in MySQL buy using GROUP_CONCAT()

SELECT GROUP_CONCAT(user_name) as UserName FROM users WHERE active = 1

in PHP,

$query = mysql_query("SELECT GROUP_CONCAT(user_name) as UserName FROM users WHERE active = 1");
$reguser_names = mysql_fetch_assoc($query);
$list_names = $reguser_names['UserName'];

The default limit of GROUP_CONCAT() is 1024.

If you want change the value of the variable the syntax is:

SET [GLOBAL | SESSION] group_concat_max_len = val;
// where val is an unsigned integer

more on this link..

Answer 2

If you're going to stick with ext/mysql, you need to use a while loop. PDOStatement has a very nice fetchAll method that can essentially be substituted for what you have.

$pdo = new PDO('mysql:host=?', 'user', 'pass');
//If `active` is 1 or 0 only, you don't need the `= 1`
echo implode(', ', $pdo->query("SELECT user_name FROM users WHERE active = 1")
    ->fetchAll());