Array and for each loop issues

Question

Code is below if I run one value in the array the results are correct if I run more than one value the results are of the price is incorrect its like it has messed around with the values somewhere ?? help appreciated

    $dido=array('42204131','22204131'); 
    foreach($dido as $did):

    $query = "select * from dispatch,link where lid=dlid and did=$did";
    $result = mysql_query($query) or die(mysql_error());
    while($row = mysql_fetch_array($result)){

    $vanc1=$row['vanc1'];
    $vanc2=$row['vanc2'];
    $vanc3=$row['vanc3'];
    $vanc4=$row['vanc4'];
    $vanc5=$row['vanc5'];

    $anc1=$row['anc1'];
    $anc2=$row['anc2'];
    $anc3=$row['anc3'];
    $anc4=$row['anc4'];
    $anc5=$row['anc5'];

    // price anc1
    $querypanc1 = "select pprice from products where pid=$anc1";
    $resultpanc1 = mysql_query($querypanc1);
    while($row = mysql_fetch_array($resultpanc1))
    {
        $priceanc1=$row[pprice];
        $tpriceanc1=$vanc1*$priceanc1;

    }
    // price anc2
    $querypanc2 = "select pprice from products where pid=$anc2";
    $resultpanc2 = mysql_query($querypanc2);
    while($row = mysql_fetch_array($resultpanc2))
    {
        $priceanc2=$row[pprice];
        $tpriceanc2=$vanc2*$priceanc2;

    }
    // price anc3
    $querypanc3 = "select pprice from products where pid=$anc3";
    $resultpanc3 = mysql_query($querypanc3);
    while($row = mysql_fetch_array($resultpanc3))
    {
        $priceanc3=$row[pprice];
        $tpriceanc3=$vanc3*$priceanc3;

    }
    // price anc4
    $querypanc4 = "select pprice from products where pid=$anc4";
    $resultpanc4 = mysql_query($querypanc4);
    while($row = mysql_fetch_array($resultpanc4))
    {
        $priceanc4=$row[pprice];
        $tpriceanc4=$vanc4*$priceanc4;

    }
    // price anc5
    $querypanc5 = "select pprice from products where pid=$anc5";
    $resultpanc5 = mysql_query($querypanc5);
    while($row = mysql_fetch_array($resultpanc5))
    {
        $priceanc5=$row[pprice];
        $tpriceanc5=$vanc5*$priceanc5;

    }


    $gtprice=$tpriceanc1+$tpriceanc2+$tpriceanc3+$tpriceanc4+$tpriceanc5;

        $qrygt="UPDATE dispatch SET gtprice=$gtprice WHERE did=$did";
        $resultgt=@mysql_query($qrygt);

        }
        endforeach; 

Answer 1

1) The only possible issue I could spot in your code, is that when some of your select pprice from products where pid ... queries do not return any data, you retain value of $tpriceancX from previous iteration.

2) Also (out of topic) you can replace your 5 blocks of repeated code with for loop.

$gtprice = 0;
for ($i = 1; $i <= 5; $i++)
{
    $querypanc = "select pprice from products where pid=".$row["anc$i"];
    $resultpanc = mysql_query($querypanc);
    while($pancrow = mysql_fetch_array($resultpanc))
    {
        $priceanc=$pancrow[pprice];
        $tpriceanc=$row["vanc$i"]*$priceanc;
        $gtprice += $tpriceanc;
    }
}

Answer 2

Your first and biggest problem is the copy-pasta nature of your code. Let's try and break down what you're doing:

• Setting up a list of ids
• Running a query on those ids
• Putting the results into an array
• Running a separate query on each of those results

You are also using some very janky syntax. (ie foreach($foo as $bar):).

Break these things down into methods. What is a method? It takes an input and transforms it into an output.

//returns an array of price information
public function getPrices($idArray) { //note the good method and parameter names!
  //do stuff
}

Now that we know what we are doing, we can start to fill in the implementation details:

public function getPrices($idArray) {
  foreach($idArray as $id) {
    //somehow get the gross-scale information
    //then put it in a data object
    //then call a function to get specific information
  }
}

What should that sub-method do? Lets look at your current code snippet:

 // price anc1
 $querypanc1 = "select pprice from products where pid=$anc1";//sets up sql query
 $resultpanc1 = mysql_query($querypanc1);                    //runs the query
 while($row = mysql_fetch_array($resultpanc1)) {             //for each result
   $priceanc1=$row[pprice];                                  //gets the price
   $tpriceanc1=$vanc1*$priceanc1;                            //calculates some other price
 }

Those last two lines really suggest an object but maybe that's too heavyweight for your purpose. The first two lines are boiler plate you repeat endlessly. Lets write a function!

public function getPrices($name, $pid, $multiplier) {
  $sqlQuery = "SELECT pprice FROM products WHERE pid=$pid";
  $result = mysql_query($sqlQuery);
  $prices = array();
  while ($row = mysql_fetch_array($result) {
    $key = "price".$name;//$key will be something like 'priceanc1'
    $prices[$key] = $row[pprice];
    $tkey = "tprice".$name;
    $prices[$tkey] = $prices[$key] * $multiplier;
  }
}

Now, this function is a bit unclean because it tries to do two things at once (queries the database and then massages the data into a usable array) but I wanted it to resemble what you were doing. With this function written we can go back to our higher level function an call it:

public function getPrices($idArray) {
  foreach($idArray as $id) {
    $sqlQuery = "SELECT * from dispatch, link WHERE lid=dlid and did=$id";
    $prices = array();
    while ($row = mysql_fetch_array($result) {
      for ($idx = 1; $idx <= 5; $idx++) {
        $name = "anc".$idx;
        $pid = $row[$name];
        $multiplier = $row["vanc".$idx];
        $priceArray = getPrices($name, $pid, $multiplier);
        $prices = array_merge($prices, $priceArray);
      }
    }
  }

  //put a var_dump here to check to see if you're getting good results!

  return $prices;//Should be the aggregated prices you've gotten from the db
}

Now, that is what you're attempting to do, but I admit I don't understand how your database is set up or what your variables actually mean. Pressing on! We also note that unnecessary massaging of data falls away.

You can call this like so:

$ids = array();
$ids[] = 42204131;
$ids[] = 22204131;
$prices = getPrices($ids);
var_dump($prices);//shows the result of your work

Now that you have the prices, you can pass them to another function to run the update:

updatePrices($prices);

I'll let you write that part on your own. But remember; break down what you're doing and have repeated elements be handled by the same function. The real lesson to learn here is that programming is really communicating: your code doesn't communicate anything because there is so much repeated noise. Use good variable names. Tighten what you're doing down to functions with single tasks. This way anyone reading your code (including you!) will know what you're trying to do and where you've gone wrong.