从多个表中获取数据mySQL[重复]

Question

我有三张桌子：

include('mysql.php');

// Get the top25
$results = mysql_query("SELECT * FROM couples ORDER BY votes DESC LIMIT 0,25");
while($row = mysql_fetch_object($results)) $top_ratings[] = (object) $row;


// Close the connection
mysql_close();

<h2>Top 25</h2>
<center>
   <table>
      <tr>
         <td>
      <tr>
         <td>
            <?php foreach($top_ratings as $key => $filename) : ?>
         <td valign="top"><img src="imagesmale/<?=$filename->imagemale?>" /></td>
         <?php endforeach ?>
         </td>
         <td>
            <?php foreach($top_ratings as $key => $filename) : ?>
         <td valign="top"><img src="imagesmale/<?=$filename->imagefemale?>" /></td>
         <?php endforeach ?>
         </td>
      </tr>
      </td>
      </tr>
   </table>
</center>

Answer 1

你只需要做第2条，但我强烈建议你也做第1条。

1. 重新构造表，使其只有一个名为“People”的表。该表将有另一个带有“gender”的列，很可能是int(1)
2. 然后要从id中获取图像文件名，您需要将people表从couples表内部连接起来（使用旧结构）:

SELECT 
    Men.filename AS manfile ,
    Women.filename AS womanfile,
    couples.votes
FROM couples
INNER JOIN Men on couples.imagemale = Men.image_id
INNER JOIN Women on couples.imagefemale = Women.image_id
ORDER BY votes DESC
LIMIT 25