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.


Anibor

25 posts

Geek


#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
DestRoYeDnz
52 posts

Master Geek


  #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





Anibor

25 posts

Geek


  #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

DestRoYeDnz
52 posts

Master Geek


  #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?



Regs
4066 posts

Uber Geek

Trusted
Snowflake

  #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!




Anibor

25 posts

Geek


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

Im lost

Regs
4066 posts

Uber Geek

Trusted
Snowflake

  #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





Anibor

25 posts

Geek


  #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!

 
 
 

Cloud spending continues to surge globally, but most organisations haven’t made the changes necessary to maximise the value and cost-efficiency benefits of their cloud investments. Download the whitepaper From Overspend to Advantage now.
nate
6473 posts

Uber Geek

Retired Mod
Trusted
Lifetime subscriber

  #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.

Anibor

25 posts

Geek


  #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?

nate
6473 posts

Uber Geek

Retired Mod
Trusted
Lifetime subscriber

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

Anibor

25 posts

Geek


  #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 :(

nate
6473 posts

Uber Geek

Retired Mod
Trusted
Lifetime subscriber

  #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?

Anibor

25 posts

Geek


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

Yes there is stuff to display

Create new topic





News and reviews »

Air New Zealand Starts AI adoption with OpenAI
Posted 24-Jul-2025 16:00


eero Pro 7 Review
Posted 23-Jul-2025 12:07


BeeStation Plus Review
Posted 21-Jul-2025 14:21


eero Unveils New Wi-Fi 7 Products in New Zealand
Posted 21-Jul-2025 00:01


WiZ Introduces HDMI Sync Box and other Light Devices
Posted 20-Jul-2025 17:32


RedShield Enhances DDoS and Bot Attack Protection
Posted 20-Jul-2025 17:26


Seagate Ships 30TB Drives
Posted 17-Jul-2025 11:24


Oclean AirPump A10 Water Flosser Review
Posted 13-Jul-2025 11:05


Samsung Galaxy Z Fold7: Raising the Bar for Smartphones
Posted 10-Jul-2025 02:01


Samsung Galaxy Z Flip7 Brings New Edge-To-Edge FlexWindow
Posted 10-Jul-2025 02:01


Epson Launches New AM-C550Z WorkForce Enterprise printer
Posted 9-Jul-2025 18:22


Samsung Releases Smart Monitor M9
Posted 9-Jul-2025 17:46


Nearly Half of Older Kiwis Still Write their Passwords on Paper
Posted 9-Jul-2025 08:42


D-Link 4G+ Cat6 Wi-Fi 6 DWR-933M Mobile Hotspot Review
Posted 1-Jul-2025 11:34


Oppo A5 Series Launches With New Levels of Durability
Posted 30-Jun-2025 10:15









Geekzone Live »

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



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.