Mysql database query help

[bambinou1980]

Hello,

 

 

I have a mistake in my query but cannot work out where:

...Any idea where my mistake is please?
I am not trying to list in a table all the resellers commissions by adding together reseller ids(GROUP BY id but SUM resellers_earnings and by date range.

SELECT             o.id,
                          o.reseller_earnings,
                          o.order_status,
                          r.reseller_name,
                          r.reseller_surname,
                          r.reseller_email,
                          r.reseller_phone,
                          r.reseller_commission,
                          r.reseller_vat_number,
                         r.resellers_id
SUM(o.reseller_earnings) as total
FROM orders    as o,
     resellers     as r
WHERE o.resellers_id = r.id
AND WHERE due_date BETWEEN '2014-08-14' AND '2015-08-14'
AND    o.order_status LIKE '%Order Completed%'
GROUP BY resellers_id ORDER BY total DESC";

My sql gives me:

Error

SQL query: Documentation


SELECT             o.id,
                          o.reseller_earnings,
                          o.order_status,
                          r.reseller_name,
                          r.reseller_surname,
                          r.reseller_email,
                          r.reseller_phone,
                          r.reseller_commission,
                          r.reseller_vat_number,
                       r.resellers_id
SUM(o.reseller_earnings) as total
FROM orders    as o,
     resellers     as r
WHERE o.resellers_id = r.id
AND WHERE due_date BETWEEN '2014-08-14' AND '2015-08-14'
AND    o.order_status LIKE '%Order Completed%'
GROUP BY resellers_id ORDER BY total DESC";
GROUP BY resellers_id ORDER BY total DESC"
 LIMIT 0, 25



MySQL said: Documentation
 #1064 - 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 'SUM(o.reseller_earnings) as total
 FROM orders    as o,
      resellers     as r
 W' at line 11

[Barand]

you are missing the comma before SUM and you have too many "WHERE"s.

 

PS It is more useful to tell us what you are trying to do instead of what you are not trying to do

Edited August 21, 2015 by Barand

[bambinou1980]

Hi Barand,

 

Yes sorry you are right.

What I am trying to achieve is this:

 

I have a table where I would like to show the reseller information as:

id Name Surname Commission Email Phone Earnings

 

 

In the order, of the above table headers, here are the rows:

 

resellers_id || reseller_name || Reseller_surname || reseller_commission || reseller_email || reseller_phone || reseller_earnings

 

Also, in that table I owuld like to add all the SUM of the reseller_earnings per resellers_id

 

Finally I would like to choose a date range based on "due_date" of the orders table.

 

 

 

 

Thank you so much as usual Barand!

 

 

Ben

[Psycho]

A few thoughts.

 

I create my queries in a logical order based on how you want the data. In this case you are gathering data about resellers with the earnings being secondary. So, I would select from the resellers table first. It would work with what you have, but it makes more sense to me to switch it.

 

Second, what if there are not records in the orders table for a particular reseller. Using a normal JOIN, as you have above, that reseller would not be included in the results. If you want ALL the resellers to be displayed (even if there are no order records to JOIN on), then you need to use a LEFT/RIGHT join. You would use a RIGHT JOIN with the order you have above, but it would be a LEFT JOIN if done in the reverse order (which makes more logical sense to me).

 

Third, you are including other fields in the order table (besides the earnings field). But, you are grouping the records from that table to get the total. I would assume each order has a unique ID, status and earning value. So, including those fields and doing a GROUP BY will retain only one value from one of the fields that were group. This makes no sense. Either leave those fields out or include them and leave out the GROUP BY to get the total - you can get the total by adding the earnings field when processing the records. But, I'm guessing you really don't need those fields.

 

Fourth, ideally you would not be using a textual value for Order Status and instead use a numeric identifier (which could map to another table with the textual values). Using a LIKE clause to filter the records is very inefficient.

 

Lastly, the SELECT list contains the field r.reseller_id, which does not exist. Based on the table schema provided it would be r.id

 

Try this:

SELECT r.id, r.reseller_name, r.reseller_surname,
       r.reseller_email, r.reseller_phone,
       r.reseller_commission, r.reseller_vat_number,
  --   o.id, o.reseller_earnings, o.order_status,
       SUM(o.reseller_earnings) as total
 
FROM resellers as r
LEFT JOIN orders as o
  ON r.id = o.resellers_id
 
WHERE o.due_date BETWEEN '2014-08-14' AND '2015-08-14'
  AND    o.order_status LIKE '%Order Completed%'
 
GROUP BY r.id
ORDER BY total DESC

Edited August 21, 2015 by Psycho

[Psycho]

One other note. The BETWEEN operator may not be working as you intend.

 

When you have this

due_date BETWEEN '2014-08-14' AND '2015-08-14'

It will match all records starting at midnight (00:00:00) on 2014-08-14 to midnight (00:00:00) on 2015-08-14. In other words, anything on 2015-08-14 from 00:00:01 to 23:59:59 will NOT be included.

 

Never mind. The due date is a DATE type and not a TIMESTAMP. So it will be inclusive.

Edited August 21, 2015 by Psycho

[bambinou1980]

Thanks Psycho for your help.

 

Here is what I was trying to achieve:

 

 

This is for a little factory that may or may not have resellers when creating an order.

 

Imagine a form where there are 2 dropdown menus at the top, one to choose the buyers(customer) and if any, one to choose a reseller.

 

When the reseller is chosen and the operator has added the quantity required to the order, a jquery code does all the calculations and creates a total for the order itself with an order id and a total of commission for the reseller.

 

The reason I added the earnings of the reseller inside the order table was because I wanted to have a static proof of the order, this way if anyone changes the reseller details, or delete the user or change it's commission percentage, I was hoping to have an order that cannot be modified in any way, this is why I kept both tables separated. As I had a script a few years back made by a dev for a little company and when the reseller was changing his details, all the invoicing details were affected(even the old invoices).

 

Now based on that order table, because there are reseller user ids, I was hoping to do all the calculations from there by simply pull up all the reseller ids(if any as we may not have a reseller per order), then group their totals together and only show in the view table the resellers that have gained a commission and not the others who have not earned anything.

 

So really all I wanted to group when the "list monthly payment to resellers" page showed up was:

 

2 date pickers

 

and a table with:

reseller id

Reseller name

Reseller surname

Reseller earnings

Reseller email

Reseller phone number

Reseller V.A.T number

 

 

Now at the end of the row, I would like to add a button that you press when the payment is done in the accoutning software, upon pressing the button, this particular reseller payment will be set as "paid", so here I also have a missing column which I believe I will include in the "order" table aswell(if I am correct).

 

 

Regarding the sql code you gave me, I am getting this error:

Catchable fatal error: Object of class mysqli_result could not be converted to string in C:\xampp\htdocs\food\admin\crud\resellers\list-payments.php on line 165

 

 

Any idea what this might me? Should the date not be showing as:

WHERE orders.due_date >= CAST('2014-01-01' AS date)
AND orders.due_date <= CAST('2016-08-21' AS date)

 

 

 

 

 

When I put my old code, I get some results but of course the sum:

$sql = "SELECT DISTINCT
  orders.resellers_id,
  resellers.reseller_name,
  resellers.reseller_surname,
  orders.reseller_earnings,
  orders.reseller_commission,
  resellers.reseller_email,
  resellers.reseller_phone
FROM orders
  INNER JOIN resellers
    ON orders.resellers_id = resellers.id
WHERE orders.due_date >= CAST('2014-01-01' AS date)
AND orders.due_date <= CAST('2016-08-21' AS date)
GROUP BY resellers.id
ORDER BY orders.due_date";

 

 

Thank you for your work.

[Barand]

 

Regarding the sql code you gave me, I am getting this error:

Catchable fatal error: Object of class mysqli_result could not be converted to string in C:\xampp\htdocs\food\admin\crud\resellers\list-payments.php on line 165

That error is a result of your processing of the query (code not shown), not the query itself.

 

 

Any idea what this might me? Should the date not be showing as:

WHERE orders.due_date >= CAST('2014-01-01' AS date)

AND orders.due_date <= CAST('2016-08-21' AS date)

WHERE orders.due_date BETWEEN '2014-01-01'  AND '2016-08-21' was OK.

 

Do you really have future orders?

Or do you want

WHERE orders.due_date BETWEEN '2014-01-01'  AND CURDATE()

[Psycho]

When the reseller is chosen and the operator has added the quantity required to the order, a jquery code does all the calculations and creates a total for the order itself with an order id and a total of commission for the reseller.

You do order calculations on the client-side? Let me know what you are selling and I'll go buy mass quantities and 'hack' the price to be $0.01

 

 

The reason I added the earnings of the reseller inside the order table was because I wanted to have a static proof of the order, this way if anyone changes the reseller details, or delete the user or change it's commission percentage, I was hoping to have an order that cannot be modified in any way, this is why I kept both tables separated. As I had a script a few years back made by a dev for a little company and when the reseller was changing his details, all the invoicing details were affected(even the old invoices).

I'm not sure what 'earnings' have to do with an order. But, if there are properties of the reseller (which can change) that affect the order, then that is data that is order specific and should be copied to the order as you say you are. Or, you can keep a historical record of those properties of the seller and reference the correct dataset when an order is placed. But, I don't know where this discussion started. If this is a response to my third point - my comments still stand. You are grouping the records to get the total which would prevent the data for those fields to be worthless.

 

 

Now based on that order table, because there are reseller user ids, I was hoping to do all the calculations from there by simply pull up all the reseller ids(if any as we may not have a reseller per order), then group their totals together and only show in the view table the resellers that have gained a commission and not the others who have not earned anything.

Then you can change the LEFT JOIN to a normal JOIN and those resellers without any matching records in the orders table will not be in the result set

 

 

So really all I wanted to group when the "list monthly payment to resellers" page showed up was:

 

2 date pickers

 

and a table with:

reseller id

Reseller name

Reseller surname

Reseller earnings

Reseller email

Reseller phone number

Reseller V.A.T number

 

Then you don't need to SELECT the fields from the order table nor do you need to do the SUM() function (unless it is possible for orders to have a zero value for the reseller earning. If that is the case, then you would need the sum and the WHERE clause could exclude the records with a zero SUM value.

 

And with respect to Barand's last post. If you are wanting all orders rom a specific date to today, then why not just use

 

WHERE orders.due_date >= '2014-01-01'

As he said, there shouldn't be any orders in the future.

[bambinou1980]

In reply to your questions Psycho:

 

You do order calculations on the client-side? Let me know what you are selling and I'll go buy mass quantities and 'hack' the price to be $0.01

It would be a bit stupid for the boss of the company to hack himself.... as  he is the one passing the orders as he wants to personnaly keep an eye on what is going on the line,and the software will be internal for now.

 

But still it would be interrested to know how would you calculate the prices without using javascript to have them in real time. So if I understand, for you, this would be a big "no no"

http://www.xsanisty.com/project/calx/

 

 

 

I'm not sure what 'earnings' have to do with an order. But, if there are properties of the reseller (which can change) that affect the order, then that is data that is order specific and should be copied to the order as you say you are. Or, you can keep a historical record of those properties of the seller and reference the correct dataset when an order is placed. But, I don't know where this discussion started. If this is a response to my third point - my comments still stand. You are grouping the records to get the total which would prevent the data for those fields to be worthless.

 

By earnings I mean that each time a business is forwarded by a reseller, the reseller get 5% in commission from the total of the order. But the commission can only be calculated when the boss will of course know the actual order quantity. This is why I added the reseller earnings(commission/income) in the order form, so if the boss cancels the order, the commission will also be removed from the Monthly earnings of the reseller.

Also when the order added, it does not mean that the commision will be given to the reseller, the order will first have to be manufactured, paid for and picked up by the buyer(business).

So in the order itself, I will have different statuses, based on those, the commission will either be accepted, paused or removed.

 

 

Then you can change the LEFT JOIN to a normal JOIN and those resellers without any matching records in the orders table will not be in the result set

 

That JOIN little thing is really really hard for me to grasp at the moment and this is part of my struggle....

Why would this:

 

Then you can change the LEFT JOIN to a normal JOIN and those resellers without any matching records in the orders table will not be in the result set

 

I checked this tutorial here and I believe I understood where I was stuck.....

http://www.tizag.com/mysqlTutorial/mysqlleftjoin.php

 

In fact when you call a left join, I was always trying to visualize what was on the "left side" of a table in phpmyadmin, but I have just understood(if I am correct) that in fact what you call "Left" is only the "left" side of the query itself as:

<?php
// Make a MySQL Connection
// Construct our join query
$query = "SELECT family.Position, food.Meal ".
"FROM family LEFT JOIN food ".
    "ON family.Position = food.Position";
 
//In this example the LEFT JOIN will be what is on the LEFT of the query  "ON family.Position = food.Position";, being "family.Position".....am I correct?
    
$result = mysql_query($query) or die(mysql_error());


// Print out the contents of each row into a table
while($row = mysql_fetch_array($result)){
    echo $row['Position']. " - ". $row['Meal'];
    echo "<br />";
}
?>

 

If my understanding is correct then it removes a lot of headaches in my understanding on what a LEFT join is.....:-)

 

