MySQL Where

[fesan]

Hello...

 

I have this MySQL code:

 

SELECT * FROM $dbtable WHERE record_date = '$mysql_record_date' AND getin = '$getin' AND car = '$car'

 

This mysql line is a part of a script that makes a overbooking for a truck/car. So if the script finds any rows matching these 3 varables it makes the outcome red.

 

But, I would like to make the getin a bit more flexible. Because now if you book a truck, it only seams booked for that exact time. but i want to know if the truck is booked for something in 30min before and after the time that is set.

 

Is this possible??

 

The getin field in my database has the type "time".

I use PHP to talk with my database and i use MySQL 5.0.51a

[cags]

Yer you should just be able to apply the same simple logic that you might have used in PHP. In psuedo code it would be...

 

WHERE time_a > time_b - 30 minutes AND time_a < time_b + 30 minutes.

 

I believe in MySQL the syntax would be

 

WHERE time_a > SUBTIME(time_b, '00:30:00') AND time_a < ADDTIME(time_b, '00:30:00')

 

Just subsittute time_a and time_b with the name of a column and/or a $variable as applicable.

[fesan]

I sort of got it to work. So thanks for the help.

Though i get a weird fault.

 

This is the whole code that sets the overbook:

 

$query = "SELECT * FROM $dbtable WHERE record_date = '$mysql_record_date' AND car = '$car' AND getin > SUBTIME('$mysql_getin', '00:30:00') AND getin < ADDTIME('$mysql_getin', '00:30:00') AND getin = '$getin'"; // 
$result = mysql_query($query) or die(mysql_error());
if($result = mysql_query($query)) { // Fikk svar
	if(mysql_num_rows($result) > 0) {
	$car_crash = "1";
} else {
	$car_crash = "0";
	}}

 

This check is done before a row is updated or created. And sets the field car_crash to 1 or 0. The next page check every row for the car_crash value and if it is 1 the outcome is red. But if i now make 2 rows, 1 with the getin time to 15:00 and creates a new one to be 14:45 no red outcome, but if i update it again, the outcome is red. I really cant understand why the code fails the first time but goes trough the next time with no change in the row.

 

Check it out your self:

 

http://www.fesan.cc/utvikling/logistikk/index.php

login with user and pasw - "test" without the quotes.

 

The whole page is in Norwegian but ill guess you understand what to do to fill things out...

[cags]

Took a quick look, but it's a bit difficult to wrap your head around when it's a foreign language. It's difficult to say what your problem might be based on the information I currently have but there are a few things that could cause different behavior to what you might expect.

 

If the time matches (ie is within the same minute) it won't appear, you'd need to change to >= and <= to match times that match to the minute.

 

The other guess would be the order your page processes information. If you select the rows that are displayed before you update the table, then it would take reloading the page to make the changes take effect.

[fesan]

Ok... Thanks for taking a look anyway.

 

I cant wrap my head around this error. So ill post some more code and hope for a easy fault that i have overseen!

 

This is the code where i check all the rows in DB for a row with the same values as the new ones.

$mysql_getin = strftime("%H:%M:%S", strtotime($getin));

$query = "SELECT * FROM $dbtable WHERE record_date = '$mysql_record_date' AND car = '$car' AND getin > SUBTIME('$mysql_getin', '00:30:00') AND getin < ADDTIME('$mysql_getin', '00:30:00')"; // 
$result = mysql_query($query) or die(mysql_error());
if($result = mysql_query($query)) { 
	if(mysql_num_rows($result) > 0) {
	$car_crash = "1";
} else {
	$car_crash = "0";
	}}

 

This part inserts/updates the new values in to DB. As you see, the last field is the overbook field:

$sql = "UPDATE ${dbtable} SET 
record_date = '$record_date', 
job_nr = '$job_nr',
coustumer = '$coustumer',
production = '$production',
getin = '$getin',
car = '$car',
driver = '$driver',
funktion = '$funktion',
comment = '$comment',
maker = '$maker',
car_crash = '$car_crash'
WHERE id = '${id}'";

 

This code is the page witch prints out the DB:

$query = "SELECT * FROM $dbtable WHERE record_date = '$today' ORDER BY getin, car ASC"; //ORDER BY record_date, getin ASC
$result = mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_array($result)){
		if($row['car_crash'] == 1){
			echo	"<tr bgcolor=#F00 id='lamp_list'> \n";
			$class = "form_red";
		} else {
//the rest of output code....

 

I have now tried to add = to every < or > and switching them over to only get total overbook, no overbook and that i have to update 2 times to actually get the overbook.

 

Thanks ALLOT!!!

[fenway]

Sorry, what is it that you're trying to achieve?