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

Recommended Posts

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

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

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

, 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

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

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

Archived

This topic is now archived and is closed to further replies.

×
×
  • 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.