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

acook · January 30, 2008

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.

pocobueno1388 · January 30, 2008

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;

acook · January 30, 2008

Hmmm, I tried that and I just got:

-

-

...as the result. Any ideas why?

pocobueno1388 · January 30, 2008

Hmm...I'm not familiar with using "odbc", so maybe it can't be used the way I tried to.

acook · January 30, 2008

Hmm...I'm not familiar with using "odbc", so maybe it can't be used the way I tried to.

Awww man, this is the problem I keep running into. Nobody knows odbc. ...and neither do I. haha

Barand · January 30, 2008

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/>";
?>

acook · January 30, 2008

Hey, wow! That worked. Is there any way you can explain how it's working (just for my own knowledge)? Thanks again!!

Barand · January 30, 2008

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 · January 30, 2008

Thanks again!! SOLVED

acook · January 30, 2008

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 · January 30, 2008

well of course you get a blank result

$a[0] is not an array so how could it have an array_sum?

u want to sum the array $a

acook · January 30, 2008

Yeah, when I do

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

I get 1. Is it just pulling the last one?

cooldude832 · January 30, 2008

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 · January 30, 2008

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 · January 30, 2008

Looks like you missed another }.

Hmm, I'm getting a blank value with this.

cooldude832 · January 30, 2008

well even with it there you need to echo it out

i.e

$var = non_numerical_array_sum($a);
echo "The Sum is: ".$var;

Barand · January 30, 2008

<?php
$total=0;
foreach ($results_array as $a)
{
    $total += $a[0];
}
echo $total;

acook · January 30, 2008

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?

cooldude832 · January 30, 2008

the array nodes are called by saying

$ARRAY_VARIABLE_NAME['ARRAY_NODE_NAME'];

if its indexed ti be like

$arary_var_name[0];

If its a non index text node it be

$array_var_name['cat'];

for say

Barand · January 30, 2008

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];

acook · January 30, 2008

Wow Barand! That works even better! Thanks a million, you really are a genius.

Sign In

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

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information