CODEWITHSUNDEEP
phpmysqlarrays
Will
Will

Reputation: 2358

Create PHP array from MySQL column

mysql_fetch_array will give me an array of a fetched row. What's the best way generate an array from the values of all rows in one column?

Upvotes: 18

Views: 111502

Answers (12)

Royendgel Silberie
Royendgel Silberie

Reputation: 176

// I'm using this for years and it works very good !! it's easy and logic
$translate = array();

while ($row = mysql_fetch_assoc($result))
{
    $columnkey = $row['translshort']; // is what you want the key to be 
    $translate[$columnkey] = $row[$language]; // this is the key and the value in the array 
}

Upvotes: 1

Max Oriola
Max Oriola

Reputation: 1374

Many years later, just another way. I don't know if its performance is better or worse than the while loop method.

For values without commas yo can do:

$res = mysql_query("SELECT GROUP_CONCAT(colname) AS values FROM tablename");
$row = mysql_fetch_assoc($res);
$arrOfValues = explode(',', $row['values']);

For a generic solution, you may use a separator:

$res = mysql_query("SELECT GROUP_CONCAT(colname SEPARATOR '##_sep_##') AS values FROM tablename");
$row = mysql_fetch_assoc($res);
$arrOfValues = explode('##_sep_##', $row['values']);

Upvotes: 0

IlludiumPu36
IlludiumPu36

Reputation: 4304

If none of the above work, try:

while( $row = mysqli_fetch_assoc( $result)){
     $array[] = $row['id'];
}

mysql_fetch_assoc is deprecated.

Upvotes: 2

quasi
quasi

Reputation: 441

If you don't need other information in that table, you can query for only the column you need and it makes it all more easy:

$query = mysql_query("SELECT * FROM table WHERE id='$int' LIMIT 1");
$column = array();
$column  = mysql_fetch_array($query);

Upvotes: -1

user2334541
user2334541

Reputation: 23

you can do this :

        $columns = array();
        $i=1;
        while( $row = mysql_fetch_array($sql) )
           {
              $columns [$i]=$row['value'];
              $i++;
           }

Upvotes: -1

Faron
Faron

Reputation: 1243

$query = mysql_query('SELECT * from yourTable');
function mysql_field_array( $query ) {
    $field = mysql_num_fields( $query );
    for ( $i = 0; $i < $field; $i++ ) {
        $names[] = mysql_field_name( $query, $i );
    }
    return $names;
}

$fields = mysql_field_array( $query );
$output = implode( ',', $fields );   //outputs the columns names
//echo count( $fields );  //this use if you want count of columns.
$columns = '{\"fields\":\".json_encode($output).\"}';
echo $columns; //for JSON output

Upvotes: -1

code_burgar
code_burgar

Reputation: 12323

$result = mysql_query("SELECT columnname FROM table WHERE x=y");

$columnValues = Array();

while ( $row = mysql_fetch_assoc($result) ) {

  $columnValues[] = $row['columnname'];

}

Upvotes: 3

GSto
GSto

Reputation: 42380

you could loop through the array, and create a new one, like so:

$column = array();

while($row = mysql_fetch_array($info)){
    $column[] = $row[$key];
//Edited - added semicolon at the End of line.1st and 4th(prev) line

}

Upvotes: 30

VolkerK
VolkerK

Reputation: 96189

If you use PDO instead of php-mysql the return value of PDO::query() implements the Traversable interface, meaning you can use it e.g. with foreach() (unlike the mysql result resource you get from mysql_query).

foreach( $pdo->query('SELECT x,y,z FROM foo') as $row ) {

And in case this does not suffice and you really, really need an array (i.e. get_class($x)==='Array'), there's the fetchAll() method.

Upvotes: 1

Dominic Rodger
Dominic Rodger

Reputation: 99841

Loop through the result:

$result = mysql_query(...);
$data = array();
while ($row = mysql_fetch_array($result)) {
    array_push($data, $row["columnyouwant"]);
}

Upvotes: 4

Tom Haigh
Tom Haigh

Reputation: 57845

There is no function to do this using the mysql extension, you can do this:

$result = array();
while ($row = mysql_fetch_array($r, MYSQL_NUM)) {
    $result[] = $row[0];
}

It is apparently marginally faster to fetch the columns in a numerically indexed array, and there is no real benefit here to having the associative array format.

Upvotes: 8

Gumbo
Gumbo

Reputation: 655785

Use a while loop to get the records and store them in an array:

$array = array();
while ($row = mysql_fetch_array()) {
    $array[] = $row['column-x'];
}

Upvotes: 4

Related Questions