How to find dates in my database?

Forums › IT Pro and developers › How to find dates in my database?

Anibor

25 posts

Geek

Topic # 70235 20-Oct-2010 13:02

OK so i want to make a loop that counts how many entry's there are in my database between two dates (within a month) i have a field called timestamp (which automatically marks when you create a new entry) which is saving d-m-y hh:mm:ss but i only really need the d-m-y part of it. so that one thing i need help with.

So i need to know how to write a loop statement that will only read the entery from this current month
\
$offset = strtotime("-1 month");
$betweenMonth1 = date("d-m-y", $offset);
$betweenMonth2 = date("d-m-y");

Ive got this which will be my two BETWEEN variables i just dont know how to write the code.
Inside will be a count variable, so everytime that loop runs the count will increase and the final number will be the amount of entery for the month.

I am coding this with php and a mysql database.

PLEASE HELP ME WITH CODE!

DestRoYeDnz

51 posts

Master Geek

Reply # 393980 20-Oct-2010 13:47

You could try

$offset = strtotime("-1 month");
$betweenMonth1 = date("d-m-Y", $offset);
$betweenMonth2 = date("d-m-Y");
$string = "SELECT * FROM table WHERE timestamp BETWEEN ".$betweenMonth1." AND ".$betweenMonth2;

if($query = mysql_query($string)){
$count = mysql_num_rows($query);
}

$count should be the number of rows returned

Anibor

25 posts

Geek

Reply # 393988 20-Oct-2010 14:02

Just realised that month -1 will just take me to this time last month, where i actually need it to be the beginning of the month till now. How do i do that?

And the BETWEEN in my select statement isnt running

DestRoYeDnz

51 posts

Master Geek

Reply # 393994 20-Oct-2010 14:11

Change

$betweenMonth1 = date("d-m-y", $offset);

to

$betweenMonth1 = date("01-m-y", $offset);

what sql error do you got?

Regs

Infrastructure Geek
4057 posts

Uber Geek
+1 received by user: 195

Trusted

Microsoft NZ

Subscriber

Reply # 393997 20-Oct-2010 14:30

Anibor: OK so i want to make a loop that counts how many entry's there are in my database between two dates (within a month) i have a field called timestamp (which automatically marks when you create a new entry) which is saving d-m-y hh:mm:ss but i only really need the d-m-y part of it. so that one thing i need help with.

let me stop you right there.... SQL is a set based query tool. It is optimised for returning sets of data quickly. you should use sql queries and aggregations as much as possible and avoid costly loops!

e.g.

select count (rowid)
from table
where timestamp between STARTTIME and ENDTIME

something like that should execute in miliseconds, whereas a LOOP will take ages!

Technical Evangelist
Microsoft NZ
about.me/nzregs
Twitter: @nzregs

Anibor

25 posts

Geek

Reply # 394004 20-Oct-2010 14:51

Im lost

Regs

Infrastructure Geek
4057 posts

Uber Geek
+1 received by user: 195

Trusted

Microsoft NZ

Subscriber

Reply # 394021 20-Oct-2010 15:20

Anibor: Im lost

basically i'm saying you dont need a loop to count records. just a single select query that returns your count as the result.

see the following for a simple count tutorial:
http://www.plus2net.com/sql_tutorial/sql_count.php

then add the BETWEEN clause in:
http://www.plus2net.com/sql_tutorial/between-date.php
or
http://www.plus2net.com/sql_tutorial/date-lastweek.php

Technical Evangelist
Microsoft NZ
about.me/nzregs
Twitter: @nzregs

Anibor

25 posts

Geek

Reply # 396423 27-Oct-2010 18:19

So i did this:
SELECT count(*) as totalQuotes FROM quotes
and that didnt work so i did this:
SELECT count(*) as $totalQuotes FROM quotes
and that didnt work, it kept returning an error on the table but i know that i have connected to the database

so i did this:
$totalQuotes= mysql_query("SELECT count(*) as totalQuotes FROM quotes") or die(mysql_error());

That is returning Resource id #4
which is better than an error right?

Please keep trying to help me!

nate

6329 posts

Uber Geek
+1 received by user: 391

Moderator

Trusted

Lifetime subscriber

Reply # 396462 27-Oct-2010 19:37

Getting resource id #4 is good, it means php was able to connect to and get the count.

Now to use this, you need to do something like:

$count = mysql_result($totalQuotes, 0, 0);
print $count;

That should give you the count you are after.

Anibor

25 posts

Geek

Reply # 396839 28-Oct-2010 14:12

Ok so i got the count all the quotes working!!! THANK YOU!!! but now i gotta get into the between statements:

//Calculate all quotes in the database
$a= mysql_query("SELECT count(*) as totalQuotes FROM quotes") or die(mysql_error());
$totalQuotes = mysql_result($a, 0, 0);
//WORKING

//Calculate this month this year
$offset = strtotime("-1 month");
$betweenMonth1 = date("d-m-y", $offset);
$betweenMonth2 = date("d-m-y");
echo "$betweenMonth1 $betweenMonth2";
$b= mysql_query("SELECT count(*) as totalQuotes FROM quotes WHERE TimeStamp BETWEEN $betweenMonth1 AND $betweenMonth2") or die(mysql_error());
$ccQuote = mysql_result($b, 0, 0);
//Not working...

This second part is returning 0 and im wondering if thats because the variable timestamp in my database is "timestamp" and not "date" could that be an issue? and if so what do i do?

nate

6329 posts

Uber Geek
+1 received by user: 391

Moderator

Trusted

Lifetime subscriber

Reply # 396878 28-Oct-2010 15:28

From what I understand, the timestamp field will change when you insert/update a record. If this is not what you're after, you will need to change it to the datetime type (or similar)

edit: I would also change the format you are passing your dates in as:

$betweenMonth1 = date("Y-m-d", $offset);

Anibor

25 posts

Geek

Reply # 399008 2-Nov-2010 10:26

The timestamp is exactly what i what yes. But that time formate hasnt changed a thing :(

nate

6329 posts

Uber Geek
+1 received by user: 391

Moderator

Trusted

Lifetime subscriber

Reply # 399252 2-Nov-2010 17:17

Anibor: The timestamp is exactly what i what yes. But that time formate hasnt changed a thing :(

Have you got any data that qualifies between the two date/times you've specified?

Anibor

25 posts

Geek

Reply # 399262 2-Nov-2010 17:47

Yes there is stuff to display