[RESOLVED] counter corresponding the ID

[Ali_baba]

Hello,

I am working on the display of the records in the list form. displaying is not a problem but the counter (visit counter) is.

Each patient ID has the date visit, and I would like to sort them in ASC order. Also I would like to have a counter that can be reset to 1 when there is the next ID.

For example:

Patient ID: 1
Visit number: 1
Date: 31/01/2006 (or any date)

Patient ID: 1
Visit number: 2
Date: 01/02/2006 (or any date)

Patient ID: 2
Visit number: 1
Date: 31/01/2006 (or any date)

Patient ID: 2
Visit number: 2
Date: 01/02/2006 (or any date)

The point is that: The patients IDs should be sorted following their dates. And the counter needs to be resetted when the ID is changed. The counter doesn't need to be saved in database.

Here is the php code. I am trying my best to beat my head with it 🙂

$result = mysql_query("SELECT IDnum,datevisitFROM DataCollection ORDER BY IDnum AND datevisit"); 
  while ($array1 = mysql_fetch_array($result1))  {

Thanks in advance 🙂

[bough]

$result = mysql_query("SELECT IDnum,datevisit FROM DataCollection ORDER BY IDnum,datevisit");

$count = 0;
$currID = 1;

while ($array1 = mysql_fetch_array($result1))
{
if($currID != $array1[0])
{
$count = 1;
} else {
$count++
}

echo "Patient ID : $array1[0]";
echo "Visit Number : $count";
echo "Date : $array[1]";
$currID = $array[0];
}

Does this help? I am at work so unable to test the code, but it should reset the counter when the ID changes from the previous one

[Ali_baba]

Hello,

Thanks for your reply. I will test this tomorrow as I got some "orders" to fix the Access database.

Thanks again once more 🙂

[Ali_baba]

Yes I checked it today, and it seems that it is not working. The ID for patients is actually IDnum comming from the database.

[Roger_Ramjet]

The reason it's not working is because you run the query into $result but then try to ittererate through $result1.

$sql = "SELECT IDnum, datevisit FROM DataCollection ORDER BY IDnum, datevisit"; 
$result = mysql_query($sql) or die ($sql . '<br>' . mysql_error);

$curr_id = 0;
$i = 1;

while ($row=mysql_fetch_array($result))  {
   if ($row['IDnum'] != $curr_id) {
       $curr_id = $row['IDnum'];
       $i = 1;
   }
   echo 'Patient ID: ' . $row['IDnum'] . '<br />';
   echo 'Visit number: ' . $i . '<br />';
   echo 'Date: ' . $row['datevisit'] . '<br /><br />';
   $i++;
}

[Ali_baba]

Yes I realized the mistake (result and result1) and it was not working
but Roger, your solution just worked like a charm. Thanks a lot sir 🙂

[bough]

oops

sorry about the typos, bad syntax from me with $array and $result

I hope that it works now roger has fixed my errors 🙂

[Ali_baba]

no problemo 🙂
I know I am newbie in PHP but not that newbie 🙂

[Ali_baba]

Hello,

I am trying to count the occurence (no of times) of a record, and to display that how many times that record exists. Also the date of its first occurence and the date of its most recent occurence. (They are saved while entering that record in the database). How may I do that? This is killing me 🙁

It should be like this:

Part ID no: 1234
record occurence: 4 times
Date of first record: 25/02/06
Date of last occurence: 28/02/06

Here is my php code:

e:
$result = mysql_query("SELECT IDnum,record_date FROM DataCollection ORDER BY IDnum, record_date"); 
  while ($array1 = mysql_fetch_array($result1))  { 

Thanks in advance 🙂