1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Selecting Using Two Tables

Discussion in 'MySQL' started by Jeremy Benson, Oct 13, 2015.

  1. #1
    I'm working on a feed where users can comment on products posted by people they're following. I don't want to create a feed table, because all the data is already present. The problem is it's proving to be really difficult. I don't know if it would be better to make a feed table or not.

    I have a `followers` table with `following` and `follower`

    I have a `products` table with `username` as the poster

    I'm trying to get a count of products where `username` = `follower` and `following` = the user currently logged on.

    The thing is I can't bind a param for following... unless I can use a sql statement to get all the followers of `following` and then count all the `products` where `username` = `follower`

    I thought of using inner join, something like below

    This isn't the right way, because I already need to know the follower. Is there a way to get all the followers of following and then count products, while doing this in a single statement?

    Thanks,
    Jeremy.
     
    Jeremy Benson, Oct 13, 2015 IP
  2. PoPSiCLe

    PoPSiCLe Illustrious Member

    Messages:
    4,623
    Likes Received:
    725
    Best Answers:
    152
    Trophy Points:
    470
    #2
    Wow. My brain hurts from the names of those columns. 'person_to_follow' is a lot better than 'follower', and 'following' should be swapped for the 'follower' name. That being said, you're looking to fetch all instances of content published by the persons the currently logged in user (the 'follower', if you bear with my name of the columns) - then why don't you just go something like:
    
    SELECT t1.*,t2.* FROM `products`t1 LEFT JOIN `followers` t2 ON t1.username = t2.person_to_follow WHERE t2.follower = :person_logged_in;
    
    Code (markup):
     
    PoPSiCLe, Oct 13, 2015 IP
  3. Jeremy Benson

    Jeremy Benson Well-Known Member

    Messages:
    364
    Likes Received:
    4
    Best Answers:
    0
    Trophy Points:
    123
    #3
    Follower = is the person following. Followers follow.
    Following = the person being followed.

    I clicked your follow button you become following, and I become the follower.

    I'm not understanding t1 and t2 here... is this table1 and table2? Also what is the period and astrix?

    Does the statement below look right?

    SELECT count(`ID`) FROM `products` LEFT JOIN `followers` ON products.username = followers.following WHERE followers.follower = :user
    Code (markup):
     
    Jeremy Benson, Oct 13, 2015 IP
  4. PoPSiCLe

    PoPSiCLe Illustrious Member

    Messages:
    4,623
    Likes Received:
    725
    Best Answers:
    152
    Trophy Points:
    470
    #4
    Yeah, that looks about right - sorry, I missed the fact you only wanted the count(*) (there's really no need counting a specific column, just do (*))
    And yes, t1. and t2. is just aliases for the table names. The asterix just means ALL - just as you can do a SELECT * FROM table_name to get all columns without having to specify each.
     
    PoPSiCLe, Oct 13, 2015 IP
  5. Jeremy Benson

    Jeremy Benson Well-Known Member

    Messages:
    364
    Likes Received:
    4
    Best Answers:
    0
    Trophy Points:
    123
    #5
    There's something missing..

    Getting error: Integrity constraint violation: 1052 Column 'ID' in field list is ambiguous'

    
    $db = new PDO($dsn, $dbUsername, $dbPassword, array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));
           
      $productCountStm = $db->prepare('
        SELECT count(`ID`) FROM
              `products` LEFT JOIN `followers` ON products.username = followers.following WHERE followers.follower = ?');
       
       $productCountStm->execute(array($user->return_username()));
       $productCount = $productCountStm->fetchColumn();
           
       $paginator = new pPaginate(5, 5, $productCount[0], isset($_GET['page']) && is_numeric($_GET['page']) ? $_GET['page'] : 0);
               
       var_dump($productCount);     
    
    PHP:
     
    Jeremy Benson, Oct 14, 2015 IP
  6. PoPSiCLe

    PoPSiCLe Illustrious Member

    Messages:
    4,623
    Likes Received:
    725
    Best Answers:
    152
    Trophy Points:
    470
    #6
    it's ambiguous because you have an ID-column in both tables. Use * instead of ID.
     
    PoPSiCLe, Oct 14, 2015 IP
  7. Jeremy Benson

    Jeremy Benson Well-Known Member

    Messages:
    364
    Likes Received:
    4
    Best Answers:
    0
    Trophy Points:
    123
    #7
    That's weird. How come it doesn't just count ID in products? That's where the count is taking place, lol.
     
    Jeremy Benson, Oct 14, 2015 IP
  8. PoPSiCLe

    PoPSiCLe Illustrious Member

    Messages:
    4,623
    Likes Received:
    725
    Best Answers:
    152
    Trophy Points:
    470
    #8
    No, it's not. You do the following "SELECT COUNT(`ID`) FROM .... rest of query here" - MySQL doesn't know that you want to count the IDs from the first (or second, or third) table. You've chosen to do it on "ID", hence MySQL tries to do it to ALL columns named ID - which won't work in this particular instance. If you must use ID, you need to prefix it with a table name - SELECT COUNT(products.ID) FROM ... and so forth.
     
    PoPSiCLe, Oct 15, 2015 IP
  9. Jeremy Benson

    Jeremy Benson Well-Known Member

    Messages:
    364
    Likes Received:
    4
    Best Answers:
    0
    Trophy Points:
    123
    #9
    Now I'm confused. What is the statement counting? I don't want to count entries in all tables. I just want to count products posted by followers in the products table. If they are a follower of user count from products table where that name is. Sql will have to loop through all the names and add it to the count taken in the products table.
     
    Jeremy Benson, Oct 15, 2015 IP
  10. PoPSiCLe

    PoPSiCLe Illustrious Member

    Messages:
    4,623
    Likes Received:
    725
    Best Answers:
    152
    Trophy Points:
    470
    #10
    You're really thinking about this all wrong - I'm not sure where (if anywhere) you've taken classes on programming, but, okay, lets see if we can explain this in a simple way.
    The "SELECT COUNT(*) FROM products" will select (count) ALL rows in that table. Regardless of what they contain - if it's a registered row, it will be counted.
    That's the simple, easy way of counting ALL rows.
    Now, you have a statement like the following:
    
    "SELECT COUNT(`ID`) FROM products LEFT JOIN followers ON products.username = followers.following WHERE followers.follower = ?"
    
    Code (markup):
    The above code selects a count of `ID` - but since it's NOT prefixed by a table-name (like products.ID) MySQL doesn't know that you want to pull the count from the products table - you haven't informed MySQL of that.
    Therefore, to avoid having to make sure there's no similar column names that has to be prefixed, you just simply use an asterisk (*) to SELECT ALL the rows - and THIS is where having a JOIN and a WHERE statement comes in. First, the LEFT JOIN limits how many rows are fetched based on matching two columns in different tables - the rows which doesn't match aren't fetched. THEN you use a WHERE clause on the RESULT from the join to limit it even further - by making sure that only the followers.follower (the table: followers, and the column: follower) that matches the user is being counted. Hence, the result is (or, should be, given that the information in the database is correct) the total count that matches your criteria.

    Nowhere are you counting ALL tables, or anything - you're always only working with the tables you assign after FROM and with joins. Nor are you counting all rows, since you've limited that by JOIN and WHERE.

    Hope that made a little more sense.


    (BTW, always wrap queries in double quotes. Will save you quite a few headaches).
     
    PoPSiCLe, Oct 15, 2015 IP
  11. Jeremy Benson

    Jeremy Benson Well-Known Member

    Messages:
    364
    Likes Received:
    4
    Best Answers:
    0
    Trophy Points:
    123
    #11
    Well, it still looks wrong to the eye...

    "SELECT COUNT(`ID`) FROM products LEFT JOIN followers ON products.username = followers.following WHERE followers.follower = ?"

    Looks like this line "products.username = followers.following" is going to count from products where username = following... but following would be the logged on user in this case. I would think that line should be "products.username = followers.follower"

    Then we would have

    "SELECT COUNT(`*`) FROM products LEFT JOIN followers ON products.username = followers.follower WHERE followers.follower = ?"

    Looks like count all rows in products limit count to names in followers table where username in product table matches user who is follower..

    Am I wrong?
     
    Jeremy Benson, Oct 15, 2015 IP
  12. PoPSiCLe

    PoPSiCLe Illustrious Member

    Messages:
    4,623
    Likes Received:
    725
    Best Answers:
    152
    Trophy Points:
    470
    #12
    Again - your naming of the columns are horrible, and I might have mixed up what is what. But you know which column should be used for what (or, you should know) - so I'm sure you'll figure it out. BTW, it's just for you to test both queries - the one returning the correct result is the right one ;)
     
    PoPSiCLe, Oct 15, 2015 IP
  13. Jeremy Benson

    Jeremy Benson Well-Known Member

    Messages:
    364
    Likes Received:
    4
    Best Answers:
    0
    Trophy Points:
    123
    #13
    I'm getting error :Column not found: 1054 Unknown column '*' in 'field list''

    
    $db = new PDO($dsn, $dbUsername, $dbPassword, array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));
           
      $productCountStm = $db->prepare("
        SELECT count(`*`) FROM
              `products` LEFT JOIN `followers` ON products.username = followers.follower WHERE followers.follower = ?");
       
       $productCountStm->execute(array($user->return_username()));
       $productCount = $productCountStm->fetchColumn();
           
       $paginator = new pPaginate(5, 5, $productCount[0], isset($_GET['page']) && is_numeric($_GET['page']) ? $_GET['page'] : 0);
    
    
    PHP:
     
    Jeremy Benson, Oct 16, 2015 IP
  14. PoPSiCLe

    PoPSiCLe Illustrious Member

    Messages:
    4,623
    Likes Received:
    725
    Best Answers:
    152
    Trophy Points:
    470
    #14
    You don't escape * it's COUNT(*)
     
    PoPSiCLe, Oct 16, 2015 IP
  15. Jeremy Benson

    Jeremy Benson Well-Known Member

    Messages:
    364
    Likes Received:
    4
    Best Answers:
    0
    Trophy Points:
    123
    #15
    Shoot, lol. I'll test that in a bit. Thanks Popsicle :p Sorry for being difficult, lol.
     
    Jeremy Benson, Oct 16, 2015 IP