Beyond Frustrated!!!

[roxie]

Hello Boys,

 

I'm having a hard time figuring out a query and it's more frustrating than sex with my last bf Okay here is the deal I have a database tables.

 

Sales

employeeID ~ Which of course identifies the employee

leadID ~ This is to identify any sort of target marketing

product ~ This is the product that was purchased.

refunded ~ If any money was refunded on the order

payment ~ This is how much money was collected.

 

Leads

leadID ~ Connects to Sales table

count ~ this is how many people inquired about

 

Employees

employeeID

firstName

lastName

 

So the query is ran on 1-?? product that are picked from a form with check boxes.

 

Name Leads Sales Refunds Net Earnings Per Lead

 

Can some one help a poor girl out? Pretty please with a kiss on top?

 

Spanx,

Roxie

 

[QuickOldCar]

Sorry to hear about your last boyfriend.

 

If you can post any relevant code, I'm sure someone would help you out.

[xtremey_ytinasni]

Depends on what you want to do. Are you trying to get the values in SALES for any employees with a matched ID?

[trq]

Your forgot to describe the actual issue your having.

[Muddy_Funster]

Your forgot to describe the actual issue your having.

Or at least the one that we can help with 

[roxie]

Sorry Boys for not giving enough information about the issues OK here is the SQL

 

SELECT sales.employeeID, SUM(sales.refunded) as refund_total, COUNT(sales.refunded) as refunds,

SUM(sales.payment) as net, COUNT(sales.payment) as sales,

                COUNT(sales.purchased) as total_sales, sales.leadID,

                leads.leadID, leads.count,

                employees.employeeID, employees.firstName, employees.lastName

FROM sales, employees, leads

WHERE sales.purchased BETWEEN ('2011/01/01', 'yyyy/mm/dd') AND ('2011/12/31', 'yyyy/mm/dd')

AND employees.employeeID = sales.employeeID

AND leads.leadID = sales.leadID

AND sales.productID = 1 OR sales.productID = 2

GROUP BY sales.affiliate

ORDER BY sales

 

I actually have a few issues:

 

[*]This query fails and I get the following error; Database query failed: Operand should contain 1 column(s)

[*]I can't figure out how to dynamically create the query to look for all of the checkboxes marked productID

 

Spanx for your quick replies,

Roxie

[kickstart]

Hi

 

Several errors.

 

First error is that you have put  ('2011/01/01', 'yyyy/mm/dd') AND ('2011/12/31', 'yyyy/mm/dd'), which is meaningless. It will probably work with just the dates specified without trying to put the format, or if you had used an odd format then you would need to convert it to a date.

 

Further you appear to be trying to order by the complete sales table rather than any columns.

 

Also you have used a GROUP BY on a field that you do not appear to be returning, while ignoring all the non aggregate fields that you are returning.

 

To get a response for all of a list of products, then assuming that the products are in an array you could use AND sales.productID IN (".explode(',',$productsArray.")

 

Without testing here is a brief attempt at what I think you want

 

SELECT sales.employeeID,  sales.leadID,  leads.leadID,  leads.count,  employees.employeeID,  employees.firstName,  employees.lastName,
SUM(sales.refunded) as refund_total,  COUNT(sales.refunded) as refunds,  SUM(sales.payment) as net,  COUNT(sales.payment) as sales,  COUNT(sales.purchased) as total_sales
FROM   sales
INNER JOIN employees ON employees.employeeID = sales.employeeID
INNER JOIN leads ON leads.leadID = sales.leadID
WHERE sales.purchased BETWEEN 2011/01/01' AND '2011/12/31'
AND sales.productID IN(1, 2)
GROUP BY sales.employeeID, sales.leadID, leads.leadID, leads.count, employees.employeeID, employees.firstName, employees.lastName,
ORDER BY employees.lastName, employees.firstName

 

All the best

 

Keith

[roxie]

Keith,

 

Spanx hon!!! I still have an issue though but it isn't with the code but rather the mySQL db table. When I try to run even the simplest query (SELECT * FROM employees) against the employees table I get this:

 

Warning: mysql_fetch_assoc() expects parameter 1 to be resource, boolean given in C:\wamp\www\sitefolder\admin\dsp_custom-report.php on line 27

 

Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given in C:\wamp\www\sitefolder\admin\dsp_custom-report.php on line 28

 

Any clue why I wouldn't be able to query the table? I even cut and pasted the table name from PHPMyAdmin incase one of them were misspelled.

 

Spanx,

Roxie

[Maq]

1) MySQL is case-sensitive, so your simple query would be "SELECT * FROM Employees".

2) Looks like your query is failing.  Use mysql_error() in your PHP code to figure out exactly what is causing it.

 

From a glance, you're missing a starting single quote here:

BETWEEN 2011/01/01'

[roxie]

Maq,

 

Spanx darling

 

Ok now one last thing and I think this thing will work

 

Alright here is what I need to do:

 

I need to figure the gross sales, refunded amount and net sales.

 

I thought the database field "sales.refunded" was the amount refunded but it is only a boolean. So the the SUM and COUNT are the same. The amount refunded is the same as "sales.payment" I am completely at a loss here please help.

[roxie]

Never mind I got it working