LEFT JOINS in fact always output the data on the table set as "left" even if empty, is this right?

 

Now why would people use "RIGHT JOIN" if we can pretty much do all the queries via a LEFT JOIN" by just placing the right table/column in the query?

 

Regarding a normal JOIN, I believe that both tables will show their data even if one or the other has an empty column, am I correct with this?

 

 

 

 

 

Then you don't need to SELECT the fields from the order table nor do you need to do the SUM() function (unless it is possible for orders to have a zero value for the reseller earning. If that is the case, then you would need the sum and the WHERE clause could exclude the records with a zero SUM value.

Yes some reseller earnings may have zero value. And yes you are both totally right about the date, it does not make sense to look for future orders which have not yet been created, unfortunately, I was not sure how to get the present time.

 

Thank you so much!

 

 

Ben

[Psycho]

But still it would be interrested to know how would you calculate the prices without using javascript to have them in real time. So if I understand, for you, this would be a big "no no"

http://www.xsanisty.com/project/calx/

 

You should NEVER rely on JavaScript (or anything client-side) for any business logic as it is very simple for a user to circumvent and pass whatever data they want. For example, if you have a select list of valid values for a particular field, you cannot assume that other values will not be passed. A malicious user can easily inject any value they want - regardless of what is in the select list. As to the above, if you were to rely on calculations made on the client-side to be used for the actual billing/charging of the customer, a malicious user could manipulate the values to anything they want.

 

 

