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.

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

Thank you!

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

Ah, I did not think about this one....now I am a little stuck with a new problem. As you can see in my first post I already have a while loop using $query as: $sql = "SELECT id, cust_order_id, cust_company, due_date, product, quantity, price, requirements, cust_order_total, order_status, resellers_id FROM orders ORDER BY id DESC $limit"; $query = mysqli_query($connection, $sql); I my while loop I am trying to output in my table all that data: <?php // Attempt select query execution if($result = mysqli_query($connection, $sql)){ if(mysqli_num_rows($result) > 0){ echo "<table class='table table-striped'>"; echo "<tr>"; echo "<th class='col-md-1 text-center'>id</th>"; echo "<th class='col-md-1 text-center'>Order ID</th>"; echo "<th class='col-md-1 text-center'>Company</th>"; echo "<th class='col-md-1 text-center'>Due Date</th>"; echo "<th class='col-md-1 text-center'>Product</th>"; echo "<th class='col-md-1 text-center'>Unit Price</th>"; echo "<th class='col-md-1 text-center'>Quantity</th>"; echo "<th class='col-md-1 text-center'>Order Total</th>"; echo "<th class='col-md-5 text-center'>Requirements</th>"; echo "<th class='col-md-1 text-center'>Reseller</th>"; echo "<th class='col-md-1 text-center'>Order Status</th>"; echo "<th class='col-md-1 text-center'></th>"; while($row = mysqli_fetch_array($query, MYSQLI_ASSOC)){ $id = htmlspecialchars($row["id"]); $cust_order_id = $row["cust_order_id"]; $cust_company = $row["cust_company"]; $due_date = $row["due_date"]; $product = $row["product"]; $quantity = $row["quantity"]; $price = $row["price"]; $requirements = $row["requirements"]; $cust_order_total = $row["cust_order_total"]; $resellers = $row["resellers_id"]; $order_status = $row["order_status"]; echo "<tr class='text-center'>"; echo "<td>" . htmlspecialchars($id) . "</td>"; echo "<td>" . htmlspecialchars($cust_order_id) . "</td>"; echo "<td>" . htmlspecialchars($cust_company) . "</td>"; $new_date = date("d-m-Y", strtotime($due_date)); echo "<td>" . $new_date . "</td>"; echo "<td>" . htmlspecialchars($product) . "</td>"; echo "<td>" . htmlspecialchars($price) . "</td>"; echo "<td>" . htmlspecialchars($quantity) . "</td>"; echo "<td>" . htmlspecialchars($cust_order_total) . "</td>"; echo "<td>" . htmlspecialchars($requirements) . "</td>"; echo "<td>" . htmlspecialchars($resellers) . "</td>"; echo "<td>" . htmlspecialchars($order_status) . "</td>"; echo "<td><a class='btn btn-default' href='update-orders.php?order=$id' role='button'>Edit</a><a class='btn btn-danger' href='delete-orders.php?order=$id' onclick=\"return confirm('Are you sure you want to delete this order?');\" role='button'>Delete</a></td>"; echo "</tr>"; } echo "</table>"; // Close result set mysqli_free_result($result); }else{ echo "Not Results to Show, get more customers!!"; }} ?> But, in one of the colum of my table I need the results from the related table of the second query: $query2 = "SELECT `orders`.*, `resellers`.`reseller_name` FROM `orders` INNER JOIN `resellers` ON `orders`.`resellers_id` = `resellers`.`id` WHERE `orders`.`id` = '46'"; All I am trying to do is output the surname and name of the reseller "echo "<th class='col-md-1 text-center'>Reseller</th>";" in that column, but that data is from the $query2. How can I merge both queries into 1 and output the name and surname of that reseller table in that column please? "$resellers = $row["resellers_id"];" like this: while($row = mysqli_fetch_array($query, MYSQLI_ASSOC)){ $id = htmlspecialchars($row["id"]); (Table `orders`) $cust_order_id = $row["cust_order_id"]; (Table `orders`) $cust_company = $row["cust_company"]; (Table `orders`) $due_date = $row["due_date"]; (Table `orders`) $product = $row["product"]; (Table `orders`) $quantity = $row["quantity"]; (Table `orders`) $price = $row["price"]; (Table `orders`) $requirements = $row["requirements"]; (Table `orders`) $cust_order_total = $row["cust_order_total"]; (Table `orders`) $resellers = $row["resellers_id"]; (Table `resellers`)I need to pull the name and surname of the resellers there $order_status = $row["order_status"]; (Table `orders`) I thought it was going to be easy after finding a working query but it is actually becoming more complicated...

Thank ou so much, well spotted, I also had another error SELECT `orders`.*, `resellers`.`reseller_name` FROM `orders` INNER JOIN `resellers` ON `orders`.`resellers_id` = `resellers`.`id` WHERE `orders`.`id` = '46'; It was "reseller_name" and not "reseller". It is working! Thanks

I also tried this but it is a fail too..it returns nothing....query is ok but nothing is returned at all. SELECT resellers_id, reseller_name, reseller_surname FROM orders T1 INNER JOIN resellers T2 ON T1.resellers_id = T2.reseller_name

Oups sorry....ahahah Here it is: I tried this but the sql does not work..... SELECT 'orders'.*, 'resellers'.'name' FROM 'orders' INNER JOIN 'resellers' ON 'orders'.'resellers_id' = 'resellers'.'id' WHERE 'orders'.'id' = '46'; I am getting SELECT 'orders'.*, 'resellers'.'name' FROM 'orders' INNER JOIN 'resellers' ON 'orders'.'resellers_id' = 'resellers'.'id' WHERE 'orders'.'id' = 46 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 '.*, 'resellers'.'name' FROM 'orders' INNER JOIN 'resellers' ON 'orders'.'reselle' at line 1

Hello, I am having a little problem. I have a listing table and would like to output the "name and surname" associated with the reseller id. I can currently output the reseller id, it is working fine but how to get his name from the associated table please(foreign key). Here is my query: <?php // Attempt select query execution if($result = mysqli_query($connection, $sql)){ if(mysqli_num_rows($result) > 0){ echo "<table class='table table-striped'>"; echo "<tr>"; echo "<th class='col-md-1 text-center'>id</th>"; echo "<th class='col-md-1 text-center'>Order ID</th>"; echo "<th class='col-md-1 text-center'>Company</th>"; echo "<th class='col-md-1 text-center'>Due Date</th>"; echo "<th class='col-md-1 text-center'>Product</th>"; echo "<th class='col-md-1 text-center'>Unit Price</th>"; echo "<th class='col-md-1 text-center'>Quantity</th>"; echo "<th class='col-md-1 text-center'>Order Total</th>"; echo "<th class='col-md-5 text-center'>Requirements</th>"; echo "<th class='col-md-1 text-center'>Reseller</th>"; echo "<th class='col-md-1 text-center'>Order Status</th>"; echo "<th class='col-md-1 text-center'></th>"; while($row = mysqli_fetch_array($query, MYSQLI_ASSOC)){ $id = htmlspecialchars($row["id"]); $cust_order_id = $row["cust_order_id"]; $cust_company = $row["cust_company"]; $due_date = $row["due_date"]; $product = $row["product"]; $quantity = $row["quantity"]; $price = $row["price"]; $requirements = $row["requirements"]; $cust_order_total = $row["cust_order_total"]; $resellers = $row["resellers_id"]; $order_status = $row["order_status"]; echo "<tr class='text-center'>"; echo "<td>" . htmlspecialchars($id) . "</td>"; echo "<td>" . htmlspecialchars($cust_order_id) . "</td>"; echo "<td>" . htmlspecialchars($cust_company) . "</td>"; $new_date = date("d-m-Y", strtotime($due_date)); echo "<td>" . $new_date . "</td>"; echo "<td>" . htmlspecialchars($product) . "</td>"; echo "<td>" . htmlspecialchars($price) . "</td>"; echo "<td>" . htmlspecialchars($quantity) . "</td>"; echo "<td>" . htmlspecialchars($cust_order_total) . "</td>"; echo "<td>" . htmlspecialchars($requirements) . "</td>"; echo "<td>" . htmlspecialchars($resellers) . "</td>"; echo "<td>" . htmlspecialchars($order_status) . "</td>"; echo "<td><a class='btn btn-default' href='update-orders.php?order=$id' role='button'>Edit</a><a class='btn btn-danger' href='delete-orders.php?order=$id' onclick=\"return confirm('Are you sure you want to delete this order?');\" role='button'>Delete</a></td>"; echo "</tr>"; } echo "</table>"; // Close result set mysqli_free_result($result); }else{ echo "Not Results to Show, get more customers!!"; }} ?>

Hi Barand, Thank you for the fast reply. I am not understanding why you add "MONTH(due_date) as month" does this function automatically recognise the /mm/ in the date itself? Thank you, Ben

Hello, I have created the below query to get all the totals of sales during a chosen date from 3 date pickers. My question now is how to get 12 months of results when I choosen a year but have each months output it's total sales. The reaosn I need this is because I need to have an array showing every month + total sales per month so I can json_encode() it and pass it into a graph using Morris.js(Graph library) as shown here: <?php $query = "SELECT SUM(cust_order_total) AS order_total_sum FROM orders WHERE due_date BETWEEN '2015-08-14' AND '2015-10-14'"; $result = mysqli_query($connection, $query); while ($row = mysqli_fetch_array($result)){ echo $row['order_total_sum'];//This return totals of sales during chosen period. } ?> This currently output well the total sales during the chosen period, so it works. Now I need the exact same type of logic but When I click on the dropdown menu that choses a Year, I would like to see: Year 2014 Month 01 -> €239 02 -> €250 03 -> €456 And so on.... Any idea please? Remember I am saving the date as yyyy-mm-dd in mysql but read it as dd-mm-yyyy on the web page. Thank you, Ben