Looking for a Strategy to get multiple tables from a MySQL Query in PHP

Question

I'm trying to build a set of results from a query in a PHP page. My where clause is complicated and potentially expensive. In my usual languages I'd do something like:

CREATE TEMPORARY TABLE ShortList (ID INT);
INSERT INTO ShortList
  SELECT ID FROM Table1 WHERE {Super long query};

SELECT * FROM Table1 JOIN ShortList ON ShortList.ID = Table1.ID;
SELECT * FROM Table2 JOIN ShortList ON ShortList.ID = Table2.Table1ID;
SELECT * FROM Table3 JOIN ShortList ON ShortList.ID = Table3.Table1ID;
SELECT * FROM Table4 JOIN ShortList ON ShortList.ID = Table4.Table1ID;

Then I'd iterate through each resultset pulling each row. Given this data it's not very easy to join across all of the tables; this would result in a lot of duplicate data.

So what's the PHP way to accomplish this?

Answer 1

I would suggest you to try writing an SQL Procedure (Stored Procedure) to accomplish this. Procedures are meant to handle complex/multiple queries.

What RDBMS are you using ? Most modern RDBMS support stored procedures.

http://en.wikipedia.org/wiki/Stored_procedure

Just in case, if you are using MySQL -> http://dev.mysql.com/tech-resources/articles/mysql-storedproc.html

Answer 2

I haven't ever done anything quite as complex as this, but I suggest you create a database access class to handle your queries. Then you can create multiple objects of that class to hold the three result sets simultaneously. Something like...

$big_query   = new database_access ();
$table1      = new database_access ();
$other_table = new database_access ();

$big_query->query ("large query string");

while ($row_array = $big_query->fetch ())
{
    $table1->query ("table 1 query string accessing ".$row_array[id]);

    while ($table1_row_array = $table1->fetch ())
    {
        $other_table->query ("other table query accessing ".$table1_row_array[table1ID]);

        while ($other_table_row_array = $other_table->fetch ())
        {
            // deal with results
        }

        // ...etc...

    }

}

I hope that's what you had in mind and I haven't gone off and answered a different question.

Answer 3

I've figured this out. I'm used to a transaction model where I have to explicitly put multiple statements into a single transaction. PHP/MySQL assumes that multiple queries are in the same transaction. So ultimately the way to do this is to run one query per SQL statement:

mysql_query("CREATE TEMPORARY TABLE ShortList (ID INT)");
mysql_query("INSERT INTO ShortList SELECT ID FROM Table1 WHERE {Super long query}");

mysql_query("SELECT * FROM Table1 JOIN ShortList ON ShortList.ID = Table1.ID");
mysql_query("SELECT * FROM Table2 JOIN ShortList ON ShortList.ID = Table2.Table1ID");
mysql_query("SELECT * FROM Table3 JOIN ShortList ON ShortList.ID = Table3.Table1ID");
mysql_query("SELECT * FROM Table4 JOIN ShortList ON ShortList.ID = Table4.Table1ID");

Hope that helps anyone else with the same problem.