Good practice for generating complex reports (MySQL/PHP)

Question

PHP mysql_query function doesn't allow multiple queries, which limits maximal report complexity. What is the best practice, if I need really complex reports generated, which require multiple temp tables and multiple queries?

UPDATE: My question is more about how to use MySQL complex queries in PHP, if mysql_query only allows single query?

You can always use PDO, Which allows for multiple queries and multiple statements. — Madara's Ghost, Apr 12 '12 at 08:20
I think you could use transactions, temporary tables to get whatever report you need. — jancha, Apr 12 '12 at 08:33
Maybe you could give an example of a query that you can't see how to do with mysql_query? — Simon, Apr 12 '12 at 08:36
I don't see what `mysql_query` PHP function has to do with creating complex MySQL reports. If you need results of multiple queries, then you're using the wrong library which is also outdated. As @Truth said, use PDO. — N.B., Apr 12 '12 at 08:43

score 1 · Answer 1 · answered Apr 12 '12 at 08:29

PHP mysql_query function doesn't allow multiple queries, which limits maximal report complexity

Not in my experience it doesn't.

Temporary tables are available for the duration of the session - if you want to use temporary tables for composing your report (since the availability of sub-queries, they are not required) what's wrong with...

 mysql_query('CREATE TEMPORARY TABLE workset ....
 mysql_query('INSERT INTO workset (...) SELECT ....
 mysql_query('UPDATE workset......
 ....
 mysql_query('SELECT * FROM workset....

Or using a stored procedure for creating a materialized view?

score 1 · Answer 2 · answered Apr 12 '12 at 08:40

well if you are looking for executing multiple queries, then why don't you use mysqli_multi_query example taken out of PHP manual:

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

$query  = "SELECT CURRENT_USER();";
$query .= "SELECT Name FROM City ORDER BY ID LIMIT 20, 5";

/* execute multi query */
if ($mysqli->multi_query($query)) {
    do {
        /* store first result set */
        if ($result = $mysqli->store_result()) {
            while ($row = $result->fetch_row()) {
                printf("%s\n", $row[0]);
            }
            $result->free();
        }
        /* print divider */
        if ($mysqli->more_results()) {
            printf("-----------------\n");
        }
    } while ($mysqli->next_result());
}

/* close connection */
$mysqli->close();
?>

references: mysqli_multi_query

score 0 · Answer 3 · edited May 23 '17 at 12:04

0

What did you mean about "maximal report complexity"? Now I use SSRS. But this is Microsoft solution. I googled that Reportico Tools may help you. As alternative, ZF (for example) contain huge functionality for interacting with databases. Possibly this article can help you.

edited May 23 '17 at 12:04

Community

1
1

answered Apr 12 '12 at 08:14

Valeriy Gorbatikov

3,459
1
15
9

Good practice for generating complex reports (MySQL/PHP)

3 Answers3