Jump to content


Photo

query error


  • Please log in to reply
3 replies to this topic

#1 puja

puja
  • Members
  • PipPipPip
  • Advanced Member
  • 39 posts

Posted 05 May 2006 - 06:37 PM

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)


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


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?

#2 corillo181

corillo181
  • Members
  • PipPipPip
  • Advanced Member
  • 896 posts

Posted 05 May 2006 - 06:42 PM

well we don't know where line 3 is.. but the first thin you should do is look a tline 3 and see if anything is out of place..

#3 onepixel

onepixel
  • Members
  • PipPip
  • Member
  • 18 posts

Posted 05 May 2006 - 06:54 PM

$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.


<a href="http://www.saheltech.com">PHP Programming - WordPress Blog Customization</a>

#4 puja

puja
  • Members
  • PipPipPip
  • Advanced Member
  • 39 posts

Posted 06 May 2006 - 03:33 PM

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.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users