Date Format Problem With Php And Mysql

KarenMac · December 2, 2012

I have the following code and cannot get my select statement to read the user input. It works if I hard code the date in the select statement - can someone please advise what I am doing wrong. Thanks. I am trying to get the month, day and year into 1 variable to match reserveDate in the database and then time is a variable by itself.

<?php include_once 'helpers.inc.php'; ?>
<?
$date=$_POST['date'];
if(isset($date) and $date=="submit"){
$month=mysql_real_escape_string($_POST['Month']);
$day=mysql_real_escape_string($_POST['Day']);
$year=mysql_real_escape_string($_POST['Year']);
$reserveStartTime=mysql_real_escape_string($_POST['Time']);
$reserveDate="$month$day$year";
echo "mmddyyyy format :$reserveDate<br>";
$reserveDate="$year$month$day";
echo "YYYYmmdd format :$reserveDate<br>";
echo "$reserveStartTime";
}
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "[url="http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"]http://www.w3.org/TR...ransitional.dtd[/url]">
<html xmlns="[url="http://www.w3.org/1999/xhtml"]http://www.w3.org/1999/xhtml[/url]">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Court Reservation Search</title>
<style type="text/css">
#form1 center {
text-align: center;
}
#form1 p {
text-align: center;
font-family: Verdana, Geneva, sans-serif;
}
</style>
<h1>Court Reservation Search</h1>
<body>
<form id="form1" name="search" method="post" action="reservations.php">
 <p> </p>
 <p class="text">Please select a date and time to make a reservation:</p>
 <p> </p>
 <p>
<label for="Month"><span class="text"><span class="text"><span class="text"><span class="text"><span class="text">Month</span>:</span></span></span></span></label>
<span class="text">
<select name="Month"size="1" id="Month">
  <option value="1">Jan</option>
  <option value="2">Feb</option>
  <option value="3">Mar</option>
  <option value="4">Apr</option>
  <option value="5">May</option>
  <option value="6">Jun</option>
  <option value="7">Jul</option>
  <option value="8">Aug</option>
  <option value="9">Sep</option>
  <option value="10">Oct</option>
  <option value="11">Nov</option>
  <option value="12">Dec</option>
  </select>
<label for="Day">Day:</label>
<select name="Day" id="Day">
  <option value="1">1</option>
  <option value="2">2</option>
  <option value="3">3</option>
  <option value="4">4</option>
  <option value="5">5</option>
  <option value="6">6</option>
  <option value="7">7</option>
  <option value="8">8</option>
  <option value="9">9</option>
  <option value="10">10</option>
  <option value="11">11</option>
  <option value="12">12</option>
  <option value="13">13</option>
  <option value="14">14</option>
  <option value="15">15</option>
  <option value="16">16</option>
  <option value="17">17</option>
  <option value="18">18</option>
  <option value="19">19</option>
  <option value="20">20</option>
  <option value="21">21</option>
  <option value="22">22</option>
  <option value="23">23</option>
  <option value="24">24</option>
  <option value="25">25</option>
  <option value="26">26</option>
  <option value="27">27</option>
  <option value="28">28</option>
  <option value="29">29</option>
  <option value="3">30</option>
  <option value="31">31</option>
</option>
   </select>
<label for="Year">Year:</label>
<select name="Year" id="Year">
  <option value="2012">2012</option>
  <option value="2013">2013</option>
  <option value="2014">2014</option>
  <option value="2015">2015</option>
  <option value="2016">2016</option>
  <option value="2017">2017</option>
  </select>
 <label for="Time">Time:</label>
<select name="Time" id="Time">
  <option value="9:00">9:00 am</option>
  <option value="9:30">9:30 am</option>
  <option value="10:00">10:00 am</option>
  <option value="10:30">10:30 am</option>
  <option value="11:00">11:00 am</option>
  <option value="11:30">11:30 am</option>
  <option value="12:00">12:00 pm</option>
  <option value="12:30">12:30 pm</option>
  <option value="13:00">1:00 pm</option>
  <option value="13:30">1:30 pm</option>
  <option value="14:00">2:00 pm</option>
  <option value="14:30">2:30 pm</option>
  <option value="15:00">3:00 pm</option>
  <option value="15:30">3:30 pm</option>
  <option value="16:00">4:00 pm</option>
  <option value="16:30">4:30 pm</option>
  <option value="17:00">5:00 pm</option>
  <option value="17:30">5:30 pm</option>
  <option value="18:00">6:00 pm</option>
  <option value="18:30">6:30 pm</option>
  <option value="19:00">7:00 pm</option>
  <option value="19:30">7:30 pm</option>
  <option value="20:00">8:00 pm</option>
  <option value="20:30">8:30 pm</option>
  <option value="21:00">9:00 pm</option>
  </select>
</span></p>
	 <p>
  <span class="text">
  <input type="hidden" class="text" name="date"value="search">
<input type="submit" value="Search" />
  </span></p>
 <p> </p>
</form>
</body>
</html>

Edited December 2, 2012 by PFMaBiSmAd
added code tags

NomadicJosh · December 2, 2012

Change this $reserveDate="$month$day$year";

to this:

$reserveDate = $month . $day . $year;

Edited December 2, 2012 by parkerj

Pikachu2000 · December 2, 2012

Is the database field a DATE data type?

KarenMac · December 2, 2012

yes the database field is a date field and that is why I left out any dashes or dots. Also - just to make sure I have it correct on my sql statement as well - I am including that code.

$sql = 'SELECT courtName, courtFee, reserveStartTime

FROM courts

INNER JOIN timeslots ON courts.courtId = timeslots.courtId

