vanvoquan Posted February 10, 2018 Share Posted February 10, 2018 Hi, i'm new to php and mysql so any helps will be greatly appreciated. I have the the code below but it looks like it's not working. I need the data id, firstname from table technictians and the total sales(add all sales in the database together) in the everyday_sale table. It's give me the total sale in the second query but not the id and firstname from the first query. If i take out the second query then the first query gave me the id and firstname just fine. It's seem like that two queries dont like each other some how. Thanks in advance and will consider give donation if got the problem solve. <HTML> <HEAD> <TITLE></TITLE> <script src="http://ajax.googleapis.com/ajax/libs/jquery/1.11.0/jquery.min.js"></script> <script src="onpage_submit.js"></script> <META name="description" content=""> <META name="keywords" content=""> <META name="generator" content="CuteHTML"> </HEAD> <BODY BGCOLOR="#FFFFFF" TEXT="#000000" LINK="#0000FF" VLINK="#800080"> <?Php $connection = new mysqli('localhost', 'root', 'Mylovev1@', 'usnailsandspa'); if ($connection->connect_errno > 0) { die ('Unable to connect to database [' . $connection->connect_error . ']'); } $sql = "SELECT * FROM technictians"; if (!$result = $connection->query($sql)) { die ('There was an error running query[' . $connection->error . ']'); } ?> <?Php $connection = new mysqli('localhost', 'root', 'Mylovev1@', 'usnailsandspa'); if ($connection->connect_errno > 0) { die ('Unable to connect to database [' . $connection->connect_error . ']'); } $query = "SELECT SUM(sale) FROM everyday_sale where technictian_id= '.$row['id'].'"; if (!$result = $connection->query($query)) { die ('There was an error running query[' . $connection->error . ']'); } ?> <?php $rows = $result->num_rows; // Find total rows returned by database if($rows > 0) { $cols = 3; // Define number of columns $counter = 1; // Counter used to identify if we need to start or end a row $nbsp = $cols - ($rows % $cols); // Calculate the number of blank columns echo '<table border="1" bgcolor="#E0F2F7" bordercolor="blue" width ="700" height ="700" align="center">'; while ($row = $result->fetch_array()) { if(($counter % $cols) == 1) { // Check if it's new row echo '<tr>'; } echo '<td><table align="center"><tr><td align="center"><font size="5" color="red"><b>'.$row['firstname'].'</b></font></td></tr><tr><td valign="top"><form action="salaries_add.php" method="post"> Sale:      <input type="text" id="num1" name="sale" style="width: 100px;" /></td></tr><tr><td valign="top"> Tip:       <input type="text" id="num2" name="tip" style="width: 100px;" /></td></tr><tr><td valign="top"> Ticket#:<input type="text" id="ticket_number" name="ticket_number" style="width: 100px;" /></td></tr><tr><td valign="top" align="center"> <input type="hidden" name="technictian_id" value="' .$row['id']. '"> <input type="submit" value="Submit"></form></td></tr> <tr><td><hr></td></tr> <tr><td align="center"><font sie="5" color="maroon"><b>Earning Totals:</b></font></td></tr> <tr><td>'.$row['SUM(sale)'].'</td></tr> </table></td>'; if(($counter % $cols) == 0) { // If it's last column in each row then counter remainder will be zero echo '</tr>'; } $counter++; // Increase the counter } $result->free(); if($nbsp > 0) { // Add unused column in last row for ($i = 0; $i < $nbsp; $i++) { echo '<td> </td>'; } echo '</tr>'; } echo '</table>'; } ?> <script> </BODY> </HTML> Quote Link to comment https://forums.phpfreaks.com/topic/306485-hi-im-new-to-php-and-mysql-so-any-helps-will-be-greatly-appreciated-i-have-the-the-code-below-but-it-looks-like-its-not-working-i-need-the-data-i/ Share on other sites More sharing options...
Barand Posted February 10, 2018 Share Posted February 10, 2018 You need connect only once at the beginning of your script, not for each query. Your problem arises because you put the results of both queries into the same variable ($result) so the second query overwrites the first. You don't need two queries for this task, one query wth a JOIN will do the job. SELECT t.id , t.firstname . t.lastname , SUM(s.sale) as sales FROM everyday_sale s JOIN technician t ON s.technician_id = t.id GROUP BY t.id Quote Link to comment https://forums.phpfreaks.com/topic/306485-hi-im-new-to-php-and-mysql-so-any-helps-will-be-greatly-appreciated-i-have-the-the-code-below-but-it-looks-like-its-not-working-i-need-the-data-i/#findComment-1556212 Share on other sites More sharing options...
vanvoquan Posted February 10, 2018 Author Share Posted February 10, 2018 Thanks Barand for quick replying. I put your new code in it give the the id and the name but not the SUM of sale in "<tr><td>'.$row['SUM(sale)'].'</td></tr>". Also it only displays the first technictian instead of all the technictians. Did i missing something? Below is the updated code <HTML> <HEAD> <TITLE></TITLE> <script src="http://ajax.googleapis.com/ajax/libs/jquery/1.11.0/jquery.min.js"></script> <script src="onpage_submit.js"></script> <META name="description" content=""> <META name="keywords" content=""> <META name="generator" content="CuteHTML"> </HEAD> <BODY BGCOLOR="#FFFFFF" TEXT="#000000" LINK="#0000FF" VLINK="#800080"> <?Php $connection = new mysqli('localhost', 'root', 'Mylovev1@', 'usnailsandspa'); if ($connection->connect_errno > 0) { die ('Unable to connect to database [' . $connection->connect_error . ']'); } $sql = "SELECT t.id, t.firstname, SUM(s.sale) as sale FROM everyday_sale s JOIN technictians t ON s.technictian_id = t.id GROUP BY t.id"; if (!$result = $connection->query($sql)) { die ('There was an error running query[' . $connection->error . ']'); } ?> <?php $rows = $result->num_rows; // Find total rows returned by database if($rows > 0) { $cols = 3; // Define number of columns $counter = 1; // Counter used to identify if we need to start or end a row $nbsp = $cols - ($rows % $cols); // Calculate the number of blank columns echo '<table border="1" bgcolor="#E0F2F7" bordercolor="blue" width ="700" height ="700" align="center">'; while ($row = $result->fetch_array()) { if(($counter % $cols) == 1) { // Check if it's new row echo '<tr>'; } echo '<td><table align="center"><tr><td align="center"><font size="5" color="red"><b>'.$row['firstname'].'</b></font></td></tr><tr><td valign="top"><form action="salaries_add.php" method="post"> Sale:      <input type="text" id="num1" name="sale" style="width: 100px;" /></td></tr><tr><td valign="top"> Tip:       <input type="text" id="num2" name="tip" style="width: 100px;" /></td></tr><tr><td valign="top"> Ticket#:<input type="text" id="ticket_number" name="ticket_number" style="width: 100px;" /></td></tr><tr><td valign="top" align="center"> <input type="hidden" name="technictian_id" value="' .$row['id']. '"> <input type="submit" value="Submit"></form></td></tr> <tr><td><hr></td></tr> <tr><td align="center"><font sie="5" color="maroon"><b>Earning Totals:</b></font></td></tr> <tr><td>'.$row['SUM(sale)'].'</td></tr> </table></td>'; if(($counter % $cols) == 0) { // If it's last column in each row then counter remainder will be zero echo '</tr>'; } $counter++; // Increase the counter } $result->free(); if($nbsp > 0) { // Add unused column in last row for ($i = 0; $i < $nbsp; $i++) { echo '<td> </td>'; } echo '</tr>'; } echo '</table>'; } ?> <script> </BODY> </HTML> Quote Link to comment https://forums.phpfreaks.com/topic/306485-hi-im-new-to-php-and-mysql-so-any-helps-will-be-greatly-appreciated-i-have-the-the-code-below-but-it-looks-like-its-not-working-i-need-the-data-i/#findComment-1556214 Share on other sites More sharing options...
Solution Barand Posted February 10, 2018 Solution Share Posted February 10, 2018 , SUM(sales) as sales Because I gave SUM() the alias "sales" you should reference that value with $row['sales']. Is the technician that appears the only one with any sales? The JOIN will only result in rows where there are matching records in both tables. If you want all technicians even if they have no sales then we need to switch to a LEFT JOIN SELECT t.id , t.firstname . t.lastname , SUM(s.sale) as sales FROM technician t LEFT JOIN everyday_sale s ON s.technician_id = t.id GROUP BY t.id Quote Link to comment https://forums.phpfreaks.com/topic/306485-hi-im-new-to-php-and-mysql-so-any-helps-will-be-greatly-appreciated-i-have-the-the-code-below-but-it-looks-like-its-not-working-i-need-the-data-i/#findComment-1556216 Share on other sites More sharing options...
vanvoquan Posted February 10, 2018 Author Share Posted February 10, 2018 (edited) Thanks Barand. After fixing the code, it displays all the technictians but not the sum of sales for that technictian.I think the data did not showing because we didnt give them the technictian_id. Here is the two table i got Table technictians: id, firstname, lastname, etc Table everyday_sale: id, technictian_id, sale I think we have to write something like : where technictian_id = '.$row['id'].' maybe? And are you accepting paypal for donation? Below is my updated code <HTML> <HEAD> <TITLE></TITLE> <script src="http://ajax.googleapis.com/ajax/libs/jquery/1.11.0/jquery.min.js"></script> <script src="onpage_submit.js"></script> <META name="description" content=""> <META name="keywords" content=""> <META name="generator" content="CuteHTML"> </HEAD> <BODY BGCOLOR="#FFFFFF" TEXT="#000000" LINK="#0000FF" VLINK="#800080"> <?Php $connection = new mysqli('localhost', 'root', 'Mylovev1@', 'usnailsandspa'); if ($connection->connect_errno > 0) { die ('Unable to connect to database [' . $connection->connect_error . ']'); } $sql = "SELECT t.id, t.firstname, SUM(s.sale) as sales FROM technictians t LEFT JOIN everyday_sale s ON s.technictian_id = t.id GROUP BY t.id"; if (!$result = $connection->query($sql)) { die ('There was an error running query[' . $connection->error . ']'); } ?> <?php $rows = $result->num_rows; // Find total rows returned by database if($rows > 0) { $cols = 3; // Define number of columns $counter = 1; // Counter used to identify if we need to start or end a row $nbsp = $cols - ($rows % $cols); // Calculate the number of blank columns echo '<table border="1" bgcolor="#E0F2F7" bordercolor="blue" width ="700" height ="700" align="center">'; while ($row = $result->fetch_array()) { if(($counter % $cols) == 1) { // Check if it's new row echo '<tr>'; } echo '<td><table align="center"><tr><td align="center"><font size="5" color="red"><b>'.$row['firstname'].'</b></font></td></tr><tr><td valign="top"><form action="salaries_add.php" method="post"> Sale:      <input type="text" id="num1" name="sale" style="width: 100px;" /></td></tr><tr><td valign="top"> Tip:       <input type="text" id="num2" name="tip" style="width: 100px;" /></td></tr><tr><td valign="top"> Ticket#:<input type="text" id="ticket_number" name="ticket_number" style="width: 100px;" /></td></tr><tr><td valign="top" align="center"> <input type="hidden" name="technictian_id" value="' .$row['id']. '"> <input type="submit" value="Submit"></form></td></tr> <tr><td><hr></td></tr> <tr><td align="center"><font sie="5" color="maroon"><b>Earning Totals:</b></font></td></tr> <tr><td>'.$row['SUM(sales)'].'</td></tr> </table></td>'; if(($counter % $cols) == 0) { // If it's last column in each row then counter remainder will be zero echo '</tr>'; } $counter++; // Increase the counter } $result->free(); if($nbsp > 0) { // Add unused column in last row for ($i = 0; $i < $nbsp; $i++) { echo '<td> </td>'; } echo '</tr>'; } echo '</table>'; } ?> <script> </BODY> </HTML> Edited February 10, 2018 by vanvoquan Quote Link to comment https://forums.phpfreaks.com/topic/306485-hi-im-new-to-php-and-mysql-so-any-helps-will-be-greatly-appreciated-i-have-the-the-code-below-but-it-looks-like-its-not-working-i-need-the-data-i/#findComment-1556217 Share on other sites More sharing options...
Barand Posted February 10, 2018 Share Posted February 10, 2018 You have LEFT JOIN everyday_sale s ON s.technictian_id = t.idThat does the matching of the ids for you. You only need a WHERE if want to restrict the output to one specific technician. Try running the query manually against your database (phpMyAdmin, Mysql Workbench or mysql command line) to verify it works. If it does work then the problem is somewherein you handling of the output in that mass of html you have as single obfuscated line of code. Quote Link to comment https://forums.phpfreaks.com/topic/306485-hi-im-new-to-php-and-mysql-so-any-helps-will-be-greatly-appreciated-i-have-the-the-code-below-but-it-looks-like-its-not-working-i-need-the-data-i/#findComment-1556219 Share on other sites More sharing options...
Barand Posted February 10, 2018 Share Posted February 10, 2018 Having broken down that mess of HTML (which uses obsolete font, color tags etc) I see that, despite what I told you, you are still using $row['SUM(sales)'] Quote Link to comment https://forums.phpfreaks.com/topic/306485-hi-im-new-to-php-and-mysql-so-any-helps-will-be-greatly-appreciated-i-have-the-the-code-below-but-it-looks-like-its-not-working-i-need-the-data-i/#findComment-1556220 Share on other sites More sharing options...
vanvoquan Posted February 10, 2018 Author Share Posted February 10, 2018 Thanks Barand, it's work now. Perfect ! Quote Link to comment https://forums.phpfreaks.com/topic/306485-hi-im-new-to-php-and-mysql-so-any-helps-will-be-greatly-appreciated-i-have-the-the-code-below-but-it-looks-like-its-not-working-i-need-the-data-i/#findComment-1556221 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.