Echo all results from mysql_query

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?

John Woo · Accepted Answer · 2013-03-31T01:37:21.687

9

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..

edited Mar 31 '13 at 01:37

answered Mar 31 '13 at 01:29

John Woo

258,903
69
498
492

Beware of the relatively low default length limit on `GROUP_CONCAT` – Explosion Pills Mar 31 '13 at 01:33
@J W well i would prefer to have them displayed in an unordered list. so would I use something like `return '
- ' . implode('
';`
– Zach Starnes Mar 31 '13 at 01:36

score 1 · Answer 2 · answered Mar 31 '13 at 01:33

1

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());

answered Mar 31 '13 at 01:33

Explosion Pills

188,624
52
326
405

I have a connect.php that already connects me to the database. could i not still use that instead of the `new PDO(creds)`? – Zach Starnes Mar 31 '13 at 01:55
@zachstarnes no you can't because `PDO` will not create a "global" connection (which is frankly not great practice). You can however wrap `PDO` with another class that includes the credentials and just create that class `class DB { public function __construct() { return new PDO(credentials); }}` `$pdo = new DB;` – Explosion Pills Mar 31 '13 at 02:02
Okay. I have multiple functions that use `mysql_query` so each time I do a query I have to do what you did above? (just making changes to the `pdo->query`) so if i change the username or password used to access the database I have to change it on all of them? – Zach Starnes Mar 31 '13 at 02:05
@zachstarnes that will work for now, but you should use properly parameterized queries in the cases where the queries require input – Explosion Pills Mar 31 '13 at 02:06

score 0 · Answer 3 · edited May 23 '17 at 12:25

0

You can use a while loop when fetching from the db.

You shouldn't use mysql_* functions in new code (why?), they are deprecated. Use PDO or MySQLi instead

edited May 23 '17 at 12:25

Community

1
1

answered Mar 31 '13 at 01:29

kero

10,647
5
41
51

score 0 · Answer 4 · edited May 18 '14 at 00:01

0

mysql_* are deprecated

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

edited May 18 '14 at 00:01

BenMorel

34,448
50
182
322

answered Mar 31 '13 at 01:30

internals-in

4,798
2
21
38

Echo all results from mysql_query

4 Answers4