PHP MYSQL array foreach and insert

Question

tbl1

id | detail
1  | this, that
2  | these, those

tbl2

id | visit  | cost
1  | this   | 20
2  | that   | 30
3  | these  | 40
4  | those  | 50

PHP:

<?php 

$id=$_GET['id'];    
$detail=$_GET['detail'];
$pieces = explode(",", $rows['detail']);

    // $pieces[0]; piece1
    // $pieces[1]; piece2        
    // $pieces[2]; piece2        
    // $pieces[3]; piece3    
    // $pieces[4]; piece4

    foreach($pieces[1] as $test){            

$query="SELECT * FROM tbl1, tbl2 WHERE
  $test = tbl2.cost AND
  tbl1.id='$id'";
    }   

$result=mysql_query($query) or die("error: " . mysql_error());

while($rows=mysql_fetch_array($result)){    
?>
<table>
    <tr>
    <td><input value="<? echo $rows['code'];?>" readonly="readonly"/></td>
    <td><input value="<? echo $rows['cost']; ?>" readonly="readonly"/></td>
    </tr>
  </table>

<?php
}
?>

for id=1, array 0=>this, 1=>that So, foreach($pieces[1] as $test), it means "that"

The above works, but, when I change $pieces[1] to $pieces[0], I still get the result which corresponds with $pieces[1]. And, when I change it to $pieces[4] which doesn't exist, I again get the result which corresponds with $pieces[1].

Where am I doing wrong? How can I make this array work?

According to tbl1.id=1 piece[0]=>this, piece[1]=>that and I want to make it work for my tables. Please help.

Answer 1

$pieces[1], the variable you are using in your foreach loop is a string, not an array. You should be looping through the $pieces array so:

foreach($pieces as $test){ 

... NOT

foreach($pieces[1] as $test){ 

Answer 2

$pieces[1] is not an array but an element of an array($pieces), so better use without loop statement,

$query="SELECT * FROM tbl1, tbl2 WHERE
  $pieces[1] = tbl2.cost AND
  tbl1.id='$id'";

Thanks

Answer 3

When you run

$result=mysql_query($query) or die("error: " . mysql_error());

It will return a result of the from query. But what is your query? It is the last string you set $query to, which is the last time iteration of your for loop. That seems to be inefficient and not your intended behavior.

Assuming that you want all the results (and don't care about query optimization), you could do something like this:

foreach($pieces as $test){            
    $query="SELECT * FROM tbl1, tbl2 WHERE
    $test = tbl2.cost AND tbl1.id='$id'";

    $result[]=mysql_query($query) or die("error: " . mysql_error());
}

foreach ($results as $result){
    $rows=array_push(mysql_fetch_array($result));
}

Now, if you only want details from a specific piece, you don't even need a for loop.

// replace 3 with the desired element
$query="SELECT * FROM tbl1, tbl2 WHERE
$pieces[3] = tbl2.cost AND tbl1.id='$id'";

$result=mysql_query($query) or die("error: " . mysql_error());

mysql_fetch_array($result);