最小化SQL语句？ - 堆栈SQL查询？

Question

I'm working on a WordPress Plugin script which extends the plugin. So, my problem is, I have to deal with the database which is pretty inefficient because I have only the first normal form...

This is the reason, why I'm forced to get the values like this:

$details = $database->query("SELECT `meta_key`, `meta_value` FROM `6v8T0_postmeta` WHERE post_id = '$pid' AND (meta_key like '$price_meta' OR meta_key like '$price_old_meta' OR meta_key like '$link_meta' OR meta_key like '$shop_meta')");

        while($row_meta = $details->fetch_assoc()){
            if($row_meta["meta_key"] == $price_meta){
                $price = $row_meta["meta_value"];
            }elseif($row_meta["meta_key"] == $price_old_meta){
                $price_old = $row_meta["meta_value"];
            }elseif($row_meta["meta_key"] == $link_meta){
                $link = $row_meta["meta_value"];
            }elseif($row_meta["meta_key"] == $shop_meta){
                $shop = $row_meta["meta_value"];
            }else{
                fwrite($myfile, "Is not matching!
");
            }
        }

1. My question now is, what is $database->query returning? Because I want to minimize my SQL queries I want to load all values I need in a two dimensional array and sort them afterwords.

Is that possible and if yes, how?

1. Stack SQL queries? - I say multiple times Select, Delete and Insert in a loop. Is it possible to stack those statements and execute one query instant of 4? - And if yes, can I continue in my code with fetch_assoc() normally? - Because I'm not sure if this is possible if I stack Delete, Insert and Select...

2. Are there any other ways to minimize SQL queries?

Greetings and Thank You!

Answer 1

First the database structure isn't inefficient, it's a widely used and accepted pattern of storing a list of key/value pairs in the database.

There are two ways you can improve the situation for yourself.

MAPPING ARRAY

This one is probably going to be your best choice, as it's very flexible and doesn't require you to change your queries.

$details = $database->prepare("
    SELECT
        `meta_key`,
        `meta_value`
    FROM
        `6v8T0_postmeta`
    WHERE
        post_id = '$pid'
        AND meta_key IN (
            %s,
            %s,
            %s,
            %s
        )
    ",
    array(
        $price_meta,
        $price_old_meta,
        $link_meta,
        $shop_meta
   )
);

$map = array();

while($row_meta = $details->fetch_assoc()){
    $map[$row_meta['meta_key']] = $row_meta['meta_value'];
}

/*

$map will look something like this

array(
    'price' => '10.99',
    'price_old' => '9.99',
    'link' => 'http://example.org/awesome-product',
    'shop' => 'main'
)

*/

$price = array_key_exists($price_meta, $map) ? $map[$price_meta] : 'default value';
$price_old = array_key_exists($price_old_meta, $map) ? $map[$price_old_meta] : 'default value';
$link = array_key_exists($link_meta, $map) ? $map[$link_meta] : 'default value';
$shop = array_key_exists($shop_meta, $map) ? $map[$shop_meta] : 'default value';

JOINS

This method makes your query long and needs you to update it everytime you want a new field added.

$details = $database->prepare("
    SELECT
        b.meta_value %s,
        c.meta_value %s,
        d.meta_value %s,
        e.meta_value %s
    FROM
        `6v8T0_postmeta` a
    LEFT JOIN
        `6v8T0_postmeta` b ON
            b.post_id = a.post_id
            AND b.meta_key = %s
    LEFT JOIN
        `6v8T0_postmeta` c ON
            c.post_id = a.post_id
            AND c.meta_key = %s
    LEFT JOIN
        `6v8T0_postmeta` d ON
            d.post_id = a.post_id
            AND d.meta_key = %s
    LEFT JOIN
        `6v8T0_postmeta` e ON
            e.post_id = a.post_id
            AND e.meta_key = %s
    WHERE
        a.post_id = '$pid'
    ",
    array(
        $price_meta,
        $price_old_meta,
        $link_meta,
        $shop_meta,
        $price_meta,
        $price_old_meta,
        $link_meta,
        $shop_meta,
        $price_meta,
        $price_old_meta,
        $link_meta,
        $shop_meta
    )
);

$map = array();

if (!($row_meta = $details->fetch_assoc())) {
    $price = $row_meta[$price_meta];
    $price_old = $row_meta[$price_old_meta];
    $link = $row_meta[$link_meta];
    $shop = $row_meta[$shop_meta];
}