My query isn't working right - help please

My sql statement isn't working on my website, and i was wondering if someone could help me out real quick

"SELECT users.first_name, users.last_name, users.user_id, venues.venue_name, venues.venue_id, venues.venue_location, checkins.checkin_time FROM users, venues, checkins
WHERE users.user_id IN ( $friend_array ) ORDER BY checkins.checkin_time DESC LIMIT 5"

On my website, it's pulling the first name and last name of what appears to be 1 random friend, and a random venue in the venue table..

However, it should be pulling the most 5 recent checkins of people you are friends with, and where they checked in.

Please help! Thanks :)


<?php
// GATHER RECENT FRIEND CHECK INS
$RecentCheckInHistoryList .= "";
if($friend_array != "") {
$RecentCheckInHistoryList = "";
$RecentCheckInHistoryList .='<div id="friendsCheckins" class="contentContainer">
<div class="header">
<p>Friends\' Recent Check-ins</p>
</div> ';
$RecentCheckInHistoryList .= '<div style="padding: 0;" class="grayBox flatTop">';

$sqlCheckin = mysql_query("SELECT users.first_name, users.last_name, users.user_id, venues.venue_name, venues.venue_id, venues.venue_location, checkins.checkin_time FROM users, venues, checkins
WHERE users.user_id IN ( $friend_array ) ORDER BY checkins.checkin_time DESC LIMIT 5") or die ("Sorry, we had a mysql error.");
while($row = mysql_fetch_array($sqlCheckin)) {
$friendFirstName = $row["first_name"];
$friendLastName = $row["last_name"];
$checkintime = $row['checkin_time'];
$checkintime = strftime("%b %d, %Y", strtotime($checkintime));
$venue_name = $row['venue_name'];
$venue_id = $row['venue_id'];
$venue_location = $row['venue_location'];
$value = $row["user_id"];
$check_pic = 'user_photos/' . $value . '/image01.jpg';

if(file_exists($check_pic)) {
$frnd_pic = '<a href="profile.php?id=' . $value . '"><img src="' . $check_pic . '" width="60px" height="60px" border="1" /></a>';
} else {
$frnd_pic = '<a href="profile.php?id=' . $value . '"><img src="user_photos/0/image01.jpg" width="60px" height="60px" border="1" /></a>
&nbsp;';
}
}

$RecentCheckInHistoryList .=' <div class="boxContainer">
<div class="avatar">' . $frnd_pic . '</div>
<div class="content">
<h3><strong><a href="profile.php?id=' . $value . '">' . $friendFirstName . ' ' . $friendLastName . '</a></strong> @ <a href="/venue.php?id=' . $venue_id . '">' . $venue_name . '</a></h3>

<small>' . $venue_location . ' - ' . $checkintime . '</small>
</div>
</div> ';
$RecentCheckInHistoryList .=' </div></div></div>';
} else {
$RecentCheckInHistoryList .='<div id="friendsCheckins" class="contentContainer">
<div class="header">
<p>Friends\' Recent Check-ins</p>
</div> <div style="padding: 0;" class="grayBox flatTop">
<div class="boxContainer">Add friends to be able to see their most recent checkins!
</div></div></div>';
}
?>

SELECT users.first_name, users.last_name, users.user_id, venues.venue_name, venues.venue_id, venues.venue_location, checkins.checkin_time FROM users, venues, checkins
WHERE users.user_id IN ( $friend_array ) ORDER BY checkins.checkin_time DESC LIMIT 5

You are joining three tables here yet you have no join conditions in your query.

you need to specify columns to join users to venues and checkins to venues.

Without that you get cross join effects.

If you have 10 rows in each table then you are getting 10x10x10 rows back in your query.

Fix that first.

Second tip, when you are having problems with a query, the php code is not necessary in your post until you/we have determined we need to see it. Otherwise it is just extra code obscuring what needs to be looked at.

SELECT users.first_name, users.last_name, users.user_id, venues.venue_name, venues.venue_id, venues.venue_location, checkins.checkin_time FROM users INNER JOIN checkins ON users.user_id=checkins.user_id
WHERE users.user_id IN ( $friend_array ) ORDER BY checkins.checkin_time DESC LIMIT 5


I have the users table and checkins table joined..how would I inner join a 3rd table, the venues table?

SELECT users.first_name, users.last_name, users.user_id, venues.venue_name, venues.venue_id, venues.venue_location, checkins.checkin_time FROM users, checkins, venues
WHERE users.user_id IN ( $friend_array ) AND users.user_id=checkins.user_id AND venues.venue_id=checkins.venue_id ORDER BY checkins.checkin_time DESC LIMIT 5


Wouldn't this work?...

How can we know? We have no idea what fields are in the venue table other than enues.venue_name, venues.venue_id, venues.venue_location and almost certainly you don't want to join on them. Or do you? Again, how can we know without seeing your DB's schema?

And it helps us to help you if you make your lines of code shorter and thus more readable:

SELECT users.first_name, users.last_name, users.user_id, venues.venue_name,
venues.venue_id, venues.venue_location, checkins.checkin_time
FROM users INNER JOIN checkins ON users.user_id=checkins.user_id
WHERE users.user_id IN ( $friend_array )
ORDER BY checkins.checkin_time DESC
LIMIT 5


As a *PURE GUESS*, I'm going to guess that the checkins table has a venue_id field in it. If so,

SELECT users.first_name, users.last_name, users.user_id, venues.venue_name,
venues.venue_id, venues.venue_location, checkins.checkin_time
FROM users INNER JOIN checkins ON users.user_id = checkins.user_id
INNER JOIN venues ON venues.venue_id = checkins.venue_id
WHERE users.user_id IN ( $friend_array )
ORDER BY checkins.checkin_time DESC
LIMIT 5

But again, that's only a guess. Only you will know what is right until/unless you show us the schema.

checkins table has the following:

checkin_id - primary key
user_id
venue_id
checkin_time - timestamp

It is only pulling members from the friend array, and it's pulling the correct checkin/venue...but it is only returning 1, not 5?

Does anyone know why the following query is only returning one result? The query is running correctly( it is pulling someone out of the friend array and only if they've been checked in to a location...but it will not show more than 1. Any suggestions, please help. Thanks!


SELECT users.first_name, users.last_name, users.user_id, venues.venue_name,
venues.venue_id, venues.venue_location, checkins.checkin_time
FROM users INNER JOIN checkins ON users.user_id = checkins.user_id
INNER JOIN venues ON venues.venue_id = checkins.venue_id
WHERE users.user_id IN ( $friend_array )
ORDER BY checkins.checkin_time DESC
LIMIT 5

Afraid you'd have to show some sample data for me to guess.

How many friends are in the $friend_array?

Did you DEBUG??? Did you echo the SQL query so you can be sure it's what you think it is?