Jump to content

[SOLVED] GROUP BY - getting weird results...

acook

By acook
in PHP Coding Help

Recommended Posts

acook Member

acook

Posted
Posted

I am trying to execute the following query:

 

SELECT COUNT(Incident_Id), Assignee_Group_Counter FROM Incident_Management WHERE (Submitter = 'Me') GROUP BY Assignee_Group_Counter;

 

When I execute from the command line, this is what I get:

 

Count(Incident_Id)      |  Assignee_Group_Count

---------------------------------------------------------

                27            |                  1               

                  1            |                  2

 

That is perfect.  That's what I'm looking for.  However, when I try to output the results via PHP it's not showing correctly.  Here's my code:

 

<html>


<?php

set_time_limit(0);

$db_user = "";
$db_pass = "";
$dsn = "OPASRPT";

$conn = odbc_connect($dsn, '', '');


$query= "SELECT COUNT(Incident_Id), Assignee_Group_Counter FROM Incident_Management WHERE (Submitter = 'Me') GROUP BY Assignee_Group_Counter";


$result = odbc_exec($conn, $query);

$results_array = array();

while(odbc_fetch_row($result))
{
  $results_array[] = array(
  'Result' => odbc_result($result, 1),
  
  );
  

  }

foreach($results_array AS $this_row)



{

echo "{$this_row['Result']}<br><br>";

}


odbc_close($conn);
?>

 

The result I get is just

 

27

 

1

 

How can I make it look like the output at the command line?  Please help.

Link to comment
https://forums.phpfreaks.com/topic/88617-solved-group-by-getting-weird-results/
Share on other sites
pocobueno1388 Regular Member

pocobueno1388

Posted
Posted

Your making it harder than it needs to be by creating your own array to store the values in. Try it this way

 

<?php

while($row = odbc_fetch_row($result)){

   echo $row['num'] . ' - ' . $row['Assignee_Group_Counter'] . '<br>';
  
}

?>

 

Your going to need to change your query to this

SELECT COUNT(Incident_Id) as num, Assignee_Group_Counter FROM Incident_Management WHERE (Submitter = 'Me') GROUP BY Assignee_Group_Counter;

 

 

Barand Prolific Member

Barand

Posted
Posted

try

<?php

set_time_limit(0);

$db_user = "";
$db_pass = "";
$dsn = "OPASRPT";

$conn = odbc_connect($dsn, '', '');


$query= "SELECT COUNT(Incident_Id), Assignee_Group_Counter FROM Incident_Management WHERE (Submitter = 'Me') GROUP BY Assignee_Group_Counter";


$result = odbc_exec($conn, $query);

$results_array = array();

while(odbc_fetch_into($result, $a))
{
    $results_array[] = $a;
}

foreach ($results_array as $a)
    echo "$a[0] | $a[1] <br/>";
?>

Barand Prolific Member

Barand

Posted
Posted

odbc_fetch_into() puts the fields of each row into an array.

 

try

echo '<pre>', print_r($results_array, true), '</pre>';

 

at the end to see what it looks like, it should help you to see what's going on.

acook Member

acook

Posted
  • Author
Posted

I know this topic is solved...but one more quick question (rather than starting a new post):

 

Is it possible to get the TOTAL results from the array count (in this case it'd be 28)?

 

I tried:

 

echo "total = " . array_sum($a[1]) . "\n";

 

But I'm getting an empty value.  Is there an easier way?  I think also I'll eventually need the individual numbers (like 27 and 1) to make percentages.  For example:

 

1 / 28

27 / 28

 

Is that possible?

cooldude832 Advanced Member

cooldude832

Posted
Posted

array_sum only works on integers or floats values so lets try somethign

 

<?php
function non_numerical_array_sum($array){
if(is_array($array)){
   $total = 0;
   foreach($array as $value){
              $total = $total+floatval($value);
    }
   return $total;
}

non_numerical_array_sum($a);
?>

 

Try that

cooldude832 Advanced Member

cooldude832

Posted
Posted

missed a }

<?php
function non_numerical_array_sum($array){
if(is_array($array)){
   $total = 0;
   foreach($array as $value){
              $total = $total+floatval($value);
    }
   return $total;
}
else{
  return FALSE;
}

non_numerical_array_sum($a);

acook Member

acook

Posted
  • Author
Posted

Thanks!!  You did it again!  That seems like a much easier solution.  Now if I want to pull a certain number out of the array is that possible?  I know I saw it somewhere in the form of:

 

($a, 1)

 

 

..but it could have been different.  Is it possible?

Barand Prolific Member

Barand

Posted
Posted

If I'd known where you were going my first solution would be different

<?php

set_time_limit(0);

$db_user = "";
$db_pass = "";
$dsn = "OPASRPT";

$conn = odbc_connect($dsn, '', '');
$query= "SELECT COUNT(Incident_Id), Assignee_Group_Counter FROM Incident_Management WHERE (Submitter = 'Me') GROUP BY Assignee_Group_Counter";

$result = odbc_exec($conn, $query);

$results_array = array();

while(odbc_fetch_into($result, $a))
{
    $results_array[$a[1]] = $a[0];                      // group is array key, count is the value
}

foreach ($results_array as $gp => $count)
{
    echo "$count | $gp <br/>";
}
$total=array_sum($results_array);                       // now it's a single dim array, you can array_sum it
echo $total;   
?>

 

Now if you want the total for group  2

<?php
$gp = 2;
echo $results_array[$gp];

Archived

This topic is now archived and is closed to further replies.

Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.