Database values to an array?

[JackFrost2002]

Hi all, i'm stuck.

I have a database which allows multiple data elements to be stored for a given value, each of which has a key.

An example of what i want to do featuring the following tables:

car = list of cars

car_attributes = list of possible attributes (e.g. number of wheels, engine size, etc)

car_data = the actual data for an attribute (e.g. 4 wheels)

A given car can have any number of attributes (>1), e.g. one car might just have the number of wheels set, while another the number of wheels and the engine size.

I want to be able to do a select like this:

select car_attribute.name, car_data.data from car_attributes, car_data where car_attributes.car_id = 1 and car_attributes.id = car_data.id;

This i want to dump into an array that I can use the attribute name as the key, e.g.

$car["wheels"], or
$car["engine"]

Does anyone have an idea how i can do this, coz i have tried a number of syntaxes and none of them give me what i need.

Thanks a lot,

Jim

[jmcneese]

which DB is this?

[vvlad]

Hi
here is modified example from PHP manual for MySQL

    $query = "SELECT * FROM my_table";
    $result = mysql_query($query) or die("Query failed");
$car = array();
 while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
        $car[] = $line;
        }        

or

    $query = "SELECT * FROM my_table";
    $result = mysql_query($query) or die("Query failed");
$car = array();
 while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
        $car[$line[car_name]] = $line;
        }        

now you can get to any car in array using this:

$car[10][attribute_name]
or
$car[car_name][attribute_name] in second example

hope this will help

[JackFrost2002]

Hi vvlad,

Thanks for the help.

Could you explain how this works, what does MYSQL_ASSOC do?

ALso, if I have a select statement like this:

select field1, field2 ...

How can i output this to an array such that field1 is the key, and field2 is the value??

Thanks for your help!

Jim