How to combine 2 MySQL queries where one is defined in another while loop?

Question

I am combining 2 queries. I can SELECT * from dialogue_list, with dialogue_list.*, but I wasn't sure how to put in the employee_id='$rowemployees[employee_id]' into my main query on top? It is combining PHP and MySQL, but can all become just MySQL.

$sqlemployees = mysqli_query('select u.fname, u.lname, d_e.*, d_list.*, d_list.emplsub.sqlcal AS sqlcalcemp 
from 
(select count(*) AS sqlcal from  dialogue_employees d_e, 
dialogue_leaders d_l 
  where
  d_l.leader_group_id = d_e.leader_group_id and
  d_l.cycle_id = $cycle_id) AS sub,
dialogue_list d_list, user u, dialogue_employees d_e, 
dialogue_leaders d_l 
where employee_id=d_list.employee_id and
u.userID = d_e.employee_id and 
d_l.leader_group_id = d_e.leader_group_id and
d_l.cycle_id = $cycle_id') or die(mysql_error());

while($rowemployees=mysqli_fetch_array($sqlemployees))
{
  $sqli=mysqli_query("select * 
  from dialogue_list 
  where employee_id='$rowemployees[employee_id]' and cycle_id='$cycle_id'")or die(mysql_error());
}

**WARNING**: If you're just learning PHP, please, do not learn the obsolete [`mysql_query`](http://php.net/manual/en/function.mysql-query.php) interface. It's awful and has been removed in PHP 7. A replacement like [PDO is not hard to learn](http://net.tutsplus.com/tutorials/php/why-you-should-be-using-phps-pdo-for-database-access/) and a guide like [PHP The Right Way](http://www.phptherightway.com/) helps explain best practices. Your user parameters are **not** [properly escaped](http://bobby-tables.com/php) and you probably have severe [SQL injection bugs](http://bobby-tables.com/) here. — tadman, Apr 15 '16 at 19:25
I am editing 10 year old code and bringing it up-to-date. I first have to combine the queries before I can do this though. I just got stumped on this PHP part with the employee_id=.'$rowemployees[employee_id]' and how to put it in the main SQL query — , Apr 15 '16 at 19:26
Sounds like quite a project. You seem to be mixing `mysqli` and `mysql_query` here, so this thing is unlikely to work at all until you fix that. If `mysqli` is properly configured you can use parameterized queries and [`bind_param`](http://php.net/manual/en/mysqli-stmt.bind-param.php) to fix most of your escaping problems. — tadman, Apr 15 '16 at 19:33
For now if I change it to mysqli will that help. I know it won't help with MySQL injection, but may bring newer standards of mysql. PDO is best but I will have to conquer that when I fix these SQL queries. So the bind_param may work. I'll look into it. — , Apr 15 '16 at 19:36
I listened to your suggestion and put mysqli in there for now. — , Apr 15 '16 at 19:47
`mysqli` is fine for now. Just be sure you're using it exclusively, as things like `mysql_error` won't work in conjunction, they're two unrelated systems despite having a similar name. [Turning on exceptions](http://stackoverflow.com/questions/18457821/how-to-make-mysqli-throw-exceptions-using-mysqli-report-strict) should fix that problem. — tadman, Apr 15 '16 at 19:51

score 0 · Answer 1 · answered Apr 15 '16 at 22:52

You have to do a LEFT JOIN I found out and see how the tables interact with each other (that is where I have to combine the cycle_id's. Select * Dialogue_list is the d_list.* and then it was seeing what combined for the employee_id.

select u.fname, u.lname, d_e.*, d_list.*, subb.sqlcal AS sqlcalemp 
from (select count(*) as sqlcal from  dialogue_employees d_e, 
    dialogue_leaders d_l 
where
    d_l.leader_group_id = d_e.leader_group_id and
    d_l.cycle_id = $cycle_id) as subb, 
user u, dialogue_employees d_e 
LEFT JOIN dialogue_list d_list ON d_e.employee_id = d_list.employee_id, 
    dialogue_leaders d_l where
    u.userID = d_e.employee_id and 
    d_l.leader_group_id = d_e.leader_group_id and
    d_l.cycle_id = $cycle_id and d_list.cycle_id = $cycle_id

score 0 · Answer 2 · edited May 23 '17 at 12:33

Consider the following SQL query using explicit joins. Also, you might need to group by employee_id in your derived count aggregate derived table, subb. Additionally, see try/catch, parameterized mysqli queries can be handled in PHP.

SQL (embedded below with $cycle_id parameterized)

SELECT u.fname, u.lname, d_e.*, d_list.*, subb.sqlcal AS sqlcalemp 
FROM

   (SELECT d_e.employee_id, count(*) as sqlcal
    FROM dialogue_employees d_e 
    INNER JOIN dialogue_leaders d_l
            ON d_l.leader_group_id = d_e.leader_group_id
    WHERE d_l.cycle_id = $cycle_id
    GROUP BY d_e.employee_id) as subb

INNER JOIN user u
        ON subb.employee_id = u.userID 
INNER JOIN dialogue_employees d_e
        ON u.userID = d_e.employee_id
INNER JOIN dialogue_leaders d_l
        ON d_l.leader_group_id = d_e.leader_group_id
 LEFT JOIN dialogue_list d_list
        ON d_e.employee_id = d_list.employee_id

WHERE d_l.cycle_id = $cycle_id
AND d_list.cycle_id = $cycle_id

PHP

$cycle_id = ...;

try {
    // DATABASE CONNECTION
    $conn = new mysqli($servername, $username, $password, $dbname);        

    // DEFINE SQL STRING
    $sql = "SELECT u.fname, u.lname, d_e.*, d_list.*, subb.sqlcal AS sqlcalemp 
            FROM            
               (SELECT d_e.employee_id, count(*) as sqlcal
                FROM dialogue_employees d_e 
                INNER JOIN dialogue_leaders d_l
                        ON d_l.leader_group_id = d_e.leader_group_id
                WHERE d_l.cycle_id = ?
                GROUP BY d_e.employee_id) as subb            
            INNER JOIN user u
                    ON subb.employee_id = u.userID 
            INNER JOIN dialogue_employees d_e
                    ON u.userID = d_e.employee_id
            INNER JOIN dialogue_leaders d_l
                    ON d_l.leader_group_id = d_e.leader_group_id
             LEFT JOIN dialogue_list d_list
                    ON d_e.employee_id = d_list.employee_id                
            WHERE d_l.cycle_id = ?
            AND d_list.cycle_id = ?";

    // PREPARE AND BIND SQL STATEMENT
    $stmt = $conn->prepare($sql);
    $stmt->bind_param("iii", $cycle_id, $cycle_id, $cycle_id);

    // EXECUTE STATEMENT
    $result = $stmt->execute();

}
catch(mysqli_sql_exception $e) {  
    echo $e->getMessage()."\n";
}    

# CLOSE CONNECTION AND DATABASE
$stmt->close();
$conn->close();

$conn = null;

How to combine 2 MySQL queries where one is defined in another while loop?

2 Answers2