Hi there,
Im new with PHP/MYSQL and really need help from u guys.
I want to find a code on how to select date range from MYSQL using PHP. Example I want to find a data with the date range is between 12-10-2010 until 15-12-2010. Im using this format date DD-MM-YYYY.
Please help me because i've done searching using google and still can't find the solution. The only way i found is using this format YYYY-MM-DD.
Thank you for your help :)
Hi mrlol.. In mysql database, you can only store date in the format of yyyy-mm-dd. So no probs. Just get the date as usual (for (e.g) 12-10-2010 ). Then, explode it using the delimiter (-) by explode() function as [ explode("-",$date) ]... { $date has the value 12-10-2010 }. After exploded, then you need to implode by the same delimiter in the format of yyyy-mm-dd. By this, you can easily search through out database and get the datas as your wish...
I hope you can understand the concept.. If still you face the problem, kindly let me know....
Have a look at the BETWEEN function in MySQL.
You could try this, although I really don't know whether it will work in the format you're using. Worth a try though
("SELECT * FROM mytable WHERE date BETWEEN '12-10-2010' AND '12-10-2010'");You need to convert your date STRINGS to an actual date first - Try:
/* http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_str-to-date */
$startDate='12-10-2010';
$endDate='15-12-2010';
$sql= "SELECT * FROM `tblPerson` WHERE `subscriptionDate` BETWEEN
STR_TO_DATE('$startDate','%d-%m-%Y') AND STR_TO_DATE('$endDate','%d-%m-%Y')";
Try this:
<?php
//Initial dates in the format DD-MM-YYYY
$small_date = "12-10-2010";
$large_date = "15-10-2010";
//Reformat date
$date1 = explode("-", $small_date);
$date2 = explode("-", $large_date);
//Final dates in the format YYYY-MM-DD
$small_date = $date1[2]."-".$date1[1]."-".$date1[0];
$large_date = $date2[2]."-".$date2[1]."-".$date2[0];
echo "Small date : ".$small_date."<br>";//This prints 2010-10-12
echo "Large date : ".$large_date; //this prints 2010-10-15
$query = "SELECT * FROM yourtable WHERE date BETWEEN '$small_date' AND '$large_date'";
?>thanks for your kind help :)
I'm going to try your ideas after this because currently im using my friends laptop, but before that i would like to give more explanation and ask few questions regarding this matter... hope you guys can give more ideas and solutions...
actually I want to use this range date for searching purposes.. the database will be based on the data previously inserted by user and the date also will be captured and submitted to mysql using this format DD-MM-YYYY.. the database in mysql should be look like this
User Post | Date Posted
A 1-10-2010
B 8-10-2010
C 18-10-2010
D 23-10-2010
E 12-11-2010so in user searching page, I would like to make a form where user can search and view post by range of date (from xx to yy) using this format DD-MM-YYYY.. just a short question... can i use any of the solutions/ideas above to run this searching?
thank you for help :)
and also how to get data from year range and as usual using same date format as stated at my post above... let say user request to view data from 2005 until 2010... i want the output to be something like this:
| user | post title | year |
A ABC 2005
A HGJ 2006
B FKL 2007
C DMS 2010
C EHD 2010please guide me.. TQ
Here is one to do this
<?php
if(!isset($_POST['find']))
{
?>
<form method = "post" action = "<?php echo $_SERVER['PHP_SELF'];?>">
<table width = "450" align = "center">
<tr>
<td><b><i>Please enter date in the field below (smaller date first)</i></b></td>
</tr>
<tr>
<td>
From :
<input type = "text" name = "small">
To :
<input type = "text" name = "large"></td>
</tr>
<tr>
<td align = "center">
<input type = "submit" name = "find" value = "SEARCH">
<input type = "reset" value = "CLEAR FORM">
</td>
</tr>
</table>
</form>
<?php
}
else
{
$small = trim($_POST['small']);
$large = trim($_POST['large']);
$connection = mysql_pconnect("localhost", "root", "password") or die("Connection failed. ".myslq_error());
mysql_select_db("me") or die("Unable to select db. ".mysql_error());
//Add 1 to the upper range, $large, else it won't make it inclusive
$query = "SELECT * FROM mm WHERE date BETWEEN DATE_FORMAT(date,'%".$small."') AND DATE_FORMAT(date, '%".($large+1)."') ORDER BY date";
$result = mysql_query($query) or die(mysql_error());
echo "<table width = '500' align = 'center'>";
echo "<tr><b>";
echo "<td>USER</td>";
echo "<td>POST TITLE</td>";
echo "<td>DATE</td>";
echo "</b></tr>";
while($record = mysql_fetch_object($result))
{
echo "<tr>";
echo "<td>".$record->user."</td>";
echo "<td>".$record->post_title."</td>";
$year_part_of_date = explode('-', $record->date);
echo "<td>".$year_part_of_date[0]."</td>";
//if you want the full date replace the $year_part_of_date[0] with $record->date
echo "</tr>";
}
echo "</table>";
}
?>thank you very much cossay :)
one more simple question from me, what is wrong with this code below..
$timezone = +8;
$tarikh=gmdate("Y-m-d", time() + 3600*($timezone+date("I"))); //current date
$date1=$row['cag_freport_date']; //date from database
$date2=$tarikh;
$diff = abs(strtotime($date2) - strtotime($date1));
$years = floor($diff / (365*60*60*24));
$months = floor(($diff - $years * 365*60*60*24) / (30*60*60*24));
$days = floor(($diff - $years * 365*60*60*24 - $months*30*60*60*24)/ (60*60*24));
//cant run if statement below.. please read my explanation below.
if($days > 30){ echo" <img border=\"0\" src=\"images/alert.gif\" width=\"23\" height=\"22\">";}i just copy paste the coding above to minus date using this format date YYYY-MM-DD.. based on the code aboce, it will print days, month and year.. let say if the day is 36 days, it will convert to let say 5 days, 1month, 0year... my question is, how to make it just print 36 days instead of 5days and 1 month and so the if statement on the code above can run successfully...
Thank you.
Further convert the month into days and then add the result of the conversion to the days to get your 36 days. For example if I have 1 month, 5 day. To get all in days I will use the simple formula below:
days = (months * number_of_days_in month) + days
Follow this code.. You can get in number of days...
<?php
// Suppose your given date is 25/2/2010...
$month=2;
$date=25;
$year=2010;
// Here, you can get the number of days of specified month...
$day=date("t",mktime(0,0,0,$month,$date,$year));
// Already you have 1 month 5 days for 36 days... [(i.e) (31days + 5 days)].. Likewise, by the above calculation, we can have no. of days for the selected month.. then add the days what you have...
$newday = $day + $days;
?>Hope you can understand.. If still problem occurs, pls let me know..
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.