Get data from multiple tables mySQL

Question

i've got 3 tables:

Men; with the following columns: image_id, filename Women; image_id, filename Couples; imagemale (to which corresponds image_id from male table),imagefemale,votes

basically the users are shown randomly pictures of men and women and they decide if they would be a good match by voting yes or no, and the table "couples" resumes all this with the votes and the ranking. I'm trying to display the first 25 in number of votes, but in order to show the pictures i need the filenames and not the image id which is all i have.

How can i do it? this is the syntax of the top25 page:

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();

and then in the html:

<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>

Thank you!

simple joins. But a single table persons with three columns would do: (image_id, filename, sex). — VMai, May 28 '14 at 18:53
`mysql_query` is an obsolete interface and should not be used in new applications and will be removed in future versions of PHP. A modern replacement like [PDO is not hard to learn](http://net.tutsplus.com/tutorials/php/why-you-should-be-using-phps-pdo-for-database-access/). If you're new to PHP, a guide like [PHP The Right Way](http://www.phptherightway.com/) can help explain best practices. — tadman, May 28 '14 at 19:19

score 0 · Answer 1 · answered May 28 '14 at 18:55

You only really need to do #2, but I highly suggest doing #1 as well.

Restructure your tables to only have a single table named "people". This table will have another column with "gender", most likely an int(1)
To then grab the image file name from the id, you need to INNER JOIN the people table from the couples table as such (using your old structure):

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

Get data from multiple tables mySQL

1 Answers1