|
PHP - Php & Mysql - Howto Select Month & Year From Unix Time
Hello. I have a mysql database with an 'entrytime' field which contains the unix timestamp the record was added.
How do I create a mysql query to select all records for a given month/year? For example, if I wanted to create a query to display all records for October 2010 how do I go about it? I am at a loss and can't find my answer via google. Thanks in advance for any help! Similar TutorialsWhat needs to be changed to make this work... Code: [Select] // Build query. $q2 = "UPDATE member SET logged_in=?, last_activity=time(), updated_on=NOW() WHERE id=? LIMIT 1"; Apparently PHP doesn't like time() Debbie i want to make a monthly report the user selects month from drop down and i must get the specified dates of that month from the DB I am using ajax to get the dates This topic has been moved to MySQL Help. http://www.phpfreaks.com/forums/index.php?topic=318996.0 Hello this code is supposed to sort by year and month, but it doesn't. It sort by year and months are random. Any suggestions? Many thanks,
$sortdata =[];
$namecolumn = array_column($data, '0');
foreach($data as $datecolumn){
$dateexplode = explode('/', $datecolumn[3]);
$sortdata[] = (isset($dateexplode[1])) ? $dateexplode[1] : ((isset($dateexplode[0])) ? $dateexplode[0] : '');
}
array_multisort($namecolumn, (($sorttype=='DESC') ? SORT_DESC : SORT_ASC), $sortdata, (($sorttype=='DESC') ? SORT_DESC : SORT_ASC), $data);
Edited May 13 by Barand code tags added Thanks to aleX_hill and RussellReal for the help so far. I'm trying to utilise the code before to find the next month. However, it needs to be used with the current year. So I altered the below <?php $nextMonth = date("m") + 1; $daysInNextMonth = date("t",mktime(0,0,0,$nextMonth)); echo $daysInNextMonth;?> to #NEXT MONTH $nextMonth = date("m") + 1; echo $nextMonth; $daysInNextMonth = date("t",mktime(0,0,0,$nextMonth)); echo $daysInNextMonth ; $year = date("o"); echo $year; I can then pass it into my url query like so: <a href="<?php echo $url_path; ?>after=<?php echo $year; ?>-<?php echo $nextMonth; ?>-01&before=<?php echo $year; ?>-<?php echo $nextMonth; ?>-<?php echo $daysInNextMonth; ?>">Next Month</a> Brilliant I thought, until it comes to December 2010, the next month needs to be January 2011. Now I'm bamboozled. How do I get around this one? Many thanks, your help is always appreciated! Hello, not sure if lack of sleep or just staring at code too long. This is what I have: Code: [Select] while($rowy = mysql_fetch_array( $resulty )) { mpdate = YYYY-MM-DD $date = $rowy['mpdate']; $year = explode('-', $date); $month = date("F", mktime(0, 0, 0, $year[1])); ?> <?php echo $year[0]; ?> <br> <?php echo $month; ?> <br> <?php echo $rowy['mptitle']; } ?> I figure if I use: if ($year[0] == date('Y"){ echo month and entry stuff } but I keep getting errors. What I am trying to do is sort everything out into year month and day ie: 2012 March Entry 1 Entry 2 February Entry 1 Entry 2 Entry 3 etc... This is what I am getting 2012 March Entry 1 2012 March Entry 2 2012 February Entry 1 etc... Is there a way to fix this? How to acheive it friends? i want my current codes to show results accouding to current month followed by year, the precious month goes to the page two.
would really appreciate if someone could help me acheive it
$tableName="records"; Edited by lovephp, 02 December 2014 - 11:50 AM. Hi Guys, Been a while since I've been on here, but I'm currently bashing my head against a wall trying to figure this one out. Basically I have a calendar that outputs Monday through Sunday, and the user clicks on a date and outputs it to the URL.... ie. ?day=$day&month=$month&year=$year. Now the next part I need to find out what day (0-6) say the 25th Feb 2011 will be wday 5... now how can I do this in code? And before anyone says why not output it to url, I cannot as the first week and last weeks use different code Cheers James right now i have my date system to say "X amount of seconds ago, X hours ago, X days ago, X years ago, How could i turn mktime() (1290874161) into a mm/dd/yyyy format? so it would say like, "Nov 27, 2010 at Hour:Minute" (whatever the hour/minute is) OK So I've got a datepicker that sends a date in d/m/y format. My DB stores the data in Unix Timestamp Which I can convert the date to with strtotime however this does the exact date & time. All I want is the actual day. I've spent hours trying to convert this with just the day with mixed results... Thanks. Hi, I've got a date picker on a form which puts data into a database in the YYYY-MM-DD format. Just wondering how I could also put the name of the month (extracted from that) as well as just the year into separate columns. Ie: To use the field race_date from the form to also fill the 'race_month' and 'race_year' columns in the database. This code obviously only fills the 'race_date' column so far: Code: [Select] global $_POST; $race_date = $_POST["race_date"] ; .... $query = "INSERT INTO 10k_races (race_date, race_month, race_year)" . "VALUES ( '$race_date', '$race_month', '$race_year')"; Hi,
I want to display my data in this format
format.JPG 54.52KB
0 downloads
Expense data i am getting like this
<td><?php
$in = "SELECT sales_invoice.invoice_id as INID, DATE_FORMAT(sales_invoice.date_invoiced,'%M') AS month, sales_invoice_line_items.invoice_id, SUM(sales_invoice_line_items.sub_total) AS Total, SUM(sales_invoice_line_items.tax_amount) AS total_tax FROM sales_invoice INNER JOIN sales_invoice_line_items ON sales_invoice.invoice_id=sales_invoice_line_items.invoice_id GROUP BY sales_invoice.invoice_id, DATE_FORMAT(sales_invoice.date_invoiced, '%Y-%m')";
$in1 = mysql_query($in) or die (mysql_error());
$total=0;
$tax =0;
while($income = mysql_fetch_array($in1))
{
$total +=$income['Total'];
$tax +=$income['total_tax'];
echo $total - $tax; }
?>
</td>
And Profit i am getting like this
<td><?php
$in = "SELECT purchase_invoice.invoice_id as INID, DATE_FORMAT(purchase_invoice.date_invoiced,'%M') AS month, purchase_invoice_line_items.invoice_id, SUM(purchase_invoice_line_items.sub_total) AS Total, SUM(purchase_invoice_line_items.tax_amount) AS total_tax FROM purchase_invoice INNER JOIN purchase_invoice_line_items ON purchase_invoice.invoice_id=purchase_invoice_line_items.invoice_id GROUP BY purchase_invoice.invoice_id, DATE_FORMAT(purchase_invoice.date_invoiced, '%Y-%m')";
$in1 = mysql_query($in) or die (mysql_error());
$total=0;
$tax =0;
while($income = mysql_fetch_array($in1))
{
$total +=$income['Total'];
$tax +=$income['total_tax'];
echo $total - $tax; }
?>
</td>
I dont know how to display data like this. Do i need to change the the table structure in my database? Please suggest
I have a file upload script that will eventually process a ton of files. I would like to upload them into sub-directories according to what year, month, and day they are uploaded.
A typical tree should look like this:
attachments/
--/2014
-----/January
--------/01
--------/02
--------/03 , etc.
-----/February
--------/01
--------/04
--------/09
--------/18
--------/20, etc
-----/March, etc
--/2015, etc.
So a file called image.jpg uploaded on 10/31/2014 would have a URL of attachments/2014/October/31/image.jpg. I understand that every time a file is uploaded, the script would have to detect through FTP whether or not folders for the year, month, and day exist, and if they don't create them. My problem is that I have no idea what the logic of this script would be. What order should I do things in? Is there a way to use maybe foreach to detect/create the folders? Any input would be appreciated.
After doing some reading im trying to make sure I understand how all this time stuff works with using sql I have a Session created for users that uses $_SESSION['login_time'] = time(); when I echo that it comes up as 1324245123. so the reason it looks like that is because that is what UNIX looks like? So thats why this code doesnt work if ($get1['date'] > strtotime('-1 minutes')) { because the date im getting from $get1 is a sql time stamp and the strtotime is UNIX and they dont match do I understand this right? Hello.. I'm a somewhat beginner in php, but i decided to create my own basic forums system with a content managing system. Right now I'm trying to figure out how to make a thread link be at the top of the list for the forum display page, when someone posts a response or creates a new thread. HELP MUCH APPRECIATED guys, please help i have table with datas like speed of vehicle, position e.t.c from morning 8 a.m. to 8 p.m. I need to get these details, but after every 15 minutes i.e. after selecting datas at 8 a.m. it shd select datas @ 8.15 a.m. then 8.30 a.m. hw can i write a mysql query for this ? or a PHP approach is appreciated This topic has been moved to MySQL Help. http://www.phpfreaks.com/forums/index.php?topic=310181.0 Code: [Select] for ($z=1;$z<=30*365; $z++) { $bal[date('Y-m-d', strtotime("+ ". $z ." days"))] = $balance;//the balance of loan here.; } As you can see Im working on loading the daily balance of a 30 year loan in an array. The code goes to hell in the year 2038. I have scoured the internet for help. I have gone to the wikipedia link and have looked at other posts but there isn't quite something that can help me. How can I load dates beyond 01-18-2038 into my array? I have heard of ways to do it with MySQL and the DATETIME() function, but that doesn't seem to make sense for what I am trying to accomplish. Please point me in the right direction. Thanks. I have to two variables $cronlast and $cronnow in my php code. I need to have the time 7am of previous day in $cronlast variable and 7am today in $cronnow variable. I need the time in UNIX timestamp format. So everyday when I print the variable I should have: $cronlast=7am Yesterday(in Unix Timestamp) $cronnow=7am Today(in Unix Timestamp) I would appreciate any help. |
|||||||