PHP and Multiple DB Selects

Question

I've got two tables from which I need to extract information, but the data from the second table depends on the information I get from the first one. Is there an easy way to handle this?

<?php
  mysql_connect('localhost', 'root', 'root') or die(mysql_error());
  mysql_select_db('stadium') or die(mysql_error());

  $result = mysql_query("SELECT * FROM events");

  $result2 = mysql_query("SELECT name FROM competitions WHERE id='$row[competition_id]' ");

  while($row = mysql_fetch_array($result)) {
    echo "<tr id=\"" . $row['id'] . "\"> \n<td>" . $row['name'] . "</td>";
    echo "<td>" . $row['competition_id'] . "</td>";
    echo "<td>" . $row['date'] . "</td></tr>";
  }
?>

Karsten · Accepted Answer · 2010-01-16 10:17:35Z

3

Use a JOIN.

SELECT e.*, c.name as competition_name FROM events e LEFT JOIN competitions c on c.id = e.competition_id

answered Jan 16, 2010 at 10:17

Karsten

14.7k5 gold badges33 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Norbert Over a year ago

First one worked. I had to change some stuff in the table so it won't overlap. Thanks!

Collectives™ on Stack Overflow

PHP and Multiple DB Selects

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related