Query kills Loop
Page 1 of 2 12 LastLast
Results 1 to 15 of 23

Thread: Query kills Loop

  1. #1
    Senior Member
    Join Date
    Sep 2012
    Posts
    269

    Query kills Loop

    This works:

    PHP Code:
        for ($Count 0$Count <= 6$Count++)
             {
             
    $date date_create($StartDate);
                 
    date_add ($datedate_interval_create_from_date_string($Count 'days'));
                 
    $IncDate date_format($date'Y-m-d');

                 echo 
    $Count ' xx ' $IncDate '<br>';
                                
               } 

    This dies after the 1st iteration:

    PHP Code:
        for ($Count 0$Count <= 6$Count++)
            {
            
    $date date_create($StartDate);
                    
    date_add ($datedate_interval_create_from_date_string($Count 'days'));
            
    $IncDate date_format($date'Y-m-d');

                    echo 
    $Count ' xx ' $IncDate '<br>';
                                
                    
    $query "SELECT * FROM TOAWorkOrders WHERE TechNum = $TechNum AND WorkDate = $IncDate";
                    if (
    $result $mysqli->query($query)) 
                        {

                            while (
    $row $result->fetch_assoc()) 
                        {
                         echo 
    $row['BBT'];
                   }
                }
        } 

    The $query itself is valid. I have tried several different queries, and no matter what i try, the script still dies prematurely. I'm still trying to get this 'mysqli' system down. I one other loop where the same thing is happening. What am I missing?
    Last edited by timstring; 02-26-2013 at 02:33 PM.

  2. #2
    High Energy Magic Dept. NogDog's Avatar
    Join Date
    Aug 2006
    Location
    Ankh-Morpork
    Posts
    13,949
    I'm guessing (without going through everything with a fine-toothed comb) that you could get what you need with a single query, using a BETWEEN <start_date> AND <end_date> clause in your WHERE clause, eliminating the for_loop.
    Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be." ~ from Nation, by Terry Pratchett

    "But the main reason that any programmer learning any new language thinks the new language is SO much better than the old one is because he’s a better programmer now!" ~ http://www.oreillynet.com/ruby/blog/...ck_to_p_1.html


    eBookworm.us

  3. #3
    Senior Member
    Join Date
    Sep 2012
    Posts
    269
    No, the for_loop is necessary because I need to know if the criteria is null. Using 'BETWEEN' only returns data that matches the criteria, in this case, 4 or 5 days out of 7. I need the full array from 0 to 6.


    if this makes any sense. Don't sound right to me.

  4. #4
    Pna lbh ernq guvf¿
    Join Date
    Jul 2004
    Location
    Kansas City area
    Posts
    19,429
    Why do you need a "full array from 0 to 6" ? If you use the single query method and only get results for days 1, 3, and 4, isn't it straightforward that days 0, 2, and 5-6 need to have "empty" place holders?

  5. #5
    Senior Member
    Join Date
    Sep 2012
    Posts
    269
    Yes, i need the empty place-holders

    I am trying to fill out a weekly calendar, so, I need to know that:

    Saturday = ''
    Sunday = 3
    Monday = ''
    Tuesday = ''
    Wednesday =
    Thursday = 5
    Friday = 9

    If I use BETWEEN, only Sunday, Thursday, and Friday will be returned. I need the full seven days.

    Doesn't matter, the query still crashes the routine
    Last edited by timstring; 02-26-2013 at 03:54 PM.

  6. #6
    Pna lbh ernq guvf¿
    Join Date
    Jul 2004
    Location
    Kansas City area
    Posts
    19,429
    Quote Originally Posted by timstring View Post
    the only way to accomplish this is with a loop.
    Really?

    Let's play a game. Think of the set of integers between 1 and 5 (inclusive). Now think of the integers 1, 3, and 4. Which values are missing from the second set that are present in the first set?

    If you said "2 and 5, of course!", then how did you know this without me going through every number in the first place and telling you whether it was or was not present in the second list?

  7. #7
    Senior Member
    Join Date
    Sep 2012
    Posts
    269
    You passed me in the night. see above.

  8. #8
    Pna lbh ernq guvf¿
    Join Date
    Jul 2004
    Location
    Kansas City area
    Posts
    19,429
    I still don't see what the problem is with the BETWEEN approach.

    You execute the query, get the results - store them in an array (indexed by day). Then you do a for() loop to loop through all days you want to display. For each of the days, if data exists in the results array you previously created, display the data from the DB. Otherwise, the DB didn't return anything from that day, so output some empty placeholder and move on. Lather, rinse, repeat.

  9. #9
    Senior Member
    Join Date
    Sep 2012
    Posts
    269
    I do believe that's what I am doing.

    Anyhoo

    PHP Code:
    $query2 "SELECT * FROM TOAWorkOrders WHERE TechNum = $TechNum AND WorkDate BETWEEN $StartDate AND $EndDate";


    if (!
    $mysqli->query($query2)) {
            
    print_r($mysqli->error_list);
        }

    else
        {
            echo 
    'the bear went over the mountain' '<br><br>';
            if (
    $result $mysqli->query($query2)) 
                {
                    while (
    $row $result->fetch_assoc()) 
                        {
                            
    $BBT $row['BBT'];
                            echo 
    $BBT;
            
                        }
                }
            }
                                } 
    kills the script
    Last edited by timstring; 02-26-2013 at 03:59 PM.

  10. #10
    Pna lbh ernq guvf¿
    Join Date
    Jul 2004
    Location
    Kansas City area
    Posts
    19,429
    Quote Originally Posted by timstring View Post
    I do believe that's what I am doing.
    It isn't in any of the code you've posted thus far.

  11. #11
    Senior Member Derokorian's Avatar
    Join Date
    Apr 2011
    Location
    Denver
    Posts
    1,784
    Also, in queries dates aren't numbers and need quotes.
    Last edited by Weedpacket; 02-26-2013 at 04:32 PM. Reason: reinserted a vital negation
    Sadly, nobody codes for anyone on this forum. People taste your dishes and tell you what is missing, but they don't cook for you. ~anoopmail
    I'd rather be a comma, then a full stop.
    User Authentication in PHP with MySQLi - Don't forget to mark threads resolved - MySQL(i) warning

  12. #12
    High Energy Magic Dept. NogDog's Avatar
    Join Date
    Aug 2006
    Location
    Ankh-Morpork
    Posts
    13,949
    Quote Originally Posted by Derokorian View Post
    Also, in queries dates aren't numbers and need quotes.
    Unless its actually an integer field storing a UNIX timestamp (though I really don't like doing that, myself, for whatever that's worth )?
    Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be." ~ from Nation, by Terry Pratchett

    "But the main reason that any programmer learning any new language thinks the new language is SO much better than the old one is because he’s a better programmer now!" ~ http://www.oreillynet.com/ruby/blog/...ck_to_p_1.html


    eBookworm.us

  13. #13
    Senior Member
    Join Date
    Sep 2012
    Posts
    269
    Y'all are majoring in minors and still haven't answered my question.

    Either way I word the query, it doesn't work. If i use

    PHP Code:
        $query "SELECT * FROM TOAWorkOrders WHERE TechNum = $TechNum AND `WorkDate` BETWEEN '$StartDate' AND '$EndDate'";
        if (
    $result $mysqli->query($query)) 
            {
                while (
    $row $result->fetch_assoc()) 
                    {
                           
    $BBT $row['BBT'];
                           echo 
    $BBT ' <br>';
                    }
                        
            } 

    Or

    PHP Code:
        for ($Count 0$Count <= 6$Count++)
        {
            
    $date date_create($StartDate);
            
    date_add ($datedate_interval_create_from_date_string($Count 'days'));
        
    $IncDate date_format($date'Y-m-d');

    echo 
    $Count ' !! ' $IncDate '<br>';

            
    $query "SELECT * FROM TOAWorkOrders WHERE TechNum = $TechNum AND `WorkDate` = '$IncDate'";
        
            if (
    $result $mysqli->query($query)) 
                {
                     while (
    $row $result->fetch_assoc()) 
                         {
                               
    $BBT $row['BBT'];
                               echo 
    $BBT '<br>';
                         }
        }
         } 
    I'm still not getting anything.

  14. #14
    Pedantic Curmudgeon Weedpacket's Avatar
    Join Date
    Aug 2002
    Location
    General Systems Vehicle "Thrilled To Be Here"
    Posts
    21,889
    Quote Originally Posted by timstring
    I'm still not getting anything.
    You said earlier that your original code "dies after the first iteration". If you're not getting anything then it would seem that it's dying during the first iteration (of either loop).
    Does "not getting anything" include not getting anything from the echo $Count . ' !! ' . $IncDate . '<br>'; line?

    Have you verified that the query itself is correct, and not simply failing (i.e., perhaps $result is false - what then? The value of $mysqli->error would give MySQL's reason for failing such as, for example, unquoted non-numeric values.)
    THERE IS AS YET INSUFFICIENT DATA FOR A MEANINGFUL ANSWER
    FAQs! FAQs! FAQs! Most forums have them!
    Search - Debugging 101 - Collected Solutions - General Guidelines - Getting help at all

  15. #15
    Senior Member
    Join Date
    Sep 2012
    Posts
    269
    When I run this:

    PHP Code:
    for ($Count 0$Count <= 6$Count++)
          {
                
    $date date_create($StartDate);
                
    date_add ($datedate_interval_create_from_date_string($Count 'days'));
                
    $IncDate date_format($date'Y-m-d');
    echo 
    $Count ' !! ' $IncDate '<br>';
           } 
    I get:
    Code:
    0 yy 25842 qq Timothy qq Lear qq 5804307158 
    0 !! 2013-01-29
    1 !! 2013-01-30
    2 !! 2013-01-31
    3 !! 2013-02-01
    4 !! 2013-02-02
    5 !! 2013-02-03
    6 !! 2013-02-04
    0 yy 82233 qq Darron qq Kolb qq 5804307159 
    0 !! 2013-01-29
    1 !! 2013-01-30
    2 !! 2013-01-31
    3 !! 2013-02-01
    4 !! 2013-02-02
    5 !! 2013-02-03
    6 !! 2013-02-04
    old begins here
    When I add a query and run this code:
    PHP Code:
    for ($Count 0$Count <= 6$Count++)
          {
                
    $date date_create($StartDate);
                
    date_add ($datedate_interval_create_from_date_string($Count 'days'));
                
    $IncDate date_format($date'Y-m-d');
    echo 
    $Count ' !! ' $IncDate '<br>';
                                
            
    $query "SELECT * FROM TOAWorkorders WHERE TechNum = $TechNum  AND WorkDate = '$IncDate'";
                if (
    $result $mysqli->query($query)) 
                      {
                            
    printf("Select returned %d rows.\n"$result->num_rows);

                           
    $result->close();
                       }
        } 
    I get:
    Code:
    0 yy 25842 qq Timothy qq Lear qq 5804307158 
    0 !! 2013-01-29  Select returned 5 rows. 
    1 !! 2013-01-30 Select returned 5 rows. 
    2 !! 2013-01-31 Select returned 4 rows. 
    3 !! 2013-02-01 Select returned 5 rows. 
    4 !! 2013-02-02 Select returned 0 rows. 
    5 !! 2013-02-03 Select returned 0 rows. 
    6 !! 2013-02-04 Select returned 5 rows. 
    
    old begins here
    The query returns the expected result, but the loop dies at the query in the first iteration. Where did the numbers for Darron Kolb go?

Thread Information

Users Browsing this Thread

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

Posting Permissions

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