How to count rows and display it

[Shades]

Hello all, so here I have is a drop down list of the `car_makes` e.g. BMW, Mercedes, Nissan, Audi and etc. So from another table called `cars_available` I want it to show the total of `car_makes` available. In the `cars_available` it has `cID`, `cMAKE`, `cMODEL` 

So where it says $total_rows I want it to show how many cars is available for each make. Please let me know how I would be able to do this or give me an example. 

 

<?php
error_reporting(E_ALL & ~E_NOTICE);
require("config/config.php");
	try {
	$database = new Connection();
$db = $database->openConnection();
	$smt = $db->prepare("SELECT * From `car_makes`");
$smt->execute();
$data = $smt->fetchAll();
	?>
	<select name="cmMAKE" id="cmMAKE">
<?php foreach ($data as $row): ?>
    <option><?=$row["cmMAKE"]?> (<?=$total_rows?>)</option>
<?php endforeach ?>
</select>
	<?php 
	$database->closeConnection();
	}
catch (PDOException $e) {
    echo "There is some problem in connection: " . $e->getMessage();
}
?>

 

<?php
error_reporting(E_ALL & ~E_NOTICE);
require("config/config.php");
    
try {
    $database = new Connection();
    $db = $database->openConnection();
    $smt = $db->prepare("SELECT * From `car_makes`");
    $smt->execute();
    $data = $smt->fetchAll();    
	    $q = $db->prepare("SELECT `cmID`, `cMAKE`,
                       COUNT(`ca`.`cMAKE`) 
                       AS `total`  
                       FROM `car_makes` 
                       AS `cm`
                       INNER JOIN `cars_available` 
                       AS `ca`
                       ON `cm`.`cmID` = `ca`.`cMAKE`");
    $q->execute();
    $query = $q->fetchAll();
    ?>
	    <?php foreach ($query as $info): ?>
                <?$count = $info["total"];?></option>
                <?php endforeach ?>
	    <select name="cmMAKE" id="cmMAKE">
        <?php foreach ($data as $row): ?>
              <option><?=$row["cmMAKE"]?> (<?=$count?>)</option>
                <?php endforeach ?>
    </select>
	<?php 
	    $database->closeConnection();
}
catch (PDOException $e) {
    echo "There is some problem in connection: " . $e->getMessage();
}
?>
	

 

Now I updated it, besides the car models it shows all of them as 2 available however, it should only be 2 available for one car model and the rest 0

[Magictallguy]

A modification to your query.
UNTESTED
 

SELECT cm.cmID, cm.cmMAKE, COUNT(ca.cMAKE) AS total
FROM car_makes AS cm
LEFT JOIN cars_available AS ca ON cm.cmMAKE = ca.cMAKE
GROUP BY cm.cMAKE
ORDER BY total DESC, cm.cmMAKE ASC

 

[Shades]

Hello @Magicaltallguy, thanks for the response.

I still receive an error 

There is some problem in connection: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'cnt' in 'order clause'

Edited July 5, 2019 by Shades

[Magictallguy]

Oop, my apologies. In the last line of the query, change "cnt" to "total"

[Shades]

@Magictallguy- Hey, thanks again however, now besides it only shows 0 for each one.

 

	<?php
error_reporting(E_ALL & ~E_NOTICE);
require("config/config.php");
    
try {
	    $database = new Connection();
    $db = $database->openConnection();
    $smt = $db->prepare("SELECT cm.cmID, cm.cmMAKE, COUNT(ca.cMAKE) AS total
                        FROM car_makes AS cm
                        LEFT JOIN cars_available AS ca ON cm.cmMAKE = ca.cMAKE
                        GROUP BY cm.cmMAKE
                        ORDER BY total DESC, cm.cmMAKE ASC");
    $smt->execute();
    $data = $smt->fetchAll();    
	    ?>
	    <select name="cmMAKE" id="cmMAKE">
        <?php foreach ($data as $row): ?>
              <option><?=$row["cmMAKE"]?> (<?=$row["total"]?>)</option>
                <?php endforeach ?>
    </select>
	<?php 
	    $database->closeConnection();
}
catch (PDOException $e) {
    echo "There is some problem in connection: " . $e->getMessage();
}
?>
	

[Magictallguy]

It's possible I've misunderstood the data correlation.
Can you provide the full-text outputs of
 

SHOW CREATE TABLE car_makes;
SHOW CREATE TABLE cars_available;

please?

[Shades]

	DROP TABLE IF EXISTS `car_makes`;
	CREATE TABLE `car_makes` (
  `cmID` int(11) NOT NULL AUTO_INCREMENT,
  `cmMAKE` varchar(50) NOT NULL DEFAULT '',
  PRIMARY KEY (`cmID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
	LOCK TABLES `car_makes` WRITE;
/*!40000 ALTER TABLE `car_makes` DISABLE KEYS */;
	INSERT INTO `car_makes` (`cmID`, `cmMAKE`)
VALUES
    (1,'BMW'),
    (2,'Merc'),
    (3,'Toyoto');
	/*!40000 ALTER TABLE `car_makes` ENABLE KEYS */;
UNLOCK TABLES;
	
# Dump of table car_models
# ------------------------------------------------------------
	DROP TABLE IF EXISTS `car_models`;
	CREATE TABLE `car_models` (
  `cmoID` int(11) NOT NULL AUTO_INCREMENT,
  `cmoMODEL` varchar(50) NOT NULL DEFAULT '',
  PRIMARY KEY (`cmoID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
	LOCK TABLES `car_models` WRITE;
/*!40000 ALTER TABLE `car_models` DISABLE KEYS */;
	INSERT INTO `car_models` (`cmoID`, `cmoMODEL`)
VALUES
    (1,'4 Series');
	/*!40000 ALTER TABLE `car_models` ENABLE KEYS */;
UNLOCK TABLES;
	
# Dump of table cars_available
# ------------------------------------------------------------
	DROP TABLE IF EXISTS `cars_available`;
	CREATE TABLE `cars_available` (
  `cID` int(11) NOT NULL AUTO_INCREMENT,
  `cMAKE` int(11) NOT NULL,
  `cMODEL` int(11) NOT NULL,
  PRIMARY KEY (`cID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
	LOCK TABLES `cars_available` WRITE;
/*!40000 ALTER TABLE `cars_available` DISABLE KEYS */;
	INSERT INTO `cars_available` (`cID`, `cMAKE`, `cMODEL`)
VALUES
    (1,1,1),
    (2,2,1),
    (3,1,1);
	/*!40000 ALTER TABLE `cars_available` ENABLE KEYS */;
UNLOCK TABLES;
	

[Magictallguy]

How about..

SELECT COUNT(avail.cID) AS total, make.cmMAKE
FROM cars_available AS avail
INNER JOIN car_models AS model ON avail.cMODEL = model.cmoID
INNER JOIN car_makes AS make ON avail.cMAKE = make.cmID
GROUP BY avail.cMAKE
ORDER BY total DESC, make.cmMAKE ASC

 

[Shades]

Thank you so much! That works perfectly!!!