optimize the query

[sandy1028]

In the below code how can I optimize the query.
Yhe fieldname has more than 3 rows for the particular id.

The while loop takes longer time to open the page if there are more records.

So how can I optimize the query so that speed increases

$result = mysql_query("select * from table1");
                $no_of_rows += mysql_num_rows($result);
        while($r = mysql_fetch_array($result)){
                $fieldname = '';
                $res = mysql_query("select distinct(filed) from table2 where id='".$r[0]."'");
                
                while($row = mysql_fetch_array($res)){
                   $fieldname .= $row[0]."\n";
                        }
                
                                array_push($records, "$r[0]#$r[1]#$r[2]#$r[3]#$r[4]#$r[5]#$fieldname");
                }

[r937]

you can optimize this by getting rid of the 2nd query inside the loop

SELECT table1.* 
     , d.*
  FROM table1
INNER
  JOIN ( SELECT id
              , filed 
           FROM table2 
         GROUP
             BY id
              , filed ) AS d
    ON d.id = table1.id

note that DISTINCT is not a function

[sandy1028]

you can optimize this by getting rid of the 2nd query inside the loop

SELECT table1.* 
     , d.*
  FROM table1
INNER
  JOIN ( SELECT id
              , filed 
           FROM table2 
         GROUP
             BY id
              , filed ) AS d
    ON d.id = table1.id

note that DISTINCT is not a function

select * from tablename where id='172017';
+------------------------------+---------------------+--------------+--------------------------------+
| Name | timestamp | id | fieldname4 |
+------------------------------+---------------------+--------------+--------------------------------+
| A | 2008-07-31 15:20:01 | 172017 | AAA/0 |
|A| 2008-07-31 15:20:01 | 172017 | BBB/1 |
+------------------------------+---------------------+--------------+--------------------------------+

Actually the row fetched is like this. I have to display in the web page as

Single Name and in fieldname4 2 rows(
NAme Filedname
A AAA/0
BBB/1

In the single <td>

How can i do that

[r937]

what i think you want is:

 Name  Fieldname
  A    AAA/0 
       BBB/1

with spaces in the 2nd row where the name was

suppressing the "repeating" name is not done with sql, this is done with your application logic

you still need only one single query with a join