donation tracker script editing

[docj]

Hi all,

 

I'm very novice with scripting and would like some assistance editing a 3rd party script.  In the script, I've found the section that calls up data from sql to get a total amount of donations.  What I'd like to change is to have it only retrieve the current month's donations.  Here's that script:

 

function getTotalAmount()

{

$qty = 0;

$link = $this->_openConnection();

$query = "SELECT SUM(amount) FROM ".$this->mysql_tableprefix."payments

WHERE type = 'web_accept' AND item LIKE '%donation%'";

$result = mysql_query($query) or die("Invalid query: " . mysql_error());

$qty = mysql_result($result, 0);

mysql_free_result($result);

$this->_closeConnection($link);

return $qty;

}

 

The table contains date information, as noted elsewhere in the script:    pdate varchar(127) collate utf8_unicode_ci default NULL,

 

I've tried inserting some scripting but did not work as I'd like.  Any help is greatly appreciated.  Oh, btw I did contact the author of the script who advised I seek help on internet in editing it, as the original script is provided as is.

 

[attachment deleted by admin]

[Pikachu2000]

This would be much simpler if the date had been properly stored in a DATE or DATETIME type field, but in what format is the date currently stored?

[docj]

Thanks for the assistance.  I pulled the database info, and it looks like 2 tables are created:  paymentdetails & payments.  I'm not sure what format pdate data is in within payments table.  That may be part of the issue with trying to modify this script.  Perhaps it would be easier to change the script to select data from the other table if it is possible to select payment date & amounts from it?  Any suggestions would be appreciated.  I've attached the table data in pdf, pages 1-10 are details table, and 11-12 are payments table.

 

[attachment deleted by admin]

[docj]

Update:  I think the pdate data is actually unix time stamp format, from what I've googled.  Is there a way to easily deal with that in PHP?

[docj]

I think if there is a way to insert something in here: 

 

$query = "SELECT SUM(amount) FROM ".$this->mysql_tableprefix."payments

      WHERE type = 'web_accept' AND item LIKE '%donation%'";

 

such as "AND pdate = " and be able to specify unix time stamp interval of this month or last 30 days, that would select just that data, correct?  Is there php code for using unix time in such a way?  (I'm googling, trying to find the answers).  Thanks.

[Pikachu2000]

There are a couple ways to handle it, depending on how much control you have over the script. The best way would be to build the query string dynamically, so you can choose the time period that gets reported. Is that a possibility for you?

[docj]

As far as control over the script, I can change it to whatever I need.  I'm not quite sure of the syntax and best way to accomplish the desired effect though.  I tried this:

 

$query = "SELECT SUM(amount) FROM ".$this->mysql_tableprefix."payments

WHERE type = 'web_accept' AND item LIKE '%donation%' AND pdate >= '(time() - 2419200)'";

 

but that doesn't work correctly.  Suggestions?

[Pikachu2000]

I didn't test these, since I don't have your database specs, but . . .

 

This should do it for current month records:

$query = "SELECT SUM(amount) FROM ".$this->mysql_tableprefix."payments WHERE type = 'web_accept' AND item LIKE '%donation%' AND ( FROM_UNIXTIME(`pdate`, '%m') = DATE_FORMAT(CURDATE(), '%m') )";

 

For the previous 30 days:

"SELECT SUM(amount) FROM ".$this->mysql_tableprefix."payments WHERE type = 'web_accept' AND item LIKE '%donation%' AND (`pdate` >= (UNIX_TIMESTAMP() - (30 *24 * 60 * 60 ))";

 

If you want to make it dynamic, just say so. It shouldn't be terribly difficult (famous last words, LOL).

[docj]

Thanks a lot!  I'll give those a try!