Page 1 of 1

Insert into MySQL DATE

Posted: Sun Aug 16, 2009 3:39 pm
by jeaux
pickle | Please use [ code=php ], [ code=text ], etc tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: :arrow: Posting Code in the Forums to learn how to do it too.


2 questions here.

First the html

Code: Select all

 
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
<link href="css/addcar.css" rel="stylesheet" type="text/css" />
</head>
<body>
<p><img src="Images/A-1 Banner.jpg" /></p>
<br />
<form action="addcaraction.php" method="post">
  <fieldset>
    <legend>Add Car</legend>
    <p><label for="year">Year:</label>
      <input type="text" name="year" />
    </p>
  <p><label for="make">Make:</label>
  <input type="text" name="make" />
  </p>
  <p><label for="model">Model:</label>
    <input type="text" name="model" />
  </p>
  <p><label for="firstname">First name:</label>
  <input type="text" name="firstname" />
  </p>
  <p><label for="lastname">Last name:</label>
  <input type="text" name="lastname" />
  </p>
  <p><label for="phone">Phone:</label> 
    <input type="text" name="phone" />
  </p>
  <p><label for="address">Address:</label>
  <input type="text" name="address" />
  </p>
  <p><label for="license">License:</label>
  <input type="text" name="license" />
  </p>
  <p><label for="dateofloss">Date of Loss:</label>
  <input type="text" name="dateofloss" />
  </p>
  <p class="submit"><input type="submit" value="Add Car" />
  </p>
  </fieldset>
</form>
</body>
</html>
 
and the processing page.

Code: Select all

 
<?php
function pt_register()
{
  $num_args = func_num_args();
   $vars = array();
 
   if ($num_args >= 2) {
       $method = strtoupper(func_get_arg(0));
 
       if (($method != 'SESSION') && ($method != 'GET') && ($method != 'POST') && ($method != 'SERVER') && ($method != 'COOKIE') && ($method != 'ENV')) {
           die('The first argument of pt_register must be one of the following: GET, POST, SESSION, SERVER, COOKIE, or ENV');
     }
 
       $varname = "HTTP_{$method}_VARS";
      global ${$varname};
 
       for ($i = 1; $i < $num_args; $i++) {
           $parameter = func_get_arg($i);
 
           if (isset(${$varname}[$parameter])) {
               global $$parameter;
               $$parameter = ${$varname}[$parameter];
          }
 
       }
 
   } else {
       die('You must specify at least two arguments');
   }
 
}
 
$errors=0;
$error="The following errors occured while processing your form input.<ul>";
pt_register('POST','year');
pt_register('POST','make');
pt_register('POST','model');
pt_register('POST','firstname');
pt_register('POST','lastname');
pt_register('POST','phone');
pt_register('POST','address');
pt_register('POST','license');
pt_register('POST','dateofloss');
if($errors==1) echo $error;
else{
$where_form_is="http".($HTTP_SERVER_VARS["HTTPS"]=="on"?"s":"")."://".$SERVER_NAME.strrev(strstr(strrev($PHP_SELF),"/"));
$message="Year: ".$year."
Make: ".$make."
Model: ".$model."
firstname: ".$firstname."
lastname: ".$lastname."
phone: ".$phone."
address: ".$address."
license: ".$license."
date of loss: ".$dateofloss."
";
$link = mysql_connect("data excluded");
mysql_select_db("cars1",$link);
$query="insert into cars (year,make,model,firstname,lastname,phone,address,license,dateofloss) values ('".$year."','".$make."','".$model."','".$firstname."','".$lastname."','".$phone."','".$address."','".$license."','".$dateofloss."')";
mysql_query($query);
 
header("Refresh: 0;url=http://towhereever.com");
?><?php 
}
?>
 
This was more or less generated by formgen or some auto script at fatcow. It's over my head but I was able to alter it to meet my needs except for inserting a date into the table. I have been playing around with using Manual date but with no success.

Any advice would be great.

Second question same topic:

What would be idea is to use the same interface that you see on expedia.com or the other travel sites that give you a little calendar to select the date. Any ideas?

Many thanks,
Joe


pickle | Please use [ code=php ], [ code=text ], etc tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: :arrow: Posting Code in the Forums to learn how to do it too.

Re: Insert into MySQL DATE

Posted: Sun Aug 16, 2009 8:10 pm
by AlanG
Well to answer your first question, it depends on what the date is used for. 3 suggestions I would offer if you would like to store the date and time would be to use either a datetime, timestamp or int data type.

The datetime is good for storing dates and times in a human readable format, the timestamp data type is good for storing dates and times (in human readable format) of when the record is modified. You should use an int(10) data type if you would like to store a unix timestamp directly into the field.

