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


# 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


  # 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


  # 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


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

Cloud Guru
4060 posts

Uber Geek

Trusted
Snowflake
Subscriber

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






25 posts

Geek


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

Im lost

Cloud Guru
4060 posts

Uber Geek

Trusted
Snowflake
Subscriber

  # 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







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!

 
 
 
 


6358 posts

Uber Geek

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



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?

6358 posts

Uber Geek

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



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

6358 posts

Uber Geek

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



25 posts

Geek


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

Yes there is stuff to display

Create new topic



Twitter and LinkedIn »



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:





News »

Major Japanese retailer partners with smart New Zealand technology IMAGR
Posted 14-Oct-2019 10:29


Ola pioneers one-time passcode feature to fight rideshare fraud
Posted 14-Oct-2019 10:24


Spark Sport new home of NZC matches from 2020
Posted 10-Oct-2019 09:59


Meet Nola, Noel Leeming's new digital employee
Posted 4-Oct-2019 08:07


Registrations for Sprout Accelerator open for 2020 season
Posted 4-Oct-2019 08:02


Teletrac Navman welcomes AI tech leader Jens Meggers as new President
Posted 4-Oct-2019 07:41


Vodafone makes voice of 4G (VoLTE) official
Posted 4-Oct-2019 07:36


2degrees Reaches Milestone of 100,000 Broadband Customers
Posted 1-Oct-2019 09:17


Nokia 1 Plus available in New Zealand from 2nd October
Posted 30-Sep-2019 17:46


Ola integrates Apple Pay as payment method in New Zealand
Posted 25-Sep-2019 09:51


Facebook Portal to land in New Zealand
Posted 19-Sep-2019 18:35


Amazon Studios announces New Zealand as location for its upcoming series based on The Lord of the Rings
Posted 18-Sep-2019 17:24


The Warehouse chooses Elasticsearch service
Posted 18-Sep-2019 13:55


Voyager upgrades core network to 100Gbit
Posted 18-Sep-2019 13:52


Streaming service Acorn TV launches in New Zealand with selection with British shows
Posted 18-Sep-2019 08:55



Geekzone Live »

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


Support Geekzone »

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.