Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 9 of 9
  1. #1
    Regular Coder
    Join Date
    Jan 2011
    Posts
    120
    Thanks
    6
    Thanked 2 Times in 2 Posts

    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 Code:
    <?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>'
    ;
    }
    ?>

  • #2
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,633
    Thanks
    4
    Thanked 148 Times in 139 Posts
    Quote Originally Posted by MattClark View Post
    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.

  • #3
    Regular Coder
    Join Date
    Jan 2011
    Posts
    120
    Thanks
    6
    Thanked 2 Times in 2 Posts
    Code:
    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?

  • #4
    Regular Coder
    Join Date
    Jan 2011
    Posts
    120
    Thanks
    6
    Thanked 2 Times in 2 Posts
    Code:
    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?...

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,133
    Thanks
    75
    Thanked 4,338 Times in 4,304 Posts
    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:
    Code:
    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,
    Code:
    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.
    Last edited by Old Pedant; 04-17-2011 at 04:12 AM.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #6
    Regular Coder
    Join Date
    Jan 2011
    Posts
    120
    Thanks
    6
    Thanked 2 Times in 2 Posts
    checkins table has the following:

    checkin_id - primary key
    user_id
    venue_id
    checkin_time - timestamp

  • #7
    Regular Coder
    Join Date
    Jan 2011
    Posts
    120
    Thanks
    6
    Thanked 2 Times in 2 Posts
    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?

  • #8
    Regular Coder
    Join Date
    Jan 2011
    Posts
    120
    Thanks
    6
    Thanked 2 Times in 2 Posts
    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!

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

  • #9
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,133
    Thanks
    75
    Thanked 4,338 Times in 4,304 Posts
    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?
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •