0

I am learning mysqli/php and admit that somethings I say below may be wrong. I've had a search and looked at other examples of similar stuff but nothing appears to fit what I want to do (i'm probably wrong)I want a table on a page to show a list of names and the number of times they appear in the table eg.

My table is setup like this, it has more columns in it but for this page I'm only interested in the following

ID  NAME    failtyp
1   SGh     BE
2   BHu     BE
3   BHu     BE
4   SGh     BE
5   ARa     BE
6   SGh     BE
7   ARa     BE
8   BHu     BE
9   BHu     BE
10  BHu     BE
11  BHu     CF
12  ARa     CF
13  SGh     CF

I want to list the names along with how many times they appear in the list when failtyp='BE'

BHu 5
SGh 3
ARa 2

I have tested my sql queries and I know I need to use these, please correct me if I'm wrong.

SELECT DISTINCT name FROM rd.poidb WHERE failtyp='BE';

SELECT name, COUNT(*) FROM rd.poidb WHERE failtyp='BE' GROUP BY name;

problem is I don't know what I need to do for the php side, everything I try lists the names but I can get the counts in the table

below gives me a list of the names, I don't know what I need to do to add the count to it, I've tried but the examples I've seen have always had references for the 2nd column which relate to table columns.

belist.php

<?php
require ('.\bin\mysqli_con.php'); // Connect to the db.

// Check connection
if ($dbcon->connect_error) {
die("Connection failed: " . $dbcon->connect_error);
} 

$sql = "SELECT name, COUNT(*) FROM rd.poidb WHERE failtyp='BE' GROUP BY name";
$result = $dbcon->query($sql);

if ($result->num_rows > 0) {
echo "<table><tr><th>Name</th><th>Errors</th></tr>";
// output data of each row
while($row = $result->fetch_assoc()) {
echo "<tr><td>" . $row["name"]. "</td></tr>";
}
echo "</table>";
} else {
echo "0 results";
}

$dbcon->close();
?>
jdavies
  • 1
  • 2
  • I think you have got a lot of typos and what is a builder?? – Anoop saju Apr 26 '16 at 15:06
  • You should use prepared statements or PDO for query to avoid sql injection. What is builder? You are selecting `builder` and `COUNT(*)` and later you are using `$row['name']`.. Seems `name` column is not even selected in sql – Abbasi Apr 26 '16 at 15:11
  • I've changed the code above, sorry builder should have been changed to name, builder is another column in the same table. I don't know PDO at all. – jdavies Apr 26 '16 at 15:22
  • just use an alias for your count field like it shown in the linked answer. – Your Common Sense Apr 26 '16 at 15:44

0 Answers0