[SOLVED] Can someone tell me what's wrong?

[ask21900]

mysql Ver 12.22 Distrib 4.0.16

 

I have only dabbled in sql and php for several years, so please forgive my ignorance for stupid errors.

 

 

function addcommission(){
dbConnect();
$array = array();
$lastmonthbegin = strtotime( "-1 month", mktime( 12, 0, 0, date("m"), 15, date("Y") ) ) - 1252800;
$lastmonthend = strtotime("+1 month", $lastmonthbegin) - 1;
$query = "SELECT * , SUM(ClientPayments_Amount) AS TotSales FROM ClientPayments GROUP BY ClientPayments_ReceivedBy WHERE ClientPayments_Received='True' AND ClientPayments_Date between ".$lastmonthbegin." AND ".$lastmonthend." "; 
$result = mysql_query($query);
while($row = mysql_fetch_array($result)){
	//$array[] = $row;
	echo "Total ". $row['ClientPayments_ReceivedBy']. " = $". $row['SUM(ClientPayments_Amount)'];
	echo "<br />";
}
dbClose();
}

 

 

When executing the function i receive "Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource...".

 

With mysql_error() it says "You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE ClientPayments_Received='True' AND ClientPayments_Date be "

 

First, I would like to know what is wrong with the query.

 

Next, I would like to find out if there is another (easier) way of accomplishing what I need.  I am simply trying to create a function to run once every month that will find the total sales  in the previous month by each employee and place the employee id, total sales by that employee, and a commission value (based on a calculation of the total sales) to another table.

 

 

Thanks in advance for any help.

[AndyB]

Change

$result = mysql_query($query);

 

to

$result = mysql_query($query) or die("Error ". mysql_error(). " with query ". $query);

 

That should point you in the right direction.  If not, post what gets displayed by the error trap.

[ask21900]

The error is with the query:

 

SELECT * , SUM(ClientPayments_Amount) AS TotSales FROM ClientPayments GROUP BY ClientPayments_ReceivedBy WHERE ClientPayments_Received='True' AND ClientPayments_Date between '1180681200' AND '1183273199'

 

 

btw: with the function, I also tried mysql_fetch_assoc($result) with the same results.

 

It occurred to me that the error might be caused when 0 rows are returned.  I doubt it, but what do I know.  To fix this, I have now added a conditional statement based on the number of rows returned.

[fenway]

And what is the output from mysql_error()? Zero rows should still return a valid but empty record set.

[ask21900]

That's what i thought, but i had nothing better to do than add the statement.

[fenway]

That's what i thought, but i had nothing better to do than add the statement.

 

But you say you're getting an error... could you post it here?

[ask21900]

But you say you're getting an error... could you post it here?

 

The error msgs were in the original post:

 

When executing the function i receive "Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource...".

 

With mysql_error() it says "You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE ClientPayments_Received='True' AND ClientPayments_Date be "

[fenway]

Yeah, you have to have your group by clause after your where clause.

[ask21900]

That worked.  Thank you so much.  (I was also missing an ending quote)