We help IT Professionals succeed at work.

PHP Loop with testing against value of a query

I am new to php.  I am trying to run a loop 5 times.  Each time the loop runs I need to run a batch file to refresh a table and test the value of a query.  If the query is > 0 (meaning there are records in the table) exit the loop. Otherwise run a different batch file and continue.  

Start Loop
Run an exec(batchfile)
SELECT     COUNT(item_code) AS ItemCount
FROM         items
ItemCount > 0
       yes - exit loop and continue with code
      No
      Repeat up to 5 times
If after 5 times, exit loop and run exec(RunRefresh2.bat)
Continue wtih code

A friend helped with some code below but I don't think it is doing exactly what I am trying to do

I don't know which type of loop to run the necessary syntax.  Can anyone help?
exec('c:\Inetpub\wwwroot\RunRefresh.bat');
$i=0;
while($i<5)
{
	$sql="SELECT COUNT(item_code) AS ItemCount FROM items";
	$stmt = sqlsrv_query( $conn, $sql);
	$rows=sqlsrv_fetch_object($stmt);
	if($rows->ItemCount<=0)
	{
	exec('c:\Inetpub\wwwroot\RunRefresh2.bat');
		sleep(20);
	}
	else
	{
		break;
	}
	$i++;	
}

Open in new window

Comment
Watch Question

Marco GasiFreelancer
CERTIFIED EXPERT
Top Expert 2010

Commented:
Try this:
$i=0;
while($i<5)
{
        exec('c:\Inetpub\wwwroot\RunRefresh.bat'); //refresh table
	$sql="SELECT COUNT(item_code) AS ItemCount FROM items";//run query
	$stmt = sqlsrv_query( $conn, $sql);
	$rows=sqlsrv_fetch_object($stmt);
	if($rows->ItemCount>0)//if table returns  a non empty result
	{
           exit;
	}
	$i++;	
}

Open in new window

Author

Commented:
Makes sense, but if after 5 loops, ($rows->ItemCount>0) is never >0, I need to run a different batch file and continue.

If I put exec('c:\Inetpub\wwwroot\RunRefresh2.bat');
after the code, won't this batch file run every time?

If >0 I would need to jump over the exec('c:\Inetpub\wwwroot\RunRefresh2.bat');
and continue with the code.
Freelancer
CERTIFIED EXPERT
Top Expert 2010
Commented:
Maybe this is right (Exit was wrong: use break as your friend did):
$RunRefresh2 = false;
$i=0;
while($i<5)
{
        exec('c:\Inetpub\wwwroot\RunRefresh.bat'); //refresh table
	$sql="SELECT COUNT(item_code) AS ItemCount FROM items";//run query
	$stmt = sqlsrv_query( $conn, $sql);
	$rows=sqlsrv_fetch_object($stmt);
	if($rows->ItemCount>0)//if table returns  a non empty result
	{
           $RunRefresh2 = true;
           break;    
	}
	$i++;	
}

if ($RubRefresh2){
  exec('c:\Inetpub\wwwroot\RunRefresh2.bat');
}

Open in new window


Cheers

Author

Commented:
Works. Thank you

Line 17 if ($RubRefresh2){

should be

if ($RunRefresh2){

$RunRefresh2 = false;
$i=0;
while($i<5)
{
        exec('c:\Inetpub\wwwroot\RunRefresh.bat'); //refresh table
	$sql="SELECT COUNT(item_code) AS ItemCount FROM items";//run query
	$stmt = sqlsrv_query( $conn, $sql);
	$rows=sqlsrv_fetch_object($stmt);
	if($rows->ItemCount>0)//if table returns  a non empty result
	{
           $RunRefresh2 = true;
           break;    
	}
	$i++;	
}

if ($RubRefresh2){
  exec('c:\Inetpub\wwwroot\RunRefresh2.bat');
}

Open in new window

Author

Commented:
Thank you for the assist
Marco GasiFreelancer
CERTIFIED EXPERT
Top Expert 2010

Commented:
Sorry for the typo :-) Good luck with your project.

Author

Commented:
Here is the final code I used on my project.
$a=0;
exec('c:\Inetpub\wwwroot\RunRefresh.bat') ; //refresh table	
sleep(2);
$sql="SELECT COUNT(item_code) AS ItemCount FROM items";//run query
$stmt = sqlsrv_query( $conn, $sql); //query
$rows=sqlsrv_fetch_object($stmt); //get row
while($a<20 && $rows->ItemCount==0)
{
	exec('c:\Inetpub\wwwroot\RunRefresh.bat') ; //refresh table	
	sleep(2);
	$sql="SELECT COUNT(item_code) AS ItemCount FROM items";//run query
	$stmt = sqlsrv_query( $conn, $sql); //query
	$rows=sqlsrv_fetch_object($stmt); //get row
	$a++;
}
/*$sql="SELECT COUNT(item_code) AS ItemCount FROM items";//run query
$stmt = sqlsrv_query( $conn, $sql); //query
$rows=sqlsrv_fetch_object($stmt); //get row*/
if($rows->ItemCount==0 && $time<20)//if table returns an empty result	 and t< 20 refresh process, then...
{
	_exec('c:\Inetpub\wwwroot\RunRefresh2.bat'); //execute
	sleep(20);     //sleep
	if(empty($time)) //if it's the first time, add $time=1;
	{
		$time=1;
	}
	else
	{
		$time++; //otherwise plus
	}
	header('Location:http://MyServer/RunRefresh.php?t='.$time); ///refresh with t variable
}
else
{
	echo '<script>alert("All items have been refreshed")</script>';
}

Open in new window