Events / News Section - PHP/MySQL Question

Well, Your structure is good.. You can use month function to get the month.

select * from table where month(datecolumn)="06"

If you want to narrow your search and get both year and month,

select * from table where year(datecolumn)="2008" and month(datecolumn)="06";

And, date is a keyword. Its better not to use date as a column name. If you use it, you have to refer the column as `date` in your queries. :)
Cheers,
Nav

Edit: If you don't want to separate it in mysql, you can also do it in php. You can use explode function (if you are storing the date in a different format) or date function.
You are absolutely right about getting the latest 5 stories. But a small change though. It should be desc instead of asc.

Well, Your structure is good.. You can use month function to get the month.

select * from table where month(datecolumn)="06"

If you want to narrow your search and get both year and month,

select * from table where year(datecolumn)="2008" and month(datecolumn)="06";

And, date is a keyword. Its better not to use date as a column name. If you use it, you have to refer the column as `date` in your queries. :)
Cheers,
Nav

Edit: If you don't want to separate it in mysql, you can also do it in php. You can use explode function (if you are storing the date in a different format) or date function.
You are absolutely right about getting the latest 5 stories. But a small change though. It should be desc instead of asc.

Ahhhh my PHP tutor returns, I hope your well friend!

Ok so in theory, could I set a variable to get the current date, then use explode (I have no knowledge of this function yet, could you give me an example of it?) to extract the month, and then pass that into the SQL query, so it would always be selecting the current month?

I was also thinking about how I would separate the SQL, because the client wants news stories to be filtered under each month heading, so would that be twelve different SQL queries using:

select * from table where year(datecolumn)="2008" and month(datecolumn)="06";
select * from table where year(datecolumn)="2008" and month(datecolumn)="07";
select * from table where year(datecolumn)="2008" and month(datecolumn)="08";

or could I grab all news from all months in one SQL statement and then somehow sift through them? I'm just not sure if 12 SQL calls is frowned upon, or even needed!

Thanks,

Anthony

I am doing great! Thanks..

could I set a variable to get the current date, then use explode (I have no knowledge of this function yet, could you give me an example of it?) to extract the month, and then pass that into the SQL query, so it would always be selecting the current month?

You can simply use, $month = date("m"); to get the current month.
Well, If you want to get the stories for each month, then yep, you have to 'talk to the database' 12 times.
You can use a loop and write a query in that loop.

for($i=1;$i<13;$i++) {
$query = "select * from table where month(datecolumn)='$i'";
//do something
}

If you want to select all the records, you can do it that way too. But then, you have to do alot of processing to do.

$query = "select * from table";
$result = mysql_query($query);
while($row = mysql_fetch_array($result)) {
 $datefield = $row['datecolumn'];
//say date is stored as 24-06-2008
list($date,$month,$year) = explode("-",$datefield);
if($month == "06") { //or whatever month
//do something
}
}

Well, its upto you to decide which one is better. I would prefer the 1st method though.
P.S. I have shown the working of explode too!

hi everybody i am a new user to php and mysql. i want to fetch a column of data from a db and show it in a list menu i have designed in a website model.
i am not able to connect to mysql bur php works in my system. what should i do for it?

thanks