从SQL查询构建数组

Question

I'm programming a script for a WordPress plugin which should extend the plugin. Right now I'm optimizing my script. But my problem is to handle the data... The data get applied by the plugin. So I cannot change anything on that. I must deal with it.

Here you can see a snippet of the database. But it is only fortune that I selected to different posts where the amount of product_shop_0/1/2/3 is same... It is variable!

At the end I would love to have an array like this:

array(92) {
  [0]=> int(5) "24202"
  array(4) {
    ["product_shops_0"]=>
    array(4) {
       ["product_shops_0_price"]=> double(4) "35.99"
       ["product_shops_0_price_old"]=> double(0) ""
       ["product_shops_0_shop"]=> int(5) "18319"
       ["product_shops_0_link"]=> string(78) "http://www.kinguin...."
    }
    ["product_shops_1"]=>
    array(4) {
       ...
    }
    ["product_shops_2"]=>
    array(4) {
       ...
    }
    ["product_shops_3"]=>
    array(4) {
       ["product_shops_3_price"]=> double(4) "40.39"
       ["product_shops_3_price_old"]=> double(0) ""
       ["product_shops_3_shop"]=> int(5) "18315"
       ["product_shops_3_link"]=> string(78) "http://www.gameladen...."
    }
  }

My problem at the moment is I do not know how to solve this problem. product_shops_0_price. **EVERY**post_idhas it's ownproduct_shops_0_price`. So, it's starts again at 0. Those data belongs together:

• Alls meta_keys and meta_values who have the same `post_id``
• Those data should get split up on product_shops_0, product_shops_1 and so on...

Does anyone know how I can stack that array like this? I tried it already, but I cannot solve it...

Here my try:

$stmt = $database->query("SELECT `meta_key`, `meta_value` FROM `fWR6qIN_postmeta` WHERE (meta_key like '$price_meta' OR meta_key like '$price_old_meta' OR meta_key like '$link_meta' OR meta_key like '$shop_meta')");

$map = array();
while($row = $stmt->fetch_assoc()){

    $price = null;
    $price_old = null;
    $link = "";
    $shop = "";

    if($row["meta_key"] == $price_meta){
        $price = $row["meta_value"];
    }elseif($row["meta_key"] == $price_old_meta){
        $price_old = $row["meta_value"];
    }elseif($row["meta_key"] == $link_meta){
        $link = $row["meta_value"];
    }elseif($row["meta_key"] == $shop_meta) {
        $shop = $row["meta_value"];
    }else{
        echo "Error
";
    }

    $tmp = array(
        $price_meta =>  $price,
        $price_old_meta => $price_old,
        $link_meta => $link,
        $shop_meta => $shop

    );

    array_push($map, $tmp);
}

Would be very happy about help! Thank You and Greetings!

Answer 1

try this, May it could help you: For regex test click here

<?php
$stmt = $database->query("SELECT pm.post_id, pm.meta_key, pm.meta_value FROM fWR6qIN_postmeta pm GROUP BY pm.post_id, pm.meta_key");

$map = array();
while($row = $stmt->fetch_assoc())
{
    preg_match('/^(product_shops_\d*)_(\S*)/', $row['meta_key'], $matches);
    $product_offset = $matches[1];

    $map[$row['post_id']][$product_offset][$row['meta_key']] = $row['meta_value'];
}