PHP function for handling MySQL infinite & nested queries

Question

I've been asked by my colleague to make a PHP function to plug into our web application to handle an infinite amount of nested (looped) queries to make our lives much easier, without being worried about loosing the current and/or previous results.

Here is the code I came up after minutes, and it seems that it works fine, however I still have these questions:

Am I re-inventing the mysqli_prepare function?
Is it smart to handling these nested queries in this way?
What could be pros and cons of using the following approach?

The actual function:

function qn($query) {
    global $db;
    $rand_var = 'r' . mktime() . mt_rand();
    $$rand_var = $db->query($query);
    return $$rand_var;
}

and in action:

if (($db instanceof mysqli) != true) {
   $db = new mysqli(DB_ADDRESS, DB_USER, DB_PASS, DB_NAME);
}

$a = qn('SELECT DISTINCT ***');

while ($row_a = $a->fetch_assoc()) {
    // do some stuff
    $b = qn('SELECT ***' . $row_a['foo']);
    while ($row_b = $b->fetch_assoc()) {
        $c = qn('SELECT COUNT(id)' . $row_b['bar']);
        // keep going ...
    }
}

Note: SQL queries are sample.

One has to say it: If you even need a "solution to handle nested queries" you probably have a much more severe problem in your data model. — Till Helge
– Till Helge, Commented Feb 25, 2013 at 13:16
Why not just do return $db->query($query);? Which to assign to local variable with unique name first? — Mikhail Vladimirov
– Mikhail Vladimirov, Commented Feb 25, 2013 at 13:17
@Mahdi BTW, when you need to generate unique names for something, you better user incrementing counter rather than random number, because this is faster, simpler and guaranteed to be unique. — Mikhail Vladimirov
– Mikhail Vladimirov, Commented Feb 25, 2013 at 13:36

Your Common Sense · Accepted Answer · 2013-02-25 13:24:27Z

Here you go: SafeMysql is what you're looking for and many, many more!

if (!($db instanceof safemysql)) {
   $db = new safemysql(...);
}

$a = $db->getCol('SELECT DISTINCT ***');
foreach ($a as $foo) {
    // do some stuff
    $b = $db->getAll('SELECT ***', $foo);
    foreach ($b as $row_b) {
        $c = $db->getOne(('SELECT COUNT(id)', $row_b['bar']);
         // keep going ...
    }
}

To answer your question

Am I re-inventing the mysqli_prepare function?

Definitely NO.
You're preparing nothing.

Is it smart to handling these nested queries in this way?

No. The probability of collision is pretty high. At least use microtime instead of mktime.

What could be pros and cons of using the following approach?

There a lot of cons

no error handling
no placeholders support
messy code
whole qn() function being useless. you can use $db->query() alone instead.

Also, Till Helge Helwig is absolutely right: all nested queries can (and should) be replaced with single query with join (and grouping).

Mikhail Vladimirov · Accepted Answer · 2013-02-25 13:29:35Z

1

The following code:

$rand_var = 'r' . mktime() . mt_rand();
$$rand_var = $db->query($query);
return $$rand_var;

could be simplified to

return $db->query($query);

Cause it just stores query result in local variable with unique name before returning it. This does not make much sense.

answered Feb 25, 2013 at 13:29

Mikhail Vladimirov

13.9k1 gold badge40 silver badges41 bronze badges

Collectives™ on Stack Overflow

PHP function for handling MySQL infinite & nested queries

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related