Parsing the date in sql query

amey1309 · May 8, 2012

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

<head>

<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);

?>

<!--

.style2 {

font-size: 12px;

font-weight: bold;

}

-->

</style>

</head>

<body>

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

<h2>RESERVATION DETAILS </h2>

<p>

<ul>

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

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

</ul>

</p>

</div>

<div>

</div>

<tr>

</tr>

<tr>

<td>

<tr>

</br>

</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>

<tr>

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

</td>

</td>

</tr>

<tr>

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

</td>

<?php gen_options("single")?>

</td>

</tr>

</table>

</td>

</tr>

<tr>

<td>

<tr>

</br>

</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>

<tr>

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

</td>

</td>

</tr>

<tr>

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

</td>

<?php gen_options("double")?>

</td>

</tr>

</table>

</td>

</tr>

<tr>

<td>

<tr>

</br>

</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

</td>

</tr>

</table>

</td>

<tr>

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

</td>

</td>

</tr>

<tr>

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

</td>

<?php gen_options("apartment")?>

</td>

</tr>

</table>

</td>

</tr>

</table>

</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');

?>

</form>

</div>

</div>

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

cyberRobot · May 8, 2012

What is the error?

If you display $arr and $dep to the screen to see if they contain what you expect?

amey1309 · May 9, 2012

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

PFMaBiSmAd · May 9, 2012

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?

darkfreaks · May 9, 2012

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>

amey1309 · May 9, 2012

Oh at last I got the error.It is the variable scope.I need to specify both $arr and $ dep as Global before using it inside a function.

Thanks you all for your precious advice and time

darkfreaks · May 9, 2012

marked as solved.

Sign In

Parsing the date in sql query

Recommended Posts

amey1309

Link to comment

Share on other sites

cyberRobot

Link to comment

Share on other sites

amey1309

Link to comment

Share on other sites

PFMaBiSmAd

Link to comment

Share on other sites

darkfreaks

Link to comment

Share on other sites

amey1309

Link to comment

Share on other sites

darkfreaks

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information