Selecting year range

Mpe

Broken In
+----+--------------+
| 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...
 
OP
M

Mpe

Broken In
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...
 

krishnandu.sarkar

Simply a DIGITian
Staff member
Hmm...

SELECT * FROM tblStatus WHERE drilling_date between '1984-04-01' AND '1985-03-01' and status = 'success' GROUP BY drilling_date
 

asingh

Aspiring Novelist
^^
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
 

krishnandu.sarkar

Simply a DIGITian
Staff member
^^
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 :)
 
OP
M

Mpe

Broken In
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.. :)
 

krishnandu.sarkar

Simply a DIGITian
Staff member
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
 

krishnandu.sarkar

Simply a DIGITian
Staff member
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:
$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++;
}
 
OP
M

Mpe

Broken In
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"
 

krishnandu.sarkar

Simply a DIGITian
Staff member
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 : *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']));
 
Top Bottom