www.webdeveloper.com
Results 1 to 5 of 5

Thread: Pulling MySQL data sequentially

  1. #1
    Join Date
    Mar 2013
    Posts
    3

    Pulling MySQL data sequentially

    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.

    PHP Code:

    $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

    PHP Code:
    echo $row['Name'];
    echo 
    $row['Mobile'];
    echo 
    $row['Telephone']; 
    etc
    etc

  2. #2
    Join Date
    May 2004
    Location
    chennai, tamil nadu, India
    Posts
    437
    --------------------------
    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
    Chris, Senior Developer, Chrisranjana.com
    Php Programmers

  3. #3
    Join Date
    Mar 2013
    Posts
    3
    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...

  4. #4
    Join Date
    May 2004
    Location
    chennai, tamil nadu, India
    Posts
    437
    Quote Originally Posted by gazza_b View Post
    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.
    Chris, Senior Developer, Chrisranjana.com
    Php Programmers

  5. #5
    Join Date
    Mar 2013
    Posts
    3
    Quote Originally Posted by chrisranjana View Post
    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!!!

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles