In the example code below we have attempted to minimize our problem into a simplified test case.
We have 3 tables: users, images and ratings.
1: We want to print all entries from the ‘ratings’ table.
2: In the ‘images’ table, there can be several images uploaded by the same users into different sections of the site, e.g. ‘user_profile_picture’ or ‘user_cover_photo’.
3: When printing the ratings, we want to fetch entries from the ‘images’ table if the user has uploaded a profile picture (section = ‘profile_picture’).
The WHERE statement in the query in the example code fails for users with no profile pictures uploaded.
How can we make sure to print all entries, as well as only grab the image connected to the user if section = profile_picture …?
CREATE TABLE `test_ratings` (
`id` int(4) UNSIGNED NOT NULL AUTO_INCREMENT,
`user_id` int(4) UNSIGNED NOT NULL,
`rating` enum('1','2','3','4','5') DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
INSERT INTO `test_ratings` (`id`, `user_id`, `rating`) VALUES
(2, 2, '4'),
(1, 1, '5');
CREATE TABLE `test_users` (
`id` int(4) UNSIGNED NOT NULL AUTO_INCREMENT,
`username` varchar(30) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
INSERT INTO `test_users` (`id`, `username`) VALUES
(1, 'Test person 1'),
(2, 'Test person 2');
CREATE TABLE `test_users_images` (
`id` int(4) UNSIGNED NOT NULL AUTO_INCREMENT,
`filename` varchar(80) NOT NULL,
`section` enum('user_cover_photo','user_profile_picture') DEFAULT NULL,
`user_id` int(4) UNSIGNED DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
INSERT INTO `test_users_images` (`id`, `filename`, `section`, `user_id`) VALUES
(1, 'rtfos.jpg', 'user_profile_picture', 2),
(2, 'exer8.jpg', 'user_cover_photo', 2);
<?php
$mysqli = new mysqli("localhost", "root", "", "website");
$result = $mysqli->query('SELECT image.filename, user.username, ratings.rating
FROM test_ratings ratings
INNER JOIN test_users user ON user.id = ratings.user_id
INNER JOIN test_users_images image ON image.user_id = user.id
WHERE image.section = \'user_profile_picture\'');
while ($row = $result->fetch_object()) {
echo '<p>' . $row->username . ' - ' . $row->filename . '</p>';
}
?>
As already commented, start with the user table and left join to it. Include the image section filter in the ON clause of the respective join.
SELECT image.filename,
user.username,
ratings.rating
FROM test_users user
LEFT JOIN test_users_images image
ON image.user_id = user.id
AND image.section = 'user_profile_picture'
LEFT JOIN test_ratings ratings
ON user.id = ratings.user_id;
P.S.: It's a WHERE clause, not a statement.
By changing the INNER JOIN to a LEFT JOIN for image.sections you keep all rows from ratings (if the user still exists in the database). To include users without a profile image you have to add a condition that image.section is allowed to be NULL.
SELECT image.filename, user.username, ratings.rating
FROM test_ratings ratings
INNER JOIN test_users user ON user.id = ratings.user_id
LEFT JOIN test_users_images image ON image.user_id = user.id
WHERE image.section = 'user_profile_picture' OR image.section IS NULL