Geekzone: technology news, blogs, forums
Guest
Welcome Guest.
You haven't logged in yet. If you don't have an account you can register now.




25 posts

Geek


Topic # 70235 20-Oct-2010 13:02
Send private message

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!

Create new topic
51 posts

Master Geek


  Reply # 393980 20-Oct-2010 13:47
Send private message

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





25 posts

Geek


  Reply # 393988 20-Oct-2010 14:02
Send private message

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

 
 
 
 


51 posts

Master Geek


  Reply # 393994 20-Oct-2010 14:11
Send private message

Change

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

to

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

what sql error do you got?

Infrastructure Geek
4057 posts

Uber Geek
+1 received by user: 195

Trusted
Microsoft NZ
Subscriber

  Reply # 393997 20-Oct-2010 14:30
Send private message

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




25 posts

Geek


  Reply # 394004 20-Oct-2010 14:51
Send private message

Im lost

Infrastructure Geek
4057 posts

Uber Geek
+1 received by user: 195

Trusted
Microsoft NZ
Subscriber

  Reply # 394021 20-Oct-2010 15:20
Send private message

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




25 posts

Geek


  Reply # 396423 27-Oct-2010 18:19
Send private message

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!

6329 posts

Uber Geek
+1 received by user: 391

Moderator
Trusted
Lifetime subscriber

  Reply # 396462 27-Oct-2010 19:37
Send private message

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.



25 posts

Geek


  Reply # 396839 28-Oct-2010 14:12
Send private message

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?

6329 posts

Uber Geek
+1 received by user: 391

Moderator
Trusted
Lifetime subscriber

  Reply # 396878 28-Oct-2010 15:28
Send private message

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);



25 posts

Geek


  Reply # 399008 2-Nov-2010 10:26
Send private message

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

6329 posts

Uber Geek
+1 received by user: 391

Moderator
Trusted
Lifetime subscriber

  Reply # 399252 2-Nov-2010 17:17
Send private message

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?



25 posts

Geek


  Reply # 399262 2-Nov-2010 17:47
Send private message

Yes there is stuff to display

Create new topic



Twitter »

Follow us to receive Twitter updates when new discussions are posted in our forums:



Follow us to receive Twitter updates when news items and blogs are posted in our frontpage:



Follow us to receive Twitter updates when tech item prices are listed in our price comparison site:



Geekzone Live »

Try automatic live updates from Geekzone directly in your browser, without refreshing the page, with Geekzone Live now.


Geekzone Live »

Our community of supporters help make Geekzone possible. Click the button below to join them.

Support Geezone on PressPatron



Are you subscribed to our RSS feed? You can download the latest headlines and summaries from our stories directly to your computer or smartphone by using a feed reader.

Alternatively, you can receive a daily email with Geekzone updates.