bambinou1980

Hello, I am confused, you the below code not return an array of the selected data from mysql? $query = "SELECT SUM(cust_order_total) AS daily_gross_sales, due_date AS day FROM orders WHERE YEAR(due_date) = YEAR(CURDATE()) AND MONTH(due_date) = MONTH(CURDATE()) GROUP BY due_date ORDER BY day"; $rows = ''; $result = mysqli_query($connection,$query); while($row = mysqli_fetch_array($result)) { $rows = mysqli_fetch_all($result, MYSQLI_ASSOC); } echo json_encode($rows); I would like to have the mysql data outputting as: array('label' => $row['cust_order_total'], 'y' => $row['due_date']); I have also tried this: $query = "SELECT SUM(cust_order_total) AS daily_gross_sales, due_date AS day FROM orders WHERE YEAR(due_date) = YEAR(CURDATE()) AND MONTH(due_date) = MONTH(CURDATE()) GROUP BY due_date ORDER BY day"; $types = array(); $result = mysqli_query($connection,$query); while(($row = mysqli_fetch_assoc($result))) { $types[] = $row['daily_gross_sales']; } print_r($types); But no luck, the array returns empty even if I have data in the database. Thank you.

Thank you so much Barand, yes this is what happened to me, I kept getting the year 1900 when trying to convert directly from mysql. A friend of mine told me to check the highcharts from here: http://www.highcharts.com/demo I will give it a go tonight on both highchart and google chart but I do find the highcharts graphics a little bit more sexy:-) Thanks again for your great help! I will open a new post if I struggle with the others.

Hi Barand, Thank you so much, it seems to be working very well, quick question for you please, to rearrange the dates directly from mysql to the format yyyy/mm/dd to dd/mm/yyyy I tried this: FROM ordersWHERE YEAR(due_date) = YEAR(date_format(CURDATE('%d/%m/%Y')))AND MONTH(due_date) = MONTH(date_format(CURDATE('%d/%m/%Y')))GROUP BY due_dateORDER BY day"; I have also tried this: WHERE YEAR(due_date) = YEAR(CURDATE(date_format('%d/%m/%Y'))) AND MONTH(due_date) = MONTH(CURDATE(date_format('%d/%m/%Y'))) But I am not getting anymore results now in the graphs, what is the best way of doing this please? I don't think it is possible to change the date format in the "mysqli_fetch_all" is it? Thank you, Ben

