Php & Mysql, filter out subqueries from PHP-for loop into MySQL statement

Question

I have to adapt a PHP-script that is connected to a extremely messy database-design. The best way to explain the problem is to post the code, within which im going to describe whats happening through comments inside the code-block. Plus a short introduction:

Short Story:
1.) There is a "mainQuery" and inside the for-loop that displays its data there are 3 nested subqueries.
2.) All three subqueries need the main_table_ID in order to count the occurences of each fetched_row.

What i want:
1.) An optimzied mainQuery that counts 3 occurences of an id separately on its own.
2.) Thus no subqueries at all inside the for loop.

What i have already achieved via optimizing the mainQuery:
1.) I can filter a subquery one at a time by, see statement:

SELECT
       main_table.id_col,
       count(table_1.a_col) AS count_1,
       main_table.a_col,
       main_table.b_col,
       main_table.c_col,
       main_table.d_col,
       main_table.e_col

FROM   main_table
LEFT JOIN
       table_1
ON
       table_1.a_col = main_table.id_col
GROUP BY
       main_table.id_col

But when i add another LEFT JOIN for the one of other nested statements inside the for-loop, the statement sums everything up and count_1 to count_3 look the same.

And now the code-block containing the subqueries and the for-loop:

<?php
$mainQuery = "SELECT 
                          main_table.id_col, 
                          main_table.a_Col, 
                          main_table.b_Col, 
                          main_table.c_col, 
                          main_table.d_col, 
                          main_table.e_col 
                   FROM 
                          main_table";

$mainQueryResult = mysql_query($mainQuery);
$mainQueryRows = mysql_num_rows($mainQueryResult);

for($j = 0 ; $j < $mainQueryRows ; ++$j){
    //mainQuery data
    $mainQueryRow = mysql_fetch_row($mainQueryResult);
    $main_table_ID = $mainQueryRow[0];

    //first subquery, can be filtered out one at a time 
    //using introductory-statement
    $count_1_subQuery = "SELECT * FROM table_1 
                         WHERE table_1.id_col = '$main_table_ID'";
    //first subquery-count of rows with matching id
    $count_1 = mysql_num_rows(mysql_query($count_1_SubQuery, $connection));

    //second subquery
    $count_2_subquery = "SELECT * FROM table_2 
             WHERE table_2.id_col = '$main_table_ID'";
    //second subquery-count of rows with matching id
    $count_2 = mysql_num_rows(mysql_query($count_2_subQuery, $connection));

    //third subquery
    $count_3_subquery = "SELECT * FROM table_3 
             WHERE table_3.id_col = '$main_table_ID'";
    //third subquery-count of rows with matching id
    $count_3 = mysql_num_rows(mysql_query($count_3_subQuery, $connection));

    //Fill Table with count_1 to count_3 
        //and main_table.a_col to main_table.e_col
    //no problems here
}
?>

I hope i could explain the problem clearly enough and i also hope one of you guys can help me, if he/she can allot time for such a question.

ypercubeᵀᴹ · Accepted Answer · 2011-12-08 13:20:47Z

1

First GROUP BY in subqueries, then JOIN to the main table:

SELECT  
      m  . id_col
    , g1 . count_1  
    , g2 . count_2  
    , g3 . count_3  
    , m  . a_col  
    , m  . b_col  
    , m  . c_col  
    , m  . d_col  
    , m  . e_col      
FROM 
        main_table AS m
    LEFT JOIN 
        ( SELECT id_col
              , COUNT(*) AS count_1  
          FROM table_1  
          GROUP BY id_col 
        ) AS g1
      ON g1.id_col = m.id_col
    LEFT JOIN 
        ( SELECT id_col
               , COUNT(*) AS count_2  
          FROM table_2 
          GROUP BY id_col 
        ) AS g2
      ON g2.id_col = m.id_col
    LEFT JOIN 
        ( SELECT id_col
               , COUNT(*) AS count_3  
          FROM table_3  
          GROUP BY id_col 
        ) AS g3
      ON g3.id_col = m.id_col

answered Dec 8, 2011 at 13:20

ypercubeᵀᴹ

116k19 gold badges181 silver badges249 bronze badges

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

1 Comment

kiltek Over a year ago

Action: "modified in above fashion". Result:"Works as intended". Thanks

Johan · Accepted Answer · 2011-12-08 13:08:58Z

0

SELECT  
  main_table.id_col,  
  count(DISTINCT t1.id_col) AS count_1,  
  count(DISTINCT t2.id_col) AS count_2,  
  count(DISTINCT t3.id_col) AS count_3,  
  main_table.a_col,  
  main_table.b_col,  
  main_table.c_col,  
  main_table.d_col,  
  main_table.e_col      
FROM  main_table m
LEFT JOIN table_1 t1 ON (t1.id_col = m.id_col)
LEFT JOIN table_2 t2 ON (t2.id_col = m.id_col)
LEFT JOIN table_3 t3 ON (t3.id_col = m.id_col)
GROUP BY m.id_col

edited Dec 8, 2011 at 13:08

answered Dec 8, 2011 at 12:17

Johan

77.4k28 gold badges205 silver badges346 bronze badges

4 Comments

kiltek Over a year ago

Result: counts incorrectly, count_1 also sums up hits made by join done for count_2 and count_3 vice versa.

Johan Over a year ago

Add a distinct in the count.

kiltek Over a year ago

Action: "Distinct added". Result: all counts do not exceed count = "1". Not working. Still searching for solutions.

ypercubeᵀᴹ Over a year ago

@kiltek: You can try by changing: count(DISTINCT t1.id_col) to count(DISTINCT t1.PK) where PK is the Primary Key of table_1.

Collectives™ on Stack Overflow

Php & Mysql, filter out subqueries from PHP-for loop into MySQL statement

2 Answers 2

1 Comment

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related