在php中优化和美化冗长而丑陋的SQL查询

Question

Im beginner in php and i have problem with sql string optimization and beauty.

$kiti_neplp = mysql_query("SELECT (SELECT coalesce(SUM(skaicius)*6, 0) FROM menesiai WHERE metai = '".$metaiat."' {$SQLmenuo} {$SQLskyrius} AND rdkodas = 'V3')
   +(SELECT coalesce(SUM(skaicius)*4, 0) FROM menesiai WHERE metai = '".$metaiat."' {$SQLmenuo} {$SQLskyrius} AND rdkodas = 'V4')
   +(SELECT coalesce(SUM(skaicius)*4, 0) FROM menesiai WHERE metai = '".$metaiat."' {$SQLmenuo} {$SQLskyrius} AND rdkodas = 'V7')
   +(SELECT coalesce(SUM(skaicius)*8, 0) FROM menesiai WHERE metai = '".$metaiat."' {$SQLmenuo} {$SQLskyrius} AND rdkodas = 'V9')
   +(SELECT coalesce(SUM(skaicius)*0.3, 0) FROM menesiai WHERE metai = '".$metaiat."' {$SQLmenuo} {$SQLskyrius} AND rdkodas = 'V11')
   +(SELECT coalesce(SUM(skaicius)*0.3, 0) FROM menesiai WHERE metai = '".$metaiat."' {$SQLmenuo} {$SQLskyrius} AND rdkodas = 'V13')
   +(SELECT coalesce(SUM(skaicius)*16, 0) FROM menesiai WHERE metai = '".$metaiat."' {$SQLmenuo} {$SQLskyrius} AND rdkodas = 'V14')
   +(SELECT coalesce(SUM(skaicius)*8, 0) FROM menesiai WHERE metai = '".$metaiat."' {$SQLmenuo} {$SQLskyrius} AND rdkodas = 'V16')
   +(SELECT coalesce(SUM(skaicius)*8, 0) FROM menesiai WHERE metai = '".$metaiat."' {$SQLmenuo} {$SQLskyrius} AND rdkodas = 'V17')
   +(SELECT coalesce(SUM(skaicius)*4, 0) FROM menesiai WHERE metai = '".$metaiat."' {$SQLmenuo} {$SQLskyrius} AND rdkodas = 'V18')
   +(SELECT coalesce(SUM(skaicius)*4, 0) FROM menesiai WHERE metai = '".$metaiat."' {$SQLmenuo} {$SQLskyrius} AND rdkodas = 'V19')
   +(SELECT coalesce(SUM(skaicius)*4, 0) FROM menesiai WHERE metai = '".$metaiat."' {$SQLmenuo} {$SQLskyrius} AND rdkodas = 'V21')
   +(SELECT coalesce(SUM(skaicius)*4, 0) FROM menesiai WHERE metai = '".$metaiat."' {$SQLmenuo} {$SQLskyrius} AND rdkodas = 'V22')
   +(SELECT coalesce(SUM(skaicius)*4, 0) FROM menesiai WHERE metai = '".$metaiat."' {$SQLmenuo} {$SQLskyrius} AND rdkodas = 'V23')
   +(SELECT coalesce(SUM(skaicius)*4, 0) FROM menesiai WHERE metai = '".$metaiat."' {$SQLmenuo} {$SQLskyrius} AND rdkodas = 'V24')
   +(SELECT coalesce(SUM(skaicius)*3, 0) FROM menesiai WHERE metai = '".$metaiat."' {$SQLmenuo} {$SQLskyrius} AND rdkodas = 'V51')
   +(SELECT coalesce(SUM(skaicius)*4, 0) FROM menesiai WHERE metai = '".$metaiat."' {$SQLmenuo} {$SQLskyrius} AND rdkodas = 'V52')
   +(SELECT coalesce(SUM(skaicius)*16, 0) FROM menesiai WHERE metai = '".$metaiat."' {$SQLmenuo} {$SQLskyrius} AND rdkodas = 'V34')
   +(SELECT coalesce(SUM(skaicius)*16, 0) FROM menesiai WHERE metai = '".$metaiat."' {$SQLmenuo} {$SQLskyrius} AND rdkodas = 'V35')
   +(SELECT coalesce(SUM(skaicius)*8, 0) FROM menesiai WHERE metai = '".$metaiat."' {$SQLmenuo} {$SQLskyrius} AND rdkodas = 'V36') 
    as kiti_neplp");

Im building sql query string but it looks realy ugly. Is it a better way to build that long and ugly query? Because now im writing 10 almost the same strings (with different coefficients and rdkodas). Of course I can write it and it works good for me, but i want to learn how to do this in a good way. Thank you for understanding

Answer 1

If you cannot make a nice way of creating a mathematical pattern from this data, the only real thing I can think of is creating an array and passing it to a function that builds the query string for you, so that you don't even have to see it.

$nums = array(6, 0, 3, //the numbers and data you have
              4, 0, 4,
              //snip
              8, 0, 36);


function createQuery($nums) {
    $qs = "SELECT ";
    for ($i = 0; $i < count($nums); $i+= 3) { //iterating over the groups of three
        $qs = $qs . "+(SELECT coalesce(SUM(skaicius)*" . $nums[$i] .
                ", " . $nums[$i + 1] . 
                ") FROM menesiai WHERE metai = '" . $metaiat . 
                "' {$SQLmenuo} {$SQLskyrius} AND rdkodas = 'V" . 
                 $nums[$i + 2] . "')+";
    }
    return $qs = $qs . "as kiti_neplp"; //return everything as a string
}

$kiti_nlpl = mysqli_query(createQuery($nums)); //execute the query

Don't run this code as is though! Test it before you deploy it onto your SQL database. You can echo createQuery($nums); on a php page to see what kinda query you'll make and if it matches the one above.

This approach will help you in the long run though, especially if you're going to be making more of these (shudder). You can just declare $nums to have different values in it before passing it along to the function. This will have the added benefit of making the part of your code that actually executes the query (the mysqli_quer();) look really nice, by hiding the crappy part somewhere you don't have to see it :P