Jump to content

query error


puja

Recommended Posts

hi im trying to do a query but i am getting the following error

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'then = 1 else = 0' at line 3

line 3 is just somewhere in query2
does anyone recognise wot is wrong with the syntax?

wot i need the code to do is
make sure that no-one can double book
check if the start date is before the end date (this works fine)
check if the dates are available according to the accommID
if the dates are available then put the details into the booking table (the insert query which also works)

[code]

if(isset($_POST['submit'])){
require_once("config.php");
$connection = @mysql_connect($db_host, $db_user, $db_password) or die("oops! error connecting");
mysql_select_db($db_name, $connection);


         $accommID = $_POST ["accommID"];
         $book_start = $_POST ["booking_start_date"];
         $book_end = $_POST ["booking_end_date"];
         $flag = $_POST ["flag"];
        

        $date1 = $book_start; //booking start date
        $date2 = $book_end; //booking end date

        $timestamp1 = strtotime($date1);
        $timestamp2 = strtotime($date2);

                //if the start date is after the end date
                if($timestamp1 > $timestamp2){
                  echo "Please enter a Booking Start Date that is before the Booking End Date";
                        }

                        //if the start date is before the end date
                        elseif ($timestamp1 < $timestamp2){
                        
                        //check if the dates given are both before or both after the dates that are already in the database then the accomm is available
                        //set the flag to 1 so that no-one else can book for them same dates while this customer is going through the booking stages
                        $query2 = "select $accommID from booking group by $accommID
                        having sum(case when ((booking_start_date < $book_start && booking_end_date < $book_end) || (booking_start_date > $book_start && booking_end_date > $book_end)
                        then $flag = 1 else $flag = 0";
                        $result2 = mysql_query ($query2) or die(mysql_error());

                        $statment = "select $accommID from booking group by $accommID
                        having sum(case when ((booking_start_date < $book_start && booking_end_date < $book_end) || (booking_start_date > $book_start && booking_end_date > $book_end)
                        then $flag = 1 else $flag = 0";
                        print $statment;
                        $query3 = mysql_query($statment) or die(mysql_error());

                        echo "The dates are available";

                        }//closes elseif

            elseif (!$result2){
                echo "The dates are not available";
            }

            $query = "INSERT INTO booking (booking_start_date, booking_end_date, accommID, flag) VALUES ('$book_start','$book_end','$accommID','$flag')";
            $result = mysql_query($query) or die (mysql_error());
             if(!$result){
             echo "Booking not added
             </td>
            </table>";
              }


             echo "</td>
            </table>";

}

else {
$accommid = $_POST['accommID'];

?>
    <form action ="<?php echo $_SERVER['PHP_SELF']; ?>" method="POST">
    <input type=hidden name=accommID value="<?=$accommid?>">
    <p align="center"> </p>
    <p align="center"><u>Booking Start Date (YYYY/MM/DD):</u>    <input type="text" name="booking_start_date" size="20"></p>
    <p align="center"><u>Booking End Date (YYYY/MM/DD) :</u>    <input type="text" name="booking_end_date" size="20"></p>
    <p> </p>
    <p><input type="submit" name=submit value="Submit"></p>
  </form>
</td>
</table>
<?

} //closes else above

[/code]

that query with the error obvioisly doesnt work and ive tried to echo out and print the variables aswell to see if that is the problem but it wont let me do that either because i cant get rid of the error at the top

hope some1 knows wots wrong with it and also is the way i am trying to find out if the dates are available being approached in the right way? or am i just way off track?
Link to comment
Share on other sites

$query2 = "select $accommID from booking group by $accommID
having sum(case when ((booking_start_date < $book_start && booking_end_date < $book_end) || (booking_start_date > $book_start && booking_end_date > $book_end)
then $flag = 1 else $flag = 0";

Parenthesis mismatch!!
I notice that there is missing closing parenthesis for the one opening after "sum" and also the one opening after "when"
Make sure the $query is well formatted first, test the script then if there still some problem, look at the logic behind the query.

Link to comment
Share on other sites

thanks but i dont think that query is going to work for what i am trying to do so i need to create a new one.

the user would have already selected the accomm they want so then i need to check if the dates they have given are available for the accommodation they have chosen.
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.