INNER JOIN bookings ON timeslots.slotId != bookings.slotId WHERE bookings.reserveDate = "$reserveDate" AND timeslots.reserveStartDateTime = "$reserveStartTime"';

$result = $pdo->query($sql);

I have also tried $_POST["reserveStartTime"], etc. as well.

Pikachu2000 · December 2, 2012

It's actually a not a bad idea to use the hyphens, it removes any ambiguity when you have a problem and start echoing data to debug things like this. Have you echoed the query string to make sure it contains the values you'd expect it to contain? If that's the actual code, I can tell already that it doesn't . . .

KarenMac · December 2, 2012

No I have not. I am fairly new at this and it has been very frustrating trying to get a user input date and how to do that. I would appreciate any suggestions if this is not going to work. I have a dropdown menu, they enter the dates and time on 4 different dropdowns. I need to combine the date and then send the date and time to a sql select query that it queries against reserved timeslots in the database (which I have this working as I said with a hard coded date) and returns timeslots available for the date the user has entered. This date problem has been ongoing and it is probably because I am new at PHP - I get the sql portion - if I ever get through with this I have decided php is not for me!

Pikachu2000 · December 2, 2012

I'm sure it is just because you're new. echo $sql; and look at the query string you get. If you don't understand why you see what you see, just say so . . .

PFMaBiSmAd · December 2, 2012

Your submitted month and day don't have leading zeros (you also have a typo in your day dropdown at the 30th day) so the hyphen separator character would be required for the database to be able to figure out the actual date.

Pikachu2000 · December 2, 2012

True, but all of that is pretty much irrelevant when the query string is in single quotes . . .

KarenMac · December 2, 2012

fix the dates and I am not sure what you mean about the query string..I apologize but I have spent hundreds of hours on this an I am brain fried now!

Pikachu2000 · December 2, 2012

Variables are not interpolated in a single-quoted string in PHP. Echo the query string and you'll see what I mean.

KarenMac · December 2, 2012

that should have been *fixed the dates and thank you for that input - it is all in the details..I think I am learning that so much I caught a typo in the Murach's PHP and MySql book.

KarenMac · December 2, 2012

I think I know what you are saying when I do echo $sql - it does not return a value for the variable but when I put "" in the query it does not like it. Somehow I need to get the value for the variables into the sql statement which obviously is not happening - when I hard code the date in the sql there are not quotes around it but the time requires single quotes. I understand I think why I am not getting a value but I am not sure i know how to fix it.

Pikachu2000 · December 2, 2012

Swap the quotes so the entire query string is enclosed in double-quotes, but the variables within it are in single-quotes. More information here: http://us1.php.net/manual/en/language.types.string.php

KarenMac · December 2, 2012

which translates into I still need some guidance please

Christian F. · December 2, 2012

When creating an SQL query, you're operating within PHP, which makes the SQL query an SQL string:

$query = "SELECT * FROM table WHERE field = value";

Now, let's say that "value" is the actual string you want to match. For MySQL to perceive it as such, you'll have to add single quotes around it, so that the finished SQL looks like this:

SELECT * FROM table WHERE field = 'value'

When adding double-quotes around that, to make it a PHP string, you get this:

$query = "SELECT * FROM table WHERE field = 'value'";

Now you can replace "value" with a variable, and remember to use real_escape_string () on it first, to get it to match a variable string.

Hopefully that cleared it up a bit?

KarenMac · December 2, 2012

now getting an undefined variable for result which leads me to believe the query is still not running properly....

if (isset($_POST['date']) and $_POST['date'] == 'Search')

//Get available times for court reservations

try

{

$sql = "SELECT timeslots.courtId, courtName, courtFee, timeslots.slotId, reserveStartTime FROM courts INNER JOIN timeslots ON courts.courtId = timeslots.courtId INNER JOIN bookings ON timeslots.slotId != bookings.slotId WHERE bookings.reserveDate = '$reserveDate' AND timeslots.reserveStartTime = '$reserveStartTime'";

$result = $pdo->query($sql);

}

catch (PDOException $e)

{

$error = 'Error retrieving courts: ' . $e->getMessage();

include 'error.html.php';

exit();

}

//getting all time slots available

while($row = $result->fetch())

{

$courts[] = array('timeslots.courtId' => $row['courtId'],'courtName' => $row['courtName'],'courtFee' => $row['courtFee'], 'timeslots.slotId' => $row['slotId'], 'reserveStartTime' =>$row['reserveStartTime']);

}

?>

KarenMac · December 2, 2012

my next problem once I get the results (which I have using hardcode) - the user selects one timeslot in the array and I need to insert that into another table in the database...I am not sure how to read the one the user selects...any help or hints would be appreciated...

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<head>

<title>List of Courts</title>

</head>

<body>

<?php if (isset($courts)): ?>

<tr><th>Court Name</th><th>Fees</th><th>Reserve Time</th><th>Reserve</th></tr>

<?php foreach ($courts as $court): ?>

<tr>

<td>

<div>

</div>

</form>

</td>

</tr>

<?php endforeach;?>

</table>

<?php endif; ?>

This presents my results with a reserve button beside each row. The user will click reserve and then I have this code written but it is not working.

try

{

$sql = 'INSERT INTO bookings SET

slotId = :timeslots.slotId,

courtId = :timeslots.courtId,

reserveDate = :reserveDate,

resereStartTime = :reserveStartTime,

reserveLength = "90"';

$s = $pdo->query($sql);

$s->execute();

}

catch (PDOException $e)

{

$error = 'Error adding reservation.';

include 'error.html.php';

exit();

}

header('Location: confirmation.php');

exit();

}

?>

Sign In

Date Format Problem With Php And Mysql

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information