[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Getting from database to make one row

Posted on 2016-09-14
17
Medium Priority
?
120 Views
Last Modified: 2016-09-15
My report will contain the person's id, # of rows, insurance and todays date
In my database person number 22 may contain
22      UH
22       AA
22        EI
Therefore in my report I want it to view as
22        3 rows           UH, AA,EI
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
  • 6
  • 5
  • 5
  • +1
17 Comments
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 41797911
I would take the easy way out here.  

SELECT {column names} FROM my_table

Retrieve the rows into an array.  Count the rows with PHP.  Collapse the field you need into an array and reformat it with implode().  

I'll try to give you a code sample in a moment.

If you're new to PHP and want to find some learning resources, this article might help.
https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html
0
 
LVL 59

Expert Comment

by:Julian Hansen
ID: 41797934
Adapt to your code
// Create our result array
$rollup = array();
$conn = new mysqli('localhost','user','password','databse');
// Error checking for DB connect
// Get the rows from the db
$query = "SELECT id, insurance FROM thetable";
$result = $conn->query($query);

// We got rows ...
if ($result) {
  // Loop through results
  while($row = $result->fetch_object()) { 
    // Is this the first time we have seen this ID - if so make an entry for it
    if (empty($rollup[$row->id])) {
      $rollup[$row->id] = array(
        'insurance' => $row->insurance, 
        'rows' => 1
      );
    }
    // Otherwise add to existing and bump row count
    else {
      $rollup[$row->id]['insurance'] .= ",{$row->insurance}";
      $rollup[$row->id]['rows']++;
    }
  }
}
// Dump the results
echo "<pre>" . print_r($rollup,true) . "</pre>";

Open in new window

Sample output
Array
(
    [22] => Array
        (
            [insurance] => UH,AA,EI,AC
            [rows] => 4
        )

    [12] => Array
        (
            [insurance] => AA,AA
            [rows] => 2
        )

    [14] => Array
        (
            [insurance] => EI,GH
            [rows] => 2
        )

    [23] => Array
        (
            [insurance] => AB
            [rows] => 1
        )

    [25] => Array
        (
            [insurance] => DC
            [rows] => 1
        )

    [11] => Array
        (
            [insurance] => AA,AT
            [rows] => 2
        )

)

Open in new window

0
 
LVL 59

Expert Comment

by:Julian Hansen
ID: 41797948
To get the output you wanted
echo "<pre>" . print_r($rollup,true) . "</pre>";
echo "<pre>";
foreach($rollup as $k => $v) {
   $rows = $v['rows'] > 1 ? 'rows' : 'row';
    echo "{$k}  {$v['rows']} {$rows} {$v['insurance']}\n";
}
echo "</pre>";

Open in new window

Output
22  4 rows UH,AA,EI,AC
12  2 rows AA,AA
14  2 rows EI,GH
23  1 row AB
25  1 row DC
11  2 rows AA,AT

Open in new window

0
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
LVL 1

Author Comment

by:Jasmine Ikhreishi
ID: 41797959
Im using PostgreSQL  is it the same?
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 41797963
Please see https://iconoun.com/demo/temp_jasmine_8.php

A couple of cautionary notes.  The use of "id" to identify anything other than the auto_increment primary key is likely to be confusing.  I used it here because you used it in the question, but in "real life" you do not want to do that.  Change the column name.

Make a Google search for the exact phrase "Should I Normalize My Database" and read the very interesting comments on both sides of the issue.

This script is mostly setup.  Hopefully the comments will help!  The demonstration starts on line 125.
<?php // demo/temp_jasmine_8.php
/**
 * Demonstrate some of the basics of MySQLi
 *
 * References that must be understood to use PHP and MySQL(i):
 *
 * http://php.net/manual/en/mysqli.overview.php
 * http://php.net/manual/en/class.mysqli.php
 * http://php.net/manual/en/class.mysqli-stmt.php
 * http://php.net/manual/en/class.mysqli-result.php
 * http://php.net/manual/en/class.mysqli-warning.php
 * http://php.net/manual/en/class.mysqli-sql-exception.php <-- DID NOT WORK PHP 5.3+, MySQL 5.1+
 *
 * http://php.net/manual/en/mysqli.construct.php
 * http://php.net/manual/en/mysqli.real-escape-string.php
 * http://php.net/manual/en/mysqli.query.php
 * http://php.net/manual/en/mysqli.errno.php
 * http://php.net/manual/en/mysqli.error.php
 * http://php.net/manual/en/mysqli.insert-id.php
 *
 * http://php.net/manual/en/mysqli-result.num-rows.php
 * http://php.net/manual/en/mysqli-result.fetch-array.php <-- DO NOT USE THIS
 * http://php.net/manual/en/mysqli-result.fetch-object.php
 */
ini_set('display_errors', TRUE);
error_reporting(E_ALL);
echo '<pre>';


// CREATE AN ARRAY OF INFORMATION TO USE FOR TEST DATA
$test_names_arrays = array
( array( "id" => "22" , "xx" => "UH" )
, array( "id" => "22" , "xx" => "AA" )
, array( "id" => "22" , "xx" => "EI" )
)
;


// DATABASE CONNECTION AND SELECTION VARIABLES - GET THESE FROM YOUR HOSTING COMPANY
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";
$db_user = "??";
$db_word = "??";

// OPEN A CONNECTION TO THE DATA BASE SERVER AND SELECT THE DB
$mysqli = new mysqli($db_host, $db_user, $db_word, $db_name);

// DID THE CONNECT/SELECT WORK OR FAIL?
if ($mysqli->connect_errno)
{
    $err
    = "CONNECT FAIL: "
    . $mysqli->connect_errno
    . ' '
    . $mysqli->connect_error
    ;
    trigger_error($err, E_USER_ERROR);
}

// ACTIVATE THIS TO SHOW WHAT THE DB CONNECTION OBJECT LOOKS LIKE
// var_dump($mysqli);


// CREATING A TABLE FOR OUR TEST DATA
$sql
=
"
CREATE TEMPORARY TABLE my_table
( my_key INT         NOT NULL AUTO_INCREMENT PRIMARY KEY
, id     VARCHAR(24) NOT NULL DEFAULT ''
, xx     VARCHAR(24) NOT NULL DEFAULT ''
)
"
;

// IF mysqli::query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res = $mysqli->query($sql))
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}

