jonnyfortis Posted August 29, 2014 Share Posted August 29, 2014 i have a table that shows payments made but want to the payments only showing from a set date(06/12/14) and before this date i dont want to show this is my sql that doesnt seem to work and is showing dates before the specified date. . "SELECT * FROM payments2014, signup2014, editprop2014 WHERE signup2014.userid = payments2014.payment_userid AND editprop2014.prop_id = signup2014.prop_id AND signup2014.userid !='page1' AND signup2014.userid !='page6' AND signup2014.userid !='page4' AND payments2014.payment_transaction_status !='none' AND payments2014.payment_transaction_status !='CANCELLEDa' AND payments2014.payment_type !='deposit' AND payments2014.payment_paid_timestamp NOT LIKE '%2012%' AND payments2014.payment_paid_timestamp NOT LIKE '%2011%' AND payments2014.payment_paid_timestamp >= '06/12/14' ORDER BY payments2014.payment_id DESC" i have some other parts in the statment but this one that should be filtering is host_payments2014.payment_paid_timestamp >= '06/12/14' thanks in advance Link to comment https://forums.phpfreaks.com/topic/290721-filter-date-to-show-dates-after-a-set-date/ Share on other sites More sharing options...
cyberRobot Posted August 29, 2014 Share Posted August 29, 2014 How is the date stored in the "host_payments2014.payment_paid_timestamp" field? It sounds like it's stored as a plain string instead of a date. More information about dates in MySQL can be found here: http://dev.mysql.com/doc/refman/5.0/en/datetime.html Link to comment https://forums.phpfreaks.com/topic/290721-filter-date-to-show-dates-after-a-set-date/#findComment-1489245 Share on other sites More sharing options...
jonnyfortis Posted August 29, 2014 Author Share Posted August 29, 2014 How is the date stored in the "host_payments2014.payment_paid_timestamp" field? It sounds like it's stored as a plain string instead of a date. More information about dates in MySQL can be found here: http://dev.mysql.com/doc/refman/5.0/en/datetime.html yes it currently is stored as VARCHAR, i need to change it to DATE but this issue i am having is the return date from the bank is MM/DD/YY and the date format is 0000-00-00 in phpMyAdmin and i not sure if the return date will populate the column correctly. Link to comment https://forums.phpfreaks.com/topic/290721-filter-date-to-show-dates-after-a-set-date/#findComment-1489246 Share on other sites More sharing options...
Barand Posted August 29, 2014 Share Posted August 29, 2014 For now you can use WHERE DATE_FORMAT(host_payments2014.payment_paid_timestamp, '%m/%d/%y') >= '2014-06-12' But you should change your table to use the correct type and format. You can use that same function to convert your dates. Also, don't use the "FROM X, Y, Z WHERE... " syntax. You should use explicit JOIN syntax ie FROM X INNER JOIN Y ON ... INNER JOIN Z ON ... Link to comment https://forums.phpfreaks.com/topic/290721-filter-date-to-show-dates-after-a-set-date/#findComment-1489248 Share on other sites More sharing options...
jonnyfortis Posted August 29, 2014 Author Share Posted August 29, 2014 For now you can use WHERE DATE_FORMAT(host_payments2014.payment_paid_timestamp, '%m/%d/%y') >= '2014-06-12' But you should change your table to use the correct type and format. You can use that same function to convert your dates. Also, don't use the "FROM X, Y, Z WHERE... " syntax. You should use explicit JOIN syntax ie FROM X INNER JOIN Y ON ... INNER JOIN Z ON ... i am trying WHERE DATE_FORMAT(host_payments2014.payment_paid_timestamp, '%m/%d/%y') >= '2014-06-12' but am getting an error from another part of the page. Warning: Division by zero in the line of code is. $additional1 = $row_rsPayment['payment_amount_paid'] / $row_rsPayment['rental_price']; // divide this by property week amount ; Link to comment https://forums.phpfreaks.com/topic/290721-filter-date-to-show-dates-after-a-set-date/#findComment-1489253 Share on other sites More sharing options...
cyberRobot Posted August 29, 2014 Share Posted August 29, 2014 but am getting an error from another part of the page. Warning: Division by zero in You'll need to add a test to make sure the denominator isn't zero before performing the equation. Perhaps you'll find a workable solution here: https://www.google.com/webhp#q=php%20division%20by%20zero Link to comment https://forums.phpfreaks.com/topic/290721-filter-date-to-show-dates-after-a-set-date/#findComment-1489255 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.