Make an array from SQL query

Question

I'm trying to go to my DB, get info from a bunch of columns based on two variables, and return all the values of all of those columns into a php list (or array), so for example, the final result would be something like $output = array($foo,$bar,$hello);

Here is the code I have right now to connect to the DB and my query, I know I am missing the vital code here, hoping someone can help me, thanks.

<? php

$var1 = 20.0;
$var2 = 15.0;

function getFromDB($var1, $var2){

mysql_connect("localhost", "test", "test") or die (mysql_error());
mysql_select_db("test") or die(mysql_error());
$queryString = "SELECT * from table1 WHERE foo=".$var1."AND bar=".$var2;
$go = mysql_query($queryString);
while($row = mysql_fetch_array($go)){
echo $row['col1']; 
echo $row['col2'];
echo $row['col3'];

}
}

getFromDB($var1,$var2);
?>

The idea is to have the values of col1, 2, and 3 in an array. Thanks! Sam

You should stop using `mysql_*` functions. They're being deprecated. Instead use [PDO](http://php.net/manual/en/book.pdo.php) (supported as of PHP 5.1) or [mysqli](http://php.net/manual/en/book.mysqli.php) (supported as of PHP 4.1). If you're not sure which one to use, [read this SO article](http://stackoverflow.com/questions/13569/mysqli-or-pdo-what-are-the-pros-and-cons). — Matt, Aug 07 '12 at 13:47
The _already_ are in an array. `$row` is an array. If you want a multidimensional array containing all rows, use `$rowset[] = $row` inside the while loop — Michael Berkowski, Aug 07 '12 at 13:48
You put those variables in an array just as you would put any variable into an array. How do you normally do it. The answer to your question is in your question! And as @Matt pointed out you should be using a better database connection method. Your code is likely vulnerable to SQL Injection — Cfreak, Aug 07 '12 at 13:48

score 1 · Accepted Answer · answered Aug 07 '12 at 13:50

1

Either one of these will work:

while($row = mysql_fetch_array($go)){
  $output[] = array($row['col1'], $row['col1'], $row['col1']);
}

or:

while($row = mysql_fetch_array($go)){
  $output[] = $row;
}

The one you should use depends on how the $row-data looks like.

answered Aug 07 '12 at 13:50

Marcus Olsson

2,485
19
35

thanks a lot, and thank you to all the other responses as well – Sam Creamer Aug 07 '12 at 13:54

score 0 · Answer 2 · answered Aug 07 '12 at 13:47

0

$data = array ();
while ( $row = ...)
{
    // appends the contents of $row to a new value in $data
    $data[] = $row;
}

print_r ($data); // <-- to see the output

answered Aug 07 '12 at 13:47

DavidS

1,660
1
12
26

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

0

$array = array();
while ($row = mysql_fetch_array($go)) {
    $array[] = array($row['col1'], $row['col2'], $row['col3']);
}

Please note that you should stop using mysql_* functions. They're being deprecated. Instead use PDO (supported as of PHP 5.1) or mysqli (supported as of PHP 4.1). If you're not sure which one to use, read this SO article.

edited May 23 '17 at 12:23

Community

1
1

answered Aug 07 '12 at 13:48

Matt

6,993
4
29
50

Why was this down-voted? It's the correct answer AND will help OP to improve his code moving forward. – Matt Aug 07 '12 at 13:51

score 0 · Answer 4 · answered Aug 07 '12 at 13:50

0

Replace

echo $row['col1'];
echo $row['col2'];
echo $row['col3'];

with

$array = $row;

and return $array.

answered Aug 07 '12 at 13:50

Shivam Sarodia

417
1
4
20

Why not `$array[] = $row`? If you're going to preserve the indices, this is how you would do it efficiently. – Matt Aug 07 '12 at 13:53
Thanks, I didn't even think of that... it's been a while since I've coded in PHP. – Shivam Sarodia Aug 07 '12 at 13:54

Make an array from SQL query

4 Answers4