Senthilkumar

I tried the end of this query But i am getting error of Error Code: 1054. Unknown column 'mnth.id' in 'order clause'

My table is Where to use ORDER BY mnth.id on query

Dear Brand, I have created the separeate table for month and changed the query with left join <?php $query = "SELECT mnth.Month, Revanue, EmployeeExpense, InfraExpense, OtherExpense FROM ( select Month from dbms.month) mnth LEFT JOIN ( select Month, sum(Revanue) as Revanue from dbms.revenue_data where dealerid = '$Emp_No' AND Status = '1' group by Month ) revenue USING(Month) LEFT JOIN ( select Month, sum(Total) as EmployeeExpense from dbms.employeeexpense where dealerid = '$Emp_No' AND Status = '1' group by Month ) EmployeeExpense USING(Month) LEFT JOIN ( select Month, sum(Total) as InfraExpense from dbms.infrastructure_expense where dealerid = '$Emp_No' AND Status = '1' group by Month ) InfraExpense USING(Month) LEFT JOIN ( select Month, sum(Total) as OtherExpense from dbms.otherexpense where dealerid = '$Emp_No' AND Status = '1' group by Month ) OtherExpense USING(Month);"; $exec = mysqli_query($conn, $query); while ($row = mysqli_fetch_array($exec)) { $Month = $row['Month']; $Revanue = $row['Revanue']; $EmployeeExpense= $row['EmployeeExpense']; $InfraExpense = $row['InfraExpense']; $OtherExpense = $row['OtherExpense']; $formatedMonth = date("M-Y", strtotime($Month)); $Expenditure = $EmployeeExpense + $InfraExpense + $OtherExpense; if ($Expenditure != 0 && $Revanue != 0) { $Absorption_Ratio = ($Revanue / $Expenditure) * 100; }else{ $Absorption_Ratio = 0; } echo "['$formatedMonth',$Absorption_Ratio],"; } ?> I am getting 12 columns on the graph. For testing i updated the datas for the month of Jan, Feb, Mar and Jun. My grap the month is displaying Jan,Feb,Mar,Jun,Apr,May,Jul,Aug,Sep,Oct,Nov,Dec Can you please tell me how to correct this

Dear Team, I am using Google column chart on my dahboard for displaying the datat from mysql databse. The chart h axis i want to display all the month by default like bellow image and for which month the data is available that only diplay here like bellow image. Iam using the bellow query for fetch the data from database <?php; $query = "SELECT revenue.Month, Revanue, EmployeeExpense, InfraExpense, OtherExpense FROM ( select Month, sum(Revanue) as Revanue from dbms.revenue_data where dealerid = '$Emp_No' AND Status = '1' group by Month ) revenue LEFT JOIN ( select Month, sum(Total) as EmployeeExpense from dbms.employeeexpense where dealerid = '$Emp_No' AND Status = '1' group by Month ) empExp USING(Month) LEFT JOIN ( select Month, sum(Total) as InfraExpense from dbms.infrastructure_expense where dealerid = '$Emp_No' AND Status = '1' group by Month ) infExp USING(Month) LEFT JOIN ( select Month, sum(Total) as OtherExpense from dbms.otherexpense where dealerid = '$Emp_No' AND Status = '1' group by Month ) OtherExpense USING(Month);"; $exec = mysqli_query($conn, $query); while ($row = mysqli_fetch_array($exec)) { $Month = $row['Month']; $Revanue = $row['Revanue']; $EmployeeExpense= $row['EmployeeExpense']; $InfraExpense = $row['InfraExpense']; $OtherExpense = $row['OtherExpense']; $formatedMonth = date("M-Y", strtotime($Month)); $Expenditure = $EmployeeExpense + $InfraExpense + $OtherExpense; $Absorption_Ratio = ($Revanue / $Expenditure )*100; echo "['$formatedMonth',$Absorption_Ratio],"; } ?> I am getting output like below image This is displaying the month only where there is data available. How can i change it to display all the months on haxis

Thank you

Dear Barand, I cleared the spaces on revenue_dep. Now i am getting output from the query. I have one more question. From my table revenue_data i am getting output of 5 rows. From my table revenuetarget i am getting output of 3 rows. With Joining of two tables the output is showing 3 rows only. Is it possible to display 5 rows joining both the tables and display 0 for Others and Workshop on column target. Pls guide me

Ok. I will check the table. Thanks for ur reply

Empty result