// ACTIVATE THIS TO SHOW THE RESULTS OF THE QUERY
// var_dump($res);


// LOADING OUR DATA INTO THE TABLE
foreach ($test_names_arrays as $person)
{
    // ESCAPE THE DATA FOR SAFE USE IN A QUERY
    $safe_id  = $mysqli->real_escape_string($person['id']);
    $safe_xx  = $mysqli->real_escape_string($person['xx']);

    // CONSTRUCT THE QUERY USING THE ESCAPED VARIABLES
    $sql = "INSERT INTO my_table ( id, xx ) VALUES ( '$safe_id', '$safe_xx' )";

    // RUN THE QUERY TO INSERT THE ROW
    $res = $mysqli->query($sql);

    // IF mysqli::query() RETURNS FALSE, LOG AND SHOW THE ERROR
    if (!$res)
    {
        $err
        = 'QUERY FAILURE:'
        . ' ERRNO: '
        . $mysqli->errno
        . ' ERROR: '
        . $mysqli->error
        . ' QUERY: '
        . $sql
        ;
        trigger_error($err, E_USER_ERROR);
    }
}


// HERE IS THE START OF THE DEMONSTRATION
$sql = "SELECT id, xx FROM my_table WHERE id='$safe_id' ";
$res = $mysqli->query($sql);

// IF mysqli_query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res)
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}
// IF WE GET THIS FAR, THE QUERY SUCCEEDED AND WE HAVE A RESULT OBJECT IN $res
// NOW WE CAN USE OTHER MYSQLI::RESULT PROPERTIES AND METHODS


// DETERMINE HOW MANY ROWS OF RESULTS WE GOT
$num     = $res->num_rows;
$num_fmt = number_format($num);

// RETRIEVE THE RESULTS SET
$collection = [];
while ($row = $res->fetch_object())
{
    // ROW BY ROW PROCESSING IS DONE HERE
    $collection[] = $row->xx;
}

// SHOW THE REPORT
$report
= PHP_EOL
. $safe_id
. ' '
. $num_fmt
. ' rows '
. implode(', ', $collection)
;

echo $report;

Open in new window

Outputs:
22 3 rows UH, AA, EI

Open in new window

