How fetch more than one result from the table and use them in query

Question

I have a problem with my code:

<?php 
    echo $s_country;
    $sql2="SELECT prod_id FROM tbl_order_item WHERE order_id='$order_id'";
    $res=mysql_query($sql2) or die(mysql_error());
    $i=0;
    $j="";
    while($rs=mysql_fetch_array($res)){
        $j = $rs['prod_id']; 
        if(trim($s_country)=='US' || trim($s_country=='United States' )){
            $sql3 = "SELECT shipping_us FROM tbl_product WHERE prod_id=".$j;
            $res=mysql_query($sql3) or die(mysql_error());
            $shipping_cost1=mysql_fetch_object($res);  
        }
        $i++;
    }
?>

What I actually want to do is to fetch the products id from the table tbl_order_item and with that products id select the shipping cost for those ids from the table tbl_product.

For example, if there is two product ids, it should select shipping cost for both the ids. But here it only works for one product id like this:

SELECT shipping_us FROM tbl_product WHERE prod_id=526

But what I'm trying to do is :

SELECT shipping_us FROM tbl_product WHERE prod_id=526
SELECT shipping_us FROM tbl_product WHERE prod_id=527

score 3 · Answer 1 · answered Dec 21 '12 at 09:16

3

you could use OR, like

SELECT shipping_us FROM tbl_product WHERE prod_id=526 OR prod_id=527

answered Dec 21 '12 at 09:16

Sudhir Bastakoti

99,167
15
158
162

score 2 · Answer 2 · answered Dec 21 '12 at 09:19

2

you can also use it like this with IN

SELECT shipping_us FROM tbl_product WHERE prod_id IN (526,527)

answered Dec 21 '12 at 09:19

Yogesh Suthar

30,424
18
72
100

score 0 · Accepted Answer · edited May 23 '17 at 11:56

0

First Solution:

Change

$res=mysql_query($sql3) or die(mysql_error()); // you have overwritten result set of first query
$shipping_cost1=mysql_fetch_object($res);

To

$newres=mysql_query($sql3) or die(mysql_error());
$shipping_cost1=mysql_fetch_object($newres);

Second Solution (much better):

$sql2="SELECT GROUP_CONCAT(prod_id SEPARATOR ',') as pid FROM tbl_order_item WHERE order_id='$order_id'";
$res=mysql_query($sql2) or die(mysql_error());

if(mysql_num_rows($res) > 0){
  $rs=mysql_fetch_array($res);
  $sql3 = "SELECT shipping_us FROM tbl_product WHERE prod_id IN (".$rs['pid'].")";
  $res=mysql_query($sql3) or die(mysql_error());
  while ($row = mysql_fetch_array($res)){
     echo $row['shipping_us']
  }
}

Recommendations:

1.Learn to prevent from MySQL Injections: Good Link

2.Mysql extension is not recommended for writing new code. Instead, either the mysqli or PDO_MySQL extension should be used. More reading: PHP Manual

edited May 23 '17 at 11:56

Community

1
1

answered Dec 21 '12 at 09:24

GBD

15,847
2
46
50

hi gbd... i used your code... and its working... but i need little more help from you for my calculation part.. i will post th code... – amith lal Dec 21 '12 at 10:07
hi gdb pls have alokk at this... after getting the shipping cost for each product it should multiply with its quantity and price .... and then sum both the total of products... can you help me pls.here is my calculation part $sales_tax=$order_total*10/100; if($s_country=='US' && $s_state=='IL' '){ $totalprice=$order_total+$shipping_cost1+$sales_tax; } elseif($s_country=='US' && $s_state !='IL' ){ $totalprice=$order_total+$shipping_cost1; } else{ $totalprice=$order_total+$shipping_cost1; } $totalprice=number_format($totalprice, 2, '.', ''); – amith lal Dec 21 '12 at 10:23
kk i tried... but its not getting correct total...it should work like this... after getting the shipping cost for each product it should multiply with its quantity and price .... and then sum both the total of products... – amith lal Dec 21 '12 at 11:18
for ex: in the db shipping costs for two products 526 and 527 is different that is for 526 cost for shipping to US is 14.99 where cost of 527 is 22.95... so when the customer order for two units of product 526 and one unit of product 527... it should calculate like this total of 526 =2*shipping cost(526)+2*price(526) and total of 527 = 1*shipping cost(527)+1*price(527).... at last the grand total = total price of 526 + total price of 527..... now i think that u got what im trying to do is.... pls help.... – amith lal Dec 21 '12 at 11:42

score 0 · Answer 4 · answered Dec 22 '12 at 04:43

my pblm was solved... and special tnx to GBD..

here is my final code...

  <?php

     echo $s_country;

    $sql2="SELECT GROUP_CONCAT(prod_id SEPARATOR ',') as pid FROM tbl_order_item WHERE order_id='$order_id'";
    $res=mysql_query($sql2) or die(mysql_error());
    $shipping_cost1 = 0;

    if(mysql_num_rows($res) > 0){
      $rs=mysql_fetch_array($res);

      if(trim($s_country)=='US' || trim($s_country=='United States' )){
        $sql3 = "SELECT shipping_us FROM tbl_product WHERE prod_id IN (".$rs['pid'].")";
        $res=mysql_query($sql3) or die(mysql_error());
        if(mysql_num_rows($res) > 0){ 
          while ($row = mysql_fetch_array($res)){
           $shipping_cost1 = $shipping_cost1 + $row['shipping_us'];
          }
        }
      } else {
          $sql3 = "SELECT shipping_outside FROM tbl_product WHERE prod_id IN (".$rs['pid'].")";
          $res=mysql_query($sql3) or die(mysql_error());
          while ($row = mysql_fetch_array($res)){
            $shipping_cost1= $shipping_cost1 + $row['shipping_outside'];
          }
        }
    }

?>

How fetch more than one result from the table and use them in query

4 Answers4