Date Comparison before update

[disinwebe]

hi, im a newbie here. im seeking help for my problem. i am also new in php but have some knowledge in sql here's my problem. my boss wants to enable update the baking chamber table once the Estimated End Time reached only. meaning if the end time doesn't meet there will be a pop up on the page that operator cannot finished unless the estimated end time is reached. Please kindly help check my code and see screenshot attached for your reference. Thanks in advanced guys.

	if($_POST['submit']=="Bake Finished")
	{
		$time = strftime('%I:%M %p');
		$hours = strftime('%H');
		$min = strftime('%M');	
		
			$query=mysql_query("SELECT * FROM tbl_baking_chamber");
			while($result=mysql_fetch_array($query))
			{
				$recno=$result['RecNo'];	
				if(isset($_POST[$recno])!=NULL)
				{				
					mysql_query("UPDATE tbl_baking_chamber set tbl_baking_chamber.TotalTime='$TotalTime' where RecNo='".$recno."'");
					mysql_query("UPDATE tbl_baking_chamber set tbl_baking_chamber.EndTime='$CurrentDate - $time' where RecNo='".$recno."'");		
					mysql_query("UPDATE tbl_baking_chamber set tbl_baking_chamber.Status='Finished' where RecNo='".$recno."'");									
							
				}
		}
	}
	
	if($_POST['submit']=="On Baking" || $_POST['submit']=="Bake Finished")
	{
		echo "<table class='tbl_Forbaking' cellspacing='1' border='0' width='680'>";
		echo "<form action='Applications.php?type=BGABaking' method='post'>";
		echo "<tr>";
		//echo "<td class='text2' align='center' bgcolor='e3e6e5' height='40'>"."MODEL"."</td>";
		echo "<td class='text2' align='center' bgcolor='e3e6e5'>"."SERIAL NUMBER"."</td>";
		echo "<td class='text2' align='center' bgcolor='e3e6e5'>"."DEFINED <br> BAKE TIME"."</td>";
		echo "<td class='text2' align='center' bgcolor='e3e6e5'>"."BATCH"."</td>";
		echo "<td class='text2' align='center' bgcolor='e3e6e5'>"."LOCATION"."</td>";
		echo "<td class='text2' align='center' bgcolor='e3e6e5'>"."START TIME"."</td>";
		echo "<td class='text2' align='center' bgcolor='e3e6e5'>"."ESTIMATED <br> END TIME"."</td>";
		
		echo "<td class='text2' align='center' bgcolor='e3e6e5'>"."<input type='submit' name='submit' value='Bake Finished' class='btn_Bake'>"."</td>";
		
		$Query = mysql_query("SELECT * FROM tbl_baking_chamber where Status='On Baking'");
		
		$count = mysql_num_rows($Query);
		$bgcolor="e3e6e5";					
		while($result = mysql_fetch_array($Query))
		{
			if($bgcolor=='e3e6e5')
			{
				$bgcolor='e9ebea';
			}
			else
			{
				$bgcolor='e3e6e5';
			}
			
			echo "<tr>";
			//echo "<td class='text1' align='center' bgcolor='$bgcolor'>".$result['Model']."</td>";
			echo "<td class='text1' align='center' bgcolor='$bgcolor'>".$result['Serial']."</td>";
			echo "<td class='text1' align='center' bgcolor='$bgcolor'>".$result['BakingHours']."</td>";
			echo "<td class='text1' align='center' bgcolor='$bgcolor'>".$result['Batch']."</td>";
			echo "<td class='text1' align='center' bgcolor='$bgcolor'>".$result['Chamber']."</td>";
			echo "<td class='text1' align='center' bgcolor='$bgcolor'>".$result['StartTime']."</td>";
			echo "<td class='text1' align='center' bgcolor='$bgcolor'>".$result['SEndTime']."</td>";
			echo "<td class='text1' align='center' bgcolor='$bgcolor'>"."<input type='checkbox' value='$result[RecNo]' id='$result[RecNo]' name='$result[RecNo]'>"."</td>";
		}
		echo "</form>";
		echo "<tr>";
		echo "<td>"."<br/>";
		echo "<tr>";
		echo "<td class='text4'>"."Total QTY.: $count";
		echo "</table>";
	}

Edited May 24, 2013 by disinwebe

[disinwebe]

[Barand]

You don't need 3 update queries, 1 will do. Syntax is

 

UPDATE tablename

SET

     a = x,

     b = y,

     c = z

WHERE whatever = something

[disinwebe]

You don't need 3 update queries, 1 will do. Syntax is

 

UPDATE tablename

SET

     a = x,

     b = y,

     c = z

WHERE whatever = something

 

 

 

my bad

thanks..

 

but my problem i think related to time difference right?

can you help me on this?

Edited May 24, 2013 by disinwebe

[jazzman1]

Wow......very bad code buddy.

 

If this is your first time using SQL in PHP you might want to consider some of the date/time MySQL functions which make this easier for you.

 

Also, never ever run queries in loops.

Edited May 24, 2013 by jazzman1

[disinwebe]

Wow......very bad code buddy.

 

If this is your first time using SQL in PHP you might want to consider some of the date/time MySQL functions which make this easier for you.

 

Also, never ever run queries in loops.

 

yes it's my first time and im a newbie here. sorry.. but im willing to learn more..

Edited May 24, 2013 by disinwebe

[jazzman1]

Well, re-design the columns which contain the date/time information.

https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html

[Q695]

Also, never ever run queries in loops.

There are RARE exceptions that you want to run a query in a loop, i.e. if one query needs to pull another to display data.

[DavidAM]

 

if($_POST['submit']=="Bake Finished")
{
	$time = strftime('%I:%M %p');
	$hours = strftime('%H');
	$min = strftime('%M');	

		$query=mysql_query("SELECT * FROM tbl_baking_chamber");
		while($result=mysql_fetch_array($query))
		{
			$recno=$result['RecNo'];	
			if(isset($_POST[$recno])!=NULL)
			{				
				mysql_query("UPDATE tbl_baking_chamber set tbl_baking_chamber.TotalTime='$TotalTime' where RecNo='".$recno."'");
				mysql_query("UPDATE tbl_baking_chamber set tbl_baking_chamber.EndTime='$CurrentDate - $time' where RecNo='".$recno."'");		
				mysql_query("UPDATE tbl_baking_chamber set tbl_baking_chamber.Status='Finished' where RecNo='".$recno."'");									
					
			}
	}
}

 

There is no need to select and loop through all the data just to update one row. Plus, that code is updating each row with the same data, which, I'm guessing, you only want on the one row that finished. Along with Barand's suggestion, that code should be something like:

if($_POST['submit']=="Bake Finished")
{
	$time = strftime('%I:%M %p');
	$hours = strftime('%H');
	$min = strftime('%M');	

	if(isset($_POST[$recno])!=NULL)
	{				
		$recno=$result['RecNo'];	
		mysql_query("UPDATE tbl_baking_chamber set TotalTime='$TotalTime', EndTime='$CurrentDate - $time', Status='Finished' where RecNo='".$recno."'");
	}
}

Of course there are still issues:

1) I don't see $TotalTime defined in the code you posted;

2) I don't see $CurrentDate defined in that code either;

3) I don't think $CurrentDate - $time is going to produce what you want;

4) In a normalized database, you would store StartTime and EndTime and calculate TotalTime when you SELECT the data.

5) I don't recommend building the query and executing it in a single line of code. It really should be:

$sql = 'SELECT ...';
$result = mysql_query($sql);
if (! $result) echo 'ERROR: $sql - ' . mysql_error();

6) The mysql extension is deprecated, you should use mysqli for new development.

 

@Q695

There are RARE exceptions that you want to run a query in a loop, i.e. if one query needs to pull another to display data.

You would normally use a JOIN for that. The exception to the rule is extremely rare.

[Barand]

If you want to compare dates and times then store them in a format that can be compared ie yyyy-mm-dd hh:ii:ss