Member Avatar

I don't know where to start so I just got to ask from the experts :). From my MySQL (database) I got a date formatted mm-dd-yyyy (i.e. 01/01/2010), now from my php page there are 3 dropdown/list menus which are for [Month]-[Day]-[Year] and a [TextField] then a [Submit Button]. When I click the [Submit Button] I got to retrieve the date then it will be shown in my texfield. Now the textfield having the value of the date = 01/01/2010 will be the one passing the values on my dropdowns {[Month = January] - [Day = 01] - [Year = 2010]} I just needed the help to how will I start programming as well the design for my database, do I get to have different tables for the parts of the date or what? Thank You for your help. :)

Edited by ekseks because: n/a
  • 6 Contributors
  • 20 Replies
  • 132 Views
  • 4 Days Discussion Span
  • Latest Post Latest Post by Zagga

Recommended Answers

All 20 Replies

Member Avatar

Formatting Dates and Times

DATE_FORMAT(date, format) and TIME_FORMAT assist in the representation of dates and times in formats other than the MySQL default format. Three examples illustrate the syntax:

SELECT DATE_FORMAT('2005-12-31', '%M %d %Y')
December 31 2005

SELECT DATE_FORMAT('2005-12-31', '%D of %M')
31st of December

SELECT TIME_FORMAT('02:17', '%H')
02

Member Avatar

Thank You Sir rch1231, I'll be posting the code that I'm currently working right now that I also found here in the forums. Thank You again.

Member Avatar

I found this here in the forums, so yah it's working but when I changed the format of my date to mm-dd-yyyy, it's entering 00-00-0000, why is that? then as you can see I also have 3 inputfields when I clicked the submit button. I want to get a certain date from my database thus putting [Name of the Month] - [Day] - [Year]. Is that possible? Thanks.

My HTML code:

<html>
<meta content= "text/html; charset= utf-8" http-equiv="Content-Type"/>
<head>

<link type="text/css" href="development-bundle/themes/base/ui.all.css" rel="stylesheet">
	<script type="text/javascript" src="development-bundle/jquery-1.3.2.js"></script>
	<script type="text/javascript" src="development-bundle/ui/ui.core.js"></script>
	<script type="text/javascript" src="development-bundle/ui/ui.datepicker.js"></script>
	<script type="text/javascript">
	$(function() {
		$("#datepick").datepicker({
			dateFormat: 'yy-m-d'
		});
	});
	</script>

</head>

<body>

<div>
<form action="insert_db.php" method="post">

Date:
<input name="mydate" id="datepick" type="text">

<input name="submit" type="submit">
<br>

<input name="month" type="text">
<input name="day" type="text">
<input name="year" type="text">

<input name="submit" type="submit">

</form>
</div>

</body>
</html>

PHP Code:

<?php
	$mydate=$_POST['mydate'];
	echo $mydate;
	$mydate = date("d/m/Y", strtotime($mydate));

	$connection = mysql_connect("localhost","root","");
	if (!$connection){
		die('Could not connect: ' . mysql_error());
	}
    mysql_select_db("enterdate", $connection);
   $sql="INSERT INTO testing (date) VALUES('$mydate')";
echo $sql;
   $flag=mysql_query($sql);
    if ($flag){
		echo "Record Entry successfull. ";
   }else{
		echo "Record Entry failed. ";
  }
	mysql_close($connection);
?>
Member Avatar

ok,now while creating database table(testing),what datatype(like integer etc..) you had given.

Member Avatar

I required an ID which is INT then a date which is a date type

Member Avatar

now change that date type to varchar in your database and then check.

Member Avatar

i thing change date format here. i dont know it works. try once.

$("#datepick").datepicker({

dateFormat: 'yy-m-d'

});

});
Member Avatar

it's working though i wanted to have my data type as a date, ow well I think this will work for now. so now to my next problem i want to put my selected date (assume that it's only one) in a text box and to different fields [Month] - [Day] - [Year] is it possible?

<html>
<meta content= "text/html; charset= utf-8" http-equiv="Content-Type"/>
<head>
</head>

<body>

<div>
<form action="insert_db.php" method="post">


<input name="month" type="text">
<input name="day" type="text">
<input name="year" type="text">

<input name="wholedate" type="text">

<input name="submit" type="submit">
</form>
</div>

</body>
</html>
Edited by ekseks because: n/a
Member Avatar

yes, use explode fuction , slipt the date and display the fields . see this link

Member Avatar

is it possible when I will be getting the date in my database? I don't know where to start can you help me for example i have 07/06/2010 in my database, i will be retrieving that after that when i press the submit button the first field will take [July], second field takes [06]. third field takes [2010].

is it also impossible to just display it automatically when my page load like [July][06][2010][07/06/2010]. thanks

Member Avatar 
$disdate=explode("/","07/06/2010");
if($disdate[0]==07)
{
$month="july";
}
$day=$disdate[1];
$year=$disdate[2];

