insert query is not inserting the ID into my database table-CodePudding

I had it working earlier but then changed my database structure a bit, which didn't or should not affect the code in question. I am trying to insert car models in a database, I have a number of brands in one table and then i want the actual cars in another, with the ID of the brand in it. So for example, the 'brands table consists of a 'brandID', 'brand_name' and 'country' columns, one of the brands i have is Toyota. Toyota's 'brandID' is 26, therefore it should, after filling in my little form for inserting cars into my 'cars' table of the database, insert the number 26 into the 'brandID' column of the 'cars' table.

The 'cars' table only has 4 columns, 'carID', 'brandID', 'model' and 'year'. When i fill in my little form, it inserts the correct carID, model and year, but not the brandID, that is 0 everytime. It might be a really simple fix, but I am just not seeing it. Below is my code, please help me so i can move on from this. At the bottom of this post are screenshots of my database setup, if my explanation wasn't clear enough.

my form:

<h3 >Add a new car to the collection</h3><br />  
            <form action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"]);?>"  method="POST">
                 <label>Year</label>  
                 <input type="text" name="year"  />  
                 <br />  
                 <?php 
                 echo "Brand Name ";
                 echo "<select name='brandname' id='brandID'>";
                 while($row = $bqresult->fetch_assoc()) {
                    $bname = $row["brand_name"];
                    $brandID = $row["brandID"];
                    echo "<option value='$brandID'>$bname</option>";
                 }
                 echo "</select>";
                 ?>
                 <br>
                 <label>Model</label>  
                 <input type="text" name="model"  />  
                 <br />  
                 <!-- <label>Engine Layout</label>  
                 <select name="engine_layout">
                    <option value="ff">FF</option> 
                    <option value="fr">FR</option> 
                    <option value="rr">RR</option> 
                    <option value="rr">AWD</option> 
                </select> -->
                 <br />  
                 <button type="submit" value="Submit" >Submit</button>
            </form>

the relevant php code:

$brandquery = "SELECT brand_name FROM brands ORDER BY brand_name";
$bqresult = $conn->query($brandquery);

if($_SERVER["REQUEST_METHOD"] == "POST"){
$year = $_POST['year'];
$brandname = $_POST['brandname'];
$model = $_POST['model'];

$insertquery = "INSERT INTO cars (year, brandID, model)
VALUES ('$year', '$brandname', '$model')";
$insertresult = $conn->query($insertquery);
header("location:add_car.php");
}

'cars' table: https://imgur.com/a/4IfBzXZ 'brands' table: https://imgur.com/a/o9InuhC

CodePudding user response：

Your bqresult Select is missing the field BrandID which you assign to the option value, thats why it is 0

$brandquery = "SELECT brand_name, brandID FROM brands ORDER BY brand_name";

CodePudding user response：

You're trying to put the brand name into the brandID column. You need to get the corresponding brandID from the brands table.

$insertquery = "INSERT INTO cars (year, brandID, model)
SELECT ?, brandid, ?
FROM brands
WHERE brand_name = ?";
$insert_stmt = $conn->prepare($insertquery);
$insert_stmt->bind_param($_POST['year'], $_POST['model'], $_POST['brandname']);
$insert_stmt->execute();

I've also shown how to convert your code to use a prepared statement with parameters to prevent SQL-injection.