Solved

Grouping certain rows together

Posted on 2016-09-15
18
95 Views
Last Modified: 2016-09-15
<?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>

Open in new window


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
0
Comment
Question by:Jasmine Ikhreishi
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 6
  • 2
18 Comments
 
LVL 1

Author Comment

by:Jasmine Ikhreishi
ID: 41799599
I also tried this
SELECT 
  client_id, 
  concat(count(*), ' rows') AS rows, 
  array_agg(insurance) AS insurance 
FROM vouchers WHERE parsing_date=CURRENT_DATE
GROUP BY client_id;

Open in new window


But I got this error:
Warning: pg_query(): Query failed: ERROR: function concat(bigint, unknown) does not exist LINE 1: SELECT client_id, concat(count(*),'rows') AS rows, array_agg... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. in index.php on line 8

Open in new window

(line 8 Is that line(query))
0
 
LVL 7

Accepted Solution

by:
D Patel earned 250 total points
ID: 41799601
0
 
LVL 1

Author Comment

by:Jasmine Ikhreishi
ID: 41799605
Im using PostgreSQL not SQL
0
Get Database Help Now w/ Support & Database Audit

Keeping your database environment tuned, optimized and high-performance is key to achieving business goals. If your database goes down, so does your business. Percona experts have a long history of helping enterprises ensure their databases are running smoothly.

 
LVL 1

Author Comment

by:Jasmine Ikhreishi
ID: 41799615
Okay but that still doesn't give me how I can put the number of rows.
0
 
LVL 7

Expert Comment

by:D Patel
ID: 41799633
add count(*) in your select statement
0
 
LVL 1

Author Comment

by:Jasmine Ikhreishi
ID: 41799634
Where could I add that so my output would be in the row column?
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 41799638
this result
| Client id | string_agg |
|-----------|------------|
|       123 |    EE,U,AA |
|       124 |         UE |
|       125 |      AA,UE |
|       126 |         CU |
        

Open in new window

using the postgres function string_agg()
select "Client id", string_agg("Insurance",',')
from table1
group by "Client id"

Open in new window

data
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')
;

Open in new window

see http://sqlfiddle.com/#!15/692bb/1

{+edit}
select "Client id", string_agg("Insurance",',' order by "Insurance")
from table1
group by "Client id"

Open in new window

to order the concatenation
0
 
LVL 1

Author Comment

by:Jasmine Ikhreishi
ID: 41799642
string_agg doesnt work for me
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 41799648
?
what is the error message?
what version of Postgres are you using?
0
 
LVL 1

Author Comment

by:Jasmine Ikhreishi
ID: 41799654
Im not sure Im using zend, but I did this code
array_agg(insurance) AS insurance

Open in new window

And the output is what I want but in them its like {"AA","UE"} or {"AA"}
Is there a way to remove the " or {}?
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 41799657
array_agg() isn't Postgres
I  can't help on PHP functions

do you want a Postres solution or a PHP solution?
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 41799664
did you try it like this?

$query= "
 SELECT v.Client_id, string_agg(v.Insurance,',' order by v.Insurance)
 from vouchers v
 WHERE v.parsing_date=CURRENT_DATE
 group by v.Client_id
";
$result = pg_query($conn,$query);
1
 
LVL 1

Author Comment

by:Jasmine Ikhreishi
ID: 41799670
Didnt work but thanks anyways
0
 
LVL 49

Assisted Solution

by:PortletPaul
PortletPaul earned 250 total points
ID: 41799676
you could be asked to define "didn't work" (it does not help isolate the problem)
are you sure your database is Postgres?

Could it be MySQL perhaps?
select `Client id`, group_concat(`Insurance`)
from table1
group by `Client id`

Open in new window

Every database vendor has their own unique way of doing the concatenation
0
 
LVL 1

Author Comment

by:Jasmine Ikhreishi
ID: 41799678
Yes my database is I just get the issue of it being unknown to my code.
0
 
LVL 1

Author Comment

by:Jasmine Ikhreishi
ID: 41799679
Cant I just trim them because all i get when i change it is operation not exist
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 41799682
sorry: can't help much on the PHP errors
0
 
LVL 1

Author Comment

by:Jasmine Ikhreishi
ID: 41799684
Its okay thank you
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
This article discusses four methods for overlaying images in a container on a web page
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question