How to grab items from a DB by date

Question

This is my script:

$spending_period = time() - (30 * 24 * 60 * 60);
$spending_period = date('Y-m-d', $spending_period);
$monthly_income_query="SELECT amount FROM budget_items WHERE (date_code >= '$spending_period') && (type=='Income') ORDER BY date_code DESC";
$monthly_income_result=mysql_query($monthly_income_query);
while($monthly_income_scan=mysql_fetch_array($monthly_income_result)){
    if($montly_income_counter >=1){
     $monthly_income=$monthly_income + $monthly_income_scan['amount'];
     }
    }

I receive an error that mysql_fetch_array() is not a valid result resource.

The goal is to grab only items in the budget_items table that have a date_code (using the DATE type) occurring within the last 30 days.

Anyone have suggestions?

Answer 1

• If something doesn't work with your query - you may want to try it out in mysql console with some sample date.
• If data is returned, then try printing out a query. I have a hunch that $spending_period variable might not be interpolated correctly into your query string (try using '{$spending_period}' instead of '$spending_period'.

Answer 2

You need to format the date as a strong and use CAST inside the select statement to accept the value as a date value.