Thread: Join two tables

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

    Join Date
    Jun 2012
    Posts
    12
    Rep Power
    0

    [SOLVED] Join two tables


    Hi,
    I have two tables 'tbl_name' & 'attending' in the same database 'csv_db'

    I would like to join 'attending' to 'tbl_name' by name. My original code is:
    Code:
    $result = mysql_query('select * from (select * from tbl_name WHERE PLAN != "" AND PLAN != "HOME"  ORDER BY NAME DESC ) AS x GROUP BY NAME, PLAN');
    
    while($row = mysql_fetch_array($result))
    I tried
    Code:
    $result = mysql_query('select * from (select * from tbl_name LEFT JOIN attending ON tbl_name.NAME =attending.NAME WHERE tbl_name.PLAN != "" AND tbl_name.PLAN != "HOME"  ORDER BY tbl_name.NAME DESC ) AS x GROUP BY tbl_name.NAME, tbl_name.PLAN');
    but I get "mysql_fetch_array() expects parameter 1 to be resource, boolean given ..."

    any idea on how to fix this. Thanks
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,795
    Rep Power
    4332
    Originally Posted by moussa854
    any idea on how to fix this. Thanks
    nope, no idea, because this isn't the php forum

    however, i can help you with the mysql

    pull it out of the php code and run it right in mysql yourself, to see if it works

    if it doesn't, post the error message
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,953
    Rep Power
    383
    Don't use 'SELECT *'. Name the columns you actually want returned, and alias those that are common to both tables - otherwise you'll create ambiguity, both in the GROUP BY clause AND in PHP, when it comes to processing the result.

    Note that this approach to the GROUPWISE MAX problem is undocumented - except by its chief proponent, and the uncorrelated subquery method is nearly always faster.

    While in development, rewrite your code as follows. Remove the error calls once everything's working...

    $query="my query goes here";

    echo $query;

    $result = mysql_query($query) or die (mysql_error());

    Finally, this particular method (mysql_) is deprecated. Consider switching to mysqli_ or PDO.
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Posts
    12
    Rep Power
    0
    Thank you all for your help. Here is what I did.
    PHP Code:
    $result mysql_query('select * from (select tbl_name.NAME, tbl_name.PLAN, attending.Attending from tbl_name LEFT JOIN attending ON tbl_name.NAME =attending.NAME WHERE tbl_name.PLAN != "" AND tbl_name.PLAN != "HOME"  ORDER BY tbl_name.NAME DESC ) AS x GROUP BY tbl_name.NAME, tbl_name.PLAN');

    while(
    $row mysql_fetch_array($result))
    {
    $body$row['tbl_name.NAME'];

    but I get "Query was empty"
    "SCREAM: Error suppression ignored for
    Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in ..."
  8. #5
  9. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,795
    Rep Power
    4332
    please run this query directly in mysql, not through php --
    Code:
    select * 
    from 
    (select tbl_name.NAME, tbl_name.PLAN, attending.Attending 
    from tbl_name 
    LEFT JOIN attending 
    ON tbl_name.NAME =attending.NAME 
    WHERE tbl_name.PLAN != "" AND tbl_name.PLAN != "HOME"  
    ORDER BY tbl_name.NAME DESC 
    ) AS x 
    GROUP BY tbl_name.NAME, tbl_name.PLAN
    i need you to see the actual error message this is causing
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,953
    Rep Power
    383
    >Here is what I did.

    I.e. nothing.

    !?!?!
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Posts
    12
    Rep Power
    0
    Originally Posted by r937
    please run this query directly in mysql, not through php --
    Code:
    select * 
    from 
    (select tbl_name.NAME, tbl_name.PLAN, attending.Attending 
    from tbl_name 
    LEFT JOIN attending 
    ON tbl_name.NAME =attending.NAME 
    WHERE tbl_name.PLAN != "" AND tbl_name.PLAN != "HOME"  
    ORDER BY tbl_name.NAME DESC 
    ) AS x 
    GROUP BY tbl_name.NAME, tbl_name.PLAN
    i need you to see the actual error message this is causing
    I got
    #1054 - Unknown column 'tbl_name.name' in 'group statement'
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,953
    Rep Power
    383
    group by x.name...

    or just

    group by name...

    Comments on this post

    • moussa854 agrees
  16. #9
  17. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Posts
    12
    Rep Power
    0
    Originally Posted by cafelatte
    group by x.name...

    or just

    group by name...
    When I removed a.name and b.name after "WHERE"
    Code:
    WHERE tbl_name.PLAN != "" AND tbl_name.PLAN != "HOME"   ORDER BY tbl_name.NAME DESC  ) AS x  GROUP BY tbl_name.NAME, tbl_name.PLAN
    it worked. I think i this is strange !!
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,953
    Rep Power
    383
    Nearly.
  20. #11
  21. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,795
    Rep Power
    4332
    Originally Posted by cafelatte
    Nearly.
    close but no seegar
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo