#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    4
    Rep Power
    0

    MySQL query help


    I need help to write a query to count the total of a user in 2 tables. Count total based on userID in the following fields by month pos_start_userID, pos_end_userID, transfer_1_userID, transfer_2_userID, transfer_3_userID.

    For example: The total pos count for John Doe in the month of September is 3, for October is 1, and for November is 1.

    TABLE users
    user_id
    user_name

    users VALUES
    (1,'John Doe),
    (2,'Jane Smith),
    (3,'Kevin Brown');

    TABLE pos
    pos_id
    pos_date
    pos_start_amount
    pos_end_amount
    pos_start_userID
    pos_end_userID

    pos VALUES
    (1,'2012-09-05 00:00:00',100,320,1,1),
    (2,'2012-09-15 00:00:00',230,543,1,3),
    (3,'2012-10-14 00:00:00',40,56,2,3),
    (4,'2012-11-03 00:00:00',24,124,2,1),
    (5,'2012-11-25 00:00:00',20,75,3,3);

    TABLE pos_transfer
    pos_id
    transfer_1
    transfer_2
    transfer_3
    transfer_1_userID
    transfer_2_userID
    transfer_3_userID

    pos_transfer VALUES
    (1,20,'','',2,'',''),
    (2,45,70,'',1,3,''),
    (3,50,120,20,2,2,1),
    (4,43,'','',3,'',''),
    (5,24,30,'',2,3,'');

    Thank you
  2. #2
  3. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    98
    Rep Power
    2
    Are you just trying to count the amount of times a user is in 2 tables? If so you can just use a query = "SELECT statement with a JOIN and WHERE criteria of user_id = 'user_id'

    and then: $num_users = mysql_num_rows ($queryResult);

    $num_users = number of user in both tables?

    Comments on this post

    • Jacques1 disagrees : bad advice; counting rows is what COUNT(*) is for
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    4
    Rep Power
    0
    Originally Posted by BitZoid
    Are you just trying to count the amount of times a user is in 2 tables? If so you can just use a query = "SELECT statement with a JOIN and WHERE criteria of user_id = 'user_id'

    and then: $num_users = mysql_num_rows ($queryResult);

    $num_users = number of user in both tables?
    Yes that's exactly what I want to do. Can you write a sample query for me to see.

    Thank You
  6. #4
  7. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    98
    Rep Power
    2
    Originally Posted by caplocks
    Yes that's exactly what I want to do. Can you write a sample query for me to see.

    Thank You
    What language are you writing the Query in?
  8. #5
  9. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,957
    Rep Power
    1046
    Hi,

    do not use mysql_num_rows() to count rows. That's the most unclean and slow way to do this. Counting rows is what MySQL is for:

    Code:
    SELECT COUNT(*) ...
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    4
    Rep Power
    0
    BitZoid Im using PHP.

    @ Jacques1: Thanks for the concern but so far BitZoid is the only one that have a solution for me. I'm learning more and more each day and I will keep this tip in mind.
  12. #7
  13. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    98
    Rep Power
    2
    Originally Posted by caplocks
    BitZoid Im using PHP.

    @ Jacques1: Thanks for the concern but so far BitZoid is the only one that have a solution for me. I'm learning more and more each day and I will keep this tip in mind.
    No clean solutions for you, but I can guide you

    $queryUsers = "SELECT user FROM table1, table 2 WHERE user='criteria'";

    $resultUsers = mysql_query($queryUsers);

    $user_count = mysql_num_rows ($resultUsers);


    Note** This uses EquiJoin which is being depricated, instead of the newer more relevant Inner Join.
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    4
    Rep Power
    0
    Originally Posted by BitZoid
    No clean solutions for you, but I can guide you

    $queryUsers = "SELECT user FROM table1, table 2 WHERE user='criteria'";

    $resultUsers = mysql_query($queryUsers);

    $user_count = mysql_num_rows ($resultUsers);


    Note** This uses EquiJoin which is being depricated, instead of the newer more relevant Inner Join.
    Thanks BitZoid I will give this a try.
  16. #9
  17. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    Originally Posted by BitZoid
    $user_count = mysql_num_rows ($resultUsers);
    this is grossly inefficient

    use COUNT(*) in sql to count rows
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  18. #10
  19. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,957
    Rep Power
    1046
    Originally Posted by r937
    this is grossly inefficient

    use COUNT(*) in sql to count rows
    I already said that. But since nobody else posted a solution, they sticked with it.



    @ caplocks:

    I don't understand the "pos" logic. What do "pos" and "pos_transfer" with their columns mean? And how exactly do you count the users?

    In any case, you'll need to JOIN the tables (like BitZoid said), GROUP them by the user ID, the MONTH() and the YEAR() and then select the COUNT(*) to calculate the number of rows for each user, month and year.
  20. #11
  21. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    98
    Rep Power
    2
    oh yeah, I'm in SQL Database class now in school and I am still using my old outdated methods.

    SELECT user_id, COUNT(user) AS [User Count] FROM table 1 INNER JOIN table 2 ON table1.user=table2.user GROUP BY user_id

    Does this look right?
  22. #12
  23. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    Originally Posted by BitZoid
    Does this look right?
    not in mysql, no

    those square brackets are a microsoftism and will work only in sql server and access
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo