Jump to content

Parsing the date in sql query


amey1309

Recommended Posts

Hello,

I am doing a hotel reservation website.First am getting the checkin (arrival) and Check out(Departure) date from user.Then in next from i fetch both this values using POST method and store it in variable $arrival and $departure and then am formattind the date as i want it in the form YYYY-MM-DD(My SQL).

Then am using the value of variable $arr and $dep in a query to fetch the records from DB but it is giving error.But when i do it in Hard code way I mean directly inserting the date in query it is running smmothly.Please help!!!! Am using Xampp 2.5

Heres my code...

 

 

 

<html xmlns="http://www.w3.org/1999/xhtml">

<head>

<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />

<title>ROOMS</title>

 

<?php

 

$arrival = $_POST['start'];

$departure = $_POST['end'];

$adults=6;

$child=2;

$room_id=101;

 

function changeFormatDate($cdate){

list($day,$month,$year)=explode("/",$cdate);

return $year."-".$month."-".$day;

}

 

// $arr="'".$arr."'";

 

$arr= changeFormatDate($arrival);

//settype($arr, "string");

 

// $timestamp=strtotime($arr1);

// $arr=date("Y-m-d",$timestamp);

 

$dep= changeFormatDate($departure);

 

// $timestamp=strtotime($dep1);

// $dep=date("Y-m-d",$timestamp);

 

?>

 

<style type="text/css">

 

 

<!--

.style2 {

font-size: 12px;

font-weight: bold;

}

-->

</style>

</head>

 

<body>

 

<!-- TOP -->

<div id="top1"><a href="index.php"></a></div>

<div id="top">

 

<ul class="menu">

<li class="home"><a href="index.php">Home</a></li>

<li class="about"><a href="about.php">About</a></li>

<li class="contacts"><a href="contact.php">Contacts</a></li>

<li class="renting"><a href="gallery.php">GALLERY</a></li>

<li class="selling"><a href="rates.php">RATES</a></li>

 

 

</ul>

 

 

</div>

 

 

 

 

<!-- HEADER -->

<!-- CONTENT -->

<div id="content">

 

<div id="leftPan">

 

<div id="services">

<h2>RESERVATION DETAILS </h2>

<p>

  <ul>

      Check In Date :<?php echo $arrival; ?><br />

      Check Out Date :<?php echo $departure; ?>  <br />

</ul>

    </p>

</p>

</div>

 

 

 

 

</div>

<div id="featured"><br />

<div>

<form action="personnalinfo.php" method="post" onsubmit="return validateForm()" name="room">

  <input name="start" type="hidden" value="<?php echo $arrival; ?>" />

  <input name="end" type="hidden" value="<?php echo $departure; ?>" />

  <input name="rooms" id="rooms" type="hidden" />

  <input name="adult" type="hidden" value="<?php echo $adults; ?>" />

  <input name="child" type="hidden" value="<?php echo $child; ?>" />

 

</div>

 

  <table bgcolor="white" border="1"  width="100%" style="float:left;table-layout:fixed" cellpadding="10" cellspacing="0" >

<col width="70%">

  <tr>

  <th colspan="2" bgcolor="white"><h2><font color=maroon>Room Type</font></h2></th>

  </tr>

  <tr>

  <td>

  <table border="0" style="float:left;table-layout:fixed" width="100%">

  <col width="55%">

  <tr>

  <td valign="top">

  <img src="img1/apt.jpg" style="float:left" />

 

</br>

<div style="margin-top:120px;margin-left:5px">

<img src="img1/apt1.jpg" />

  <img src="img1/apt2.jpg" />

  <img src="img1/apt3.jpg" />

  </div>

  </td>

  <td>

  <h3>Appartment</h3>

  <br>

    <span class="price">Price:</span> <span class="number">Rs. 5,000.00</span><br />

<a> Apparment in HOTEL BELLA has 2 Rooms with connecting door.It can accomodate 4 Adult and 2 children.

And are located on Beach side to give you comfort and a panoramic view so that you can have a luxury accommodation.<br>

*Sitting area

<br>*jacuzzi shower</br>

*Large terrace overlooking the sea

*Jacuzzi

*Light therapy

*Air treatment <a href="#">more...</a></p><br />

  </td>

  </tr>

  </table>

 

  </td>

  <td valign="top">

  <table border=0 width="100%" cellspacing="10">

  <tr>

      <td align="left">

        <label><h3>People : </h3></label> 

</td>

<td align="right">

  <img src="img1/i1.jpg" />

</td>

  </tr>

 

  <tr>

  <td align="left" >

        <label><h3>Rooms : </h3></label> 

   

</td>

<td align="right" >

<?php gen_options("single")?>

</td>

  </tr>

  </table>

  </td>

  </tr>

 

 

 

  <tr>

  <td>

  <table border="0" style="float:left;table-layout:fixed" width="100%">

  <col width="55%">

  <tr>

  <td valign="top">

  <img src="img1/double.jpg" style="float:left" />

 

</br>

<div id="featured">

<img src="img1/double1.jpg" />

  <img src="img1/double2.jpg" />

  <img src="img1/double3.jpg" />

  </div>

  </td>

  <td>

  <h3>Double</h3>

  <br>

    <span class="price">Price:</span> <span class="number">Rs. 3,000.00</span><br />

<a>Double rooms in HOTEL BELLA has Double bed. And can accomodate 2 Adults and 2 kids. It is comfortable and pleasant, with balcony and sea view.

We hope that you will enjoy your summer holidays in Bella.

BASIC:

Telephone.

Satellite TV.

Safety Deposit Box.

Mini Bar - Refrigerator.

Air condition.

Shower with or without cabin.

Hair Dryer.

Balcony.<a href="#">more...</a></p><br />

  </td>

  </tr>

  </table>

 

  </td>

  <td valign="top">

  <table border=0 width="100%" cellspacing="10">

  <tr>

      <td align="left">

        <label><h3>People : </h3></label> 

</td>

<td align="right">

  <img src="img1/i2.jpg" />

</td>

  </tr>

  <tr>

  <td align="left">

        <label><h3>Rooms : </h3></label> 

   

</td>

<td align="right" >

<?php gen_options("double")?>

   

</td>

  </tr>

  </table>

  </td>

  </tr>

 

 

 

 

  <tr>

  <td>

  <table border="0" style="float:left;table-layout:fixed" width="100%">

  <col width="55%">

  <tr>

  <td valign="top">

  <img src="img1/single.jpg" style="float:left" />

 

</br>

<div id="featured">

<img src="img1/single1.jpg" />

  <img src="img1/single2.jpg" />

  <img src="img1/single3.jpg" />

  </div>

  </td>

  <td>

  <h3>Single</h3>

  <br>

    <span class="price">Price:</span> <span class="number">Rs. 2,000.00</span><br />

<a> Single Room in HOTEL BELLA has single bed, bathroom.Can accomodated single person. is comfortable and pleasant, with balcony and sea view

Telephone

Satellite TV

Safety Deposit Box

Mini Bar - Refrigerator

Air condition

Hair Dryer

<a href="#">more...</a></p><br />

  </td>

  </tr>

  </table>

 

  </td>

  <td valign="top">

  <table border=0 width="100%" cellspacing="10">

  <tr>

      <td align="left">

        <label><h3>People : </h3></label> 

</td>

<td align="right">

  <img src="img1/i3.jpg" />

 

</td>

  </tr>

  <tr>

  <td align="left">

        <label><h3>Rooms : </h3></label> 

   

</td>

<td align="right" >

<?php gen_options("apartment")?>

   

</td>

  </tr>

  </table>

  </td>

  </tr>

</table>

 

<div  style="margin-top:1200px;margin-left:5px;text-align:right;">

<input type="image" src="img1/book.jpg" name="book" value="submit"/>

</div>

 

 

 

 

 

 

  <?php

 

 

  function gen_options($type)

  {

  // print "$id";

  // print "$type";

$con = mysql_connect("localhost","root","");

if (!$con)

  {

  die('Could not connect: ' . mysql_error());

  }

 

mysql_select_db("hotel", $con);

 

$count=0;

$result = mysql_query("SELECT * FROM rooms where type='$type'");

 

while($row = mysql_fetch_array($result))

  {

  $a=$row['room_no'];

//print "$a";

  // $query1 = mysql_query("SELECT count(*) FROM `room_inventory` WHERE room_no='$a' and status='active'");

// $query=mysql_query("SELECT count(*) FROM `room_inventory` WHERE room_no='$a' and ((arrival<='2012-05-11' AND  departure>='2012-05-11')OR(arrival<='2012-05-13' AND  departure>='2012-05-13'))");

 

  //$query=mysql_query("SELECT count(*) FROM `room_inventory` WHERE room_no='$a'  and ((arrival BETWEEN '2012-05-11' AND '2012-05-13') or (departure BETWEEN '2012-05-11' AND '2012-05-13'))");

 

  //$query=mysql_query("SELECT count(*) FROM `room_inventory` WHERE room_no='$a' and ((arrival<='$arr' AND  departure>='$arr')OR(arrival<='$dep' AND  departure>='$dep'))");

  $quer=sprintf("SELECT count(*) FROM `room_inventory` WHERE room_no='$a' and ((arrival<='%s' AND  departure>='%s')OR(arrival<='%s' AND  departure>='%s'))",$arr,$arr,$dep,$dep);

$query=mysql_query($quer);

 

//$query=mysql_query("SELECT count(*) FROM `room_inventory` WHERE room_no='$a'  and ((arrival BETWEEN '$arr' AND '2012-05-13') or (departure BETWEEN '$arr' AND '2012-05-13'))");

 

$r=mysql_fetch_array($query);

$v=$r['count(*)'];

// print "$v";

 

if($v==0)

{

  $count++;

    // print "$count";

}

 

}

 

  echo '<select name="room1" class="ed" id="r1">';

  for($i=0;$i<=$count;$i++)

{

    echo '<option>'.$i.'</option>';

    } 

  echo '</select>';

 

mysql_close($con);

}

 

 

// echo "$arrival\n";

// print "$departure\n";

echo "$arr";

echo "$dep";

// echo date_format($arrival, 'Y-m-d');

 

?>

 

 

 

 

<input type="hidden" name="result" id="result" />

</form>

 

</div>

 

<div class="clear"></div>

 

</div>

 

<!-- FOOTER -->

 

<div id="footer">

 

 

 

<p><a href="index.php">HOME</a> |<a href="about.php"> ABOUT US </a>|<a href="contact.php"> CONTACTS </a>|<a href="gallery.php"> GALLERY </a>|<a href="rates.php"> ROOM RATES </a></p>

</div>

 

 

 

 

</body>

</html>

Link to comment
https://forums.phpfreaks.com/topic/262248-parsing-the-date-in-sql-query/
Share on other sites

$arr and $dep are displaying the correct value if I print them but the query is not getting executed.

 

$query=mysql_query("SELECT count(*) FROM `room_inventory` WHERE room_no='$a' and ((arrival<='$arr' AND  departure>='$arr')OR(arrival<='$dep' AND  departure>='$dep'))")

 

where as if I try the hard code as below,it is working perfectly fine displaying the correct info as one would get after runnung the same query in My sql.

 

$query=mysql_query("SELECT count(*) FROM `room_inventory` WHERE room_no='$a' and ((arrival<='2012-05-11' AND  departure>='2012-05-11')OR(arrival<='2012-05-13' AND  departure>='2012-05-13'))");

 

Note:Am formatting the date as yyyy-mm-dd from dd/mm/yyyy as My sql demands it.You can get date formatting function in my first post.

You need to form the query statement in a php variable (you have done that in at least one place), echo the resulting query statement, and post it so that someone here could actually see what it is.

 

Edit: Also, what is the actual data type of the columns you are storing the dates in?

cleaned up some redundancy in your code. not sure why the backticks are there i took them out.

 

 

let us know if you have more problems.

 

 


<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>ROOMS</title>

<?php
   
   $arrival = (string) changeFormatDate($_POST['start']);
   $departure = (string) changeFormatDate($_POST['end']);
    $adults=6;
$child=2;
$room_id=101;