Dear Barand, Pls help me

I am using this script to display the data on my PHP page. So i wrote the my sql code on single line. On your previous suggession i changed the single line query and it works perfectly on my another page. SELECT act.category, target, actual FROM ( SELECT category, sum(quantity) as actual FROM tiv_data WHERE brand = 'XCMG' AND dealerid = '81019218' GROUP BY category ) act JOIN ( SELECT category, sum(total) as target FROM currentyeardata WHERE Dealer_ID = '81019218' GROUP BY category ) targ USING (category);

Dear Barand, I tried the same query for another table. But the output is not working. My query is SELECT act.Revenue_Dep, target, actual FROM ( SELECT Revenue_Dep, sum(Revanue) as actual FROM dbms.revenue_data WHERE dealerid = '81019218' GROUP BY Revenue_Dep ) act JOIN ( SELECT Revenue_Dep, sum(Total) as target FROM dbms.revenuetarget WHERE Dealer_ID = '81019218' GROUP BY Revenue_Dep ) targ USING (Revenue_Dep) The output is If i use bot the query separtly i am getting output SELECT Revenue_Dep, sum(Total) as target FROM dbms.revenuetarget WHERE Dealer_ID = '81019218' GROUP BY Revenue_Dep; SELECT Revenue_Dep, sum(Revanue) as actual FROM dbms.revenue_data WHERE dealerid = '81019218' GROUP BY Revenue_Dep; Can you please tell me where i am doing the mistake on Join query

Dear Barand, Thanks for your support. It is working now.

Dear Barand, I am trying this bellow subquery select Category,sum(Quantity) as Actual from dbms.tiv_data WHERE Brand='XCMG' AND Category = (select Category, sum(Total) as Target from dbms.currentyeardata WHERE Dealerid = '81019218' group by Category); But i am getting error "Error Code: 1241. Operand should contain 1 column(s)"

Dear Barand, when i am running select * from query on tiv_data it returns total 11 rows When i am running select * from query on currentyear data it returns total 20 rows When i am running the inner join query as per your suggession i am getting total 90 row Because of this the sum value is updating wrong. Can you please suggest me what to do for this problem

