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 11 of 11
  1. #1
    Regular Coder
    Join Date
    Oct 2009
    Posts
    190
    Thanks
    12
    Thanked 2 Times in 2 Posts

    Return results from two querys and compare

    ...sort of

    I have page that displays a member list of the entire site. The list is built by searching for all members in the "users" (MySQL)table. For each row returned, a (html)table row is generated and displayed.

    The logged in user has the option of "following" any user displayed in this (html)table.

    Everything so far works great.

    The Problem is

    I have another (MySQL)table that tracks who is following who. 3 rows (ID, user, following) where ID is an auto incrementing unique ID, user is the unique identifier from the users (MySQL)table and following is the unique identifier from the users (MySQL)table that the current user is following.

    I want for the members list to display "unfollow" instead of "follow" for members that the user is already following. I'm not entirely sure how to go about doing this.

    I know that I need to check the "following" table and fetch all of the current user's followed members, and then compare that to the the user's in the member list.

    here is how the member list table is displayed
    PHP Code:
      <table>
            <tr>
            <td><strong>Name</strong></td>
            <td><strong>Last Race</strong></td>
            <td><strong>XXXXX</strong></td>
            <td><strong>License</strong></td>
            <td><strong>Message</strong></td>
            <td><strong>Follow</strong></td></tr>
            <?php #returns results
                  
    for ($j 0$j $rows ; ++$j)
                  {
                      
    $fname    mysql_result($result$j'fname');
                      
    $lname    mysql_result($result$j'lname');
                      
    $license     mysql_result($result$j'license');
        
                  echo 
    "<tr>
                  <td>$fname $lname</td>
                  <td>Last Race</td>
                  <td>Rank</td>
                  <td><a href=\"http://www.XXXXXX.XXX/results/index.php?compid=$license\">$license</a></td>
                  <td>Message</td>
                  "
    ;
                  if (
    $following == $license)
                  {
                      echo
    "
                      <td><form class=\"submit-link\" method=\"get\" action=\"includes/follow-sql.php\"><input type=\"submit\" value=\"Un-Follow\"><input name=\"license\" type=\"hidden\" value=\"$license\"></form></td>"
    ;
                  }
                  else {
                      echo
    "
                  <td><form class=\"submit-link\" method=\"get\" action=\"includes/follow-sql.php\"><input type=\"submit\" value=\"Follow\"><input name=\"license\" type=\"hidden\" value=\"$license\"></form></td>"
    ;    
                  }
                  }
            
    ?>
            </table>

  • #2
    Senior Coder Rowsdower!'s Avatar
    Join Date
    Oct 2008
    Location
    Some say it's everything.
    Posts
    2,027
    Thanks
    5
    Thanked 397 Times in 390 Posts
    I have another (MySQL)table that tracks who is following who. 3 rows (ID, user, following) where ID is an auto incrementing unique ID, user is the unique identifier from the users (MySQL)table and following is the unique identifier from the users (MySQL)table that the current user is following.
    So does 'following' return an array or is it a CSV or what?
    The object of opening the mind, as of opening the mouth, is to shut it again on something solid. –G.K. Chesterton
    See Mediocrity in its Infancy
    It's usually a good idea to start out with this at the VERY TOP of your CSS: * {border:0;margin:0;padding:0;}
    Seek and you shall find... basically:
    validate your markup | view your page cross-browser/cross-platform | free web tutorials | free hosting

  • #3
    Regular Coder
    Join Date
    Oct 2009
    Posts
    190
    Thanks
    12
    Thanked 2 Times in 2 Posts
    Quote Originally Posted by Rowsdower! View Post
    So does 'following' return an array or is it a CSV or what?
    This could be tricky with all the terminology being reused. I'm assuming you mean the variable $following?

    The actual value of following is part of the issue. Each user is going to potentially have many different users that they are following, so my instinct is to approach that in the same manner that my member list is being populated.


    $user is identified via a session cookie
    PHP Code:
    $query2  "SELECT follow FROM follow_list WHERE user='$user'";
    $result2 mysql_query($query2);
    $rows2     mysql_num_rows($result2);
     for (
    $r 0$r $rows2 ; ++$r)
          {
              
    $following mysql_result($result2$r'follow');

          } 

    follow_list table looks like this:

    [ID][user][following]


    User and following are numbers that identifies the users in the users table.
    Last edited by tspek; 01-14-2010 at 07:33 PM.

  • #4
    Senior Coder Rowsdower!'s Avatar
    Join Date
    Oct 2008
    Location
    Some say it's everything.
    Posts
    2,027
    Thanks
    5
    Thanked 397 Times in 390 Posts
    Quote Originally Posted by tspek View Post
    ...User and following are numbers that identifies the users in the users table.
    This is what I mean. When a user is following more than one other user the field 'following' would need to have more than one ID in it. So, these numbers held in the table row called "following," are they stored as comma separated values (CSV)? If not, how are they stored?

    The answer to that will determine how exactly to go about creating an array of the users being followed.

    Then, when you have your array of followed users, all you need to do is loop through your full list of users when printing. While in the loop if the user ID exists in the list of followed users you print a different link ("unfollow") than if the user ID does not exist in the list of followed users.

    It would look something like this:
    PHP Code:
    if(in_array($license,$following)){
    //...print un-follow link here...
    }
    else {
    //...print follow link here

    The object of opening the mind, as of opening the mouth, is to shut it again on something solid. –G.K. Chesterton
    See Mediocrity in its Infancy
    It's usually a good idea to start out with this at the VERY TOP of your CSS: * {border:0;margin:0;padding:0;}
    Seek and you shall find... basically:
    validate your markup | view your page cross-browser/cross-platform | free web tutorials | free hosting

  • Users who have thanked Rowsdower! for this post:

    tspek (01-15-2010)

  • #5
    Regular Coder
    Join Date
    Nov 2009
    Location
    Hamilton, New Zealand
    Posts
    126
    Thanks
    0
    Thanked 17 Times in 17 Posts
    I assume that he will be inserting a row for each person he is following. As in, only the ID field is unique. For example, the following 3 records could be in the db:
    ID User Following
    1 1 2
    2 1 3
    3 2 3

    Hence user 1 is following both users 2 and 3, and user 2 is following only user 3. User 3 is not following anyone.

    If my assumption is correct, you could do the following:

    PHP Code:
    $q1 "SELECT * FROM users";
    $userid 1;//Use what ever method to grab userid (cookies or whatever) here.
    while ($fetch mysql_fetch_array($q1)){
    //Echo your html table here
      
    $followid $fetch['id'];
      
    $q2 "SELECT * FROM followuser WHERE user = '$userid' && following = '$followid'";
      if (
    mysql_num_rows($q2) == 0){
        echo 
    "<a href'#">Follow</a>";
      }else{
        echo "
    <a href#">Unfollow</a>";
      
    }

    Also, you may not need the auto_inc ID field as the user and following fields together make a unique match.
    Affordable Web Design (New Zealand Based)
    Internet Marketing Guru
    PHP/mySQL Expert
    -------------------------------------------

  • Users who have thanked Rebbu for this post:

    tspek (01-15-2010)

  • #6
    Regular Coder
    Join Date
    Oct 2009
    Posts
    190
    Thanks
    12
    Thanked 2 Times in 2 Posts
    Exactly. Thank you i'll give that a shot.

    Quote Originally Posted by Rebbu View Post
    I assume that he will be inserting a row for each person he is following. As in, only the ID field is unique. For example, the following 3 records could be in the db:
    ID User Following
    1 1 2
    2 1 3
    3 2 3

    Hence user 1 is following both users 2 and 3, and user 2 is following only user 3. User 3 is not following anyone.

    If my assumption is correct, you could do the following:

    PHP Code:
    $q1 "SELECT * FROM users";
    $userid 1;//Use what ever method to grab userid (cookies or whatever) here.
    while ($fetch mysql_fetch_array($q1)){
    //Echo your html table here
      
    $followid $fetch['id'];
      
    $q2 "SELECT * FROM followuser WHERE user = '$userid' && following = '$followid'";
      if (
    mysql_num_rows($q2) == 0){
        echo 
    "<a href'#">Follow</a>";
      }else{
        echo "
    <a href#">Unfollow</a>";
      
    }

    Also, you may not need the auto_inc ID field as the user and following fields together make a unique match.
    Last edited by tspek; 01-15-2010 at 03:31 AM.

  • #7
    Regular Coder
    Join Date
    Oct 2009
    Posts
    190
    Thanks
    12
    Thanked 2 Times in 2 Posts
    I like what you're getting at here too. Thanks for the feedback.

    Quote Originally Posted by Rowsdower! View Post
    This is what I mean. When a user is following more than one other user the field 'following' would need to have more than one ID in it. So, these numbers held in the table row called "following," are they stored as comma separated values (CSV)? If not, how are they stored?

    The answer to that will determine how exactly to go about creating an array of the users being followed.

    Then, when you have your array of followed users, all you need to do is loop through your full list of users when printing. While in the loop if the user ID exists in the list of followed users you print a different link ("unfollow") than if the user ID does not exist in the list of followed users.

    It would look something like this:
    PHP Code:
    if(in_array($license,$following)){
    //...print un-follow link here...
    }
    else {
    //...print follow link here


  • #8
    Regular Coder
    Join Date
    Oct 2009
    Posts
    190
    Thanks
    12
    Thanked 2 Times in 2 Posts
    I just had a tada moment...I'm not sure why this didn't occur to me earlier. I was over thinking this a bit. This works how I want it, is there any reason it's not an efficient way to do this?

    PHP Code:
    <?php #returns results

     
          
    for ($j 0$j $rows ; ++$j)
          {
                
                
            
    $fname    mysql_result($result$j'fname');
            
    $lname    mysql_result($result$j'lname');
            
    $license     mysql_result($result$j'license');
            
          echo 
    "<tr>
          <td>$fname $lname</td>
          <td>Last Race</td>
          <td>Rank</td>
          <td><a href=\"http://www.usacycling.org/results/index.php?compid=$license\">$license</a></td>
          <td>Message</td>
          "
    ;
          
    #Finds Follow List
            
    $query2        "SELECT user, follow FROM follow_list WHERE user='$user' and follow='$license'";
            
    $result2    mysql_query($query2);
            
    $rows2        mysql_num_rows($result2);
            
            if(
    $rows2 == 1)
            {
              echo
    "
              <td><form class=\"submit-link\" method=\"get\" action=\"includes/follow-sql.php\"><input type=\"submit\" value=\"Un-Follow\"><input name=\"license\" type=\"hidden\" value=\"$license\"></form></td>"
    ;
             }
          else 
            {
              echo
    "
          <td><form class=\"submit-link\" method=\"get\" action=\"includes/follow-sql.php\"><input type=\"submit\" value=\"Follow\"><input name=\"license\" type=\"hidden\" value=\"$license\"></form></td>"
    ;    
             }
          }

  • #9
    Senior Coder Rowsdower!'s Avatar
    Join Date
    Oct 2008
    Location
    Some say it's everything.
    Posts
    2,027
    Thanks
    5
    Thanked 397 Times in 390 Posts
    It's probably not good to query your database that many times just to save on PHP code. You only need to find the data once so you should really only request it once.
    The object of opening the mind, as of opening the mouth, is to shut it again on something solid. –G.K. Chesterton
    See Mediocrity in its Infancy
    It's usually a good idea to start out with this at the VERY TOP of your CSS: * {border:0;margin:0;padding:0;}
    Seek and you shall find... basically:
    validate your markup | view your page cross-browser/cross-platform | free web tutorials | free hosting

  • #10
    Regular Coder
    Join Date
    Oct 2009
    Posts
    190
    Thanks
    12
    Thanked 2 Times in 2 Posts
    Quote Originally Posted by Rowsdower! View Post
    It's probably not good to query your database that many times just to save on PHP code. You only need to find the data once so you should really only request it once.
    That occurred to me on my drive in to work.

  • #11
    Regular Coder
    Join Date
    Oct 2009
    Posts
    190
    Thanks
    12
    Thanked 2 Times in 2 Posts
    So the final result looks like this:

    PHP Code:
    #sets array in case user not following anyone
    $follow[0] = "0";

    #stores results into array
    for($i 0$i $rows2; ++$i)
    {
        
    $followarr    mysql_result($result2$i'follow');
        
    $follow[$i] = "$followarr";
    }

    ###############

    #returns results
                  
    for ($j 0$j $rows ; ++$j)
                  {    
                      
    $fname    mysql_result($result$j'fname');
                      
    $lname    mysql_result($result$j'lname');
                      
    $license     mysql_result($result$j'license');
                    
                  echo 
    "<tr>
                  <td>$fname $lname</td>
                  <td>Rank</td>
                  <td><a href=\"http://www.usacycling.org/results/index.php?compid=$license\">$license</a></td>
                  <td>Message</td>
                  "
    ;
                 
                    
                    if(
    in_array($license$follow))
                      {
                      echo
    "
                      <td><form class=\"submit-link\" method=\"get\" action=\"includes/unfollow-sql.php\"><input type=\"submit\" value=\"Un-Follow\"><input name=\"license\" type=\"hidden\" value=\"$license\"></form></td>"
    ;
                      }
                  else 
                      {
                      echo
    "
                  <td><form class=\"submit-link\" method=\"get\" action=\"includes/follow-sql.php\"><input type=\"submit\" value=\"Follow\"><input name=\"license\" type=\"hidden\" value=\"$license\"></form></td>"
    ;    
                      }
                  } 


  •  

    Posting Permissions

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