Page 1 of 1

help|cant seem to add date and time to db

Posted: Mon Jul 26, 2010 1:20 pm
by adamrain
ok i get the data from the inputs one of the inputs is date this format "2010-07-13" i trid to do somthon and it didnt work...whith the time, im not sure in which format to save it on db.

i tried a lot of things and it dosnt work (it saves the other data on db but not time or date).

here is the code:

Code: Select all

<?php
session_start();
 if (isset($_SESSION['user']))
{
  $user=$_SESSION['user'];
}
else
{
  echo "Not Logged in <br><br> <A href='login.php'>Login</a>";
  exit;
}
include 'connect.php';
//get user id so php will know under which user id to put it in the second block
$id_user=$_SESSION['id'];
if(isset($_POST['create_event'])){
    
    $name=$_POST["name"];
    $content=$_POST["content"];
    $time=$_POST["time"];
    $date=$_POST["date"];
    //chack date
    //Check the length of the entered Date value 
    
    
if((strlen($date)<10)OR(strlen($date)>10)){
    echo("Enter the date in 'dd/mm/yyyy' format");
}
    //$date2 = strtotime($date);
    //$date2 = date('y-m-d', );
    //exit($date2 );
    //$date = date("Y-m-d", ); 
   
    
    
    //insert to events
    $sql = "INSERT into event(name,content,date,time) VALUES ('$name','$content','$date','$time')";
    //exit($sql);
    
    $result = mysql_query($sql);
    $topicid = mysql_insert_id();
    $relations="INSERT into relations (id_user,id_event) VALUES ('$id_user','$topicid')";
    $do=mysql_query($relations);
    
        if(!$result) {
             echo"bad sql query";
                  
    
        }
    else{   echo 'You have successfully created <a href="events2.php'. '">your new event</a>.';}
             

     }

  
?>

Re: help|cant seem to add date and time to db

Posted: Mon Jul 26, 2010 3:45 pm
by califdon
Your INSERT code looks correct (although using "date" and "time" for field names is risky, those are keywords in PHP). What data types are those fields in the table?

Use the mysql command 'DESCRIBE event' to display the table structure.

Re: help|cant seem to add date and time to db

Posted: Tue Jul 27, 2010 10:57 am
by adamrain
the typs are date and time for date and time, im consdring changing it to str and when i call it use strtotime()on it.

and i use phpmyadmin so i dont realy need "DESCRIBE " to see the structure. but its good for general knowledge anyway.

what dos the forum say shod i try saving it in db as date and time or as strings and the convert it to date and time in php?

or if you have an example or better idea that could be helpful (i assume some of the members have experience with those pesky date and time with sql).

oh and caling the date is no problem it works fine, but i cant save it in db from php.

thank you

Re: help|cant seem to add date and time to db

Posted: Tue Jul 27, 2010 1:11 pm
by califdon
Your problem is simply that you are not following the format for dates in MySQL. You are asking the user to input the date as "dd/mm/yyyy" but you are not converting that to what MySQL requires: "YYYY-MM-DD" (the difference between "/" and "-" is crucial, too). It looks like you tried to do that in the commented-out lines, but you didn't quite get it right. This should work:

Code: Select all

$date2=substr($date,6)."-".substr($date,3,2)."-".substr($date,0,2);
Even then, if the user enters the date incorrectly, you will have problems. A better way to specify dates would be to use dropdown selections for Month, Day, and Year. That will force users to provide correctly formatted date components. You could do the same for times (but do you really need seconds??), and remember that MySQL Time format requires colon separators (:).

I see that you tried echoing out your SQL string, that's a good debugging practice. You have to know what it should like, though. Your Date should look like this: '2010-07-30' and your time should look like this: '11:03:00'.

I would DEFINITELY not recommend using strings for dates or times! That would prevent you from using any of the many valuable functions available for managing dates and times in both MySQL and PHP. If you're not already familiar with these, take just a quick look at these: http://dev.mysql.com/doc/refman/5.1/en/ ... tions.html.

Re: help|cant seem to add date and time to db

Posted: Tue Jul 27, 2010 2:02 pm
by adamrain
i have a date picker and i tried a lot of methods.

no i dont need seconds i dont even need it as time ill change it to string.

i think ill jast use strings and convert them to date