PHP MYSQL - Combine Query From Another Variable

Question

May I know if I can combine queries from different variable or is there any other way. I want to insert $query_1 to $query_2

Example:

$sql = "SELECT l_id from mlk";
$result = mysqli_query($conn, $sql);
$count = mysqli_num_rows($result); 

if (mysqli_num_rows($result) > 0) {
    while($row = mysqli_fetch_assoc($result)) {
        $l_id[] = $row["l_id"];
     }
}

mysqli_free_result($result);

foreach($l_id as $row){
    $query_1= "(select count(*) from staff s where s.l_id = $row) as mlk_$row,";
}

$query_2= "SELECT mlk.lokasi,
//insert $query_1 here  
from mlk INNER JOIN staff WHERE staff.l_id=mlk.l_id GROUP BY mlk.lokasi";
$result = mysqli_query($conn, $sql);

mlk is a table that stores staff working location
$query_2 will display the Location Name and I also want to display the total number of staff on that particular location. eg, Kuala Lumpur, 10

Shadow · Accepted Answer · 2017-03-03 04:06:22Z

2

Just include the counting in the 2nd query:

SELECT mlk.lokasi, count(*) as no_of_staff 
from mlk
INNER JOIN staff on staff.l_id=mlk.l_id
GROUP BY mlk.lokasi

I also changed the implicit join into an explicit one by moving the join condition from the where clause to the on clause.

answered Mar 3, 2017 at 4:06

Shadow

34.5k10 gold badges67 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Amran Over a year ago

thanks for the answer @Shadow but will that count all the no. of staff. What if I want to count the no of staff base on location? eg, Kuala Lumpur | 10, Melaka | 5, Johor | 7

Shadow Over a year ago

Try it and you shall see :) Also you should read an sql tutorial on aggregate functions and group by...

Amran Over a year ago

It works great! Thanks for the info and advice. Will look through it.

Manohar Godase · Accepted Answer · 2017-03-03 04:48:29Z

0

Use left join for this to show all the locations and then each location staff count.

SELECT mlk.lokasi, count(*) as no_of_staff from mlk LEFT JOIN staff on staff.l_id=mlk.l_id GROUP BY mlk.lokasi order by mlk.lokasi ASC

answered Mar 3, 2017 at 4:48

Manohar Godase

1094 bronze badges

Collectives™ on Stack Overflow

PHP MYSQL - Combine Query From Another Variable

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related