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

[amith_ami]

hi all... pls help me...
i have a problem with my code...
if there is multiple product id in the table tbl_order_item for an order_id. then it should fetch those product id from that table tbl_order_item.
here is my code...

    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'];
            $i++;
        }

after fetching... echo $j shows the result as 526527 instead of 526 and 527. i use these two id to fetch their shipping cost from the table tbl_product. here each product have different shipping costs, so i need to fetch the corresponding shipping costs for those id for calculating total price.
here is the code where im trying to fetch the shipping costs.

     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);
            }

any help pls... tnx in advnc...

[radhakrishna.p]

hai amiyar

there is no mistake in your code but you are appending the product_id to varia ble j thats it after the first line of while loop look at once there

 $j .= $rs['prod_id'];

thats the reason you are getting 526527 instead of 526 and 527.

i think you got the point

go ahead by changing your code

[amith_ami]

tnx 4 ur reply.... radhakrishna...
u mean removing that dot from the code.. like this

$j = $rs['prod_id'];

then it shows only one id 526... but i need both the id for selecting their shipping costs.. can you pls help me.....

[cmps]

Dear Amiyar ... What is happening to you is very logical.
Lets debug what you are doing:

 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'];
        $i++;
    }

No syntax error ... but a logical error exists.
in the while loop you are doing this:
in the first loop j= 526;
in the second loop you are doing this "append to j 527" so j = 526527;

So j is no more the target id you want to fetch from database.
so what you want to do here is to put to put the code

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);
}

inside the while loop:

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']; //I removed the dot cuz i don't want to append
            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);

                /*YOUR CODE GOES HERE*/
                echo theCalculation;
            }
            $i++;
        }

Hope you understand the logical error.
Good Luck

[amith_ami]

tnx 4 ur reply.... amirbwb...
i used the code as like you mentioned...
but it works for only id like this

SELECT shipping_us FROM tbl_product WHERE prod_id=526

i need to select shipping cost for both id like this

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

now i think you got that what im trying to do

[radhakrishna.p]

hai amiyar,
you said it works for one id and not working for other id

i think thats not correct

check your database details with the second product id by manualy if it shows or not

so that i can give proper answer for your question

but the logic is correct for one id and it works for 100 id's too

This sounds like a case for a JOIN or a subquery.

$shipfield = (stristr($s_country, 'us') || stristr($s_country, 'united states')) ? 'shipping_us' : 'shipping_non_us'; //or whatever the field's called -stristr ain't great - so use a different function if you need a more accurate comparision
$sql = "SELECT p.$shipfield AS shipfield FROM tbl_order_item AS o INNER JOIN tbl_product AS p ON o.prod_id = p.prod_id WHERE o.order_id= ='$order_id'";

[cmps]

Dear Amiyar, the code i wrote fetch for both id and not only the first one ...
Maybe you mean you want to do this:

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

?

[amith_ami]

hi tnx 2 all... my pblm was solved...
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'];
          }
        }
    }
    ?>

You're still running multiple queries. If the shipping country is the same for every product in an order (I assume it is) - I can't see why you need to do this. Running an sql query in a loop is always a warning to try to re-jig it to a JOIN, subquery or to use concatenation to build a long query in the loop and execute once the loop is done.

I believe my INNER JOIN solution will allow you to just run the one query, however, you'd need to tweak field names etc.

[amith_ami]

hi... diafol tnx a lot... i used your solution and its working great for my issue.....