Sorting data by date

Senate

One of the columns in my database has the date I added the entry, in the format YYYY-MM-DD.

What's the easiest and neatest way to pull out all entries from a specific month?

One of the other fields is an integer.. an amount.. and I want to pull the total for the current month out.

I am doing the total at the moment with a mysql query like so:

SELECT client_ip, sum(data_usage) as 'datatotal'

and it works fine, but pulls the ENTIRE total out. I now want an option for just the current month.

cgraz

Hmm, You could get the dates to return just January, February, March, etc by using

$query = mysql_query("SELECT DATE_FORMAT(date_d, '%M') AS date FROM yourtable WHERE id='$id'");

where date_d is your field name.

I'm not sure if this would work but maybe something like

$query = mysql_query("SELECT * FROM yourtable WHERE DATE_FORMAT(date_d, '%M') = 'January'");

Let me know how that works. I'm curious to know if that would work or how it would need to be modified to work.

Cgraz

Senate

Hrm can't get that to work no matter how much I play with it.

It would be possible to use that to extract the month that a particular entry was added, but you can't seem to use it in a WHERE clause.

Thanks for helping though 🙂 Any other ideas? I'm sick as hell today and brain isn't working.. but have to get this done. 🙁

cgraz

Senate, just tested the following code and got it to work just fine

<?

include("config.php");

// Connect To MySQL Server
@mysql_connect($server, $username, $password) or die("Couldn't Connect to Database");

// Select Database
@mysql_select_db($database) or die("Couldn't Select Database");

$query = mysql_query("SELECT * FROM $table WHERE DATE_FORMAT(inputedon, '%M') = 'May'");

while($a_row=mysql_fetch_array($query)) {

   echo "id: " . $a_row["id"] . "<br>\n";

}

?>

inputedon was the name of my timestamp field. That echo'd the id's of all the records that were inputted in May. Try it again and let me know how it goes.

On the query I posted earlier, did you replace yourtable with the name of your table, and date_d with the field name of your timestamp in the database, and format it to your needs? What exactly didn't work? Wouldn't output anything or parse error or. . . ?

Also make sure the = 'May' part is equal to a month you know is in the database. If you don't have anything in there for January, then don't do = 'January'

Well the above code is tested and works great. Let me know if you run into any problems.

Cgraz

Senate

Hrm.. i found my problem.. i'm too eager to get this finished!

I should've tested the basics first, instead I just plugged it into my script.

Yes, you're right.. your code works brilliantly..

It doesn't however work within my script since my SELECT call is using sum(data.. !!! STOP PRESS !!!

While reading the code to show you what I have in there, I just noticed a VERY stupid little parse error.

Damn i'm an idiot.

It now works fine.. mate, you're a legend. Sorry about my brain today.. i'm normally quite competent with PHP but i'm really really sick after a big weekend.. so I can't think straight at all.

Thanks again. 🙂

cgraz

No problem, glad you got it working.

Cgraz