Insert Date from drop-down menus

[rsquaredpgh]

I've searched a few forums and haven't had any luck. Maybe someone can help ...

Using PHP/MySql, I have a form which inserts a record and it works well except for one thing. I have three drop down menu fields named sDay, sMonth, and sYear - I need to combine those three values and insert into a field in the db called startDate. From what I've read I guess I need to concatenate the values but I'm new to PHP and I'm unclear about how to tweak the Dreamweaver Insert Record code. I've attached the code that exists now.

Thanks to anyone who can give me a hand.

[Newsgroup_User]

rsquaredpgh wrote:

> I've searched a few forums and haven't had any luck. Maybe someone
> can help ...
>
> Using PHP/MySql, I have a form which inserts a record and it works
> well except for one thing. I have three drop down menu fields named
> sDay, sMonth, and sYear - I need to combine those three values and
> insert into a field in the db called startDate. From what I've read I
> guess I need to concatenate the values but I'm new to PHP and I'm
> unclear about how to tweak the Dreamweaver Insert Record code. I've
> attached the code that exists now.
>
> Thanks to anyone who can give me a hand.
>
>
>
>
> if ((isset($HTTP_POST_VARS["MM_insert"])) &&
> ($HTTP_POST_VARS["MM_insert"] == "bookroom")) {
> $insertSQL = sprintf("INSERT INTO bookings (roomID, clientID,
> adults, children) VALUES (%s, %s, %s, %s)",
> GetSQLValueString($HTTP_POST_VARS['roomID'],
> "text"),
> GetSQLValueString($HTTP_POST_VARS['clientID'], "text"),
> GetSQLValueString($HTTP_POST_VARS['adults'], "int"),
> GetSQLValueString($HTTP_POST_VARS['children'], "int"));
>
> mysql_select_db($database_connGR, $connGR);
> $Result1 = mysql_query($insertSQL, $connGR) or die(mysql_error());

Having written a couple of reservation systems I'd recommend you use a
date picker application - it's much easier for the end user and very
difficult to make mistakes

something like
http://www.javascriptkit.com/script/script2/tengcalendar.shtml for
example

HTH

--
Buzby
There's nothing more dangerous than a resourceful idiot

[rsquaredpgh]

Thanks for the tip. I looked at the Date picker you suggested - looks good but it doesn't allow the yyyy-mm-dd format that MySql requires. Since you've done it before, what's the best way to convert it?

[Newsgroup_User]

rsquaredpgh wrote:
> I have a form which inserts a record and it works well except
> for one thing. I have three drop down menu fields named sDay, sMonth, and sYear
> - I need to combine those three values and insert into a field in the db called
> startDate. From what I've read I guess I need to concatenate the values but I'm
> new to PHP and I'm unclear about how to tweak the Dreamweaver Insert Record
> code. I've attached the code that exists now.

Amend the code like this:

if ((isset($HTTP_POST_VARS["MM_insert"])) &&
($HTTP_POST_VARS["MM_insert"] ==
"bookroom")) {
$startDate = $_POST['sYear'].'-'.$_POST['sMonth'].'-'.$_POST['sDay'];
$insertSQL = sprintf("INSERT INTO bookings (roomID, clientID, adults,
children, startDate) VALUES (%s, %s, %s, %s, %s)",
GetSQLValueString($HTTP_POST_VARS['roomID'],
"text"),
GetSQLValueString($HTTP_POST_VARS['clientID'],
"text"),
GetSQLValueString($HTTP_POST_VARS['adults'],
"int"),
GetSQLValueString($HTTP_POST_VARS['children'],
"int"),
GetSQLValueString($startDate, "text"));

It looks as though you are using Dreamweaver MX, which used deprecated
PHP code. You may find that $HTTP_POST_VARS doesn't work on modern PHP
servers. It should be replaced by $_POST. MX 2004 and Dreamweaver 8 use
the recommended version.

--
David Powers
Adobe Community Expert
Author, "Foundation PHP for Dreamweaver 8" (friends of ED)
http://foundationphp.com/

[rsquaredpgh]

Good to know!

[rsquaredpgh]

Ok. I've done away with the drop down boxes in favor of a date picker. I'm now trying to convert the mm-dd-yyyy format to MySql format (yyyy-mm-dd) when the form variable is passed. I'm trying to use the split function to do this but so far I'm unsuccessful. Can someone look at my code and tell if they see where the problem is? Recordset produced is always based on the default values ( $startDate_rsAV = "01/03/2007" & $endDate_rsAV = "01/04/2007") and not the input values. I'm getting pretty frustrated as I've been at this all day.

Thanks.

[Newsgroup_User]

rsquaredpgh wrote:
> Ok. I've done away with the drop down boxes in favor of a date picker. I'm now
> trying to convert the mm-dd-yyyy format to MySql format (yyyy-mm-dd) when the
> form variable is passed. I'm trying to use the split function to do this but so
> far I'm unsuccessful. Can someone look at my code and tell if they see where
> the problem is?

There's not just one problem, but many. For a start, you shouldn't be
adding slashes, but removing them. Next, you're using different
variables for your default and form variables, so the form variables
will never be used. There are also some extraneous characters in the SQL
query.

The following code should do what you want:

<?php
$startDate_rsAV = '01/03/2007';
if (isset($_POST['startDate'])) {
$startDate_rsAV = (get_magic_quotes_gpc()) ? $_POST['startDate'] :
stripslashes($_POST['startDate']);
}
list($sMonth, $sDay, $sYear) = split('[/.-]', $startDate_rsAV);
// check that date elements are all numbers
if (is_numeric($sMonth) && is_numeric($sDay) && is_numeric($sYear)) {
$sDate = "$sYear-$sMonth-$sDay";
}
else {
echo 'Invalid start date';
exit;
}

$endDate_rsAV = '01/04/2007';
if (isset($_POST['endDate'])) {
$endDate_rsAV = (get_magic_quotes_gpc()) ? $_POST['endDate'] :
stripslashes($_POST['endDate']);
}
list($eMonth, $eDay, $eYear) = split('[/.-]', $endDate_rsAV);
// check that date elements are all numbers
if (is_numeric($eMonth) && is_numeric($eDay) && is_numeric($eYear)) {
$eDate = "$eYear-$eMonth-$eDay";
}
else {
echo 'Invalid end date';
exit;
}

mysql_select_db($database_connGR, $connGR);
$query_rsAV = "SELECT * FROM calendar WHERE calDate >= '$sDate'
AND calDate < '$eDate'";
$rsAV = mysql_query($query_rsAV, $connGR) or die(mysql_error());
$row_rsAV = mysql_fetch_assoc($rsAV);
$totalRows_rsAV = mysql_num_rows($rsAV);
?>

--
David Powers
Adobe Community Expert
Author, "Foundation PHP for Dreamweaver 8" (friends of ED)
http://foundationphp.com/

[rsquaredpgh]

Ok. David. Now I have to buy your book.. 🙂