Fields Checkbox Coming from the database

Navigation
1

I have 3 tables, respectively:

  • Vehicles
    • vehicle_id, vehicle_name .
  • Accessories
    • acessorio_id, acessorio_name
  • Vehicle_accessories
    • vehicle_id
    • acessorio_id

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

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

The SQL query code of the edit page looks like this: In this query it only returns the checkboxes that have been 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;

    
asked by anonymous 21.10.2015 / 23:21

3 answers

1

From what I'm seeing, your problem is one of condition, you're bringing only the marked inputs, in this case you just put everyone and ignore the filter, look how you could do this: 

$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'];
}
    
22.10.2015 / 20:31
1

Okay ... I think the simplest way for you to resolve this question is:

1 - create a query to list all registered accessories 2 - create a query to retrieve all the accessories that the XPTO vehicle has. Do the while of the query result and store it in an array. 3 - When you loop the 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.

    
22.10.2015 / 19:47
1

You can use this single select that will return both the accessories in a certain car and those 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)

    
22.10.2015 / 19:50
manipulating DataTables.net data with Json [how to manipulate json to display img in column] search query in cakephp