TIMESTAMP Query

[pam_w]

Hi,

 

I am creating a website using PHP and MYSQL for my dissertation. The main element of the site is an online application form. When each application is submitted, the date and time is recorded using a timestamp.

 

I was wondering if there is a way I can use this to produce a query showing all the applications submitted today? I wanted to have an edit box so the user can enter today's date and this then searches the submitted field and compares this to the TIMESTAMP. There is only one problem both the date and time are recorded, I wondered if there is a way to search just the date and not the time of the TIMESTAMP?

 

If this is not possible (or really complicated) is there an easier way to create this query?

 

Any help would be great... this has been driving me crazy!

 

Pam

[Barand]

If you aren't interested in the time element make your date_submitted column type DATE and just write CURDATE() to it when you insert a new row

[pam_w]

Will the date need to be converted to SQL format? This is probably obvious but I'm not sure how to enter the CURDATE() value. I am using Dreamweaver, so should I use a hidden field and assign the value to this field?

 

 

I have been having trouble with dates as well. Is there a simple way to convert a date input as dd/mm/yyyy to the MySQL format?

 

 

Pam

[pam_w]

Thanks for your help, I managed to work out the first part,  do you have any suggestions for changing the date input by a user from dd/mm/yyyy to sql format?

 

 

Pam

[BlueSkyIS]

i assume you're trying to get from from dd/mm/yyyy to yyyy-mm-dd

 

<?php
$a_date = "31/03/2008";
$dparts = explode("/",$a_date);
$sql_date = "{$dparts[2]}-{$dparts[1]}-{$dparts[0]}";
echo $sql_date;
?>

[Barand]

I'm not sure how to enter the CURDATE() value.

 

INSERT INTO mytable (datecol) VALUES ( CURDATE() )

 

 

I have been having trouble with dates as well. Is there a simple way to convert a date input as dd/mm/yyyy to the MySQL format?

 

if $dmy = '22/03/2008'

 

INSERT INTO mytable (datecol) VALUES ( STR_TO_DATE( '$dmy' , '%d/%m/%Y') )

 

[BlueSkyIS]

ah, much more concise as usual! i am not as familiar with MySQL date/time functions.

[sqlnoob]

this is what I use in my site to figure out the difference in days between a made time and the current time

 

$century = mktime(12, 0, 0, 3, 13, 2008);
$today = time();
$timedifference = $today - $century;
$moddifference = floor($timedifference / 86400);

 

if you replace the $century variable with the time you get from an sql field (one that comes with the post). Then you can get the time that corresponds with the post.

 

I divide by 86400 for a reason. There are 60 seconds in a minute, 60 minutes in an hour and 24 hours in a day. So 60*60*24 = 86400

 

So there are 86400 seconds in a day. The $timedifference is the difference in seconds between the two dates, so if you divide it by 86400 you get it in days.

 

Naturally this is done for only one post. Now all you have to do is figure out a way, to get all the posts you want to check it for and then use an if-else construction to check wether it's a day old or not (or the way you want it to, like 2 days or whatever).

 

I'm only a noob at this, but I hope this helps you. Best of luck!

[redarrow]

SELECT TIMEDIFF(`appointment_start`,`appointment_end`) AS duration FROM appointments;

 

or

 

SELECT IF(TIME_TO_SEC(last_date)>=TIME_TO_SEC(first_date),
TIME_TO_SEC(last_date)-TIME_TO_SEC(first_date),
86400+(TIME_TO_SEC(last_date)-TIME_TO_SEC(first_date)))
FROM table;

[sqlnoob]

^

|

 

now that will work even better

[pam_w]

If the value of the date in this insert statement comes from an edit box, can anyone point out where I'm going wrong:

 

 $insertSQL = sprintf("INSERT INTO project (Project_Title, Start_Date, End_Date) 
VALUES %s, (STR_TO_DATE(%s, '%d/%m/%y'), STR_TO_DATE(%s, '%d/%m/%y')",
                       GetSQLValueString($_POST['Project_Title'], "text"),
                       GetSQLValueString($_POST['Start_Date'], "date"),
                       GetSQLValueString($_POST['End_Date'], "date") .... 

 

I'm pretty new to all this...

 

Pam

[Barand]

What does GetSQLValueString() do with a date value?

[pam_w]

This is the complete statement I just edited it a bit before:

 

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "application_form")) {
  $insertSQL = sprintf("INSERT INTO project (Project_name, Project_code, Project_qc_name, Start_Date, End_Date, BEN_Time, BEN_Distributer_email, Out_of_hours_support, Details, Size_id, ClearQuest_id, Platform_id, Project_manager_id, Testing_leader_id, Comments, submitted, `Time`) VALUES (%s, %s, %s, STR_TO_DATE(%s, '%d/%m/%y'), STR_TO_DATE(%s, '%d/%m/%y'), %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, curdate(), curtime())",
                       GetSQLValueString($_POST['Project_Title'], "text"),
                       GetSQLValueString($_POST['Project_Code2'], "text"),
                       GetSQLValueString($_POST['Project_QC_Title'], "text"),
                       GetSQLValueString($_POST['Start_Date'], "date"),
                       GetSQLValueString($_POST['End_Date'], "date"),
                       GetSQLValueString($_POST['BEN_Time'], "double"),
                       GetSQLValueString($_POST['BEN_Distributer_Email'], "text"),
                       GetSQLValueString($_POST['outofhours'], "text"),
                       GetSQLValueString($_POST['Details'], "text"),
                       GetSQLValueString($_POST['Size'], "int"),
                       GetSQLValueString($_POST['ClearQuest_Project'], "int"),
                       GetSQLValueString($_POST['Platform'], "int"),
                       GetSQLValueString($_POST['Project_Manager'], "int"),
                       GetSQLValueString($_POST['Testing_Leader'], "int"),
                       GetSQLValueString($_POST['Comments'], "text"),
                       GetSQLValueString($_POST['curdate'], "date"),
                       GetSQLValueString($_POST['hiddenField'], "date"));

  mysql_select_db($database_Project_Application, $Project_Application);
  $Result1 = mysql_query($insertSQL, $Project_Application) or die(mysql_error());

 

At the moment if I run the code I get this error:

 

Warning: sprintf() [function.sprintf]: Too few arguments in C:\wamp\www\Project_Application\projectapplication.php on line 55
Query was empty

[Barand]

To put a "%" inside a sprintf format string it needs to be "%%" so

 

... STR_TO_DATE(%s, '%%d/%%m/%%y'), STR_TO_DATE(%s, '%%d/%%m/%y'),

[pam_w]

Hi,

 

It worked, but the date is entered into the database with the year 2020 instead of 2008 - the month and day are fine though...

 

Pam

[pam_w]

It's ok I just needed a capital Y,

 

Thanks again for your help