Dear Team, I am having a table which is displaying actual and target values from two different mysql table. My table is In above table when i am selecting the month the actual and target date should display from the databse. When i am using the bellow code all the target value is displaying <script>//..................month hide function function myFunction() { var month = document.getElementById("month").value; var dealerid = document.getElementById("dealerid").value; $.ajax({ type: 'POST', dataType: 'JSON', url: 'Revenue_Data.php', data: 'month=' + month + '&dealerid=' + dealerid, //data: 'month=' + month + '&dealerid=' + dealerid, success: function (response) { //var jsonData = JSON.parse(response); var len = response.length; //alert(jsonData); for (var i = 0; i < len; i++) { var Category_Name = response[i].Category_Name; var Description = response[i].Description; var Month_Value = response[i].Month_Value; var result = response[i].result; //var Excavator = response[i].Excavator; //var Grader = response[i].Grader; //var WheelLoader = response[i].WheelLoader; //var SLM = response[i].SLM; //var Parts_Service_ID = response[i].Parts_Service_ID; //var Revanue = response[i].Revanue; //var Margin = response[i].Margin; document.getElementById(result).value = Month_Value; //document.getElementById('ExcavatorActual'+Parts_Service_ID).value = Excavator; //document.getElementById('GraderActual'+Parts_Service_ID).value = Grader; //document.getElementById('WheelLoaderActual'+Parts_Service_ID).value = WheelLoader; //document.getElementById('SLMActual'+Parts_Service_ID).value = SLM; //document.getElementById('RevenueActual'+Parts_Service_ID).value = Revanue; //document.getElementById('Margin'+Parts_Service_ID).value = Margin; } } }); } </script> <?php // Include the database config file include("../connection.php"); //error_reporting(0); error_reporting(E_ALL); ini_set('display_errors', '1'); //ini_set('display_errors','Off'); if(!empty($_POST["month"]) ){ $Month=$_POST["month"]; $dealerid = $_POST["dealerid"]; $Mon = date('M', strtotime($Month)); $sql = "select $Mon,Category_Name,Description from revenuetarget WHERE Dealer_ID = '$dealerid'"; $result = mysqli_query($conn, $sql); while ($row = mysqli_fetch_array($result)) { $Category_Name = $row['Category_Name']; $Description = $row['Description']; $Month_Value = $row[$Mon]; $Description1= str_replace(' ', '', $Description); $Category_Name1= str_replace(' ', '', $Category_Name); $result1 = trim($Category_Name1); $result1 .= trim($Description1); $return_arr[] = array( "Category_Name" => $Category_Name, "Description" => $Description, "result" => $result1, "Month_Value" => $Month_Value, ); } $sql = "select * from revenue_data WHERE dealerid = '$dealerid' AND month = '$Month'"; $result = mysqli_query($conn, $sql); while ($row = mysqli_fetch_array($result)) { $Excavator = $row['Excavator']; $Grader= $row['Grader']; $WheelLoader = $row['WheelLoader']; $SLM = $row['SLM']; $Parts_Service_ID = $row['Parts_Service_ID']; $Revanue = $row['Revanue']; $Margin = $row['Margin']; $return_arr1[] = array( "Excavator" => $Excavator, "Grader" => $Grader, "WheelLoader" => $WheelLoader, "SLM" => $SLM, "Parts_Service_ID" => $Parts_Service_ID, "Revanue" => $Revanue, "Margin" => $Margin, ); } echo json_encode($return_arr); //echo json_encode($return_arr1); } ?> When i am using the bellow code all my actual values are displaying <script>//..................month hide function function myFunction() { var month = document.getElementById("month").value; var dealerid = document.getElementById("dealerid").value; $.ajax({ type: 'POST', dataType: 'JSON', url: 'Revenue_Data.php', data: 'month=' + month + '&dealerid=' + dealerid, //data: 'month=' + month + '&dealerid=' + dealerid, success: function (response) { //var jsonData = JSON.parse(response); var len = response.length; //alert(jsonData); for (var i = 0; i < len; i++) { //var Category_Name = response[i].Category_Name; //var Description = response[i].Description; //var Month_Value = response[i].Month_Value; //var result = response[i].result; var Excavator = response[i].Excavator; var Grader = response[i].Grader; var WheelLoader = response[i].WheelLoader; var SLM = response[i].SLM; var Parts_Service_ID = response[i].Parts_Service_ID; var Revanue = response[i].Revanue; var Margin = response[i].Margin; //document.getElementById(result).value = Month_Value; document.getElementById('ExcavatorActual'+Parts_Service_ID).value = Excavator; document.getElementById('GraderActual'+Parts_Service_ID).value = Grader; document.getElementById('WheelLoaderActual'+Parts_Service_ID).value = WheelLoader; document.getElementById('SLMActual'+Parts_Service_ID).value = SLM; document.getElementById('RevenueActual'+Parts_Service_ID).value = Revanue; document.getElementById('Margin'+Parts_Service_ID).value = Margin; } } }); } </script> <?php // Include the database config file include("../connection.php"); //error_reporting(0); error_reporting(E_ALL); ini_set('display_errors', '1'); //ini_set('display_errors','Off'); if(!empty($_POST["month"]) ){ $Month=$_POST["month"]; $dealerid = $_POST["dealerid"]; $Mon = date('M', strtotime($Month)); $sql = "select $Mon,Category_Name,Description from revenuetarget WHERE Dealer_ID = '$dealerid'"; $result = mysqli_query($conn, $sql); while ($row = mysqli_fetch_array($result)) { $Category_Name = $row['Category_Name']; $Description = $row['Description']; $Month_Value = $row[$Mon]; $Description1= str_replace(' ', '', $Description); $Category_Name1= str_replace(' ', '', $Category_Name); $result1 = trim($Category_Name1); $result1 .= trim($Description1); $return_arr[] = array( "Category_Name" => $Category_Name, "Description" => $Description, "result" => $result1, "Month_Value" => $Month_Value, ); } $sql = "select * from revenue_data WHERE dealerid = '$dealerid' AND month = '$Month'"; $result = mysqli_query($conn, $sql); while ($row = mysqli_fetch_array($result)) { $Excavator = $row['Excavator']; $Grader= $row['Grader']; $WheelLoader = $row['WheelLoader']; $SLM = $row['SLM']; $Parts_Service_ID = $row['Parts_Service_ID']; $Revanue = $row['Revanue']; $Margin = $row['Margin']; $return_arr1[] = array( "Excavator" => $Excavator, "Grader" => $Grader, "WheelLoader" => $WheelLoader, "SLM" => $SLM, "Parts_Service_ID" => $Parts_Service_ID, "Revanue" => $Revanue, "Margin" => $Margin, ); } //echo json_encode($return_arr); echo json_encode($return_arr1); } ?> I want to display botht the values (Actual and target) on same time. How can i get two array values on same time and display on the table.

