Josh Smith Josh Smith - 6 months ago 7
SQL Question

Insert values into one table from form and other multiple other tables

I have a form that has three inputs. Two dropdowns and a text input. The dropdowns are populated from my database before they're displayed. The text input is new data from the user. The user will add a new asset to my database but will have to choose the item's manufacturer and category.

Once the asset is added I want the asset table to be updated. I am stuck on how INSERT INTO my asset_names table using both form data and the existing values from the dropdowns. I need to take the values, user-friendly words, and select their corresponding IDs from their respective databases.

INSERT INTO asset_names (asset_name, asset_category_id, asset_manufacturer_id, asset_status_id)
VALUES ( :newAssetName,
( SELECT category_id FROM asset_categories WHERE category_name=:newAssetCategory),
( SELECT manufacturer_id FROM manufacturers WHERE name=:newAssetManufacturer),
1)";
$stmt = $pdo->prepare( $sql );

//Bind value.
$stmt->bindValue( ':newAssetName', $newAssetName );
$stmt->bindValue( ':newAssetCategory', $newAssetCategory );
$stmt->bindValue( ':newAssetManufacturer', $newAssetManufacturer );


//Execute.
$stmt->execute();


If I hardcode values into the statement variables then the statement works. It is almost as if the variables are null now.

Answer

Rather than use subqueries. Just pass the data to the DOM.

<option value="<?php echo $id;?>"><?php echo $stringversion;?></option>

then you can just bind the value as expected.

"INSERT INTO asset_names (asset_name, asset_category_id, asset_manufacturer_id, asset_status_id)
VALUES ( :newAssetName,
        :newAssetCategory),
        :newAssetManufacturer),
        1)";
$stmt = $pdo->prepare( $sql );
//Bind value.
$stmt->bindValue( ':newAssetName', $newAssetName  );
$stmt->bindValue( ':newAssetCategory', $newAssetCategory );
$stmt->bindValue( ':newAssetManufacturer', $newAssetManufacturer );