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 8 of 8

Thread: Mysql JOIN

  1. #1
    New to the CF scene
    Join Date
    Feb 2009
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Mysql JOIN

    I'm trying to use join two queries into a single query using JOIN. However... doesn't seem to be working.

    PHP Code:
    $getData = @mysql_query("SELECT `a`.`user`,`a`.`data`,`a`.`cost`,`b`.`name` FROM `table_a` `a` JOIN `table_b` `b` ON `a`.`user`=`b`.`user`"); 
    I'm unsure how to display my tables, so I resorted to just creating two simple tables on my own website. If you do not feel it's safe to start randomly clicking links, below is a copy of the HTML code that you can post somewhere so you don't have to click the link if you feel unsafe about it.

    http://www.intotheunknownls.com/tables.html

    Code:
    <TABLE BORDER=1><TR><TD COLSPAN=3>Table A</TD></TR>
    <TR><TD>User</TD><TD>Data</TD><TD>Cost</TD></TR>
    <TR><TD>1</TD><TD>Data1</TD><TD>Cost1</TD></TR>
    <TR><TD>2</TD><TD>Data2</TD><TD>Cost2</TD></TR></TABLE>
    
    
    <TABLE BORDER=1><TR><TD COLSPAN=2>Table B</TD></TR>
    <TR><TD>User</TD><TD>Name</TD></TR>
    <TR><TD>1</TD><TD>Name1</TD></TR>
    <TR><TD>2</TD><TD>Name2</TD></TR></TABLE>
    I think I'm going about joining in all the wrong ways but.. bah. I just need to be able to use the "User" column from Table A to determine which rows to get in Table B. "User" will always be the same in both tables.
    Last edited by Luignata; 03-03-2009 at 04:06 AM. Reason: Small errors

  • #2
    Regular Coder ninnypants's Avatar
    Join Date
    Apr 2008
    Location
    Utah
    Posts
    504
    Thanks
    10
    Thanked 47 Times in 47 Posts
    Try this:
    PHP Code:
    "SELECT a.user, a.data, a.cost, b.name FROM table_a as a, table_b as b where a.user = b.user" 
    It's simpler than an actual join and probably works better for what you're doing here. also I don't think you need the ` around the shorthand table then again around the column I believe it should go around both. You actually don't even really need them.

  • #3
    New to the CF scene
    Join Date
    Feb 2009
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hmmm, that didn't seem to work.

    PHP Code:
    SELECT `a`.`user`,`a`.`data`,`a`.`cost`,`b`.`nameFROM `table_a` AS `a`, `table_b` AS `bWHERE `a`.`user`='1'); 
    Didn't work. Echoing mysql_error() resulted in nothing. So I tried:

    PHP Code:
    SELECT `a`.`user`,`a`.`data`,`a`.`cost`,`b`.`nameFROM `table_a` AS `a`, `table_b` AS `bON `a`.`user`=`b`.`userWHERE `a`.`user`='1' 
    But that gave me an error in my Syntax when echoing mysql_error().


  • #4
    Regular Coder ninnypants's Avatar
    Join Date
    Apr 2008
    Location
    Utah
    Posts
    504
    Thanks
    10
    Thanked 47 Times in 47 Posts
    Sorry I forgot to tell you that you need to specify what b.user needs to be equal to like this:
    PHP Code:
    "SELECT a.user, a.data, a.cost, b.name FROM table_a as a, table_b as b where a.user = 1 AND b.user = a.user" 
    This will select the data from tables a and b where a.user is equal to one and there is a b.user equal to the a.user

  • #5
    Master Coder
    Join Date
    Apr 2003
    Location
    in my house
    Posts
    5,211
    Thanks
    39
    Thanked 201 Times in 197 Posts
    or better:

    Code:
    SELECT a.user
         , a.data
         , a.cost 
         , b.name
        FROM table_a a
        INNER
        join table_b b
        on a.user = b.user 
        and a.user = ?
    ? is the variable for the user whose data you want. leave that last line out if you want to get all users data, where they are in both tables.


    bazz
    "The day you stop learning is the day you become obsolete"! - my late Dad.

    Why do some people say "I don't know for sure"? If they don't know for sure then, they don't know!
    Useful MySQL resource
    Useful MySQL link

  • #6
    Master Coder
    Join Date
    Dec 2007
    Posts
    6,682
    Thanks
    436
    Thanked 890 Times in 879 Posts
    Quote Originally Posted by bazz View Post
    or better:

    Code:
    SELECT a.user
         , a.data
         , a.cost 
         , b.name
        FROM table_a a
        INNER
        join table_b b
        on a.user = b.user 
        and a.user = ?
    ? is the variable for the user whose data you want. leave that last line out if you want to get all users data, where they are in both tables.

    bazz
    php don't have a bind like perl for variables so op must use a php variable in query instead of question mark ?.

    best regards

  • #7
    Master Coder
    Join Date
    Apr 2003
    Location
    in my house
    Posts
    5,211
    Thanks
    39
    Thanked 201 Times in 197 Posts
    yeh sorry for being confusing; I knew that.

    I was just trying to show the query and I don't know the variable he/she would be using. ? was just a substitute for that and was not meant as a placeholder. Thanks for pointing it out oesxyl.

    bazz
    "The day you stop learning is the day you become obsolete"! - my late Dad.

    Why do some people say "I don't know for sure"? If they don't know for sure then, they don't know!
    Useful MySQL resource
    Useful MySQL link

  • #8
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    MySQLi will allow you to bind you're parameters to mysqlstmt objects, but mysql will not.
    PHP Code:
    $stmt $mysqli->prepare("SELECT a.user, a.data, a.cost, b.name
                FROM table_a a
                LEFT JOIN table_b b ON (a.user = b.user)
                WHERE a.user = ?"
    );
    $stmt->bind_param('i'$userid);
    $stmt->execute(); 
    The huge advantage of course is that we don't need to formulate the proper quotations and we can start batch inserts without rewriting the actual query.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 
    Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)


  •  

    Posting Permissions

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