Selecting from table comparing with another table?

[kaos057]

I am trying to select data from a table where a certain field in that table matches a certain field from another table.

   <?php
  $resulta = mysql_query("SELECT * FROM table_1");
  while ($myrow = mysql_fetch_row($resulta)){
  $resultb = mysql_query("SELECT ID FROM table_2 WHERE Name = '$myrow[2]'");
  $myrowb = mysql_fetch_array($resultb)){
  echo $myrow[1] $myrowb['UD'];
    }
  ?>
 

what am i doing wrong here? It works but it takes a long time to load the page.

[razz]

Try this syntax for the query:

$query = "SELECT * FROM table_2 WHERE table_2.Name = table_1.Name";

$result = mysql_query($query);

I believe that would do it for the query string. Also on the while loop you might do a mysql_num_rows() on the result and the do a for loop like this:

$numresults = mysql_num_rows($$result);

for ($i = 0; $i < $numresults; $i++)
{
//code here
}

I think a for loop is faster as it is based on a count of an existing array. I could be way wrong on that too.

On the query part the fields names DO NOT have to be the same either. You could have:

$query = "SELECT * FROM table_2 WHERE table_2.Name = table_1.user";

[kaos057]

It still takes about 5 minutes to load the page. Also, I guess you didn't notice that I also need to display data from the first table as well as get the id from the second that the name matches to.

p.s. sorry for double post not sure how that happened.

[Shrike]

Try only selecting the columns you actually need. select * from ... is going to take longer than select column1, column2 ... if you don't need the extra data don't request it 🙂

[razz]

I did not notice the needing data from both, sorry about that. I agree with Shrike that if you do not need it all, i.e. '*', then be more specific on your query. That could help the speed issue.