Checkbox Fields From Database

Question

I have 3 tables, respectively:

• Vehicles
  • veculo_id, veculo_name.
• Accessories
  • acessorio_id, accessorio_name
• Vehicles
  • veculo_id
  • accessorio_id

On the vehicle registration page the fields accessories come from table database accessories as a field of the kind checkbox. When I register the vehicle, the registration is inserted in the tables vehicles and vehicles.

Even this part works normally, but on the page of edit I can only catch the ones that have already been marked (by checkbox) during the registration of the vehicle, and what I wanted was to show all the accessories of the table, including those not marked, leaving only marked those that were marked during the registration.

The SQL query code of the edit page looks like this: This query only returns the checkboxes that were marked in the register!

<?php 
$getVehicleID = filter_input(INPUT_GET, 'vehicleID', FILTER_VALIDATE_INT);       

$ReadItens = new Read;
$ReadItens->FullRead("SELECT cars.car_id,car_vehicles_and_items.car_id,car_additional.additional_id,car_additional.additional_title
FROM car_vehicles_and_items INNER JOIN cars ON cars.car_id = car_vehicles_and_items.car_id 
INNER JOIN car_additional ON car_additional.additional_id = car_vehicles_and_items.additional_id where car_vehicles_and_items.car_id  = : id", " id=$getVehicleID");

foreach ($ReadItens->getResult() as $lisItens):

echo "<input ";

if ($lisItens['car_id'] == $getVehicleID):  

echo "checked ";
endif;

echo "type=\"checkbox\" id=\"{$lisItens['additional_id']}\"  name=\"car_additional[]\" value=\"{$lisItens['additional_id']}\">";
echo $lisItens['additional_title'];
endforeach;

Answer 1

Okay... I believe the simplest way for you to resolve this issue is:

1 - create a query to list all registered accessories 2 - create a query to recover all the accessories that the XPTO vehicle has. Do the while of the query result and store in an array. 3 - when you loop item "1" (which lists the registered accessories), do an if:

if (in_array($linhaDoLoopDoItem1, $arrayDoItem2)) { 
    echo "checked";
}

Remembering that this IF needs to be inside the checkbox tag.

Answer 2

You can use this single select that will return both the accessories in a particular car and the ones that are not.

SELECT A.ACESSORIO_ID, A.ACESSORIO_NOME, "TRUE" CHECKED FROM ACESSORIOS A JOIN VEICULOS_ACESSORIOS VA ON VA.ACESSORIO_ID = A.ACESSORIO_ID WHERE VA.VEICULO_ID = X UNION SELECT A.ACESSORIO_ID, A.ACESSORIO_NOME, 'FALSE' FROM ACESSORIOS A WHERE NOT EXISTS (SELECT 1 FROM VEICULOS V JOIN VEICULOS_ACESSORIOS VA ON VA.ACESSORIO_ID = A.ACESSORIO_ID WHERE V.VEICULO_ID = VA.VEICULO_ID AND V.VEICULO_ID = X)

Answer 3

From what I see, your problem is of condition, you are bringing only the marked inputs, in this case, just put all and ignore the filter, look just how you could do it:

$getVehicleID = filter_input(INPUT_GET, 'vehicleID', FILTER_VALIDATE_INT);  

$ReadItens = new Read;
$ReadItens->FullRead("
      SELECT cars.car_id,
             car_vehicles_and_items.car_id,
             car_additional.additional_id,
             car_additional.additional_title
      FROM   car_vehicles_and_items 
      LEFT JOIN cars
             ON cars.car_id = car_vehicles_and_items.car_id
      LEFT JOIN car_additional 
             ON car_additional.additional_id = car_vehicles_and_items.additional_id
      WHERE :n", " n=1");

foreach ($ReadItens->getResult() as $lisItens) {

    echo "<input type=\"checkbox\" " . 
         "id=\"{$lisItens['additional_id']}\" ";
         "name=\"car_additional[]\ " .
         "value=\"{$lisItens['additional_id']}\"" . 
         (($lisItens['car_id'] == $getVehicleID) ? 'checked = \"checked\"' : '') .
         ">" . $lisItens['additional_title'];
}