function changeFormatDate($cdate){
list($day,$month,$year)=explode("/",$cdate);
return $year."-".$month."-".$day;
}
function gen_options($type)
  {
  // print "$id";
  // print "$type";
$con = mysql_connect("localhost","root","");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("hotel", $con);

      $count=0;   
$result = mysql_query("SELECT * FROM rooms where type='$type'");

while($row = mysql_fetch_array($result))
   {
  $a=$row['room_no'];
//print "$a";
  // $query1 = mysql_query("SELECT count(*) FROM `room_inventory` WHERE room_no='$a' and status='active'");
// $query=mysql_query("SELECT count(*) FROM `room_inventory` WHERE room_no='$a' and ((arrival<='2012-05-11' AND  departure>='2012-05-11')OR(arrival<='2012-05-13' AND  departure>='2012-05-13'))");

  //$query=mysql_query("SELECT count(*) FROM `room_inventory` WHERE room_no='$a'  and ((arrival BETWEEN '2012-05-11' AND '2012-05-13') or (departure BETWEEN '2012-05-11' AND '2012-05-13'))");

  //$query=mysql_query("SELECT count(*) FROM `room_inventory` WHERE room_no='$a' and ((arrival<='$arr' AND  departure>='$arr')OR(arrival<='$dep' AND  departure>='$dep'))");
  $quer=sprintf("SELECT count(*) FROM room_inventory WHERE room_no='$a' and ((arrival<='%s' AND  departure>='%s')OR(arrival<='%s' AND  departure>='%s'))",$arrival,$arrival,$departure,$departure);
$query=mysql_query($quer);

//$query=mysql_query("SELECT count(*) FROM `room_inventory` WHERE room_no='$a'  and ((arrival BETWEEN '$arr' AND '2012-05-13') or (departure BETWEEN '$arr' AND '2012-05-13'))");

$r=mysql_fetch_array($query);
$v=$r['count(*)'];
// print "$v";

if($v===0)
{
  $count++;
    // print "$count";
}

}

  echo   '<select name="room1" class="ed" id="r1">';
  for($i=0;$i<=$count;$i++)
   {
     echo '<option>'.$i.'</option>';
    } 
  echo '</select>';

mysql_close($con);
}


?>

<style type="text/css">


<!--
.style2 {
   font-size: 12px;
   font-weight: bold;
}
-->
</style>
</head>

<body>

<!-- TOP -->
<div id="top1"><a href="index.php"></a></div>
<div id="top">

<ul class="menu">
<li class="home"><a href="index.php">Home</a></li>
<li class="about"><a href="about.php">About</a></li>
<li class="contacts"><a href="contact.php">Contacts</a></li>
<li class="renting"><a href="gallery.php">GALLERY</a></li>
<li class="selling"><a href="rates.php">RATES</a></li>


</ul>


</div>




<!-- HEADER -->
<!-- CONTENT -->
<div id="content">

<div id="leftPan">

<div id="services">
<h2>RESERVATION DETAILS </h2>
<p>
  <ul>
      Check In Date :<?php echo $arrival; ?><br />
      Check Out Date :<?php echo $departure; ?>  <br />
</ul>
    </p>
</p>
</div>




</div>
<div id="featured"><br />
<div>
<form action="personnalinfo.php" method="post" onsubmit="return validateForm()" name="room">
  <input name="start" type="hidden" value="<?php echo $arrival; ?>" />
  <input name="end" type="hidden" value="<?php echo $departure; ?>" />
  <input name="rooms" id="rooms" type="hidden" />
  <input name="adult" type="hidden" value="<?php echo $adults; ?>" />
  <input name="child" type="hidden" value="<?php echo $child; ?>" />

</div>

  <table bgcolor="white" border="1"  width="100%" style="float:left;table-layout:fixed" cellpadding="10" cellspacing="0" >
<col width="70%">
  <tr>
  <th colspan="2" bgcolor="white"><h2><font color=maroon>Room Type</font></h2></th>
  </tr>
  <tr>
  <td>
  <table border="0" style="float:left;table-layout:fixed" width="100%">
  <col width="55%">
  <tr>
  <td valign="top">
  <img src="img1/apt.jpg" style="float:left" />

</br>
<div style="margin-top:120px;margin-left:5px">
<img src="img1/apt1.jpg" />
  <img src="img1/apt2.jpg" />
  <img src="img1/apt3.jpg" />
  </div>
  </td>
  <td>
  <h3>Appartment</h3>
  <br>
    <span class="price">Price:</span> <span class="number">Rs. 5,000.00</span><br />
<a> Apparment in HOTEL BELLA has 2 Rooms with connecting door.It can accomodate 4 Adult and 2 children.
And are located on Beach side to give you comfort and a panoramic view so that you can have a luxury accommodation.<br>
*Sitting area
<br>*jacuzzi shower</br>
*Large terrace overlooking the sea
*Jacuzzi
*Light therapy
*Air treatment <a href="#">more...</a></p><br />
  </td>
  </tr>
  </table>

  </td>
  <td valign="top">
  <table border=0 width="100%" cellspacing="10">
  <tr>
       <td align="left">
        <label><h3>People : </h3></label>   