Hello, I have an htaccess file like this: <Limit GET POST> order allow,deny allow from xxx.xxx.xxx.xx </Limit> Options +FollowSymLinks IndexIgnore */* RewriteEngine on RewriteCond %{REQUEST_FILENAME} !-f RewriteCond %{REQUEST_FILENAME} !-d RewriteRule ^(.*)$ index.php/$1 [L] <Files 403.shtml> order deny,allow deny from all </Files> allow from xxx.xxx.xxx.xx #Fred allow from xxx.xxx.xxx.xxx #Ben What I would like to do is now let one url pass through the ip protection, I wish to have this(or more) url public. http://mysite.com/here_it-is.php How to do this please? Thank you, Ben

Oups, my apology, I forgot to remove my sample.... Here is my new code: <?php $query = "SELECT SUM(cust_order_total) as daily_gross_sales, DAY(due_date) as day FROM orders WHERE YEAR(due_date) = YEAR(CURDATE()) AND MONTH(due_date) = MONTH(CURDATE()) GROUP BY day ORDER BY due_date"; $rows = ''; $result = mysqli_query($connection,$query); $total_rows = mysqli_num_rows($result); if($total_rows > 0) { $rows = mysqli_fetch_all($result, MYSQLI_ASSOC); echo json_encode($rows); } ?> json output [{"daily_gross_sales":"112.50","day":"27"},{"daily_gross_sales":"937.50","day":"28"}] <!--Content--> <script> Morris.Line({ // ID of the element in which to draw the chart. element: 'morris-line-chart', // Chart data records -- each entry in this array corresponds to a point // on the chart. data: <?php echo json_encode($rows);?>, // The name of the data record attribute that contains x-values. xkey: 'day', <-----I also tried with ['day'] but it did not make a difference. // A list of names of data record attributes that contain y-values. ykeys: ['daily_gross_sales'], // Labels for the ykeys -- will be displayed when you hover over the // chart. labels: ['Total Sales'], lineColors: ['#0b62a4'], xLabels: 'Days', // Disables line smoothing smooth: true, resize: true }); </script>

Ok, so here is where I am, based on my current table(Those are the real names as previously they were just samples). I am getting a good json encoded data but nothing is showing up in the graph: <div id="myfirstchart" style="height: 250px;"></div> </div> <?php $query = "SELECT SUM(cust_order_total) as daily_gross_sales, DAY(due_date) as day FROM orders WHERE YEAR(due_date) = YEAR(CURDATE()) AND MONTH(due_date) = MONTH(CURDATE()) GROUP BY day ORDER BY due_dates"; $rows = ''; $query = "SELECT cust_order_total,due_date FROM orders ORDER BY due_date"; $result = mysqli_query($connection,$query); $total_rows = mysqli_num_rows($result); if($result) { $rows = mysqli_fetch_all($result, MYSQLI_ASSOC); } ?> The json output is: [{"cust_order_total":"112.50","due_date":"2015-08-27"},{"cust_order_total":"70.00","due_date":"2015-08-28"},{"cust_order_total":"75.00","due_date":"2015-08-28"},{"cust_order_total":"112.50","due_date":"2015-08-28"},{"cust_order_total":"41.25","due_date":"2015-08-28"},{"cust_order_total":"166.25","due_date":"2015-08-28"},{"cust_order_total":"105.00","due_date":"2015-08-28"},{"cust_order_total":"120.00","due_date":"2015-08-28"},{"cust_order_total":"95.00","due_date":"2015-08-28"},{"cust_order_total":"112.50","due_date":"2015-08-28"},{"cust_order_total":"40.00","due_date":"2015-08-28"}] Now here is how I have added the data according to this example: http://www.codediesel.com/visualization/display-line-and-area-charts-in-php-and-mysql/ The graph shows nothing at the moment, but it should show daily sales. I am not understand 2 things, why the array still shows"cust_order_total" when we have an alias called "daily_gross_sales", and why don't we have at least 1 tiny result in the graph using the x and y names shown in the array, any idea please? <!--Content--> <script> Morris.Line({ // ID of the element in which to draw the chart. element: 'morris-line-chart', // Chart data records -- each entry in this array corresponds to a point // on the chart. data: <?php echo json_encode($rows);?>, // The name of the data record attribute that contains x-values. xkey: 'due_date', // A list of names of data record attributes that contain y-values. ykeys: ['cust_order_total'], // Labels for the ykeys -- will be displayed when you hover over the // chart. labels: ['Total Sales'], lineColors: ['#0b62a4'], xLabels: 'Days', // Disables line smoothing smooth: true, resize: true }); </script> Thank you!

Thank you so much Barand, I will try all of this now :-)

$sql = "SELECT sales_values, dates FROM orders WHERE dates BETWEEN yyyy/mm/dd AND NOW() ORDER BY dates"; Sorry about the sql statement, I had kids running around while I was writing the post and could not concentrate properly..... On one single page I am wishing to show this as live graphs: 1 graph with the current month and daily sales(so this will correspond to days on the X axis and prices on the Y axis). 1 graph with the current week from Monday to Sunday and daily sales(so this will correspond to days on the X axis and prices on the Y axis). 1 graph with the current Year and Weekly sales(so this will correspond to weeks on the X axis and prices on the Y axis). Thank you Barand,

Hello, I would like to populate my first morris.js graph from here: http://morrisjs.github.io/morris.js/ But, I am not sure where to start, I understand that we can encode php in a way that we get a json output using json_encode() Where I am lost is here. I will have in mysql a columin with a date yyyy/mm/dd and another column with the "sales_values", let's say I pull those 2 columns with a SELECT as $sql = "SELECT from orders sales_values, dates dates BETWEEN yyyy/mm/dd AND NOW() ORDER BY dates"; How to I know take those results to make them work so they can get applied to the below code from morris.js please? new Morris.Line({ // ID of the element in which to draw the chart. element: 'myfirstchart', // Chart data records -- each entry in this array corresponds to a point on // the chart. data: [ { year: '2008', value: 20 }, { year: '2009', value: 10 }, { year: '2010', value: 5 }, { year: '2011', value: 5 }, { year: '2012', value: 20 } ], // The name of the data record attribute that contains x-values. xkey: 'year', // A list of names of data record attributes that contain y-values. ykeys: ['value'], // Labels for the ykeys -- will be displayed when you hover over the // chart. labels: ['Value'] }); Thank you, Ben

Thank you but this is the type of tool I was actually looking for, looks pretty good? With php I am ok now, but mysql I believe could be written far faster by using a tool https://www.devart.com/dbforge/mysql/querybuilder/ Before I used to spend ages trying to work out templates for boostrap 3, now I use Layoutit.com and spend only 10 minutes per layout instead of hours and get the same result. All I want is to be more efficient with my own work by spending more time thinking about logic and less time on tiny things like this little commas, I think that having tools like mysql benchmark, layout it and others really helps, all i need now is a cool tool that writes the queries for me based on a visual implementation, I will still think about the logic of the queries but at least will not lose times on misplaced commas. I will try one of those tool next week and see if it helps...I am sure it will.

Thank you so much about the comma....yes it worked without it. Do you know a good tool I could use that helps building queries please so I do not have to look at all those tiny details? Regarding setting multiple ids from PAID to UNPAID, yes this is exactly what I wanted to do, update all the payments from UNPAID to PAID. The reason being, in that table you have all the monthly earnings of each resellers added as separated total(grouped by reseller ids). 1 time per month, the site owner will pay each reseller and upon clicking on the button PAID, all the earnings that are calculated on different earning sheets will all change from UNPAID to PAID. Thanks again! Ben

Unfortunately, I have not managed to get it work , I changed slightly my query but still get an error: $query2 = "UPDATE orders SET has_reseller_been_paid = '%PAID%', WHERE due_date BETWEEN '2010-01-01' AND NOW() AND order_status = '%Order Completed%' AND has_reseller_been_paid = '%UNPAID%' AND orders_resellers_id = $reseller_id"; $result2 = mysqli_query($connection, $query2); The error I am getting is: Database query failed. 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 due_date BETWEEN '2010-01-01' AND NOW() AND order_status = '%Order ' at line 3

Thank you so much! I did not even realised I used a select instead of update....:-(((( About the has_reseller_been_paid, it is either set to "PAID" OR "UNPAID". Let me try and see if it works by changing it to update...:-)

Hello, I am trying to SET a value based on my SELECT but the output error is: Database query failed. 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 ' SET has_reseller_been_paid = 'PAID'' at line 13 $query = "SELECT r.resellers_id, o.orders_id, o.order_status, o.has_reseller_been_paid, SUM(o.reseller_earnings) as total FROM orders AS o LEFT JOIN resellers as r ON r.resellers_id = o.orders_resellers_id WHERE o.due_date BETWEEN '2010-01-01' AND NOW() AND o.order_status RLIKE '[[:<:]]Order Completed[[:>:]]' AND o.has_reseller_been_paid RLIKE '[[:<:]]UNPAID[[:>:]]' AND resellers_id = $reseller_id SET has_reseller_been_paid = 'PAID'"; $result = mysqli_query($connection, $query); I have checked my database and the columns are there. I think it is my synthax, any idea where I went wrong please? Also a quick question, if you have 3 queries on one page, it is ok to use the same $result variable each time or will it causes problem and instead I shoulod rename it to $query1, $query2,$query3 and $result1 = mysqli_query($connection, $query1 $result2 = mysqli_query($connection, $query2 $result3 = mysqli_query($connection, $query3 Thank you,

Ok My apology I have just worked out why.... on the php output as I using $row['rseller_earnings']; instead of $row['total']; ​ Thank you so much everyone!

Yes my apology in between I did change the qeury which produced some results. So It is nearly there, the problem is that the reseller_earnings are not suming up, it is weird because it should work without problem, I am not seeing what is wrong here. $sql = "SELECT r.resellers_id, r.reseller_name, r.reseller_surname, r.reseller_email, r.reseller_phone, r.reseller_commission, r.reseller_vat_number, o.orders_id, o.reseller_earnings, o.order_status, o.has_reseller_been_paid, SUM(o.reseller_earnings) as total FROM orders AS o LEFT JOIN resellers as r ON r.resellers_id = o.orders_resellers_id WHERE o.due_date BETWEEN '2014-08-14' AND '2016-08-14' GROUP BY r.resellers_id ORDER BY total DESC";​

What I am getting now is some entries that are empty. I think the problem comes from my JOIN, I think it should be on the orders and not on the resellers.

Thanks Barand for the explanations!

Hello, I have this query which is returning a blank page at the moment: $sql = "SELECT r.resellers_id, r.reseller_name, r.reseller_surname, r.reseller_email, r.reseller_phone, r.reseller_commission, r.reseller_vat_number, o.orders_id, o.reseller_earnings, o.order_status, o.has_reseller_been_paid, SUM(o.reseller_earnings) as total FROM orders AS o LEFT JOIN resellers as r ON o.orders_resellers_id = r.resellers_id WHERE o.due_date BETWEEN '2014-08-14' AND NOW() AND o.order_status LIKE '%Order Completed%' GROUP BY r.resellers_id ORDER BY total DESC"; Based on the below tables, could you please let me know if you see an aerror anywhere? All I am trying to do is have all the resellers ids group together and show how much money we owe them each month. For this I need to put the "UNPAID" resellers from the "o.has_reseller_been_paid" at the top of the ladder with the sum of all their earnings from the o.reseller_earnings row. Thank you!

Thank you Barand, Actually what I meant is not exactly this, I just wanted to know why sometime we create an actual foreign key in the database and why sometime it is just done via the query like this guy here explains: http://stackoverflow.com/questions/2947440/foreign-keys-vs-joins Here is said: ​Joins are defined using foreign keys only. Of course, you might not define the foreign key in the database. Using foreign key will better the performance (provided the foreign key selection is right). Now why would create an actual foreign key in the database itself rather than just in the query add better performance to the database please? Any idea? ​ Thank you, ​ Ben​ ​

Hi Psycho, Thank you so much. I loved this " LEFT table (i.e. first table - orders) and JOIN any matching records from the right table (resellers)." It makes it very easy to understand. Regarding the ids....ohh...I have changed all the id names yesterday in all my table and pages according to the a post I read that was going again the fact that I should not use the same table name ids. No worry, or now I will leave it and in my next project I will follow those rules. Quick question please, what difference does it makes to use foreign key vs joins please? At the moment I prefer to use joins because I always get warning that I cannot edit a parent table via a child table but would the foreign key structure be better than JOINS? Thank you

Yes you are right, I have removed it. Yes excellent idea, it took me some time but I have actually changed all the ids of all my tables and changed all the ids in all my CRUDS , now each tables has it's own id name, I won't make the same mistake in the future, very good point. I have a small problem, imagine that sometime an order may or may not have a reseller added to the order form, therefore soemetime, the form will register a reseller id and sometime will register 0 as no resellers will be chosen from the dropdown menu. What I am experiencing now, is that with the below query, if there are no resellers and the id is set to "0", I do not see the results in the select query..... $sql = "SELECT o.orders_id, o.cust_order_id, o.cust_company, o.due_date, o.product, o.quantity, o.price, o.requirements, o.cust_order_total, o.order_status, o.reseller_earnings, o.orders_resellers_id, r.reseller_name, r.reseller_surname FROM orders as o, resellers as r WHERE o.orders_resellers_id = r.resellers_id ORDER BY due_date DESC";​ I remember once in another thread, one of you proposed to use something called a JOIN to go around this problem. Could you please show me a quick example on the above query how to show the results by force of the "orders" table even if there are no resellers found with the id of "0". Thank you so much, lucky you are here!!I would have been totally stuck long time ago, I am slowly understanding the magic of MySQL...but Oh Boy....it is not an easy task to learn all of this after a hard day of work that has nothing to do with coding......

Hello, I cannot work out this one, I have this select statement $sql = "SELECT o.id, o.cust_order_id, o.cust_company, o.due_date, o.product, o.quantity, o.price, o.requirements, o.cust_order_total, o.order_status, o.reseller_earnings, o.resellers_id, r.reseller_name, r.reseller_surname, r.id FROM orders as o, resellers as r WHERE o.resellers_id = r.id ORDER BY due_date DESC"; In my html table I would like to output the "o.id" but each time the "r.id" is being output, any idea why please? I tried to add instead "$id = $row["o.id"] but no luck. I also tried "$id = $row["irders.id"]; <?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'>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'>Reseller Earnings</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 = $row["id"];<---------------------------------this is pulling the r.id instead of o.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["reseller_name"] . ' ' . $row["reseller_surname"]; $reseller_earnings = $row["reseller_earnings"]; $order_status = $row["order_status"]; echo "<tr class='text-center'>"; 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($reseller_earnings) . "</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 orders!!"; }} ?> Thank you.

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

In reply to your questions Psycho: 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/ 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. 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: 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? 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