asked on
Grouping certain rows together
<?php
error_reporting(E_ALL);
ini_set('display_errors' ,1);
require "connection.php";
$query= "SELECT * from vouchers WHERE parsing_date=CURRENT_DATE ";
$result = pg_query($conn,$query);
?>
<!DOCTYPE html>
<html>
<head>
<link href="//netdna.bootstrapcdn.com/bootstrap/3.0.3/css/bootstrap.min.css" rel="stylesheet">
<link href = "http://fonts.googleapis.com/css?family=Roboto:400">
<style>
.responstable {
margin: 1em 0;
width: 100%;
overflow: hidden;
background: #FFF;
color: #024457;
border-radius: 10px;
border: 1px solid #167F92;
word-wrap: break-word;
}
</style>
</head>
<body>
<div class="container-fluid">
<div>
<h1>Clients</h1>
</div>
<table class="responstable" rules='all' style='border-collapse: collapse;'>
<thead>
<tr>
<th>Client id</th>
<th>Insurance</th>
<th>Number of rows</th>
</tr>
</thead>
<?php
while($row = pg_fetch_array($result))
{
?>
<tbody>
<td><?php echo $row['client_id']; ?></td>
<td><?php echo $row['insurance']; ?></td>
</tr>
<?php }?> </tbody>
</table>
</div>
</body>
</html>
I have the code above that outputs:
Client id Insurance
------------ ---------------
123 AA
123 EE
123 U
125 AA
126 CU
124 UE
125 UE
I want my out put to be:
Client id Number of rows Insurance
------------ ----------------------- --------------
123 3 AA,EE,U
125 2 AA,UE
126 1 CU
124 1 UE
PHPPostgreSQL
Last Comment
Jazzy 1012
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Im using PostgreSQL not SQL
ASKER
Okay but that still doesn't give me how I can put the number of rows.
add count(*) in your select statement
ASKER
Where could I add that so my output would be in the row column?
this result
{+edit}
| Client id | string_agg |
|-----------|------------|
| 123 | EE,U,AA |
| 124 | UE |
| 125 | AA,UE |
| 126 | CU |
select "Client id", string_agg("Insurance",',')
from table1
group by "Client id"
CREATE TABLE Table1
("Client id" varchar(12), "Insurance" varchar(15))
;
INSERT INTO Table1
("Client id", "Insurance")
VALUES
('123', 'AA'),
('123', 'EE'),
('123', 'U'),
('125', 'AA'),
('126', 'CU'),
('124', 'UE'),
('125', 'UE')
;
{+edit}
select "Client id", string_agg("Insurance",',' order by "Insurance")
from table1
group by "Client id"
ASKER
string_agg doesnt work for me
?
what is the error message?
what version of Postgres are you using?
what is the error message?
what version of Postgres are you using?
ASKER
Im not sure Im using zend, but I did this code
Is there a way to remove the " or {}?
array_agg(insurance) AS insurance
Is there a way to remove the " or {}?
array_agg() isn't Postgres
I can't help on PHP functions
do you want a Postres solution or a PHP solution?
I can't help on PHP functions
do you want a Postres solution or a PHP solution?
did you try it like this?
$query= "
SELECT v.Client_id, string_agg(v.Insurance,','
from vouchers v
WHERE v.parsing_date=CURRENT_DAT
group by v.Client_id
";
$result = pg_query($conn,$query);
$query= "
SELECT v.Client_id, string_agg(v.Insurance,','
from vouchers v
WHERE v.parsing_date=CURRENT_DAT
group by v.Client_id
";
$result = pg_query($conn,$query);
ASKER
Didnt work but thanks anyways
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Yes my database is I just get the issue of it being unknown to my code.
ASKER
Cant I just trim them because all i get when i change it is operation not exist
sorry: can't help much on the PHP errors
ASKER
Its okay thank you
Open in new window
But I got this error:
Open in new window
(line 8 Is that line(query))