 |
24-04-2011, 10:26 AM
|
#1 (permalink)
|
|
Right Off the Assembly Line
Join Date: Mar 2011
Posts: 11
|
Selecting year range
+----+--------------+
| id | drilling_date
+----+--------------+
| 1 | 1984-02-12
| 2 | 1987-03-08
| 3 | 1988-11-01
| 4 | 2005-06-30
| 5 | 2009-12-28
| 6 | 1984-03-10
+----+--------------+
how can i select all data that falls between March 1984 - April 1985, March 1985 - April 1986 and so on...
|
|
|
|
Advertisements. Register and be a member of the community to get rid of them.
|
|
Advertisement
|
|
24-04-2011, 10:32 AM
|
#2 (permalink)
|
|
Simply a DIGITian
Join Date: Nov 2007
Location: Kolkata
Posts: 2,942
|
Re: Selecting year range
Can you please elaborate it??
What language?? What it actually is??
__________________
- Read The Forum RULES First.
- Before PM'ing Or Asking Any Questions To Any Mod Read The FAQ's
- Before Starting A New Thread Read The STICKY THREADS First
- Before Participating In Bazaar Section Read The BAZAAR RULES
|
|
|
24-04-2011, 10:44 AM
|
#3 (permalink)
|
|
Right Off the Assembly Line
Join Date: Mar 2011
Posts: 11
|
Re: Selecting year range
Sorry..
Its MySQL
i have a table like this
+----+--------------+--------------+
| id | drilling_date | status /// some more columns here
+----+--------------+--------------+
| 1 | 1984-02-12 | success
| 2 | 1987-03-08 | un-success
| 3 | 1988-11-01 | success
| 4 | 2005-06-30 | success
| 5 | 2009-12-28 | un-success
| 6 | 1984-03-10 | success
+----+--------------+--------------+
how can i select all the data between April 1984 - March 1985 , April 1985 - March 1986 and so on....
and also how to select all success and un-success between this given periods
i also use PHP to do the Query...
|
|
|
24-04-2011, 10:53 AM
|
#4 (permalink)
|
|
Simply a DIGITian
Join Date: Nov 2007
Location: Kolkata
Posts: 2,942
|
Re: Selecting year range
Hmm...
SELECT * FROM tblStatus WHERE drilling_date between '1984-04-01' AND '1985-03-01' and status = 'success' GROUP BY drilling_date
__________________
- Read The Forum RULES First.
- Before PM'ing Or Asking Any Questions To Any Mod Read The FAQ's
- Before Starting A New Thread Read The STICKY THREADS First
- Before Participating In Bazaar Section Read The BAZAAR RULES
|
|
|
24-04-2011, 11:43 AM
|
#5 (permalink)
|
|
Super Moderator
Join Date: May 2008
Location: New Delhi
Posts: 5,548
|
Re: Selecting year range
^^
Would not the GROUP by remove his duplicates (if any) for the drilling_date field.
Guess if he wants the whole chunk:
SELECT * FROM tblStatus
WHERE drilling_date between '1984-04-01' AND '1985-03-01'
Give the counts:
SELECT COUNT(status) FROM tblStatus
WHERE drilling_date between '1984-04-01' AND '1985-03-01'
GROUP BY status
ORDER BY status
__________________
MSI P45 Platinum(BIOS v1.7B)|Q9550[E0]@3.85Ghz@1.320V[453x8.5]MCH@1.184V|ICH@1.55V|DDR_V_Ref_A_B@1.05V|NH-D14|Corsair TWIN2X4096-8500C5(5-5-5-15)@1089Mhz@2.14V
2xHD4890[Xfire]@1000/900[MEM/GPU]|Corsair 650TX|Seagate180GB+80GB+WD1TB|SONY-DVD-R|CM690|2x120mm Scythe Ultra Kaze|DELL S2409W|APC 1100VA|Scythe Kaze Server
Windows 7 Ultimate RTM - 64BIT|Catalyst 10.5 (8.14.10.0753) forced with RadeonPRO|PS3 160GB|Sony 40EX520|AC Ryan POHD Mini|APC 800VA|APC 800VA|D425KT|CM100 Elite|2TB WD|Acer D255
Test your spoiler tags before submitting
|
|
|
24-04-2011, 12:12 PM
|
#6 (permalink)
|
|
Simply a DIGITian
Join Date: Nov 2007
Location: Kolkata
Posts: 2,942
|
Re: Selecting year range
Quote:
Originally Posted by asingh
^^
Would not the GROUP by remove his duplicates (if any) for the drilling_date field.
Guess if he wants the whole chunk:
SELECT * FROM tblStatus
WHERE drilling_date between '1984-04-01' AND '1985-03-01'
Give the counts:
SELECT COUNT(status) FROM tblStatus
WHERE drilling_date between '1984-04-01' AND '1985-03-01'
GROUP BY status
ORDER BY status
|
Ya ya, you are right, thanks for correcting me
__________________
- Read The Forum RULES First.
- Before PM'ing Or Asking Any Questions To Any Mod Read The FAQ's
- Before Starting A New Thread Read The STICKY THREADS First
- Before Participating In Bazaar Section Read The BAZAAR RULES
|
|
|
24-04-2011, 01:48 PM
|
#7 (permalink)
|
|
Right Off the Assembly Line
Join Date: Mar 2011
Posts: 11
|
Re: Selecting year range
thanks a lot guys
just one more Q..
How can I have a php array like this
"April,89-March,90","April,90-March,91","April,91-March,92"
i really need it for my chart..
|
|
|
24-04-2011, 01:49 PM
|
#8 (permalink)
|
|
Simply a DIGITian
Join Date: Nov 2007
Location: Kolkata
Posts: 2,942
|
Re: Selecting year range
It's simple.
$arr = array("April,89-March,90","April,90-March,91","April,91-March,92");
Take a look for more reference : PHP: Arrays - Manual
__________________
- Read The Forum RULES First.
- Before PM'ing Or Asking Any Questions To Any Mod Read The FAQ's
- Before Starting A New Thread Read The STICKY THREADS First
- Before Participating In Bazaar Section Read The BAZAAR RULES
|
|
|
24-04-2011, 01:54 PM
|
#9 (permalink)
|
|
Right Off the Assembly Line
Join Date: Mar 2011
Posts: 11
|
Re: Selecting year range
Actually what i meant is getting the array from mysql the drilling_date column
|
|
|
24-04-2011, 02:09 PM
|
#10 (permalink)
|
|
Simply a DIGITian
Join Date: Nov 2007
Location: Kolkata
Posts: 2,942
|
Re: Selecting year range
You mean you want to put the dates from drilling_date column in an array??
In that case just run a loop and insert in to array.
Like
PHP Code:
$arr = array();
i=0;
$result = mysql_query("SELECT drilling_date from tblStatus", $link);
while($row = mysql_fetch_array($result))
{
$arr[i]=$row['drilling_date'];
i++;
}
__________________
- Read The Forum RULES First.
- Before PM'ing Or Asking Any Questions To Any Mod Read The FAQ's
- Before Starting A New Thread Read The STICKY THREADS First
- Before Participating In Bazaar Section Read The BAZAAR RULES
|
|
|
24-04-2011, 02:25 PM
|
#11 (permalink)
|
|
Right Off the Assembly Line
Join Date: Mar 2011
Posts: 11
|
Re: Selecting year range
I want to put the month,year like this in an array that is retrieve from mysql
"April,89-March,90","April,90-March,91","April,91-March,92"
|
|
|
24-04-2011, 07:06 PM
|
#12 (permalink)
|
|
Simply a DIGITian
Join Date: Nov 2007
Location: Kolkata
Posts: 2,942
|
Re: Selecting year range
Ok, not a big deal, after getting the date from mysql, use strtotime() to convert and date() to format it as per your need.
Take a look for the formats : http://www.eltcalendar.com/stuff/datemysqlphp.html
Eg.:
month name in full (January) = F
year, 2 digit (00-99) = y
date("F,y"strtotime($row['drilling_date']));
__________________
- Read The Forum RULES First.
- Before PM'ing Or Asking Any Questions To Any Mod Read The FAQ's
- Before Starting A New Thread Read The STICKY THREADS First
- Before Participating In Bazaar Section Read The BAZAAR RULES
|
|
|
25-04-2011, 10:16 AM
|
#13 (permalink)
|
|
Right Off the Assembly Line
Join Date: Mar 2011
Posts: 11
|
Re: Selecting year range
Thanks a lot..
Will try it out
|
|
|
25-04-2011, 09:29 PM
|
#14 (permalink)
|
|
Simply a DIGITian
Join Date: Nov 2007
Location: Kolkata
Posts: 2,942
|
Re: Selecting year range
You are welcome
Happy Coding
__________________
- Read The Forum RULES First.
- Before PM'ing Or Asking Any Questions To Any Mod Read The FAQ's
- Before Starting A New Thread Read The STICKY THREADS First
- Before Participating In Bazaar Section Read The BAZAAR RULES
|
|
|
| Thread Tools |
|
|
| Display Modes |
Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
|