Put mysql results in one php array, like this?

Question

To get an array like this array("123","456","789"); I use the code:

$Regids = mysql_query("SELECT regid FROM $tabel WHERE active = '1'");
while($row = mysql_fetch_array($Regids))
{
    $result_array[] = "\"".$row['regid']."\"";
}
$regIDs = implode(',', $result_array);
$registrationIDs = array($regIDs); // array("123","456","789");

but I would expect PHP/mySQL has a simpler/faster solution for this?

@PetervanderWal, I need this array structure to send push messages with gcm. (from Holland Peter?) — Harry, Dec 01 '13 at 13:47
Please don't use `mysql_*` functions anymore, they are deprecated. See [Why shouldn't I use mysql_* functions in PHP?](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) for details. Instead you should learn about [prepared statements](http://bobby-tables.com/php.html) and use either [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli). If you can't decide which, [this article](http://php.net/manual/en/mysqlinfo.api.choosing.php) will help you. If you pick PDO, [here is a good tutorial](http://wiki.hashphp.org/PDO_Tutorial_for_MySQL_Developers). — Marcel Korpel, Dec 01 '13 at 13:55

Zeeshan · Answer 1 · 2013-12-01T14:19:09.780

0

try this .. use Group concat in query ...

$Regids = mysql_fetch_array(mysql_query("SELECT GROUP_CONCAT(regid) as regids FROM $tabel WHERE active = '1'"));
echo $Regids[0]['regids']; // 123,456,789

for getting result "123","456","789" try this

$Regids = mysql_fetch_array(mysql_query("SELECT GROUP_CONCAT('\"',CONCAT(regid),'\"') as regids FROM $tabel WHERE active = '1'"));
    echo $Regids[0]['regids']; // "123","456","789"

edited Dec 01 '13 at 14:19

answered Dec 01 '13 at 13:51

Zeeshan

1,659
13
17

Your result is 123,456,789. I need "123","456","789" – Harry Dec 01 '13 at 14:04
try to use this query ... mysql_fetch_array(mysql_query("SELECT GROUP_CONCAT(CONCAT("'",regid,"'")) as regids FROM $tabel WHERE active = '1'")); – Zeeshan Dec 01 '13 at 14:07

score 0 · Answer 2 · answered Dec 01 '13 at 13:57

I doubt that your code produces the result you want.

// assuming the this query produces 123,456,789
$Regids = mysql_query("SELECT regid FROM $tabel WHERE active = '1'");
// $row contains: array("123")
while($row = mysql_fetch_array($Regids))
{
    $result_array[] = "\"".$row['regid']."\"";
}
// $result_array now contains: array("\"123\"", "\"456\"", "\"798\"");
$regIDs = implode(',', $result_array);
// $regIDS now contains a single string: "\"123\",\"456\",\"798\"";
$registrationIDs = array($regIDs);
// registrationIDs now is an array containing a single string: array("\"123\",\"456\",\"798\"");

If you really need an array that looks like this: array("123","456","789"); it is much simpler.

$Regids = mysql_query("SELECT regid FROM $tabel WHERE active = '1'");
while($row = mysql_fetch_array($Regids))
    $registrationIDs[] = $row['regid'];

and that's all.

If your mysql result contains the number as an integer instead of an string you can convert it like this:

$Regids = mysql_query("SELECT regid FROM $tabel WHERE active = '1'");
while($row = mysql_fetch_array($Regids))
    $registrationIDs[] = strval($row['regid']);

Also, keep in mind that the mysql_* functions are becoming deprecated. Don't start new code with it and make plans to port your existing code to mysqli_* or PDO.

I did, when you echo $registrationIDs it gives 'array', so you have to use $registrationIDs[x] for one result. In my code the output is correct but I was wondering for a simpler/faster solution. — Harry, Dec 01 '13 at 14:32
You should use `print_r()` or `var_dump()` for debugging, they give you the actual variable content. `echo` only gives you a textual representation which can be misleading — Gerald Schneider, Dec 01 '13 at 14:53
You are right about that, my mis-thinking answer, but still your solution does not give what I want. It gives: `Array ( [0] => 123 [1] => 456 [2] => 789` — Harry, Dec 01 '13 at 15:14

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

You can use PDO implementation. At first sight, it may be more difficult to understand, but once you get used to it, it reveals to be really powerful and handy (IMHO! One year ago i switched to it and i love it)!

For your example, the PDO implementation would be like this:

/*CONNECT TO DB, FIRST. $dbh contains a handler to the current DB connection*/
$stmt = $dbh->prepare("SELECT regid FROM table WHERE active = '1'");
$stmt->execute();
$Regids = $stmt->fetchAll(PDO::FETCH_COLUMN,0);

There are many formatting options you can specify, like

PDO::FETCH_COLUMN

PDO::FETCH_ASSOC

and more...These options will allow you to get the array formatted as you prefer. As you can see i got the result in just 3 simple rows.

EDIT

Note: you are not escaping PHP variables before inserting them in your Query, and your code may suffer SQL INJECTION. Be careful!! Here is a simple guide to prevent it. (In my code, just to be clear, i avoided the problem by just putting the table name instead of $table, just to show simply how to get the result you wanted.)

Put mysql results in one php array, like this?

3 Answers3