1

I am trying to calculate something from the DB level and would like to know if its possible to an if/else or ifnull or other to evaluate this? IF not what is a good approach?

Is it possible to do something this way?

I've tried several ways, but here is my latest example.

IF (SELECT markups.markup FROM markups LIMIT 1) 
THEN
    SELECT ((SELECT AVG(products.price) FROM products WHERE products.code = "8G435G232AB") * (SELECT markups.markup FROM markups LIMIT 1)) + (SELECT AVG(products.price) FROM products WHERE products.code = "8G435G232AB") AS code_8G435G232AB FROM products ORDER BY products.code LIMIT 1
ELSE
    SELECT AVG(products.price) FROM products WHERE products.code = "8G435G232AB"
2

You can use dynamic sql for this purpose

The if statement chekcm if there are markups if so it tales the firwst query into a user defined variable @sql

and the executes it (see manual)

SELECT IF(EXISTS(SELECT markups.markup FROM markups)
, @sql:=' SELECT ((SELECT AVG(products.price) FROM products WHERE products.code = "8G435G232AB") * (SELECT markups.markup FROM markups LIMIT 1)) + (SELECT AVG(products.price) FROM products WHERE products.code = "8G435G232AB") AS code_8G435G232AB FROM products ORDER BY products.code LIMIT 1'
,@sql:='SELECT AVG(products.price) FROM products WHERE products.code = "8G435G232AB"');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
2
0

thanks to @nbk

After brushing up on multi_query() I got it working doing the following

        $sql = " SELECT IF(EXISTS(SELECT markups.markup FROM markups)
                                , @sql:='SELECT ((SELECT AVG(products.price) FROM products WHERE products.code = ''8G435G232AB'') * (SELECT markups.markup FROM markups LIMIT 1)) + (SELECT AVG(products.price) FROM products WHERE products.code = ''8G435G232AB'') AS code_8G435G232AB FROM products ORDER BY products.code LIMIT 1'
                                
                                , @sql:='SELECT AVG(products.price) FROM products WHERE products.code = ''8G435G232AB'''); ";

        $sql .= "PREPARE stmt FROM @sql; ";
        $sql .= "EXECUTE stmt; ";
        $sql .= "DEALLOCATE PREPARE stmt; ";

        if($cnx->multi_query($sql)){
            do {
                if ($res = $cnx->store_result()) {
                    $averageQuery = $res->fetch_all(MYSQLI_ASSOC)[0];
                    $res->free();
                }
            } while ($cnx->more_results() && $cnx->next_result());
        }

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.