help i need to generate student ID grouping by school

[kcachilove]

i have a table in my database (Mysql),
with fields syear,
school_id ,student_id
(syear is current year),(school_id is the ID of each school,
i have more than 10 schools),(student_id is the student id) .
i want to write a php script that will assign
new ID to each students grouping it by school id using the current year.
So on every school id the new ID will be assign to student from 1,if the year changes
the school id will also start afresh again (Yearly Student Registration). and any number
that it has assign for that year will not be repeated again the same year for each school...
..i tried using mysql query but it dint work cause if i select * the ID it will assign will
be different from the id it will assign when i select a student,meanwhile i ll not want
to create any more table or field for that.
Please i will appreciate if you can help me with a script for that.Or help me correct the code below.

<?
$connection = mysql_connect('localhost', 'root', '') or die(mysql_error());
mysql_select_db('edosis') or die(mysql_error());

$result = mysql_query("select * from student_enrollment order by student_id") or die (mysql_error());

if (mysql_num_rows($result) > 0 )
{
while($row = mysql_fetch_row($result,MYSQL_ASSOC))
{

$i++;
$school_id=$row['school_id'];

$student_id=$row['student_id'];
$eyear=date('Y');

echo "$eyear/$school_id/$student_id/$i<br/>";

}
}else{

echo 'no row found';
}

?>

[kcachilove]

kcachilove;10995976 wrote:

i have a table in my database (Mysql),
with fields syear,
school_id ,student_id
(syear is current year),(school_id is the ID of each school,
i have more than 10 schools),(student_id is the student id) .
i want to write a php script that will assign
new ID to each students grouping it by school id using the current year.
So on every school id the new ID will be assign to student from 1,if the year changes
the school id will also start afresh again (Yearly Student Registration). and any number
that it has assign for that year will not be repeated again the same year for each school...
..i tried using mysql query but it dint work cause if i select * the ID it will assign will
be different from the id it will assign when i select a student,meanwhile i ll not want
to create any more table or field for that.
Please i will appreciate if you can help me with a script for that.Or help me correct the code below.

<?
$connection = mysql_connect('localhost', 'root', '') or die(mysql_error());
mysql_select_db('testdatabase') or die(mysql_error());

$result = mysql_query("select * from student_enrollment order by student_id") or die (mysql_error());

if (mysql_num_rows($result) > 0 )
{
while($row = mysql_fetch_row($result,MYSQL_ASSOC))
{

$i++;
$school_id=$row['school_id'];

$student_id=$row['student_id'];
$eyear=date('Y');

echo "$eyear/$school_id/$student_id/$i<br/>";

}
}else{

echo 'no row found';
}

?>

i have gotten to this point now. i can print into a multidimensional array but i can't get the IDs i need which i thought i could get using the keys of the subarrays, is not working. the code is below

 $sql = "SELECT school_id, student_id,syear
        FROM student_enrollment
        order by student_id";
$query = mysql_query($sql);
$combinedResults = array();

while($result = mysql_fetch_array($query)) {
    $combinedResults[$result['school_id']][] = $result['student_id'];
}

echo '<ul>';

// now loop through the combined results
foreach(array_keys($combinedResults) as $groupKey) {
    echo '<li>'.$groupKey;
    echo '<ul>';
    foreach($combinedResults[$groupKey] as $item) {

    echo '<li>'.$item.'</li>';
	//echo '<li>'.$i++.'</li>';
	//print_r(array_values($item));

}
echo '</ul></li>';
}

echo '</ul>';  

[Weedpacket]

That looks more or less right to me.... $groupKey should certainly be a value of school_id. Can you better describe what is happening beyond it "not working"?

Here's a slightly different array structure, since you'll have noticed that there's nowhere in your $combinedResults array for the other fields.

$sql = "SELECT school_id, student_id,syear
        FROM student_enrollment
        order by student_id";
$query = mysql_query($sql);

$students_by_school = array()
while($result = mysql_fetch_array($query)) {
	$students_by_school[$result['school_id']][$result['student_id'] = $result;
}


echo '<ul>';
// now loop through the combined results
foreach($students_by_school as $school_id => $students) {
	echo '<li>', $school_id;
	echo '<ul>';
	foreach($students as $student_id => $student) 	{
		echo '<li>', $student_id, '/', $student['syear'], '</li>';
		// $student also has fields 'school_id'
		// and 'student_id' for completeness.
	}
	echo '</ul>';
}
echo '</ul>';

[kcachilove]

thanks so much Weedpacket

i have gone through your post ,but the student id is still running through all the schools, what i want to achieve is to assign the ID from 1 in every school,I only intend to use the student_id I have now to sort for the arrangement of the new ID. I need to generate a new ID for all the students in each school which will always start from one every year.

[Weedpacket]

Maybe I'm tired, but I'm not making any sense out of what you just wrote. Do you mean you want

echo '<ul>';
// now loop through the combined results
foreach($students_by_school as $school_id => $students) {
    echo '<li>', $school_id;
    echo '<ul>';
    $new_id = 1;
    foreach($students as $student_id => $student)     {
        echo '<li>', $student_id, ' => ', $new_id++, '</li>';
        // $student also has fields 'school_id'
        // and 'student_id' for completeness.
    }
    echo '</ul>';
}
echo '</ul>'; 

[kcachilove]

thanks Weedpacket

its printing the new ID now but i want to let you know what i want to do with it, so you can help me on it'
the Reg number i generate for students was like this

 
//$sch_id2  this is the school_id
//$Current_yr  this is the current year
//$st_id this is the Student _id

$Reg=$sch_id2/$Current_yr/$st_id;

// this $Reg will generate for 3 different school 
//for school_id=1 
//$Reg=1/2012/01
             1/2012/02
              1/2012/03
//for school_id=2
               2/2012/04
              2/2012/05
              2/2012/06
//for school_id=3
               3/2012/07
              3/2012/08
              3/2012/09

[COLOR="YellowGreen"]but what i want to achieve is

//for school_id=1 
//$Reg=1/2012/01
             1/2012/02
              1/2012/03
//for school_id=2
               2/2012/01
              2/2012/02
              2/2012/03
//for school_id=3
               3/2012/01
              3/2012/02
              3/2012/03
[/COLOR]

please how can i extract $new_id++ so i can use it a the new ID, but i notice that the new ID will change to 1 each time i call a single student from the database. Is there a better way to achieve this, cos what i want is to generate a new ID for the student that i can group by the school ,and i will not want it to be changing. If there another better way to achieve it please let me know.....thanks

[johanafm]

I'm curious as to why you'd want ids to start from 1 each year. Depending on the specific needs, I'd either use two or three tables. If you for some reason don't have to deal with students changing schools (such as not caring about what school they attended last year), two tables is enough: student and school. If you do want to be able to track what schools each student attended at any given time, you'd need three tables: student, school, student_school (student_id, school_id, started, ended).
Since the students would be given ids ranging from one to MAX(int) when inserted into the student table, this would also be the value inserted into student_school. I don't see how it would make any difference if a student has id 1 or 55. The id would still uniquely identify the student, and would also allow you to retrieve what school they are or have been attending. You can still count the number of students by retrieving all students which attended a specfic school at a given date.