get month from database

[mraza]

Hi, I m trying to extract data by month from my date table.

my date format 2011-03-28

 

my query

$sql = "select * from table WHERE MONTH(date) = 03 " 

 

Now problem is i have many records in 3rd month but it only display the first row with above $sql. How can i see all month records.

Thanks

[Psycho]

Where is your code to display the results. The problem may be in that code. Also, do you really want to show all records from a specific month or do you really want to show the records from a specific MONTH & YEAR? That query above would get records from March of any year.

[mraza]

thanks for reply sir,

here is my code

$sql= $dbc->dbRunSql("select * from points WHERE MONTH(date) = '03' " );
$records = mysql_fetch_assoc($sql);
print_r($records);

i am doing a print_r and i see only one row

Array
(
    [id] => 1
    [userid] => 2
    [points] => 1000
    [date] => 2011-03-05
    [month] => 0
)

only above shows but i have more records in 03 Month.

Edit: i wants to show record from a month but like to know for year too. thanks

[Psycho]

Well, MONTH() will return the numeric value of the data. So, March would be 3 not "03". Try that. And,. why do you have a month field in the database?

[mraza]

Thanks, I tried with only '3' but still returns first row from database,

about month field i was thinking to add month separately if cant make it work this way.

[Psycho]

Well, I just tested it several different ways and it worked for me.

SELECT * FROM `scan_jobs` WHERE MONTH(`started`) = 3
SELECT * FROM `scan_jobs` WHERE MONTH(`started`) = '03'
SELECT * FROM `scan_jobs` WHERE MONTH(`started`) = "03"

 

The problem must be in your data. Is the field set as a date field and did you validate that the values you expect for those records is correct? if you have a page where you are entering the records you might have a bug that is not saving the dates correctly.

 

EDIT: Scratch that

 

You still need to run a mysql_fetch for EACH record. The first one will only get the first record!!!

 

$sql= $dbc->dbRunSql("select * from points WHERE MONTH(date) = '03' " );
while($record = mysql_fetch_assoc($sql))
{
    print_r($record);
}

[mraza]

Thank you again , exactly , now it shows all records .

Best Regards