Query kills Loop

[timstring]

This works:

	for ($Count = 0; $Count <= 6; $Count++)
         {
	     $date = date_create($StartDate);
             date_add ($date, date_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:

	for ($Count = 0; $Count <= 6; $Count++)
	    {
		$date = date_create($StartDate);
                date_add ($date, date_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?

[NogDog]

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.

[timstring]

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.

[bradgrafelman]

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?

[timstring]

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

[bradgrafelman]

timstring;11024521 wrote:

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?

[timstring]

You passed me in the night. see above.

[bradgrafelman]

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.

[timstring]

I do believe that's what I am doing.

Anyhoo

$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

[bradgrafelman]

timstring;11024533 wrote:

I do believe that's what I am doing.

It isn't in any of the code you've posted thus far.

[Derokorian]

Also, in queries dates aren't numbers and need quotes.

[NogDog]

Derokorian;11024539 wrote:

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 😉 )?

[timstring]

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

	$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

	for ($Count = 0; $Count <= 6; $Count++)
    {
        $date = date_create($StartDate);
        date_add ($date, date_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.

[Weedpacket]

timstring wrote:

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 [font=monospace]echo $Count . ' !! ' . $IncDate . '<br>'; [/font] line?

Have you verified that the query itself is correct, and not simply failing (i.e., perhaps [font=monospace]$result[/font] is false - what then? The value of [font=monospace]$mysqli->error[/font] would give MySQL's reason for failing such as, for example, unquoted non-numeric values.)

[timstring]

When I run this:

for ($Count = 0; $Count <= 6; $Count++)
      {
            $date = date_create($StartDate);
            date_add ($date, date_interval_create_from_date_string($Count . 'days'));
            $IncDate = date_format($date, 'Y-m-d');
echo $Count . ' !! ' . $IncDate . '<br>';
       }

I get:

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:

for ($Count = 0; $Count <= 6; $Count++)
      {
            $date = date_create($StartDate);
            date_add ($date, date_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:

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?

[NogDog]

        $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();
        }
        else  // find out what happened
        {
            die("<pre>Query failed: ".$mysqli->error.PHP_EOL.$query."</pre>");
        }

[timstring]

The script runs without errors. So, something must be happening after the query, but I'll be switched if i can find what's happening. There's nothing but braces after the loop.

[Weedpacket]

But this code lies inside another loop which you haven't shown us. There might be something inside that loop (before the code you've shown) that is breaking after its first iteration (in fact it's probably this unmentioned loop to which "1st iteration" has alluded to all along).

[timstring]

Since I couldn't figure out my problem, I basically started over. Here's the code that is broken in the same spot:

if ($SystemNumEntry == 0) $query = "SELECT * FROM Systems";
else $query = "SELECT * FROM Systems WHERE SystemNum = '$SystemNumEntry'";

if ($result = $mysqli->query($query)) 
	{
	 while ($row = $result->fetch_assoc()) 
			{
			$SystemNum = $row["SystemNum"];
			$System = $row["System"];
			$LongSystem = $row["LongSystem"];

			echo $SystemNum . ' !! ' . $System . ' $$ ' . $LongSystem . '<br>';

			if ($TechNumEntry == 0) $query2 = "SELECT * FROM Techs WHERE SystemNum = '$SystemNum'";
			else $query2 = "SELECT * FROM Techs WHERE SystemNum = '$SystemNum' AND TechNum = $TechNumEntry";

			if ($result = $mysqli->query($query2)) 
				{
				 while ($row = $result->fetch_assoc()) 
					{
					echo $row["FirstName"] . ' ' . $row["LastName"] . '<br>';

					if ($result = $mysqli->query("SELECT * FROM TOAWorkOrders WHERE TechNum = '$TechNum'")) 
						{
						printf("Select returned %d rows.\n", $result->num_rows);
						$result->close();
						}

						else  // find out what happened 
							{ 
							die("<pre>Query 3 failed: ".$mysqli->error.PHP_EOL.$query."</pre>"); 
							}
					}
				}

			else  // find out what happened 
				{ 
				die("<pre>Query 2 failed: ".$mysqli->error.PHP_EOL.$query."</pre>"); 
				}
			}



	}
else  // find out what happened 
	{ 
	die("<pre>Query 1 failed: ".$mysqli->error.PHP_EOL.$query."</pre>"); 
	} 

If '$SystemNumEntry' is 0, then the outer loop should cycle through 66 records. But, the while loop dies after hitting the 2nd query.. If '$TechNum' = 0, && '$SystemNum' = 0, the loop should cycle through 615 records, but the 2nd loop dies when it hits the next query. The traps don't report any errors. To make a short story long, the script gets to the end of the next query and doesn't cycle. I gots to say that this is really weird.

[timstring]

I fixed it. i added a '2' in second query so that it reads:

	if ($TechNumEntry == 0) $query2 = "SELECT * FROM Techs WHERE SystemNum = '$SystemNum'";
           else $query2 = "SELECT * FROM Techs WHERE SystemNum = '$SystemNum' AND TechNum = '$TechNumEntry'";
           if ($result2 = $mysqli->query($query2)) 
                {

and then:

if ($result3 = $mysqli->query("SELECT * FROM TOAWorkOrders WHERE TechNum = '$TechNum' AND WorkDate BETWEEN '$StartDate' AND '$EndDate'")) 
      {
       printf("Select returned %d rows.\n", $result3->num_rows);
       $result3->close();
       }

Who would have thought?