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...

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

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.....

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

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

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

Member Avatar for diafol

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'";

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

?

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'];
          }
        }
    }
    ?>
Member Avatar for diafol

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.

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.