</td>
<td align="right">
  <img src="img1/i1.jpg" />
</td>
  </tr>

  <tr>
  <td align="left" >
        <label><h3>Rooms : </h3></label>   
   
   </td>
<td align="right" >
<?php gen_options("single");?>
</td>
  </tr>
  </table>
  </td>
  </tr>



  <tr>
  <td>
  <table border="0" style="float:left;table-layout:fixed" width="100%">
  <col width="55%">
  <tr>
  <td valign="top">
  <img src="img1/double.jpg" style="float:left" />

</br>
<div id="featured">
<img src="img1/double1.jpg" />
  <img src="img1/double2.jpg" />
  <img src="img1/double3.jpg" />
  </div>
  </td>
  <td>
  <h3>Double</h3>
  <br>
    <span class="price">Price:</span> <span class="number">Rs. 3,000.00</span><br />
<a>Double rooms in HOTEL BELLA has Double bed. And can accomodate 2 Adults and 2 kids. It is comfortable and pleasant, with balcony and sea view.
We hope that you will enjoy your summer holidays in Bella.
BASIC:
Telephone.
Satellite TV.
Safety Deposit Box.
Mini Bar - Refrigerator.
Air condition.
Shower with or without cabin.
Hair Dryer.
Balcony.<a href="#">more...</a></p><br />
  </td>
  </tr>
  </table>

  </td>
  <td valign="top">
  <table border=0 width="100%" cellspacing="10">
  <tr>
       <td align="left">
        <label><h3>People : </h3></label>   
</td>
<td align="right">
  <img src="img1/i2.jpg" />
</td>
  </tr>
  <tr>
  <td align="left">
        <label><h3>Rooms : </h3></label>   
   
   </td>
<td align="right" >
<?php gen_options("double");?>
   
</td>
  </tr>
  </table>
  </td>
  </tr>




  <tr>
  <td>
  <table border="0" style="float:left;table-layout:fixed" width="100%">
  <col width="55%">
  <tr>
  <td valign="top">
  <img src="img1/single.jpg" style="float:left" />

</br>
<div id="featured">
<img src="img1/single1.jpg" />
  <img src="img1/single2.jpg" />
  <img src="img1/single3.jpg" />
  </div>
  </td>
  <td>
  <h3>Single</h3>
  <br>
    <span class="price">Price:</span> <span class="number">Rs. 2,000.00</span><br />
<a> Single Room in HOTEL BELLA has single bed, bathroom.Can accomodated single person. is comfortable and pleasant, with balcony and sea view
Telephone
Satellite TV
Safety Deposit Box
Mini Bar - Refrigerator
Air condition
Hair Dryer
<a href="#">more...</a></p><br />
  </td>
  </tr>
  </table>

  </td>
  <td valign="top">
  <table border=0 width="100%" cellspacing="10">
  <tr>
       <td align="left">
        <label><h3>People : </h3></label>   
</td>
<td align="right">
  <img src="img1/i3.jpg" />

</td>
  </tr>
  <tr>
  <td align="left">
        <label><h3>Rooms : </h3></label>   
   
   </td>
<td align="right" >
<?php gen_options("apartment");?>
   
</td>
  </tr>
  </table>
  </td>
  </tr>
</table>

<div  style="margin-top:1200px;margin-left:5px;text-align:right;">
<input type="image" src="img1/book.jpg" name="book" value="submit"/>
</div>






  <?php


  

// echo "$arrival\n";
// print "$departure\n";
echo "$arrival";
echo "$departure";
// echo date_format($arrival, 'Y-m-d');

?>




<input type="hidden" name="result" id="result" />
</form>

</div>

<div class="clear"></div>

</div>

<!-- FOOTER -->

<div id="footer">



<p><a href="index.php">HOME</a> |<a href="about.php"> ABOUT US </a>|<a href="contact.php"> CONTACTS </a>|<a href="gallery.php"> GALLERY </a>|<a href="rates.php"> ROOM RATES </a></p>
</div>




</body>
</html>

Archived

This topic is now archived and is closed to further replies.

×
×
  • 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.