SQL: Determining position in a group

daymobrew

I have a database for a school with a first come, first served enrolment policy.
I would like to email parents with their unique enrolment number and also their position in the list.

The db fields include: pe_num, name, class, year (other fields like email, phone, address are probably not relevant for this question).
The year is 2008, 2009 etc
The class is an enum: JI (junior infants), SI (senior infants), 1st etc

How can I generate info like:
$pe_num is $POSITION in line for $class for $year
e.g. 134 is 12th in line for JI for 2008.

What do I need to add to this?
SELECT pe_num, name WHERE class='JI' and year=2008 ORDER BY pe_num ASC

I am using MySQL 5.0 with php 5.2.2.
I'd like to be able to write a script that will email each person on the list with the info.

I know that I could run the above SELECT statement and have a counter to generate the $POSITION info, but wonder if it is possible to delegate this to MySQL.

Eoin

Can you not just add an auto incrementing column to the table?

daymobrew

eoin_s wrote: »

Can you not just add an auto incrementing column to the table?

I wouldn't have thought so because the number would depend on the class and year. And, to make it more complicated, I've had to change a few year entries (date of birth rule).
The pe_num column is unique and could be auto incrementing (I didn't make it so because not all pre numbered forms were returned).

Here is an example of the db: (columns: pe_num,name,class,year)
1,Paul,SI,2008
2,John,JI,2009
3,Jean,JI,2008
4,Mick,JI,2008
5,Sean,JI,2009
6,Jack,SI,2008

So, Jack is 2nd in the list for SI 2008; Jean is 1st in the list for JI 2008.
Would an auto incrementing column work? What happens when the parents of Sean decide they want him to start school in 2010?

As said, I'm happy to select all from a specific class/year and count the position in the list as the script goes along.

Webmonkey

If you are running this in PHP can't you just use the Array Dataset index's as the position?

Like
[php]
$query = "SELECT pe_num, name FROM parents WHERE class='JI' and year=2008 ORDER BY pe_num ASC";
$result = mysql_query($query);
$count;
for ($count=0; $count < count($result); $count++)
{
//$count holds the position
$row = $result[$count]; //Get the row array from result array
printf("Number: %d<br />Name: %s<br />Position: %d<br /><br />",$row,$row,$count);
}
[/php]

I don't know if that is what you mean or not. Confused

*Haven't tested code.

Webmonkey

Or you could do it this way:

SET @a:=0;
SELECT pe_num,name, @a:=@a+1 as position FROM parents WHERE class='JI' AND year = '2007' ORDER BY pe_num ASC;

Then you will end up with an extra column/field in your dataset displaying the position. I'm guessing this is more to do with what you want to accomplish.



The PHP Code:

[php]
<?php

$conn = mysql_connect("localhost","root","xxxxx") or die ("Failed to Connect");
mysql_select_db("yourdatabase") or die ("Failed to Select DB");

mysql_query("'BEGIN MYTRANS",$conn);
mysql_query("SET @a:=0;",$conn);
$resultSet = mysql_query("SELECT pe_num,name, @a:=@a+1 as position FROM yourtable WHERE class='JI' AND year = '2007' ORDER BY pe_num ASC;",$conn);
mysql_query('COMMIT',$conn);

while ($row = mysql_fetch_assoc($resultSet))
{
//Do your emailing here
printf("Enr Num: %d | Name: %s | Position: %d<br />",$row,$row,$row);
}
?>
[/php]

Output:

Enr Num: 34 | Name: Joe | Position: 1
Enr Num: 42 | Name: Mary | Position: 2
Enr Num: 43 | Name: John | Position: 3
Enr Num: 54 | Name: Harry | Position: 4

Hope this helps.

daymobrew

Webmonkey wrote: »

If you are running this in PHP can't you just use the Array Dataset index's as the position?

As I've said, this is my fallback plan (and quite an acceptable one).
As I'm not too familiar with SQL and I know that the forum (or maybe it was the Programming forum where I should have posted this ) has SQL experts, I wondered if there was an purely SQL way.

Thanks both for the algorithm 'review.'

Webmonkey

What bout the SQL way I posted above, (and all that trouble printing the nice photo graph with accompanying code!) any good?

daymobrew

Webmonkey wrote: »

What bout the SQL way I posted above, (and all that trouble printing the nice photo graph with accompanying code!) any good?

Oops, sorry, I didn't see the second post - looks great - I'll give that a go. Thanks.

daymobrew

Webmonkey wrote: »

Or you could do it this way:

SET @a:=0;
SELECT pe_num,name, @a:=@a+1 as position FROM parents WHERE class='JI' AND year = '2007' ORDER BY pe_num ASC;

This is perfect; it works; really appreciated.

Webmonkey

daymobrew wrote: »

This is perfect; it works; really appreciated.

Excellent Stuff

trellheim

is there no rownum concept in mySQL ?

Webmonkey

trellheim wrote: »

is there no rownum concept in mySQL ?

I don't think so no - but someone correct me if I'm wrong.

democrates

Daymobrew gave a good answer there, the first thing that occurred to me was to use the same counter idea, just on the php side when retrieving the rows.

EG use mysql_num_rows($result) to run a for loop fetching the rows starting with $i=0, so the applicants ordinality would be $i + 1.

daymobrew

democrates wrote: »

Daymobrew gave a good answer there, the first thing that occurred to me was to use the same counter idea, just on the php side when retrieving the rows.

Yes, this is the obvious solution but I was looking for a pure SQL method and Webmonkey's one works perfectly.