Double booking prevention

[Kenny_Luck]

$sql="SELECT COUNT(*) as conflicts
FROM rental
WHERE plateNum = $plateNum AND Dispatch > $Dropoff AND Dropoff > $Dispatch";
if("0"){
  $sql="INSERT INTO rental (Rentdate,Dispatch,Dropoff,Dis_TIME,Drop_TIME,Ins,Tcost,PDcost,plateNum,userID)VALUES(CURDATE(),'$Dispatch','$Dropoff','$Dis_TIME','$Drop_TIME','$Ins','$Tcost','$PDcost','$plateNum','$userID')";  
    $result=$conn->query($sql);
}else{ echo"Fail";}
 

 

how do I get the value of the conflicts im now very clueless

[requinix]

You have a query there already. It returns a count. If you don't want a count and you want data instead then change the query to suit...

[Kenny_Luck]

i dont quiet understand what you are trying to say

 

[maxxd]

15 minutes ago, requinix said:

You have a query there already. It returns a count. If you don't want a count and you want data instead then change the query to suit...

The query is never actually run, though, so it technically doesn't return anything. It is an excellent example of SQL injection, though - or it would be if it was actually executed. Also, if("0") will - unless I'm mistaken - never be false.

OP - is this for a class or did you get a project thrust upon you?

[Kenny_Luck]

Im just learning the double prevention which im going to do for my assignment 

[maxxd]

15 minutes ago, Kenny_Luck said:

Im just learning the double prevention which im going to do for my assignment 

OK. First thing I would recommend is to read the documentation.

You don't mention if you're using PDO, mysqli, or another DB abstraction, but each has a different usage so it's important you read the documentation for the API you're using. Right now, you define a select query but don't actually run the query. You also have a conditional statement on what is effectively a non-existent comparison, so the logic is flawed.

There are a lot of good resources out there, but they're sometimes buried under really bad resources. Ask here - someone will tell which you have. For instance, if you've quoted the full extent of your code here, I'd venture a guess that whatever source you're using to learn is not terribly good.

Edited September 19, 2019 by maxxd

[Kenny_Luck]

I using mysqli , the code that i posted is the one i write , so you are basically saying that my query for 

2 hours ago, Kenny_Luck said:

$sql="SELECT COUNT(*) as conflicts
FROM rental
WHERE plateNum = $plateNum AND Dispatch > $Dropoff AND Dropoff > $Dispatch";

is not run right

[maxxd]

I'm not saying it's not run right, I'm saying it's not run at all.

You assign the SQL string to the variable $sql, then check if "0". If "0", you overwrite the value of $sql with a completely different string, which you then execute. If not "0" (will never happen), you print the word "Fail".

Read out loud to yourself or someone else (preferably someone patient and understanding) each step of your code. If it helps, write it out in what's called 'psuedo-code' - prose versions of what the code is doing at each step of the process.

[Kenny_Luck]

10 minutes ago, Kenny_Luck said:

$sql="SELECT COUNT(*) as conflicts
FROM rental
WHERE plateNum = $plateNum AND Dispatch > $Dropoff AND Dropoff > $Dispatch";

So this mean that this code is completely useless and i cant get the Conflicts value at all 

 

[maxxd]

No. You can get the conflicts value very easily by actually executing the query you've written.

[Kenny_Luck]

Im so confuse right now

[Kenny_Luck]

if i change 

3 hours ago, Kenny_Luck said:

if("0"){
  $sql="INSERT INTO rental (Rentdate,Dispatch,Dropoff,Dis_TIME,Drop_TIME,Ins,Tcost,PDcost,plateNum,userID)VALUES(CURDATE(),'$Dispatch','$Dropoff','$Dis_TIME','$Drop_TIME','$Ins','$Tcost','$PDcost','$plateNum','$userID')";

if($sql == 0)

is still the same the value wont come out

[chhorn]

$sql is just the string, within the code you are not even running the SQL statement with query().

[Barand]

Apart from your PHP code not actually executing the first query, the query itself is wrong and will not find all conflicts. Consider...

Bookings 2 - 5 are conflicting with with your required booking dates of $dispatch (A) to $dropoff (B).

A booking conflicts if starts before $dropoff and ends after $dispatch. ( S < B AND E > A )

Your query will only find bookings like resources 4 and 6 above (ie S > A AND E > B )

Try

$sql="SELECT COUNT(*) as conflicts
        FROM rental
        WHERE plateNum = :plateNum 
        AND Dispatch < :dropoff AND Dropoff > :dispatch";
$stmt = $pdo->prepare($sql);
$stmt->execute( [  'plateNum' => $plateNum,
                   'dropoff'  => $Dropoff,
                   'dispatch' => $Dispatch
                ]);
$conflicts = $stmt->fetchColumn();
if ($conflicts == 0) {
    
    // go ahead
}

EDIT: NOTE: Depending on your booking policy on what time of day bookings start and finish, the above "<" and ">"  comparisons may need to be "<=" and ">=".

Edited September 19, 2019 by Barand

[maxxd]

5 hours ago, Kenny_Luck said:

Im so confuse right now

Try reading the manual. Barand went  along way toward giving you what the rest of us have been saying - you need to run the query.

[Psycho]

12 hours ago, maxxd said:

Also, if("0") will - unless I'm mistaken - never be false.

FYI: if("0") will always return FALSE. Although it is a unique case due to PHP being a "loosely" typed language. The integer 0 is FALSE in most/all languages and a string value is normally TRUE. In this case it is a string value with the zero character. Because PHP doesn't have strong distinction between variable types it interprets the string "0" as the integer 0 and is therefore FALSE

More info here: https://www.php.net/manual/en/language.types.boolean.php

Quote

When converting to boolean, the following values are considered FALSE:

• the boolean FALSE itself
• the integers 0 and -0 (zero)
• the floats 0.0 and -0.0 (zero)
• the empty string, and the string "0"
• an array with zero elements
• the special type NULL (including unset variables)
• SimpleXML objects created from empty tags

Edited September 19, 2019 by Psycho

[maxxd]

On 9/19/2019 at 11:21 AM, Psycho said:

Because PHP doesn't have strong distinction between variable types it interprets the string "0" as the integer 0 and is therefore FALSE

You are absolutely correct - thank you for the correction!

[Kenny_Luck]

I now had mostly figure out and able to run it and use it , thank you for all your help 
specially Barand thanks