Pulling MySQL data sequentially

[gazza_b]

Can never find the answer to my problems - always find something close but not the solution!!!

Anyway, a relative newbie to all this and would like some help as I just can't find the solution with for() continue() next() etc etc - all confusing.

My problem is - I am creating an online roster for a volunteer organistion.

Every day there is a Manager on call, but if they are unavailable, the roster will just go to the next available Name.

If the particular Name is the last row of the table, it needs to Loop back to the top and go through the names in sequential order.

EG


--------------------------
id | Name
----------------------------
1 | Joe
2 | Bill
3 | Bob
4 | Richard
5 | Tom
----------------------------
Scenario

Bill is rostered on, but has indicated via an Admin Form he is unavailable today. Therefore the Roster will indicate Bob as the Duty Manager. If Bob has also indicated not available, then would show Richard as Duty Manager.

If Tom was down as Duty Manager, but was unavailable, would show Joe etc etc

Basically Loops until next available Name (in sequence).

I realise that this could end up an infinite Loop if NO ONE available so would have to break after one / two complete Loops?

My current code I'm experimenting with is (found on the PHP Site) which displays relevant data according to the query string.

$query = "SELECT * FROM users LEFT JOIN days ON users.id = days.id WHERE users.Avail = 'Y' AND users.Manager = 'y' AND users.Active = 'Y' AND days.tues = 'Y' AND users.Tues = 'Y'";

$result = mysqli_query($roster,$query);

while ($row = mysqli_fetch_assoc($result))
 {

          printf ("Name: %s\n", $row['Name'].'<br /><br /><br />');

  // Looping through the resultset.
 }

// Now if you need to loop through it again, you would first call the seek function:
mysqli_data_seek($result,0);

while ($row = mysqli_fetch_assoc($result))
 {
         printf ("Name: %s\n", $row['Name']. '<br />');
// Looping through the resultset again.
 }
mysqli_close($roster); 

Above just shows below results -

Name: Bob Smith
Name: Tom Jones

Then repeats again

Name: Bob Smith
Name: Tom Jones

After a successfully query, would then just place the output into specific areas of a HTML Page

echo $row['Name'];
echo $row['Mobile'];
echo $row['Telephone']; 

etc
etc

[chrisranjana]

--------------------------
id | Name
----------------------------
1 | Joe
2 | Bill
3 | Bob
4 | Richard
5 | Tom
----------------------------

Maybe have another table column `lastused` and save the timestamp

1)whenever someone indicates they are NOT available
2)After a particular Name is shown in the search result.

Then every time use an ORDER BY `lastused` clause with the main query

[gazza_b]

Thx Heaps Chris

Have implemented the Timestamp idea...

Seems to be OK, but as soon as the record is updated, the sequential order throws a hissy fit - all good if the record is updated sequentially.

Will continue to pursue this to see if I can work it out...

[chrisranjana]

Thx Heaps Chris



Seems to be OK, but as soon as the record is updated, the sequential order throws a hissy fit - all good if the record is updated sequentially.

On second thoughts I guess you need to update the `lastused` column ONLY when a person comes up in the search result as a manager on call for that day, since anyway the unavailable users are eliminated using the sql query already.

Also instead of defining the `lastused` column as timestamp type, define it as a normal int type and then store a manually generated php timestamp in it.

[gazza_b]

Also instead of defining the `lastused` column as timestamp type, define it as a normal int type and then store a manually generated php timestamp in it.

What I have done based on your comments was to define the 'lastused' as a DATETIME, manually set the times say a minute apart (only on Managers, the rest can stay at 00:000 00 whatever), then used a SET lastused= now() ....

The DATETIME then is updated on Search result.

WIll give this a good workover in the next few days and see if it gives me any grief or not. NOT I hope!!!