I need to have an input field either as a pop java script calender (preferably) or just a mm/dd/yyyy selection, then have that converted to a Unix Timestamp which will then be sent to my Mysql database.
Can anyone point me in the right direction???
Thanks!!!
Troitone:geek:
$date = "12/25/2006";
list($month, $day, $year) = split('/', $date);
$timeStamp = mktime(0, 0, 0, $month, $day, $year);
I don’t know if I’m going about this the right way. I am making a page for a band, and they need to enter showdates in. The Forms Fields are Location, Date, and Details. I want them to be able to type in the location and details, and select a date, and when they hit submit, that date is automatically turned into a Unix Timestamp.
I know it must be simple, but I don’t really know where to go from here. This is your code echoing the Unix Timestamp. ::
<?
$date = “12/25/2006”;
list($month, $day, $year) = split(’/’, $date);
$timeStamp = mktime(0, 0, 0, $month, $day, $year);
echo $timeStamp;
?>
How would I use that to input into a my Mysql database, if this is the code for the form?
<input name=“gig_Date” type=“text” id=“gig_Date” size=“40”>
and here’s part of my sql code
$insertSQL = sprintf(“INSERT INTO gigs (gigID, gig_Date, gig_Location, gig_Details) VALUES (%s, %s, %s, %s)”,
GetSQLValueString($_POST[‘hiddenField’], “int”),
GetSQLValueString($_POST[‘gig_Date’], “int”),
GetSQLValueString($_POST[‘gig_Location’], “text”),
GetSQLValueString($_POST[‘gig_Details’], “text”));
When the information is passed from the form, you need to take the date and convert it into the Unix Timestamp using:
list($month, $day, $year) = split('/', $date);
$timeStamp = mktime(0, 0, 0, $month, $day, $year);
Where $date is the date that was input in the form. Which means you could just have:
list($month, $day, $year) = split('/', $_POST['gig_Date']);
$timeStamp = mktime(0, 0, 0, $month, $day, $year);
Now you have the Unix Timestamp in the variable $timeStamp.
For your MySQL table your gigDate field (or whatever you called it) could be a Date or Timestamp. To insert this timestamp you’d use something like:
mysql_query("INSERT INTO gigs (gigID, gigDate, gigLocation, gigDetails) VALUES ('', FROM_UNIXTIME($timeStamp), 'Some bar', 'Gig details!')");
The FROM_UNIXTIME() is the key. It’ll convert the timestamp into the type for you for your field type.
Now when you want to select from the database you’re not going to get a unix timestamp returned to you. Depending on the field type you may get YYYY-MM-DD. You could just output that as is, but that’s not a very normal date for people to read. You may want it as MM/DD/YYYY. Which means you could either use PHP to split the date like we did above and just output it as you want it. Or you can get the date field from MySQL using UNIX_TIMESTAMP() and use the PHP date function to make the date the way you want it. That could look something like:
$result = mysql_query("SELECT UNIX_TIMESTAMP(gigDate) AS date, gigLocation, gigDetails FROM gigs LIMIT 3");
while($row = mysql_fetch_array($result)){
echo "Date: " . date('n/j/Y', $row['date']) . "<br />";
echo "Location: " . $row['gigLocation'] . "<br />";
echo "Details: " . $row['gigDetails'];
}
And well, there you go.
BTW - I know the PHP and the MySQL code isn’t coded in the best possible way. And I left out things I normally would do, but it’s just an example to show you how to work with the Unix Timestamp, MySQL and PHP.
On more thing – I created a MySQL database to test the code to make sure it worked. 
I didn’t want you using code that may not be right. It’s not copy and use code either, if you want to use it make sure to make the changes to the MySQL table information so it works with your database. That goes for the variables as well.
The script totally works! Now I need to figure out how to update it. Here’s what I’ve got so far:
list($month, $day, $year) = split(’/’, $_POST[‘gig_Date’]);
$timeStamp = mktime(0, 0, 0, $month, $day, $year);
if ((isset($_POST[“MM_update”])) && ($_POST[“MM_update”] == “edit_gig_form”)) {
$updateSQL = sprintf(“UPDATE gigs SET gig_Date=’$timeStamp’, gig_Location=%s, gig_Details=%s WHERE gigID=%s”,
GetSQLValueString($_POST[‘gig_Date’], “text”),
GetSQLValueString($_POST[‘gig_Location’], “text”),
GetSQLValueString($_POST[‘gig_Details’], “text”),
GetSQLValueString($_POST[‘hiddenField’], “int”));
But I can’t figure out how to make it work. Any suggestions?
Thanks!
Okay, I just need to ask this since I was wondering about it before. Why are you using sprintf() to format your SQL query? I understand it’s use, but I just don’t see the need for it for what you’re doing with the code.
Skip using the sprintf() function and just make yourself an ol’ fashioned string for your SQL queries…
$gig_Location = $_POST['gig_Location'];
$gig_Details = $_POST['gig_Details'];
$gigID = $_POST['gigID']; // See Note below
/*
Note: I don't see where you get the gigID from. If it's from the form's hidden
field (the $_POST["hiddenField"]) -- consider renaming the form's hidden field to gigID. It may be clear to you what was passed, but if someone takes over your code later, it's not very well named.
*/
$sql = "UPDATE gigs SET gig_Date=FROM_UNIXTIME($timeStamp), gig_Location='$gig_Location', gig_Details='$gig_Details' WHERE gigID=$gigID";
The just use mysql_query($sql)… should be straight forward enough.
BTW – I know this is suppose to be for members to enter data on their own website, but you still may want to do some checking of the data being passed from the form to PHP - and before you enter it into MySQL. I’m not sure if this is only for users logged in, or if anyone could access the page. If anyone can access the page you need to watch out for SQL Injection attacks, and it doesn’t hurt to check quoted information being passed either. get_magic_quotes_gpc() is perfect for that. I’ve had clients come to me in the past because they changed hosts only to find out their code from a previous web coder wasn’t working right. Turned out that the was no check for magic quotes gpc.
Thanks for all of your help! I’m still trying to get everything straightened out. I’m working within’ Dreamweaver to set this up. I’m not sure if it’s putting in alot of code I don’t need. Here’s everything so far:
<?php
function GetSQLValueString($theValue, $theType, $theDefinedValue = “”, $theNotDefinedValue = “”)
{
$theValue = (!get_magic_quotes_gpc()) ? addslashes($theValue) : $theValue;
switch ($theType) {
case “text”:
$theValue = ($theValue != “”) ? “’” . $theValue . “’” : “NULL”;
break;
case “long”:
case “int”:
$theValue = ($theValue != “”) ? intval($theValue) : “NULL”;
break;
case “double”:
$theValue = ($theValue != “”) ? “’” . doubleval($theValue) . “’” : “NULL”;
break;
case “date”:
$theValue = ($theValue != “”) ? “’” . date(“Y-m-d”,strtotime($theValue)) . “’” : “NULL”;
break;
case “time”:
$theValue = ($theValue != “”) ? “’” . date(“H:i:s”,strtotime($theValue)) . “’” : “NULL”;
break;
case “datetime”:
$theValue = ($theValue != “”) ? “’” . date(“Y-m-d H:i:s”,strtotime($theValue)) . “’” : “NULL”;
break;
case “defined”:
$theValue = ($theValue != “”) ? $theDefinedValue : $theNotDefinedValue;
break;
}
return $theValue;
}
$editFormAction = $_SERVER[‘PHP_SELF’];
if (isset($_SERVER[‘QUERY_STRING’])) {
$editFormAction .= “?” . htmlentities($_SERVER[‘QUERY_STRING’]);
}
list($month, $day, $year) = split(’/’, $_POST[‘gig_Date’]);
$timeStamp = mktime(0, 0, 0, $month, $day, $year);
if ((isset($_POST[“MM_update”])) && ($_POST[“MM_update”] == “edit_gig_form”)) {
$updateSQL =
$gig_Location = $_POST[‘gig_Location’];
$gig_Details = $_POST[‘gig_Details’];
$gigID = $_POST[‘gigID’];
GetSQLValueString($_POST[‘gig_Location’], “text”),
GetSQLValueString($_POST[‘gig_Details’], “text”),
GetSQLValueString($_POST[‘gigID’], “int”));
$sql = “UPDATE gigs SET gig_Date=FROM_UNIXTIME($timeStamp), gig_Location=’$gig_Location’, gig_Details=’$gig_Details’ WHERE gigID=$gigID”;
mysql_select_db($database_connGigs, $connGigs);
$Result1 = mysql_query($updateSQL, $connGigs) or die(mysql_error());
}
mysql_select_db($database_connGigs, $connGigs);
$query_rst_Gigs_Edit = “SELECT * FROM gigs”;
$rst_Gigs_Edit = mysql_query($query_rst_Gigs_Edit, $connGigs) or die(mysql_error());
$row_rst_Gigs_Edit = mysql_fetch_assoc($rst_Gigs_Edit);
$totalRows_rst_Gigs_Edit = mysql_num_rows($rst_Gigs_Edit);
?>
I personally don’t use DreamWeaver so I’m not sure what it’s adding or what you’re adding in that code. Plus I’m not sure how large this project is that you’re working on.
For example, I don’t see a need for the function GetSQLValueString(). It doesn’t sound like what you’re doing is that involved, so it might be an overkill.
Your project - from what I understand is to build a backend for a band to enter gig information. In the database for each gig you have an ID, Date, Location and Details, right? You can check the location and details for the gig before you put them into the database to see if you’ll need to use addslashes. And when you grab that info from the database you can use stripslashes as needed before displaying it. The date, you covert into the unix timestamp and then insert it into MySQL. When you get that value from MySQL, you use PHP to convert it to the right date format.
So really, there’s no need for the function that I can see.
I guess I’m looking at this in a simple way. You need a page with a form for the users to add the information to the database. You send that to a PHP page which checks the gig details and gig location to see if you’re going to need to use addslashes before putting the info into the database. For the date, you already have the method to convert the date into a unix timestamp. And the gigID is set to automatically increment - so you don’t need to worry about that.
Using the data after it’s been checked and properly set up to be entered into the database, you make your MySQL query and input the data into the database. Which only takes a few lines…
Make a MySQL connection
Select the database for the gig information
Make your SQL insert query
Check to make sure everything entered (no errors)
Close the MySQL connection
The same goes for the update of a gig. When the information is updated for a gig, check the info being passed from the form to PHP. Format the information as needed (convert the date, use addslashes as needed, etc). Then you’re doing the same as before…
Make a MySQL connection
Select the database for the gig information
Make your SQL update query
Check to make sure everything updated (no errors)
Close the MySQL connection
The only difference is really the update query.
There’s nothing really complicated with the MySQL queries that you’ll be using so you don’t really need to make more work for yourself by creating functions.
:: Copyright KIRUPA 2024 //--