Hi Friends,
I have a little problem, i want to get the distinct Date with only date, month and year without time. But when i am using that query:
"SELECT DISTINCT(lot_date) from auct_lots_full ORDER BY lot_date ASC";
It will give me distinct date but with time also because the time is also saved in my database.
e.g i have values
2009-07-31 00:00:00
2009-07-31 09:58:00
2009-07-31 09:59:00
2009-07-31 09:59:00
2009-07-31 10:00:00
2009-07-31 10:00:00
and my query give me the result
2009-07-31 00:00:00
2009-07-31 09:58:00
2009-07-31 09:59:00
2009-07-31 10:00:00
but i want the result
2009-07-31 (just distinct Date not with time)
Please give me the query.
Thanks and Regards
Query for Disinct Date not with time
Moderator: General Moderators
-
habib009pk
- Forum Commoner
- Posts: 43
- Joined: Sun Jul 05, 2009 11:28 pm
Re: Query for Disinct Date not with time
This is what you need:
http://dev.mysql.com/doc/refman/5.1/en/ ... ction_date
Take a look at all of the functions listed there
http://dev.mysql.com/doc/refman/5.1/en/ ... ction_date
Take a look at all of the functions listed there
There are 10 types of people in this world, those who understand binary and those who don't
-
habib009pk
- Forum Commoner
- Posts: 43
- Joined: Sun Jul 05, 2009 11:28 pm
Re: Query for Disinct Date not with time
Hi,
Thanks for helping me, from your given function i am using that code for my desired result
//Query to Get the Date from the table
mysql_select_db($database_kansai, $kansai);
$query_rsdate = "select distinct(DATE(lot_date)) from auct_lots_full";
$rsdate = mysql_query($query_rsdate, $kansai) or die(mysql_error());
$row_rsdate = mysql_fetch_assoc($rsdate);
echo $totalRows_rsdate = mysql_num_rows($rsdate); //it will give me the no of records 6 as i have the data of 6 days in my database.
Now after that for retrieval of data i am using using that code
<form action="japan_auction_manage.php" method="post" name="dateform" id="dateform">
<select name="date" id="date">
<option value="">Any</option>
<?php
do {
?>
<option value="<?php echo $row_rsdate['DATE(lot_date)'];?>" ><?php echo $row_rsdate['DATE(lot_date)'];?></option>
<?php
} while ($row_rsdate= mysql_fetch_assoc($rsdate));
?>
</select>
</form>
Now the problem is that as the above query gave 6 records so loop will be excuting 6 times
but no value has been printed in the option area.
So please help me what code has been written in the bold area of the select list object. so we can see the date in the option portion.
Thanks and Regards
Thanks for helping me, from your given function i am using that code for my desired result
//Query to Get the Date from the table
mysql_select_db($database_kansai, $kansai);
$query_rsdate = "select distinct(DATE(lot_date)) from auct_lots_full";
$rsdate = mysql_query($query_rsdate, $kansai) or die(mysql_error());
$row_rsdate = mysql_fetch_assoc($rsdate);
echo $totalRows_rsdate = mysql_num_rows($rsdate); //it will give me the no of records 6 as i have the data of 6 days in my database.
Now after that for retrieval of data i am using using that code
<form action="japan_auction_manage.php" method="post" name="dateform" id="dateform">
<select name="date" id="date">
<option value="">Any</option>
<?php
do {
?>
<option value="<?php echo $row_rsdate['DATE(lot_date)'];?>" ><?php echo $row_rsdate['DATE(lot_date)'];?></option>
<?php
} while ($row_rsdate= mysql_fetch_assoc($rsdate));
?>
</select>
</form>
Now the problem is that as the above query gave 6 records so loop will be excuting 6 times
but no value has been printed in the option area.
So please help me what code has been written in the bold area of the select list object. so we can see the date in the option portion.
Thanks and Regards
-
manojsemwal1
- Forum Contributor
- Posts: 217
- Joined: Mon Jun 29, 2009 4:13 am
- Location: India
Re: Query for Disinct Date not with time
in your query when u insert the date that time u just enter the date only like $date = date("d/m/y"); this method.
it will insert only date after that you can call your distinct query and when u want to display the result just use.
$AddDate=mysql_result($rs,$n,12);
$dateTime = new DateTime($AddDate);
$formatted_date=date_format ($dateTime, 'd-m-Y' );
the $formatted_date will be date month and year
i think it will help u
regards
Manoj
it will insert only date after that you can call your distinct query and when u want to display the result just use.
$AddDate=mysql_result($rs,$n,12);
$dateTime = new DateTime($AddDate);
$formatted_date=date_format ($dateTime, 'd-m-Y' );
the $formatted_date will be date month and year
i think it will help u
regards
Manoj
-
habib009pk
- Forum Commoner
- Posts: 43
- Joined: Sun Jul 05, 2009 11:28 pm
Re: Query for Disinct Date not with time
Hi,
Thanks of Help but is has been done and my result is perfect now
Regards
Thanks of Help but is has been done and my result is perfect now
Regards