0
 
LVL 1

Author Comment

by:Jasmine Ikhreishi
ID: 41797964
I have this so far what can I add to get the output I need/?
<?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);



?>
  <?php 
  	while($row = pg_fetch_array($result))
	{ 


  ?>
  
  <tbody>


     <td><?php echo $row['client_id']; ?></td>

     <td><?php echo $row['insurance']; ?></td> 
     <td><?php echo $row['parsing_date']; ?></td>
    
    
    </tr>
  <?php  }?>  </tbody>

Open in new window


This gives me 3 rows: client id, insurance and date , However I want to add the row and put each insurance with each other?
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 41797968
PostgreSQL
It's nice if you tell us those "details" when you post the question.  And you might want to use Request Attention and ask a moderator to add this to the PostGreSQL Zone.  It's also nice to post your test data, or your code set, if you have some code already.

Short answer - mostly this is the same as MySQL.  Longer answer - Why in the world would you choose PostGreSQL instead of the vastly more popular and mature MySQL database?
0
 
LVL 1

Author Comment

by:Jasmine Ikhreishi
ID: 41797981
My database is on PostgrelSQL thats why
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 41797983
0
 
LVL 1

Author Comment

by:Jasmine Ikhreishi
ID: 41797987
I know how to return number of rows but I dont know how to put each in a column and add the other variables to it.
0
 
LVL 59

Expert Comment

by:Julian Hansen
ID: 41798015
The code I posted will work with a minor change to post_gres.
require "connection.php";
// Create our result array
$rollup = array();
// Get the rows from the db
$query= "SELECT * from vouchers WHERE parsing_date=CURRENT_DATE ";
$result = pg_query($conn,$query);

// We got rows ...
if ($result) {
  // Loop through results
  while($row = pg_fetch_object($result)) { 
    // Is this the first time we have seen this ID - if so make an entry for it
    if (empty($rollup[$row->id])) {
      $rollup[$row->id] = array('insurance' => $row->insurance, 'rows' => 1);
    }
    // Otherwise add to existing and bump row count
    else {
      $rollup[$row->id]['insurance'] .= ",{$row->insurance}";
      $rollup[$row->id]['rows']++;
    }
  }
}
// Dump the results
echo "<pre>" . print_r($rollup,true) . "</pre>";
echo "<pre>";
foreach($rollup as $k => $v) {
   $rows = $v['rows'] > 1 ? 'rows' : 'row';
    echo "{$k}  {$v['rows']} {$rows} {$v['insurance']}\n";
}
echo "</pre>";

Open in new window

0
 
LVL 59

Expert Comment

by:Julian Hansen
ID: 41798021
If you want to output as a table then you can replace lines 25 to 30 with this
echo "<table>";
foreach($rollup as $k => $v) {
   $rows = $v['rows'] > 1 ? 'rows' : 'row';
    echo "<tr><td>{$k}</td><td>{$v['rows']} {$rows}</td><td>{$v['insurance']}</td></tr>";
}
echo "</table>";

Open in new window

0
 
LVL 111

Assisted Solution

by:Ray Paseur
Ray Paseur earned 1000 total points
ID: 41798258
... but I dont know how to put each in a column and add the other variables to it.
Please see lines 151, et seq, in the code snippet posted here:
https://www.experts-exchange.com/questions/28969738/Getting-from-database-to-make-one-row.html#a41797963
0
 
LVL 9

Expert Comment

by:Brian Tao
ID: 41799167
Another suggestion is to use PostgreSQL built-in ARRAY_AGG() function.  It's very similar to GROUP_CONCAT() in MySQL.
0
 
LVL 59

Accepted Solution

by:
Julian Hansen earned 1000 total points
ID: 41799235
$query= "SELECT * from vouchers WHERE parsing_date=CURRENT_DATE ";

Open in new window

Indicates we might be dealing with more than one contact_id in the results. Therefore any solution is going to have to take into account a result set that requires grouping on the contact_id.
In light of this, Brian's comment above is one that should probably be taken the most seriously as it lets the DB do the most work.
A MySQL implementation would be simple
$query =  <<< QUERY
  SELECT 
    `contact_id`, 
    COUNT(*) AS `rows`, 
    CONCAT(GROUP_CONCAT(`insurance`), ' rows') AS `insurance`
  FROM `vouchers`
  WHERE `parsing_date`=CURRENT_DATE
  GROUP BY `contact_id`
