Php: get results sets from database via mysql

Question

I am encountering a problem while extracting info from a database using php+mysql and thought it will be good if somebody here may suggest a way out.

Problematic Code:

$selectedProtocols=array(1,2);
for($i = 0; $i<2; $i++)
{
  $result = mysql_query("SELECT throughput FROM session where mainProtocol='$selectedProtocols[$i]'");
  while($row = mysql_fetch_array($result))
    {   
      $throughput_temp += $row['throughput'];
    }
  $selectedProtocols[$selectedProtocols[$i]]=$throughput_temp;
}

Following are the concerned database enteries:

mainProtocol  name  throughput
1             Skype 34
2             HTTP  43
1             FTP   54

Now, following LOC gives correct output i.e. (34+54=) 88

echo "1 has throughput=".$selectedProtocols[$selectedProtocols[0]]."<br>";

But, following LOC gives ouput as zero instead of 43

echo "2 has throughput=".$selectedProtocols[$selectedProtocols[1]]."<br>";

I think there is some problem with method of fetching the result set while querying database. Any idea what wrong am I doing?

Answer 1

Everything looks fine... The only thing I see is have you initialized the $throughput_temp variable earlier? You might want to put it before the while and after the $result. That way your variables won't get reused from the last run. Try debugging the loop by adding an echo for the while, counting the amount of times it runs while the $i is 1.

Answer 2

Where is throughput_temp being initialized? It should be initialized to 0 at the beginning of the for loop.

Answer 3

I'm very confused by your use of the $selectedProtocols array there.

Consider this line:

// sp = selectedProtocols ... too much typing otherwise!

$sp[$sp[1]]

// given that $sp == [1, 2]
// resolve the inner

$sp[1] == 2 // therefore:
$sp[$sp[1]] == $sp[2]

// but there is no $sp[2], hence the error

I would change it to this:

$sp = array(1 => 0, 2 => 0);

foreach (array_keys($sp) as $id) {
    $result = mysql_query("SELECT throughput FROM session where mainProtocol = '$id'");
    while($row = mysql_fetch_array($result)) {   
        $sp[$id] += $row['throughput'];
    }
}

---

Response to comment:

when the array sp is (1=>0,2=>34,6=>67,15=>56...)

To loop through an array which doesn't have sequential (or even numeric) keys, you can use a couple of methods, but the easiest one is foreach. There are two forms of the foreach loop:

$array = array(1=>0, 2=>34, 6=>67, 15=>56);

// the first form:
foreach ($array as $value) {
    echo $value;    // "0", "34", "67", "56"
}

// the second form:
foreach ($array as $key => $value) {
    echo $key . "," . $value;    // 1,0 2,34 6,67 15,56
}

So you see that you could use the second method there to get all the ids and values from the array.

Answer 4

$throughput_temp is not re-initialized at the top of the for loop. Besides that, your code would be clearer without the unnecessary array nesting.

Answer 5

You're bug aside (although this should fix it too), you would be better off only doing one query. I would rewrite this as:

$selectedProtocols = array();
$result = mysql_query("SELECT `throughput`, `mainProtocol` FROM `session` WHERE `mainProtocol` IN (1,2)");

while( $row = mysql_fetch_object($result) ) {
  if ( !isset($selectedProtocols[$row-> mainProtocol]) ) {
    $selectedProtocols[$row->mainProtocol] = $row->throughput;
  } else {
    $selectedProtocols[$row->mainProtocol] += $row->throughput;
  }
}

Hope that helps

Answer 6

I don't know but I think I see a logic error in your code.

$selectedProtocols=array(1,2);
for($i = 0; $i<2; $i++)
{
$result = mysql_query("SELECT throughput FROM session where mainProtocol='$selectedProtocols[$i]'");
  $throughput_temp=0;
  while($row = mysql_fetch_array($result))
    {   
      $throughput_temp += $row['throughput'];
    }
  $selectedProtocols[$selectedProtocols[$i]]=$throughput_temp; 
/* $selectedProtocols[$selectedProtocols[$i]] is equivalent to $selectedProtocol[1 or 2]. 
Therefore you are escaping the index 0 of your array and automatically starts at 1, in your
 case. So the next iteration gives you index 2 which is already out of bounds for your 
array.*/
    }

Try this code:

 $selectedProtocols=array(1,2);
for($i = 0; $i<2; $i++)
{
  $throughput_temp = 0;
 // echo $selectedProtocols[$i];
  $result = mysql_query("SELECT throughput FROM session where mainProtocol='$selectedProtocols[$i]'");
  while($row = mysql_fetch_array($result))
    {   
      //echo $row['throughput'];
      $throughput_temp += $row['throughput'];
    }
  $selectedProtocols[$i]=$throughput_temp;
}
echo "1 has throughput=".$selectedProtocols[0]."<br>";
echo "2 has throughput=".$selectedProtocols[1]."<br>";