Dear Team, I am creating google pie chart from mysql database on my PHP project. My actual chart and output is <script type="text/javascript"> google.charts.load("current", {packages:["corechart"]}); google.charts.setOnLoadCallback(drawChart); function drawChart() { var data = google.visualization.arrayToDataTable([ ['class Name','Students'], <?php $query = "select Brand, SUM(ASP) as sum from dbms.tiv_data where Category ='EXCAVATOR' AND Dealerid = '$Emp_No' group by Brand order by sum DESC"; $exec = mysqli_query($conn, $query); while ($row = mysqli_fetch_array($exec)) { $Brand = $row['Brand']; $ASP = $row['sum']; echo "['$Brand',$ASP],"; } ?> ]); var options = { is3D: true, }; var chart = new google.visualization.PieChart(document.getElementById('Excavator_MS')); chart.draw(data, options); } </script> <div class="row " style="width:100%;margin-left:5px"> <div class="column card" style="width:33.33%;text-align:center"> <label style="text-align:center">Excavator</label> <div id="Excavator_MS" style="width: 100%; height: 200px;" onclick="Excavator()"></div> </div> </div> But i want the filter option should be placed on the graph My database is like bellow I want once i select the month the chart data should change without refresh the page. Can any one help me how to do this

Dear Team, I am having two different tables dbms.tiv_data & dbms.currentyeardata. Wheni am using sum function on bothe the tabel seperatly, it is working fine select Category,sum(Quantity) as Actual from dbms.tiv_data WHERE Brand='XCMG' group by Category the output is select Category, sum(Total) as Target from dbms.currentyeardata group by Category The output is But wheni am using inner join query , i am getting wrong output SELECT dbms.tiv_data.Category, sum(dbms.tiv_data.Quantity) AS Actual,sum(dbms.currentyeardata.Total) AS Target from dbms.currentyeardata Inner join dbms.tiv_data ON dbms.tiv_data.Dealerid = dbms.currentyeardata.Dealer_ID WHERE dbms.tiv_data.Dealerid = '81019218' AND dbms.tiv_data.Brand = 'XCMG' group by dbms.tiv_data.Category order by Actual ASC THe worng output is Can any one please correct where i am doing mistake on this query

Dear Barand, Thanks for your reply. It is the problem of space on the the ID name. Now i removed the space on Category and Description using the bellow code $Description = str_replace(' ', '', $row['Description']); Now all the target values are updating properly Once again thank you team for supporting me to complete this.

Dear Team, Waiting for your support. Please help me

Dear Team, Please help to solve this issue

Dear mac_gyver, as per your guidness I have included the Category name and Description on table column ID. My table <tr> code is <tr> <td align='center'> <?php echo $n++ ?> </td> <td style="text-align:center"> <?php echo $row['Department']; ?> </td> <td hidden> <input type="text" name="Department[]" id="Department" value=" <?php echo $row['Department']; ?> " style="width:100px" readonly /> </td> <td> <?php echo $row['Description']; ?> </td> <td hidden> <input type="text" name="Description[]" id='Description' value=" <?php echo $row['Description']; ?> " style="width:100px" readonly /> </td> <td style="text-align:center" hidden> <?php echo $row['Indicative_Margin']; ?> </td> <td hidden> <input type="text" name="Indicative_Margin[]" id="Indicative_Margin" class="Indicative_Margin" value=" <?php echo $row['Indicative_Margin']; ?> " style="width:100px" readonly /> </td> <td style="width:100px"> <input type="number" class="form-control ExcavatorTarget " name="ExcavatorTarget[]" step="0.01" pattern="^\d+(?:\.\d{1,2})?$" id=" EXCAVATOR<?php echo $row['Description']; ?>" value="" style="width:100%" /> </td> <td style="width:100px"> <input type="number" class="form-control ExcavatorActual calc" name="ExcavatorActual[]" step="0.01" pattern="^\d+(?:\.\d{1,2})?$" id="ExcavatorActual" value="" style="width:100%" /> </td> <td style="width:100px"> <input type="number" class="form-control GraderTarget " name="GraderTarget[]" step="0.01" pattern="^\d+(?:\.\d{1,2})?$" id="GRADER<?php echo $row['Description']; ?>" value="" style="width:100%" readonly /> </td> <td style="width:100px"> <input type="number" class="form-control GraderActual calc" name="GraderActual[]" step="0.01" pattern="^\d+(?:\.\d{1,2})?$" id="GraderActual" value="" style="width:100%" /> </td> <td style="width:100px"> <input type="number" class="form-control WheelLoaderTarget " name="WheelLoaderTarget[]" step="0.01" pattern="^\d+(?:\.\d{1,2})?$" id=" WHEELLOADER<?php echo $row['Description']; ?>" value="" style="width:100%" readonly /> </td> <td style="width:100px"> <input type="number" class="form-control WheelLoaderActual calc" name="WheelLoaderActual[]" step="0.01" pattern="^\d+(?:\.\d{1,2})?$" id="WheelLoaderActual" value="" style="width:100%" /> </td> <td style="width:100px"> <input type="number" class="form-control SLMTarget " name="SLMTarget[]" step="0.01" pattern="^\d+(?:\.\d{1,2})?$" id=" SLM<?php echo $row['Description']; ?>" value="" style="width:100%" readonly /> </td> <td style="width:100px"> <input type="number" class="form-control SLMActual calc" name="SLMActual[]" step="0.01" pattern="^\d+(?:\.\d{1,2})?$" id="SLMActual" value="" style="width:100%" /> </td> <td style="width:100px"> <input type="number" class="form-control RevenueTarget " name="RevenueTarget[]" step="0.01" pattern="^\d+(?:\.\d{1,2})?$" id="RevenueTarget" value="" style="width:100%" readonly /> </td> <td style="width:100px"> <input type="number" class="form-control RevenueActual" name="RevenueActual[]" id="RevenueActual" step="0.01" pattern="^\d+(?:\.\d{1,2})?$" value="" style="width:100%" readonly /> </td> <td style="width:200px" > <input type="number" name="Margin[]" class="form-control Margin" id="Margin" value="" style="width:100%" readonly autocomplete="off" /> </td> <td hidden align='center' style="width:10%"> <input type="checkbox" checked="checked" class="checkbox" name='lang[]' value="1" /> </td> </tr> Then i am fetching values from the php. My php code is <?php // Include the database config file include("../connection.php"); //error_reporting(0); error_reporting(E_ALL); ini_set('display_errors', '1'); //ini_set('display_errors','Off'); if(!empty($_POST["month"]) ){ $Month=$_POST["month"]; $dealerid = $_POST["dealerid"]; $Mon = date('M', strtotime($Month)); //$Category_Name = $_POST["Category_Name"]; // $Description = $_POST["Description"]; $sql = "select $Mon,Category_Name,Description from revenuetarget WHERE Dealer_ID = '$dealerid'"; $result = mysqli_query($conn, $sql); while ($row = mysqli_fetch_array($result)) { $Category_Name = $row['Category_Name']; $Description = $row['Description']; $Month_Value = $row[$Mon]; $return_arr[] = array( "Category_Name" => $Category_Name, "Description" => $Description, "Month_Value" => $Month_Value, ); } echo json_encode($return_arr); } ?> Then i am setting the values on the column id using ajax. Ajax code is <script>//..................month hide function function myFunction() { var key = $(this).attr('data-key'); var month = document.getElementById("month").value; var dealerid = document.getElementById("dealerid").value; //var Description = document.getElementById("Description").value; // var Description = $("#Description").val(); $.ajax({ type: 'POST', dataType: 'JSON', url: 'Revenue_Data.php', data: 'month=' + month + '&dealerid=' + dealerid, //data: 'month=' + month + '&dealerid=' + dealerid, success: function (response) { var len = response.length; for (var i = 0; i < len; i++) { var Category_Name = response[i].Category_Name; var Description = response[i].Description; var Month_Value = response[i].Month_Value; // document.getElementById("WheelLoaderTarget").value = Month_Value; document.getElementById(Category_Name + Description).value = Month_Value; } } }); } </script> But the value is not appending on the table. Can you please check and confirm the mistake

Please help me

Dear Team, Please help me

I am setting the target for all my descriptions monthly wise to my all dealers. The dealer will enter the actual vaues monthly wise and submit the form. So when the dealer is selecting the month, he wants to know what is his target for the description on that particular selected month. This is the concept of these page.

Dear mac I am just learning the php code with help of experts like you and I never used Indexed/ Pivoted data in loop and dont have idea on this. please help me