You can insert the current datetime value using now() (MySQL function) in your sql query, whereas the current unix timestamp could be got by using time() (PHP function).

Re: Insert into MySQL DATE

Posted: Mon Aug 17, 2009 6:14 am
by jeaux
As of now the MySQL field is set to DATE and it expects a format like this. yyyy-mm-dd. But if I put 2009-01-01. It gets stored like this 0000-00-00. I will in the future be needing to do calculations with the date, but can't even get it entered into the table as of yet.

Re: Insert into MySQL DATE

Posted: Mon Aug 17, 2009 12:37 pm
by AlanG
When entering a date, leave out leading zeros.
Instead of 2009-01-01, try 2009-1-1. I read a few articles on the net and they all seem to have done this.

Also, when you are doing calculations on it, use PHP's strtotime() function.

Code: Select all

 
<?php
    // Fetch the date and assign it to $mydate
    $mydate = strtotime($mydate); // $mydate now stores a unix timestamp
?>
 

Re: Insert into MySQL DATE

Posted: Sun Sep 06, 2009 5:41 pm
by jeaux
Many thanks. After playing around with it and realizing I didn't need any of MySQL's date functions I decided on using an unsigned integer and using php's unix time stamp.

HTML

Code: Select all

 
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
<link href="css/addcar.css" rel="stylesheet" type="text/css" />
</head>
<body>
<p><img src="Images/A-1 Banner.jpg" /></p>
<br />
<form action="addcaraction.php" method="post">
  <fieldset>
    <legend>Add Car</legend>
    <p><label for="year">Year:</label>
      <input class = "textbox" type="text" name="year" />
    </p>
  <p><label for="make">Make:</label>
  <input class = "textbox" type="text" name="make" />
  </p>
  <p><label for="model">Model:</label>
    <input class = "textbox" type="text" name="model" />
  </p>
  <p><label for="firstname">First name:</label>
  <input class = "textbox" type="text" name="firstname" />
  </p>
  <p><label for="lastname">Last name:</label>
  <input class = "textbox" type="text" name="lastname" />
  </p>
  <p><label for="phone">Phone:</label> 
    <input class = "textbox" type="text" name="phone" />
  </p>
  <p><label for="address">Address:</label>
  <input class = "textbox" type="text" name="address" />
  </p>
  <p><label for="license">License:</label>
  <input class = "textbox" type="text" name="license" />
  </p>
    <p><label for="vin">VIN:</label>
  <input class = "textbox" type="text" name="vin" />
  </p>
  <p><label for="dateofloss">Date of Loss:</label>
  <select name="dolmonth" id="month">
    <option value="01">Jan</option>
    <option value="02">Feb</option>
    <option value="03">Mar</option>
    <option value="04">Apr</option>
    <option value="05">May</option>
    <option value="06">Jun</option>
    <option value="07">Jul</option>
    <option value="08">Aug</option>
    <option value="09">Sep</option>
    <option value="10">Oct</option>
    <option value="11">Nov</option>
    <option value="12">Dec</option>
  </select>
    <select name="dolday" id="day">
      <option>1</option>
      <option>2</option>
      <option>3</option>
      <option>4</option>
      <option>5</option>
      <option>6</option>
      <option>7</option>
      <option>8</option>
      <option>9</option>
      <option>10</option>
      <option>11</option>
      <option>12</option>
      <option>13</option>
      <option>14</option>
      <option>15</option>
      <option>16</option>
      <option>17</option>
      <option>18</option>
      <option>19</option>
      <option>20</option>
      <option>21</option>
      <option>22</option>
      <option>23</option>
      <option>24</option>
      <option>25</option>
      <option>26</option>
      <option>27</option>
      <option>28</option>
      <option>29</option>
      <option>30</option>
      <option>31</option>
    </select> 
    <select name="dolyear" id="year">
      <option selected="selected">2009</option>
      <option>2010</option>
    </select>
  </label>
  </p>
  <p class="submit"><input type="submit" value="Add Car" />
  </p>
  </fieldset>
</form>
</body>
</html>
 
 
PHP

Code: Select all

 
<?php
$con = mysql_connect("EXCLUDED DATA");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
 
mysql_select_db("cars1", $con);
 
$datestring = $_POST[dolday]."-".$_POST[dolmonth]."-".$_POST[dolyear];
$date = strtotime($datestring);
 
$sql="INSERT INTO cars (year, make, model, firstname, lastname, phone, address, license, VIN, dateofloss)
VALUES
('$_POST[year]','$_POST[make]','$_POST[model]','$_POST[firstname]','$_POST[lastname]','$_POST[phone]','$_POST[address]','$_POST[license]','$_POST[vin]','$date')";
 
if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }
echo "1 record added";
 
mysql_close($con)
?> 
 
And yea I know my code is sloppy but it works :drunk: