Assume that the project and client tables exist in DB, and that the results of the LEFT JOIN merge are sorted in the order of client.id.
mysql>SELECT p.id, p.project_name, p.client_id, c.client_name FROM project p LEFT JOIN client c ON p.client_id =c.id WHERE p.active_flag ='1'ORDER BY c.id DESC;
Findings
+--------------------------------------------------------------------------------------------------------------------------------------------
| id | project_name | client_id | client_name |
+-----+--------------------------------------------------------------+-----------+-------------+
| 50 | A List | 9 | admin |
| 188 | B List | 9 | admin |
| 7 | C List | 2 | user |
| 8 | D List | 2 | user |
What are the ways to categorize and output the above results by client_name?
As for the output, I would like to use the following HTML.
I would appreciate it if you could let me know.
Thank you for your cooperation.
First of all, it is common for PHP to use PDO to retrieve data from the database.To execute SQL statements using PDO and retrieve data:
// Connect to the database.
$dsn='mysql:dbname=yourdbname;host=yourhost';
$user='youruser';
$password='yourpass';
$dbh = new PDO($dsn, $user, $password);
// Execute SQL statements.
$sql="SELECT p.id, p.project_name, p.client_id, c.client_name
FROM project p LEFT JOIN client cON p.client_id=c.id
WHERE p.active_flag = '1'
ORDER BY c.id DESC";
$sth = $dbh->prepare($sql);
$th->execute();
// Retrieves all data specified in the SQL statement.
$result=$th->fetchAll();
If you want to print data from DB for each client_name, create an array with client_name as the key.
<pre><code>// Data are stored in an array for each client_id, project_id. $data=array(); foreach($result as$project){ $client_name = $project ['client_name']; $project_id = $project['id']; $data[$client_name][$project_id] = $project; }All you have to do is display it in HTML, so you just need to output $data using the for loop.
print"<ul>";
foreach($data as$client_name=>$projects){
print "<li>{$client_name}}";
print "<ul>";
foreach($projects as$project){
print "<li>{$project['project_name']}</li>";
}
print "</ul>";
}
print "</ul>";
What about multidimensional arrays
$a['client_name']['project_name']
© 2024 OneMinuteCode. All rights reserved.