Php PDO循环资源而不是获取

Question

I have been searching the internet and this forum, but I still can't figure out a good way for doing it FAST and specially without running into memory usage issues. Here goes my task:

What I did in traditional mysql:

I have 3 tables, lets say: table1, table2, and table3 Let's assume that table1 has 300.000 rows (they are purchase items, for example) Let's assume that table2 is the list of products, and has 10.000 rows Let's assume that table 3 is the services lists, with also 10.000 rows

Now, what I need to do is generate a report listing every purchase in a loop, which will check if the the purchase item is for a product or a service, and if it is a product it will output the name of the product by getting the correspondent name column on the table2. The same would happen if it is a service...

1) In the manuals and forums it's said that I cannot run another query in PDO without finishing reading all my rows... which is this case as I need to run another query to get the name of the product/service. (In mysql this is pretty simple as I can handle many result resources and just iterate between them with mysql_result)

2) Some people said that using fetchall or even fetch would explode my memory usage and I would not be able to do this query....I already tried getting a an array with all the results (300K) and it figuratively explodes the memory usage of my server...

So guys can you help me? I am trying to move my servers to windows azure and they only allow pdo os sqlsrv.....

Ok as some people have asked here goes my theoretical code:

// connect to mysql database
$sql = @mysql_connect("{server}","{login}","{password}");
@mysql_select_db("{database}");

// query:
$result = mysql_query("SELECT * FROM table1 ORDER BY purchase_date ASC");

for($i=0;$i<$num;$i++){

   if(mysql_result($result,$i,"purchase_type")==1){

         $result1 = mysql_query("SELECT product_name FROM table2 WHERE id='".mysql_result($result,$i,"purchase_type_id")."'");       
         $name = mysql_result($result1,0,"product_name");

   }else if(mysql_result($result,$i,"purchase_type")==2){

         $result1 = mysql_query("SELECT service_name FROM table3 WHERE id='".mysql_result($result,$i,"purchase_type_id")."'"); 
         $name = mysql_result($result1,0,"service_name");

   }else{

         $name = '{error}';
   }

   echo(($i+1)". ".$name." <br/>");

}

PS: This is just an example code....the real code is by far longer and more complex than that.... But if I can solve the theory here I can figure out the rest... :)

PS2: Before someone asks.... table2 and table3 have different columns....

Thanks,

Answer 1

SELECT 
  table1.*,
  COALESCE(table2.product_name, table3.service_name, '{error}') as name
FROM table1 
LEFT JOIN table2 
  ON table1.purchase_type = 1
  AND table2.id = table1.purchase_type_id
LEFT JOIN table3 
  ON table1.purchase_type = 2
  AND table3.id = table1.purchase_type_id
ORDER BY table1.purchase_date

Note that by letting purchase_type_id refer to either table2 or table3 you miss some advantages from foreign keys.