QUERY;
$result = $conn->query($query);
echo "<table>";
while($row = $result->fetch_object()) {
  echo <<< ROW
    <tr>
      <td>{$row->contact_idk}</td>
      <td>{$row->rows}</td>
      <td>{$row->insurance}</td>
    </tr>
ROW;
}
echo "</table>";

Open in new window

A PostGres version of this might be
$query =  <<< QUERY
SELECT 
  contact_id, 
  concat(count(*), ' rows') AS rows, 
  array_agg(insurance) AS insurance 
FROM vouchers 
GROUP BY contact_id
QUERY;

$result = pg_query($conn,$query);
echo "<table>";
while($row = pg_fetch_object($result)) { 
  echo <<< ROW
    <tr>
      <td>{$row->contact_idk}</td>
      <td>{$row->rows}</td>
      <td>{$row->insurance}</td>
    </tr>
ROW;
}
echo "</table>";

Open in new window

You can see the query in this SQLFiddle
0
 
LVL 1

Author Comment

by:Jasmine Ikhreishi
ID: 41799538
@Julian Hansen I copied your exact code here:
<?php 
error_reporting(E_ALL);
ini_set('display_errors' ,1);
require "connection.php";
// Create our result array
$rollup = array();
$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>Parsing Date</th>
		<th>Number of rows</th>
	 
	</tr>
</thead>

  
  <tbody>
  <tr>
  <?php 

// We got rows ...
if ($result) {
	// Loop through results
	while($row = pg_fetch_object($result)) {
		// Is this the first time we have seen this ID - if so make an entry for it
		if (empty($rollup[$row->client_id])) {
			$rollup[$row->client_id] = array('insurance' => $row->insurance, 'rows' => 1);
		}
		// Otherwise add to existing and bump row count
		else {
			$rollup[$row->id]['insurance'] .= ",{$row->insurance}";
			$rollup[$row->id]['rows']++;
		}
	}
}
// Dump the results
echo "<pre>" . print_r($rollup,true) . "</pre>";
echo "<table>";
foreach($rollup as $k => $v) {
   $rows = $v['rows'] > 1 ? 'rows' : 'row';
    echo "<tr><td>{$k}</td><td>{$v['rows']} {$rows}</td><td>{$v['insurance']}</td></tr>";
}
echo "</table>";
  ?>

 
    
    
    </tr>
  </tbody>
</table>
 
</div>

</body>
</html>

Open in new window


I just got error 500 on the display page
0
 
LVL 59

Expert Comment

by:Julian Hansen
ID: 41799830
You need to work the sample code into your own code - if you cut and paste without adapting you will have problems

For instance - you have your own <table> echoed from the script - when you pasted my code in you included the <table> I had in my sample.
You can see your sample working against a MySQL database here
However, I would prefer to use the code in my previous post based on Brian's earlier suggestion

<?php 
error_reporting(E_ALL);
ini_set('display_errors' ,1);
require "connection.php";
// Create our result array
$rollup = array();
$query= <<< QUERY
SELECT 
  client_id, 
  count(*) AS rows, 
  array_agg(insurance) AS insurance,
  parsing_date
FROM vouchers 
where parsing_date = CURRENT_DATE
GROUP BY client_id, parsing_date 
QUERY;
$result = pg_query($conn,$query);
echo $conn->error;
?>

<!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>Parsing Date</th>
        <th>Number of rows</th>
       
      </tr>
    </thead>
    <tbody>
    <tr>
<?php 
// We got rows ...
  // Loop through results
  while($row = pg_fetch_object($result)) {
    echo <<< ROW
      <tr>
        <td>{$row->client_id}</td>
        <td>{$row->insurance}</td>
        <td>{$row->parsing_date}</td>
        <td>{$row->rows}</td>
      </tr>
ROW;
  }
?>
      </tr>
    </tbody>
  </table>
</div>
</body>
</html>

Open in new window

0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

This article discusses four methods for overlaying images in a container on a web page
Originally, this post was published on Monitis Blog, you can check it here . In business circles, we sometimes hear that today is the “age of the customer.” And so it is. Thanks to the enormous advances over the past few years in consumer techno…
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…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
Suggested Courses

656 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