If you have a
MariaDB,
MySQL,
SQLite, etc. database that may have more than one occurrence of a value
for a particular column in a
table, but want to display only unique values, you can fillter out the
repeated values using SELECT DISTINCT. E.g., I have a table
in a database where each
record, aka row, in the table holds two fields: one holds
an account id (Acct) and the other a project name.
An account can be associated with more than one project and any project may
have many account IDs associated with it.
To produce a list of the projects in the table without listing any project more than once and to list the projects in alphabetical order, I can use the SQL statement below:
SELECT DISTINCT Project FROM Acct_Projects ORDER BY Project;
Each record, aka row, in the table holds two fields: one holds an account id (Acct) and the other a project name. For the PHP code I use to query the SQLite database that holds the data to display a list of all the projects in the table, I use the following code:
<?php
$filename = "/Users/jasmith1/Documents/www/SGRS/SGRS.db";
$query_string = "SELECT DISTINCT Project FROM Acct_Projects ORDER BY Project";
$db_handle = new SQLite3($filename);
$results = $db_handle->query($query_string);
echo "<table>\n";
echo "<tr><td><b>Project</b></td></tr>\n";
while ($row = $results->fetchArray()) {
echo "<tr><td><a href=\"project_acctids.php?project=" .
$row['Project'] . "\">" . $row['Project'] . "</a></td></tr>\n";
}
echo "</table>\n";
?>I make the project displayed on each row of the
HTML table a clickable link that will open another page,
project_acctids.php that will, for the project that is clicked
on, display all of the account ids associated with that project.