That is not to say that you can't add business logic to client-side code to provide the user a better experience. You just can't rely upon it. So, you would first want to ensure you have all business logic implemented on the server-side. Then, you can add logic on the client-side where it makes sense. For example, let's say you have a field for an email address and you want to validate that the format of the value matches what an email address should look like. You would add logic on the server to check the format and throw an appropriate error if found to be invalid. But, maybe you don't want to have the user POST the page before finding out such an error exists. You could then add additional client-side logic to check the email address when the user exist the field or when they attempt to POST.

 

So, going back to the calculation. As stated above, you absolutely want to have server-side logic to do the calculations when it comes to billing/charging. If you want to give the user some immediate feedback, you could ad​d client-side logic to do the same calculation. But, that can be a problem as you would have to ensure the two processes are in sync. Otherwise, you risk the displayed (client-side) value not matching what the user is ultimately charged. A better approach would be to write the logic one-time on the server-side. That process could be used for both the final calculation when the order is submitted and you could use it within an AJAX call to provide the user a total without having to POST the page. Just don't get that total to display in the client and populate a field for the purpose of using it when the user submits the page as they can change it (even if it is a read-only field). You will want to call the same logic to calculate the price when the order is submitted.

[bambinou1980]

Thank you so much for taking the time to explain all the above points. I see, so Ajax would be pretty good to learn to help me with this kind of problems in the future. I will check out a few courses online and see if I understand it's principle. Is Ajax always the same type of calls or can it get really complex?

