Highlight table rows that do not fall in a numeric sequence

[jdeutsch]

I've tried searching all around: Google, the forums here, various other places, but all with no luck.

 

I'm not new to PHP, and this might not be a scripting question necessarily (might be more of a logic question), but it's driving me crazy:

 

I'm building a grading program for a school I work at.  There are several state tests given each year, and we want to store all the information in a MySQL DB.  Each student has a student id number, as well as a test reference number.  Some of the reference numbers are not in the correct sequence because the table must absolutely be sorted by the students' last names.

 

What I'd like to do is be able to highlight any rows that do not follow the standard numerical sequence (1, 2, 3...).  For example:

 

Ref #  ID #      Last Name

1        100000  Arnold

2        100001  Burns

3        100002  Cassidy

5        100003  Donaldson  (this row needs to be highlighted)

4        100004  Cummings

 

At the moment, my script seems to be highlighting every row after (and including) the out-of-sequence row.  I've got the basics working: since the reference numbers begin with 1, I've got the script checking to see if the reference number is equal to $i (for my for loop) plus 1, but I know that this is what is causing the problem of all rows after being highlighted.

 

I've tried starting with an empty variable ($refnum=0) and making this equal to the next ref num in the sequence (only if the current one is one more than the last), but that doesn't work either.

 

If anyone has done this before and has a sample script to help me out, or even just some suggestions, thoughts, comments, or a solution that they can describe (with or without code), it would be immensely appreciated!

[sKunKbad]

OK, since this is more of a logic question, then why if the table is sorted by last name do you have to worry about the id being in line too. Most tables would only have one primary key.

[jdeutsch]

Each test is identified by the student's reference number.  Ultimately, the sorting of the table has nothing to do with this number, but because both the last name and the reference number are ways we identify tests (especially when inputting grades), I'd like the table to have some sort of obvious styling applied to that row to indicate that while it is in alphabetical order, it is out of sequence.

 

It's a relatively backward system we have: when we grade, online, everything must be sorted by last name; however, when we organize the physical test papers, everything must be sorted by the reference number.

 

For the most part the there are maybe two or three records whose reference numbers are out of sequence (out of 600 or so test-takers), and these are people who registered late and therefore were given reference numbers after the original batch was assigned (if this all makes sense to anyone).

 

Despite the fact that I am frequently sitting and coding, I'm actually not a logically-intelligent person, and small problems like this (that I feel have really simple answers) baffle me for days!

[sKunKbad]

I would think that you could take a static variable, in a for or while loop, and if the id is out of sync with the current variable value, mark that row with the background color, and for this row DO NOT increase the variable. During the next pass of the loop, the static variable should be in sync. If you can show some code, I'm sure we can get you set up quickly

[Andy-H]

$_query = "SELECT Ref, ID, `Last Name` FROM tableName ORDER BY `Last Name` ASC";
$_result = mysql_query($_query);

$n = 0;

while ( $_row = mysql_fetch_assoc($_result) )
{
extract($_row, EXTR_PREFIX_ALL, "stud");

  if ($stud_Ref == $n + 1)
  {
        $n+= 1;
        // Ref order correct, display data
   }else{
      //Display different BG color
   }

}

[jdeutsch]

@skunk

$s=get_all_students();
for($i=0;$i<count($s);$i++) {
...
$regnum=student_reference_number($s[$i]['student_id'],$_SESSION['test_info']['test']['id'],$_SESSION['test_info']['session']['session_id']);
if($regnum!=($i+1)) {
	$style.=" unsequenced";
	$title=$s[$i]['student_last_name'].", ".$s[$i]['student_first_name']."'s reference number is out of the numerical sequence.";
}
...
}

This was what I had done initially.  The script checked the Reg. Num. ($regnum) against the current value of $i as the for loop processed.  This caused all of the rows after the initially unsequenced one to be highlighted as well.

 

@Andy

$s=get_all_students();
$j=0;
for($i=0;$i<count($s);$i++) {
...
$regnum=student_reference_number($s[$i]['student_id'],$_SESSION['test_info']['test']['id'],$_SESSION['test_info']['session']['session_id']);
if($regnum!=($j+1)) {
	$style.=" unsequenced";
	$title=$s[$i]['student_last_name'].", ".$s[$i]['student_first_name']."'s reference number is out of the numerical sequence.";
} else {
	$j++;
}
...
}

I also tried what you suggested, but it caused a similar issue as above, except while the first snippet didn't start highlighting all rows until the third or fourth unsequenced record, this version (which includes $j), highlighted every row after the initial unsequenced record.

 

I definitely appreciate the suggestions and the help.  I hope the code snippets help you to help me.

 

[Andy-H]

Can you explain what happens in your functions? For example, the get_all_students() function is used to return a multi-dimentional array containing the query data (im guessing) ??

 

Perhaps if you could show the array contents if you are using a test database rather than real students' info?

 

I am going to have to go now tbh, college tomorrow (well today; lol) but I'm sure if you provide the above; someone can help you.