Joao Torres Joao Torres - 5 months ago 11
PHP Question

using one key to multiple values in php

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

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
    );
}
Comments