Issue
In need to show only those car brands that are already in DB. I'm not very good in Back End. Need your help :) thanks.
If there is no BMW car in DB don't show BMW in brands. One more request if there is any BMW car so show BMW brand and show total how many cars are in BMW brand.
<div class="col-md-3 col-sm-6 option-item">
<select data-placeholder="gege" class="form-control brand" class="form-select brand" aria-label="Default select example" name="brand_id" style="height: 40px;">
<?php
$statement2 = $pdo->prepare("SELECT * FROM tbl_car WHERE brand_id!=0");
$statement2->execute();
$result2 = $statement2->fetchAll(PDO::FETCH_ASSOC);
foreach ($result2 as $row2)
{
$statement = $pdo->prepare("SELECT * FROM tbl_brand WHERE brand_id=? ORDER BY brand_name ASC");
$statement->execute($row2['brand_id']);
$result = $statement->fetchAll(PDO::FETCH_ASSOC);
foreach ($result as $row) {
$statement2 = $pdo->prepare("SELECT * FROM tbl_car WHERE brand_id!=0");
$statement2->execute();
$result2 = $statement2->fetchAll(PDO::FETCH_ASSOC);
foreach ($result2 as $row2)
{
$brand_available = count($result2);
?>
<option></option>
<option value="<?php echo $row['brand_id']; ?>"><?php echo $row['brand_name'].' current brand has '.$brand_available.' cars.'; ?></option>
<?php
}
}
}
?>
</select>
</div>
CREATE TABLE IF NOT EXISTS `tbl_brand` (
`brand_id` tinyint(11) NOT NULL AUTO_INCREMENT,
`brand_name` varchar(255) NOT NULL,
PRIMARY KEY (`brand_id`)
) ENGINE=InnoDB AUTO_INCREMENT=64 DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `tbl_model` (
`model_id` int(11) NOT NULL AUTO_INCREMENT,
`model_name` varchar(255) NOT NULL,
`brand_id` int(11) NOT NULL,
PRIMARY KEY (`model_id`)
) ENGINE=InnoDB AUTO_INCREMENT=416 DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `tbl_car` (
`car_id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(255) NOT NULL,
`brand_id` int(11) NOT NULL,
`model_id` int(11) NOT NULL,
PRIMARY KEY (`car_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;
Solution
If I understand well. First try to get all car brands present in the DB using a SELECT DISTINCT query. Then for each brand you need to check if there are any cars associated with that brand. If there are cars, display the make and total number of cars. Otherwise, do not show the mark.
I couldn't simulate it here, but try to do it like this, if it doesn't work I'll try another approach. Sorry about my English
<div class="col-md-3 col-sm-6 option-item">
<select name="brand_id" class="form-control brand-select" aria-label="Select example">
<?php
// Query to get all distinct brands from tbl_brand
$stmt = $pdo->prepare("SELECT DISTINCT b.brand_id, b.brand_name FROM tbl_brand b JOIN tbl_car c ON b.brand_id = c.brand_id ORDER BY b.brand_name ASC");
$stmt->execute();
$brands = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach ($brands as $brand) {
// Query to get the count of cars for this brand
$stmt = $pdo->prepare("SELECT COUNT(*) AS count FROM tbl_car WHERE brand_id = ?");
$stmt->execute([$brand['brand_id']]);
$count = $stmt->fetch(PDO::FETCH_ASSOC)['count'];
if ($count > 0) {
// Display the brand and count of cars
echo "<option value='{$brand['brand_id']}'>{$brand['brand_name']} ({$count} cars)</option>";
}
}
?>
</select>
</div>
Answered By - Danilo Fagundes
0 comments:
Post a Comment
Note: Only a member of this blog may post a comment.