I have two tables showing a list of customers and their corresponding ratings. Most of the customers have more than one rating each. I would like to show a list of all the customers and their ratings. I am extracted this info from the database and using php to show it in a file. This is what I am getting for example:
customer1 - rating
customer1 - rating
customer1 - rating
customer2 - rating
customer2 - rating
customer3 - rating
This is what I want:
customer1 - rating
- rating
- rating
customer2 - rating
- rating
customer3 - rating
There are certain things I need to do to the rating. Some will be approved and some not and only the approved ones will be used. Those approved ones should be added together and an average found.
$stmt = $conn->prepare("
SELECT *, AVG(rating) AS rating_avg
FROM companies
LEFT JOIN ratings ON companies.id = ratings.company_id
WHERE category LIKE ?
AND location LIKE ?
GROUP BY companies.id
ORDER BY FIELD(type, 'premium', 'standard', 'basic')");
This is my coding so far which is not working as the SQL statement doesn't differentiate between the approved and not approved. Also, all companies must show whether they have a rating or not, but not all ratings should show.
`
$sqlb ="SELECT *
FROM companies";
$resultb = mysqli_query($conn, $sqlb);
$row = array();
while(($row = mysqli_fetch_assoc($resultb))) {
$id = $row['id'];
echo $company_name = $row['company_name'];//what is the column name
$stmt = $conn->prepare("
SELECT *
FROM ratings
WHERE ratings.company_id = ?
");
$stmt->bind_param("i", $id);
$stmt->execute();
$result = $stmt->get_result();
while(($ratings = mysqli_fetch_assoc($result))) {
if ($ratings['approve'] == 'approved') {
echo '<br />';
echo $ratings['rating'].' ';
echo $ratings['review'];
} //if
} //while ratings
echo '<hr />';
} //while
`
