using one key to multiple values in php

Question

I'm developing a webaplicattion in php where there will be an area called 'My Products'. This area you can see the products that you listed. Lets say you placed a car to sell. You will have something like:

• Model: R8
• Color: Yellow
• Brand: Audi
• Type: Diesel
• Price: 90000
• CarID: 1

My problem: How can I select the carid so that I have the cardid as the key and the other values (eg. model, color, etc...) as values?

I'm using mysql to store the listings. SQL TABLE:

    +---------+------------------------------------------+------+-----+---------+----------------+
| Field   | Type                                     | Null | Key | Default | Extra          |
+---------+------------------------------------------+------+-----+---------+----------------+
| carid   | int(11)                                  | NO   | MUL | NULL    | auto_increment |
| brand   | enum('Alfa Romeo','Aston Martin','Audi') | NO   |     | NULL    |                |
| color   | varchar(20)                              | NO   |     | NULL    |                |
| type    | enum('gasoline','diesel','eletric')      | YES  |     | NULL    |                |
| price   | mediumint(8) unsigned                    | YES  |     | NULL    |                |
| mileage | mediumint(8) unsigned                    | YES  |     | NULL    |                |
| model   | text                                     | YES  |     | NULL    |                |
| year    | year(4)                                  | YES  |     | NULL    |                |
| user    | varchar(30)                              | YES  |     | NULL    |                |
+---------+------------------------------------------+------+-----+---------+----------------+

My pseudo code is something like this:

1. Fetch ID of the cars that was listed by certain user;
2. Use that ID as a key;
3. Get every other options to be the values (diesel, yellow, etc...)

I have tried some codes since array_combine to foreach inside foreach. This is the code I have ended up so far:

$qry_id = "select carid from cars where user='$login'";
if ($car_id =  mysqli_query($link, $qry_id)){

    while ($row = mysqli_fetch_assoc($car_id)){
        //It will fetch the iD of the user in the DB
        $user_id = $row["carid"];

        $qry_model = "select model from cars where carid='$user_id'";
        if($model_obj = mysqli_query($link, $qry_model)){

            while ($row_2 = mysqli_fetch_assoc($model_obj)){

                $model = $row_2['model'];
                $final_array = array($user_id => $model);
            }
        }   

    }
}

I don't know if this is the right approach since it's my first webapp I'm building. I'm just using the model value in this code for the simplicity sakes.

Answer 1

$q = "SELECT * FROM cars where user=?";
if ($stmt =  $pdo->preapre($q)){
    $result=[];
    $stmt->execute([$login]);
    while ($row = $stmt->fetchObject()){
        $carId = $row->carid;
        unset($row->carid);
        $result[$carId]=$row;
    }
}   

now note that $pdo is object of pdo connecttion to the database not mysqli, you can do the same thing with mysqli but i am not uses to it

also i don't recommend that you use the user name on every column you should instead store the userId as foreign key to the primary key id in the table user

that will save alot more storage make the query faster ( it's easier to look for numbers than string ) and user can change it's name without having to change his name in all other tables ( userid won't be changing of course )

also carid should be unsigned int(10) not int(11) if wondering why 10 see that post MySQL datatype INT(11) whereas UNSIGNED INT(10)?

Answer 2

You may do the following

Note this query is not safe and pron to SQL Injection, I would recommend to use prepared statements or PDO

The $carArray variable will finally have the array with carid as key in it

$query = "select * from cars where user='$login'";
$result = mysqli_query($query);
$carArray = array();
while ($row = mysqli_fetch_assoc($result)){
$carArray[$row['carid']] = $row;
}   

Answer 3

To avoid SQL injection, use prepared statements. You can use one query to fetch all attributes for the cars:

$qry_id = "select carid, model, price, color from cars where user=?";
$stmt = mysqli_prepare($link , $qry_d) or die("SQL statement error");
// Bind the login parameter to the statement
mysqli_stmt_bind_param($stmt, "s", $login);
mysqli_stmt_execute($stmt);
// bind every column in the SELECT
mysqli_stmt_bind_result($stmt, $user_id, $carid, $model, $price, $color);
while (mysqli_stmt_fetch($stmt)){
    $final_array[] = array(
        "model" => $model,
        "price" => $price,
        "color" => $color
    );
}