Combine two queries in php, newbie please help

PHP
,
1

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.

<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', 'username', 'password');
        
        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', 'username', 'password');
        
        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>&nbsp;</td>';         
                        }
                        echo '</tr>';
                }
                echo '</table>';
        }
?>
<script>
</BODY>
</HTML>
2

Your second query is overwriting the first one because you use the same variable: $result.

3

Since you are new, lets start with the basics. As your title suggests, a single query will suffice. I’d suggest starting with a simple schema just to get up to speed with queries.

# schema.sql
use tech;

drop table if exists everyday_sale;
drop table if exists technictians;

create table technictians
(
  id INTEGER UNSIGNED AUTO_INCREMENT NOT NULL,

  firstname  VARCHAR(255) NOT NULL,

  CONSTRAINT technictians_primaryKey PRIMARY KEY(id)

) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;

create table everyday_sale
(
  id INTEGER UNSIGNED AUTO_INCREMENT NOT NULL,

  technictians_id INTEGER UNSIGNED NOT NULL,

  sale INTEGER UNSIGNED NOT NULL,

  CONSTRAINT everyday_sale_primaryKey PRIMARY KEY(id),

  CONSTRAINT
    technictians_foriegnKey_parent
    FOREIGN KEY(technictians_id)
    REFERENCES  technictians(id)
    ON DELETE CASCADE

) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;

INSERT INTO technictians (firstname) values('Donald');
INSERT INTO technictians (firstname) values('Mike');

INSERT INTO everyday_sale (technictians_id,sale) values(1,5);
INSERT INTO everyday_sale (technicians_id,sale) values(1,6);
INSERT INTO everyday_sale (technictians_id,sale) values(1,7);
INSERT INTO everyday_sale (technictians_id,sale) values(2,10);
INSERT INTO everyday_sale (technictians_id,sale) values(2,20);

Assuming you have a database called tech created then do something like:
mysql
source schema.sql;
show tables;
select * from technictians;

So what you have now is a simple and easy to modify schema with some sample data. Now your sales query will look something like:

SELECT technictians.id,firstname,SUM(sale) AS total_sales
FROM technictians
LEFT JOIN everyday_sale ON technictians.id = everyday_sale.technictians_id
GROUP BY technictians.id;

Running the query should result in:

+----+-----------+-------------+
| id | firstname | total_sales |
+----+-----------+-------------+
|  1 | Donald    |          18 |
|  2 | Mike      |          30 |
+----+-----------+-------------+

At this point you can edit your php file and chop out most of your query and loop stuff replacing it with the query above. Should simplify things.

I won’t get into rewriting your code here but you might consider moving the query to the top of the file before you dump out any html stuff.

3 Likes
4

works perfect ! Thanks millions ahundiak.

5

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.