Selecting year range

[Mpe]

+----+--------------+
| 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...

 

[krishnandu.sarkar]

Can you please elaborate it??

What language?? What it actually is??

 

[Mpe]

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]

Hmm...

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

 

[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

 

[krishnandu.sarkar]

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

 

[Mpe]

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]

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

 

[Mpe]

Actually what i meant is getting the array from mysql the drilling_date column

 

[krishnandu.sarkar]

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

$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++;
}

 

[Mpe]

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]

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']));

 

[Mpe]

Thanks a lot..

Will try it out

 

[krishnandu.sarkar]

You are welcome

Happy Coding