PHP / MySQL：替换多个查询

Question

I have two MySQL tables:

CREATE TABLE IF NOT EXISTS `table_a` (
  `keyA` int(11) NOT NULL AUTO_INCREMENT,
  `somevalue1` varchar(100) NOT NULL,
  `somevalue2` int(11) NOT NULL,
  PRIMARY KEY (`keyA`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

CREATE TABLE IF NOT EXISTS `table_b` (
  `foreignkey_keyA` int(11) NOT NULL,
  `something1` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `table_a` (`keyA`, `somevalue1`, `somevalue2`) VALUES
(1, 'Data 1', 0),
(2, 'Data 2', 0);

INSERT INTO `table_b` (`foreignkey_keyA`, `something1`) VALUES
(1, 0),
(1, 0);

Now I want to get the data from table "table_b" for each entry from a SELECT query of "table_a":

$finalData = array();
$rows = "SELECT * FROM table_a";
foreach ($r in $rows)
{
   $r['table_b_data'] = "SELECT * FROM table_b WHERE foreignkey_keyA=" . $r['keyA'];
   $finalData[] = $r;
}
echo(json_encode($finalData);

So that the final json result looks like:

[
{"keyA":1, "somevalue1":"Data 1", "somevalue2":0, 
  "table_b_data":[
     {"foreignkey_keyA":1, "something1":0},
     {"foreignkey_keyA":1, "something1":0}
  ]
}
]

The problem with this is that in each foreach loop a MySQL query is performed. Is there a better (faster) way to do this? Another solution I can think of is to use a JOIN between the tables but then I have to post-process the resulting array to match the JSON output.

Regards,

Answer 1

use the following query to get data in single query

          select *  from table_b left inner join  table_a  on foreignkey_keyA=keyA