iT邦幫忙

0

想學習只要一個查詢,不會有第二個查詢的方式?

$product_data = $pdo->query(
        "SELECT
          p.url AS url,
          p.description AS description,
          p.info AS info,
          p.note AS note,
          p.content AS content,
          p.name AS pname,
          p.icon AS icon,
          p.price AS price,
          p.original_price AS original_price,
          p.prod_id AS prod_id,
          p.stock AS stock,
          p.views AS views,
          s.name AS sname,
          c.name AS cname
        FROM `product` as p
        JOIN
          `store` as s ON p.store_id = s.store_id
        JOIN
          `category` as c ON c.category_id = p.category_id
        WHERE
          p.active = '1'
        ORDER BY `prod_id` DESC "
      );
while($row = mysqli_fetch_array($product_data)){
      $theme = $pdo->query(
        "SELECT
        t.name as tname
        FROM `product` as p
        JOIN
          `product_theme` as pt ON p.prod_id = pt.prod_id
        JOIN
          `theme` as t ON t.theme_id = pt.theme_id
        WHERE
          p.prod_id = '{$row['prod_id']}' "
      );

如圖,這是我在 while 迴圈又查詢一次的樣子
想問一下如果是這個程式碼
我能怎麼把它改成,只要在 $product_data 就能一次做完所有事?
這是有可能做到的嗎?

麻煩程式碼貼上來,不要貼圖片~

1 個回答

0
最佳解答

應該是這樣吧,其實你已經寫出來了~

$product_data = $pdo->query(
    "SELECT
          p.url AS url,
          p.description AS description,
          p.info AS info,
          p.note AS note,
          p.content AS content,
          p.name AS pname,
          p.icon AS icon,
          p.price AS price,
          p.original_price AS original_price,
          p.prod_id AS prod_id,
          p.stock AS stock,
          p.views AS views,
          s.name AS sname,
          c.name AS cname,
          t.name AS tname
        FROM `product` as p
        JOIN `store` as s ON p.store_id = s.store_id
        JOIN `category` as c ON c.category_id = p.category_id
        JOIN `product_theme` as pt ON p.prod_id = pt.prod_id
        JOIN `theme` as t ON t.theme_id = pt.theme_id
        WHERE p.active = '1'
        ORDER BY `prod_id` DESC "
);
看更多先前的回應...收起先前的回應...
asys0512 iT邦研究生 5 級 ‧ 2018-07-17 23:37:28 檢舉

咦?那我這一段呢 where p.prod_id = '{$row['prod_id']}'

不需要了,JOIN product_theme as pt ON p.prod_id = pt.prod_id,這行等同於where的條件,你可以試一下把prod_id印出來,看跟之前的作法比對一下prod_id有沒有一樣就知道了

asys0512 iT邦研究生 5 級 ‧ 2018-07-18 10:22:51 檢舉

啊,忘了給一段 $theme

<?=mysqli_num_rows($theme)!=0 ?'/':'';?>
          <? while ($themes = mysqli_fetch_array($theme)){ ?>
            <span class="admin-discount-tag admin-tag-themes"><?=$themes['tname'];?></span>
          <? }?>

如果是這樣呢,還有辦法同一隻查詢?

一樣吧,你要的只是$themes['tname'],現在變成了$product_data['tname'],SQL條件並沒有改變,除示SQL條件改變,不然撈出來後,要用什麼方式過濾是php在處理

我要發表回答

立即登入回答