Group results by name

[TEENFRONT]

Hi

 

Just a quick one.

 

I want my output result to look like this. In its simplistic form I have 2 columns, NAME and NUMBER.

 

Adam

1

2

3

 

Becky

1

2

3

 

Charlie

1

2

3

 

 

So the output results are grouped by the "name" field, then ordered by the "number" field.

 

I thought it was GROUP BY, but that function is something else.

 

Iv tried searching around but im unsure what you call this form of grouping results.

 

Many Thanks!

[jake2891]

select * from tablename order by name,number asc

[TEENFRONT]

Thanks jake,

 

That gets me these results

 

Adam 1

Adam 2

Adam 3

 

Becky 1

Becky 2

Becky 3

 

etc

etc

 

How do i then group the results just by name?

[jake2891]

what exactly are you trying to accomplish with this query? group by is used in conjuction with aggregate functions

[TEENFRONT]

Im trying to achieve what i posted above

 

The results need to come out like this

 

Adam

1

2

3

 

Becky

1

2

3

 

Charlie

1

2

3

[unlishema.wolf]

Can you pst you output code? Like the code you have it put the names and numbers out with.

[TEENFRONT]

Sure i simply have a select like auggested above..

 

$query = "SELECT INCIDENT_NO, SCHEME_CODE FROM incidents WHERE JOB_DATE_TIME LIKE '$searchDate' ORDER BY SCHEME_CODE, INCIDENT_NO ASC";

$sql = mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_array($sql)){
echo $row['SCHEME_CODE'];
echo "<br>";
echo $row['INCIDENT_NO'];
}

 

That results in

 

Adam

1

Adam

2

Adam

3

 

Becky

1

Becky

2

Becky

3

 

 

[unlishema.wolf]

This will take me a min. I have to write the code on a phone keyboard. Lol watch for my next post.

[TEENFRONT]

many thanks

[unlishema.wolf]

while($row = mysql_query($query) or die(mysql_error());
{
  echo $row['SCHEME_CODE'];
  echo "<br>";
  while($row2 = mysql_query($query) or die(mysql_error());
  {
    if($row['SCHEME_CODE'] = $row2['SCHEME_CODE'])
    {
      echo $row2['INCIDENT_NO'];
      echo "<br>";
    }
  }

}

 

you may have to fix the if statement cause i don't remember if php uses double equals or single lol.

[unlishema.wolf]

I made a mistake. This will group them but will post it multiple times also. Give me a min to fix this error.

[unlishema.wolf]

$i=0;
while($row = mysql_query($query) or die(mysql_error());
{
  if(i<1) $temp = $row['SCHEME_CODE'];
  else $temp = "";

  if(!temp = $row['SCHEME_CODE']) echo $row['SCHEME_CODE'];
  echo "<br>";
  echo $row['INCIDENT_NO'];
  $i=$i+1;
}

 

you may have to fix the second if statement cause i don't remember how to do a not statement. I have been coding c++ a lot lately.

 

[Adam]

Assuming that SQL works as you want, try this:

 

$sql = "SELECT INCIDENT_NO, SCHEME_CODE FROM incidents WHERE JOB_DATE_TIME LIKE '$searchDate' ORDER BY SCHEME_CODE, INCIDENT_NO ASC";
$query = mysql_query($sql) or trigger_error('MySQL Error: ' . mysql_error());

$prev_code = '';

while ($row = mysql_fetch_assoc($query))
{
    if ($prev_code != $row['SCHEME_CODE'])
    {
        $prev_code = $row['SCHEME_CODE'];
        echo '<br />' . $prev_code . '<br />';
    }

    echo $row['INCIDENT_NO'] . '<br />';
}

 

@unlishema.wolf

 

It's double-equals. Also mysql_query only returns a result resource, you then have to loop through that using one of the fetch functions (e.g. mysql_fetch_assoc).

[TEENFRONT]

MrAdam,

 

That works perfectly! Many Many thanks!

[unlishema.wolf]

I know I use to code in php a lot but since I lost internet I haven't had any use to also no local server to test with. Btw you have better code there I have been in a bad situation for awhile now and my brian is just starting to recover. Like a lot of stress. Hoping to get internet back within the next month.

 

Also I'm typing on a phone keyboard. Lol

[TEENFRONT]

Hi,

 

Iv just changed it slightly as i now need to count the amount of INCIDENT_NO's per SCHEME_CODE. Iv got this..

 

$sql = "SELECT INCIDENT_NO, SCHEME_CODE FROM incidents WHERE JOB_DATE_TIME LIKE '$searchDate' AND SCHEME_CODE != '' ORDER BY SCHEME_CODE, INCIDENT_NO ASC";
$query = mysql_query($sql) or trigger_error('MySQL Error: ' . mysql_error());

$prev_code = '';
$num = '';

while ($row = mysql_fetch_assoc($query))
{
    if ($prev_code != $row['SCHEME_CODE'])
    {
        $prev_code = $row['SCHEME_CODE'];
        echo $num; 
        $num =''; 
        echo '<br />' . $prev_code . '<br />';
    }

    $num++; 

}

}

 

that outputs

 

ALLASS
5
CTLR
83
DHOL1
174
FMNI
37
FR
104
HEX
173
JMR
4
RANS
14
ROLR
2
SCAS
2
SDC
58
TE1

 

As you can see the last one TE1 is missing its count. I know why - its because the code is set to count the previous SCHEME_CODE then on outputting the new SCHEME_CODE, show the number of incidents, and the last one, doesnt have one after to show.

 

Any advice? Or another way to count each incidents per title?

 

The output looks like this (cut down) without the adding of  incident amounts.

 

ALLASS
1
2
3
4
CTLR
1
2
3
4

 

So what im doing is counting the amount of "numbers" (INCIDENT_NO's) per title (SCHEME_CODE) .. and running into an issue with the last one.

 

[unlishema.wolf]

Sadly I can't see all of the outputs due to my phone not being able to scroll the

 part. I can see it if you would upload it in a text file or something.

[TEENFRONT]

Il mark this as solved as my original question is sorted. cheers!