put if condition for every month.
display $day,$month,$year where you want.

Member Avatar 
$disdate=explode("/","07/06/2010");
if($disdate[0]==07)
{
$month="july";
}
$day=$disdate[1];
$year=$disdate[2];

put if condition for every month.
display $day,$month,$year where you want.

I don't know where to start. Do I got to have another php page for retrieving the data from my MySQL or just only one page since that for now I only want to get the date in my database and as soon my page loads the date's also present in the textfields ow btw, disregard the submit button basically I don't know how to retrieve values in my database (MySQL) can anyone help?

Edited by ekseks because: n/a
Member Avatar

can I use explode for a retrieved data in my database? or it is just only for manual input of what i need to explode.. because I need to explode a date format in my database

Member Avatar

Hi ekseks,

The date format (in MySQL) stores the date as yyyy-mm-dd which is the standard format for dates. You could change the order of the date before you display it (after you get it from the database) to get it as mm-dd-yyyy by using the explode function.

You may want to consider using a javascript calendar to make it easier to choose the date. I use one I found at http://www.frequency-decoder.com/2009/09/09/unobtrusive-date-picker-widget-v5 as it includes a non javascript option and it automatically fills in the seperate date boxes.


Zagga

Member Avatar

Hi Zagga,

Can you at least give me an example how to code explode, with a retrieve data in the database (MySQL) particularly date? Thanks for the help. I just don't know where to start. Thank you again Sir Zagga.And, about datepickers I've already used datepickers, and yes I was able to work with it for sometime but about displaying the date to fields using PHP to retrieve the date and using the explode function is another thing :). So if you could help me thank you :).

Edited by ekseks because: n/a
Member Avatar

Hi ekseks,

I am assuming you are using the type "Date" to store the date in your database.
I am also assuming you have the variable $mydate that contains the date.

If $mydate is in the format YYYY-MM-DD:

$temp_mydate = explode("-",$mydate);
$year = $temp_mydate["0"];
$month = $temp_mydate["1"];
$day = $temp_mydate["2"];

If $mydate is in the format MM-DD-YYYY:

$temp_mydate = explode("-",$mydate);
$month = $temp_mydate["0"];
$day = $temp_mydate["1"];
$year = $temp_mydate["2"];

This will give you 3 seperate variables $day, $month and $year to process how you like.

To store the date in the database, combine the variables in the format YYYY-MM-DD

$newdate = $year . "-" . $month . "-" . $day;

Hope this helps.
Zagga

Member Avatar

Hi Sir Zagga,

Here is what I'm currently working with. Can you please check my code and see where I went wrong. Thank You. :)

Warning: explode() expects parameter 2 to be string, resource given in C:\xampp\htdocs\date\xy.php on line 12

<?php
$username="root";
$password="";
$database="pickadate";

mysql_connect('localhost',$username,$password);
@mysql_select_db($database) or die("Unable to select database");

$query = "SELECT date_value FROM `date` WHERE id = '2'"; 
$result = mysql_query($query) or die(mysql_error());
$row = mysql_fetch_array($result);
$temp_mydate = explode("-",$row);
$month = $temp_mydate["0"];

//$row = date("m/d/Y", strtotime($row));
//$temp_mydate=explode("/", $row);
if($temp_mydate[0]==01)
{
$month="january";
}
if($temp_mydate[0]==02)
{
$month="february";
}
if($temp_mydate[0]==03)
{
$month="march";
}
if($temp_mydate[0]==04)
{
$month="april";
}
if($temp_mydate[0]==05)
{
$month="may";
}
if($temp_mydate[0]==06)
{
$month="june";
}
if($temp_mydate[0]==07)
{
$month="july";
}
if($temp_mydate[0]==08)
{
$month="august";
}
if($temp_mydate[0]==09)
{
$month="september";
}
if($temp_mydate[0]==10)
{
$month="october";
}
if($temp_mydate[0]==11)
{
$month="november";
}
if($temp_mydate[0]==12)
{
$month="december";
}
/*$day=$temp_mydate[1];
$year=$temp_mydate[2];
?>*/ 

$day = $temp_mydate["1"];
$year = $temp_mydate["2"];

?>

<input name = "month" value="<?php echo $month ?>" type="text" />
<input name = "day" value="<?php echo $day ?>" type="text" />
<input name = "year" value="<?php echo $year ?>" type="text" />
Member Avatar

You didn't specify what field you want from $row.

Change line 12 to

$temp_mydate = explode("-",$row[B]['Date'][/B]);

Zagga
(Just Zagga, I'm not a Sir just yet) :)

Member Avatar

Hi Zagga,

It works GREAT! Thank you! :) til' my next problem for PHP! =))

Edited by ekseks because: n/a
Member Avatar

Hi again,

Glad you got it working :)
On a side note, you don't need to keep calling if($temp_mydate[0]==01) as you have already assigned that value to $month, so you could just write if($month == 01)


Zagga

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.