Jump to content

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 i

vanvoquan

By vanvoquan
in PHP Coding Help

 Share
Go to solution Solved by Barand,

Recommended Posts

vanvoquan Member

vanvoquan

Posted
Posted

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:&nbsp&nbsp&nbsp&nbsp&nbsp <input type="text" id="num1" name="sale" style="width: 100px;" /></td></tr><tr><td valign="top">
Tip:&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp <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>
Link to comment
Share on other sites
Barand Prolific Member

Barand

Posted
Posted

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
Link to comment
Share on other sites
vanvoquan Member

vanvoquan

Posted
  • Author
Posted

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:&nbsp&nbsp&nbsp&nbsp&nbsp <input type="text" id="num1" name="sale" style="width: 100px;" /></td></tr><tr><td valign="top">
Tip:&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp <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>
Link to comment
Share on other sites
  • Solution
Barand Prolific Member

Barand

Posted
  • Solution
Posted

, 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
Link to comment
Share on other sites
vanvoquan Member

vanvoquan

Posted
  • Author
Posted (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:&nbsp&nbsp&nbsp&nbsp&nbsp <input type="text" id="num1" name="sale" style="width: 100px;" /></td></tr><tr><td valign="top">
Tip:&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp <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 by vanvoquan
Link to comment
Share on other sites
Barand Prolific Member

Barand

Posted
Posted

You have

LEFT JOIN everyday_sale s ON s.technictian_id = t.id
That 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.

Link to comment
Share on other sites
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.