Select value from foreach result

Question

Hi is there a way to get the value from each foreach result. For Example I have a table named tblsamp.

id |   data   |
1  |    d1    |
2  |    d2    |
3  |    d3    |

then put some query:

$query = $this->db->query("SELECT * FROM tblsamp");
foreach ($query->result() as $row){
echo $row->data .'<br>';   
}

Result is:

d1
d2
d3

Now what I want is I want to get the value of the first result and compare the value from another table, and so on...

For Example:

if('d1' == '(value from another table)'{
 \\do something here
} else if ('d2' == '(value from another table)'{
 \\and so on.....
}

how can I do this guys? TIA!.

sounds like something you could be doing in the query with joins, but we would need more information — , Dec 14 '16 at 03:08
I think that you are saying you have two tables, and you want to see if `Value1` from `Table1` is the same as `Value1` from `Table2`, then if `Table1.Value2 == Table2.Value2`, then if `Table1.Value3 == Table2.Value3`, and so on. Is that correct? — scorgn, Dec 14 '16 at 03:12

S. Imp · Answer 1 · 2016-12-15T01:08:49.263

You could add a query within your query, but this is not recommended -- things can get REALLY slow. It might be something like this:

// your original query
$query = $this->db->query("SELECT * FROM tblsamp");
foreach ($query->result() as $row){
    // for each record in your original query, we run a new query derived from the result
    // OBVIOUSLY you need to change this line of code to be specific to
    // your database table's structure because some_col is probably not
    // the name of a column in your database
    $subquery = $this->db->query("SELECT * FROM other_table WHERE foo=" . $row->some_col);
    $subrows = $subquery->result();
    // output $subrows here?
    // note that $subrows might be an array with any number any number of records
}

the exact code will depend on the db classes you are using. It's codeigniter, if I'm not mistaken? EDIT: it is codeigniter.

The correct solution here is going to depend on your database table definitions. I would strongly suggest creating a JOIN using CodeIgniter, but can't offer much more advice without some idea of what your db structures are going to look like and how many records in the second table exist for each record in the first table.

I use this one but the problem is the result of the first value continue on the next data.. so all the output value is the same — zeus2026, Dec 14 '16 at 08:07

score 1 · Answer 2 · answered Dec 14 '16 at 03:35

It looks like you are using codeigniter based off the $this->db->query syntax. With codeigniter you can change a table into an array using $query->result_array().

If you are sure both tables have the same amount of rows, you can use something like the following.

$query = $this->db->query("SELECT * FROM tbl1");
$table1 = $query->result_array();
$query = $this->db->query("SELECT * FROM tbl2");
$table2 = $query->result_array();
for ($x = 0; $x <= count($table1); $x++) {
    if ($table1[$x] === $table2[$x]) {
        //DO SOMETHING HERE
    }
}

If $table1 might have more rows than table2, I would change it to

$query = $this->db->query("SELECT * FROM tbl1");
$table1 = $query->result_array();
$query = $this->db->query("SELECT * FROM tbl2");
$table2 = $query->result_array();
for ($x = 0; $x <= count($table1); $x++) {
    if (isset($table1[$x]) && isset($table2[$x])) {
        if ($table1[$x] === $table2[$x]) {
            //DO SOMETHING HERE
        }
    } else {
        break;
    }
}

score 1 · Answer 3 · edited May 23 '17 at 12:13

You can find you have matches in 2 tables however you may want to do more research about How SQL query can return data from multiple tables

How can an SQL query return data from multiple tables

This may work for your basic example

$query = $this->db->query("SELECT * FROM tblsamp,tblother");   

$rst = mysql_query($query);

if(mysql_affected_rows() > 0) {

  while($row = mysql_fetch_array($rst)) :
    if($row['data'] == '$row['otherdata']') {
      echo 'you got a match' . $row['data'] . ' In table sample to ' . $row['otherdata'] . ' In other table<br>';
    }
    else {
      echo ' no match found in results;
    }
}
endwhile;

Select value from foreach result

3 Answers3