Events / News Section - PHP/MySQL Question

[antwan1986]

Hi everyone, and thanks for reading. I've a client who will be wanting an events/news section for their website and I have a question about handling this information from a database.

Lets say that the table consisted of:

story_id    -     To make each story unique.
title       -     The title of the story.
story       -     The actual bulk of the story.
date        -     The date of the story.

I'm wondering how I would go about categorising each of these statements into specific months, I was thinking that instead of having a date field, I'd have a month field with data like "June" or "July", and then use 12 SQL statements to select all from a specific month, but then that isn't reusable when it comes to more than one year (the stories from different years will mix).

So if I was to use a date field i.e. 24/06/08 for each story, then how would I use PHP to separate them into specific months? I know that if I use MySQL to sort them, it'll put them into date order but what I'd want to do is separate them into months.

Also, for things like "the latest five stories" would that be:

"SELECT * FROM table ORDER BY date ASC LIMIT 5"

Thanks very much,

Anthony

[nav33n]

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.

[antwan1986]

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

[nav33n]

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!

[ferminselvin]

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