create array from mysql query php

Question

Hi. I have a little problem that I don't understand. I have a db that has an owner and type (and more off course). I want to get a list of all the type values that has owner equal to the current user, but I get only two result

$sql = "SELECT type FROM cars WHERE owner='".mysql_real_escape_string($_SESSION['username'])."' AND selling='0' ORDER BY id DESC "; 

 $result = mysql_query($sql,$con); 

 print_r(mysql_fetch_array($result));

prints out:

Array ( [0] => 18 [type] => 18 )

and

$sql = "SELECT type FROM cars WHERE owner='".mysql_real_escape_string($_SESSION['username'])."' AND selling='0' "; 

prints out:

Array ( [0] => 16 [type] => 16 ) 

And the result should be something like 19, 19, 18, 17, 16 in an array. Thats all the types that has me as set as owner.

I have got this working now:

for ($x = 0; $x < mysql_num_rows($result); $x++){
 $row = mysql_fetch_assoc($result);  
 echo $row['type']; 
}

Here I print out all the values correctly, but I need to create an array with all the values. I though I could use array_push, but there most be a better way of doing it. I thought I would get all the type values with a simple mysql query.

Answer 1

You do need to iterate through...

$typeArray = array();
$query = "select * from whatever";
$result = mysql_query($query);

if ($result) {
    while ($record = mysql_fetch_array($results)) $typeArray[] = $record['type'];
}

Answer 2

$type_array = array();    
while($row = mysql_fetch_assoc($result)) {
    $type_array[] = $row['type'];
}

Answer 3

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

Answer 4

Very often this is done in a while loop:

$types = array();

while(($row =  mysql_fetch_assoc($result))) {
    $types[] = $row['type'];
}

Have a look at the examples in the documentation.

The mysql_fetch_* methods will always get the next element of the result set:

Returns an array of strings that corresponds to the fetched row, or FALSE if there are no more rows.

That is why the while loops works. If there aren't any rows anymore $row will be false and the while loop exists.

It only seems that mysql_fetch_array gets more than one row, because by default it gets the result as normal and as associative value:

By using MYSQL_BOTH (default), you'll get an array with both associative and number indices.

Your example shows it best, you get the same value 18 and you can access it via $v[0] or $v['type'].

Answer 5

You could also make life easier using a wrapper, e.g. with ADODb:

$myarray=$db->GetCol("SELECT type FROM cars ".
    "WHERE owner=? and selling=0", 
    array($_SESSION['username']));

A good wrapper will do all your escaping for you too, making things easier to read.