A few examples I saw a little while ago showed that in your page you have to include another php file with the ajax call, but does this process of having an external file to get the ajax call to start is always always or can you initiate an ajax call directly from the same form file used to POST the data?

 

Thank you,

 

 

Ben

[Psycho]

AJAX makes sense in this situation because the other solution would require duplicate logic to be crated in JavaScript. That only makes things more difficult and error prone. Since you already have to create server-side code to do the calculation, you can just use AJAX (JavaScript that would call the server-side code) to get the same results. That way you only need to write the logic one-time and then re-purpose it for multiple uses (one to provide immediate feedback to the user and two to do the official calculation when processing the order). But, if the problem was about validating that a required field had a value it might be easier to write separate logic for server-side and client-side.

 

As for how to implement AJAX, that is beyond the scope of a forum post. but, JQuery makes it very simple. Here is how I would implement it:

 

1. Create a file with a function to perform the calculations based on data passed to it. Typically this would be a list of products and quantities. The logic would look up the prices from the DB and do the multiplication and addition to get the subtotal. Then there might be additional logic around shipping or, in this case, comissions. The function would return the results.

 

2. The page that receives the form POST will process the data into the format expected by the function and then get the total for use in creating the order records.

 

3. Create another page to receive the AJAX request. That page would only need to format the POST data to send to the function and get the total. Then that page would return the total to the calling AJAX function

 

4. Create a trigger on the page to get the total (without the user actually submitting the order). The trigger would call a JavaScript function to send the relevant form field data to the page on #3 above.