Retrieve data from multiple tables MySQL

Question

I have 2 tables: "media" and "rating"

I need to retrieve all data from both tables where "media" has a title, description and a image. "rating" only stores the media id and a vote (1 - 5).

My problem is that when i try to retrieve data and the "rating" table is empty then no data is shown and if there are more votes in the "rating" table than in the "media" table then it show duplicates of the "media".

<?php
$query  = "SELECT * ";
$query .= "FROM media, rating ";
$query .= "WHERE media.id = media_fkey";
$result = mysqli_query($link, $query);
if (!$result) {
    die("Database query failed.");
}

?>

I hope that this makes sense. :-)

Thank you in advance!

// René

possible duplicate of SQL getting data from multiple tables

19greg96
– 19greg96

2014-10-23 10:45:05 +00:00
Commented Oct 23, 2014 at 10:45 — 19greg96
– 19greg96, Commented Oct 23, 2014 at 10:45

Arun · Accepted Answer · 2014-10-23 10:54:40Z

1

try this

$query = "SELECT * FROM media AS m JOIN rating AS r ON m.id = r.media_fkey"

$result = mysqli_query($link, $query);

if (!$result) {
    die("Database query failed.");
}

or you can use the field name with object names other than *

SELECT m.id, m.data, r.media_fkey......

answered Oct 23, 2014 at 10:54

Arun

3,7519 gold badges51 silver badges90 bronze badges

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

1 Comment

Arun Over a year ago

@VanDerPrygel: with pleasure :)

Collectives™ on Stack Overflow

Retrieve data from multiple tables MySQL

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related