PHP/MySQL help with JOIN

Discussion in 'Web Design and Development' started by Dimwhit, Feb 12, 2009.

  1. Dimwhit macrumors 68000

    Dimwhit

    Joined:
    Apr 10, 2007
    #1
    I've got a site that I need to expand. I've got a table of users and I need to add other tables (a timesheet, registrations, etc.). So I'm going to need to learn how to use JOIN to work with the multiple tables. Which I've never done before.

    What I'm looking for is suggestions on a good tutorial for learning this. I've done some google searches but haven't come up with one that's clicking with me. I do best with good example code with explanation following.

    Anyone have a suggestion on a good tutorial?
     
  2. Aea macrumors 6502a

    Aea

    Joined:
    May 23, 2007
    Location:
    Denver, Colorado
    #2
    LEFT JOIN table.column ON table1.column
    INNER JOIN table.column ON table1.column or WHERE table.column = table1.column

    I've got quite a bit of experience with writing SQL, but JOINs are genuinely simple.

    Edit: Post your schema & tables / columns you want to join if you're having issues.

    Edit2: Rights are more conceptually difficult but I doubt you need them here :)
     
  3. NoNameBrand macrumors 6502

    Joined:
    Nov 17, 2005
    Location:
    Halifax, Canada
    #3
    MySQL also lets you use implicit joins:


    SELECT tableA.column1, tableB.column3, tableA.column3 FROM tableA, tableB, tableC WHERE tableC.column1 = tableA.column2 AND tableC.column2 = tableB = tableB.column2 AND tableC.column3 LIKE 'foo%' ORDER BY tableA.column1;


    That selects data from tables A & B based on data in table C. You'd want indices on the joining columns in A & B, and the search query in C.
     
  4. davethewave macrumors member

    Joined:
    Jul 30, 2007
    Location:
    Trieste, Italy
    #4
    this is a working but bad practice.
    take a look on a free sql course, there are plenty on the web.
    bye DV
     
  5. NoNameBrand macrumors 6502

    Joined:
    Nov 17, 2005
    Location:
    Halifax, Canada
    #6
  6. Dimwhit thread starter macrumors 68000

    Dimwhit

    Joined:
    Apr 10, 2007
    #7
    Thanks all! I'm looking to dive into this first part of next week. I'll check out some links and post what I'm trying to do if I can't figure it out. I can tell that it will be fairly easy once I grasp the concept. I just need to get over that hump. :)
     
  7. SrWebDeveloper macrumors 68000

    SrWebDeveloper

    Joined:
    Dec 7, 2007
    Location:
    Alexandria, VA, USA
    #8
    How about posting the "best practice" version of that exact same query in post #3 for all reading this to benefit? Examples really help in addition to the links covering concepts and syntax.

    -jim
     
  8. SrWebDeveloper macrumors 68000

    SrWebDeveloper

    Joined:
    Dec 7, 2007
    Location:
    Alexandria, VA, USA
    #9
    I terms of concept for the database format being discussed here:

    • Use the JOIN keyword to link (ie. "join") primary keys and foreign keys.
    • Use the WHERE clause to limit your result set to only the records you are interested in.
    This came from one of the excellent links provided by NoNameBrand, it caught my eye and deserves prominence in this thread. That sums it up nicely as to concept, for me, short and simple.

    -jim
     
  9. Dimwhit thread starter macrumors 68000

    Dimwhit

    Joined:
    Apr 10, 2007
    #10
    Since you offered. :)

    OK, as I expected, I'm still a little clueless. Here's what I have:

    Table 1: users
    fields: username, firstname, lastname (plus some others I don't need here)

    Table 2: timesheet
    fields: username, period, reg, sick, vac, hol

    I figured out, when submitting a new timesheet, how to put the username from the 'users' table into the 'timesheet' record, along with the info submitted in the timesheet. What I need to do is pull up a user's timesheet. They are logged in, so I can fetch the current username to match to both tables (a simple $session->username does the trick in echo statements). I'm just clueless on the query. I read through several tutorials and hove come up with a number of different ways to word a query, include this one that doesn't work:

    SELECT users.username, timesheet.username FROM users,timesheet WHERE users.username=timesheet.username

    First, does my query need to contain all the fields I want to use? I'll be 'printing' all the fields from the 'timesheet' table, as well as the username/firstname/lastname from the 'users' table.

    Would something like this do the trick (I'm away from my work computer until tomorrow):

    SELECT * FROM users,timesheets WHERE users.username,timesheet,username=$session->username (or however it is I would pull the current username from the session--is there a best way to do that?).
     
  10. trule macrumors 6502

    Joined:
    Mar 16, 2007
    #11
    SELECT *
    FROM users
    INNER JOIN timesheets ON username
    WHERE username LIKE "Dim%"

    or similar

    Its also a good idea for each table to have an ID column with an AutoIncrement. Just a tip, it comes in handy later on.
     
  11. Dimwhit thread starter macrumors 68000

    Dimwhit

    Joined:
    Apr 10, 2007
    #12
    The timesheets table does have an ID column. The user table doesn't.

    So with that JOIN query, I should be able to pull from both table like normal?
     
  12. trule macrumors 6502

    Joined:
    Mar 16, 2007
    #13
    Yes.
     
  13. Dimwhit thread starter macrumors 68000

    Dimwhit

    Joined:
    Apr 10, 2007
    #14
    Something's not working right. I echoed the query and result to see what I'm getting. The query is coming back:

    SELECT * FROM users INNER JOIN timesheet ON 'username' WHERE 'username' = 'admin'

    which looks right. 'admin' is the username I'm looking for. But I'm get zero results, when I should be 4 results (there are 4 rows in the timesheet table with 'admin' as the username that match the one now in 'users' with the same username).

    I'm going to try a couple different things, but am I missing something obvious?
     
  14. trule macrumors 6502

    Joined:
    Mar 16, 2007
    #15
    Start with:

    SELECT *
    FROM users
    WHERE username = 'admin'

    and once that's working add the JOIN. And RTM for your database...you will find the answer pretty fast.
     
  15. Dimwhit thread starter macrumors 68000

    Dimwhit

    Joined:
    Apr 10, 2007
    #16
    Not sure what RTM means. Could you clarify that?

    So I did this: SELECT * FROM users WHERE username = '$session->username

    and got a result of 1, which is right.

    So I did this: SELECT * FROM timesheet WHERE username = '$session->username

    and got a result of 4, which is right.

    So I tried this: SELECT * FROM users INNER JOIN timesheet ON 'username'

    and got a result of 0, which isn't right.

    Then I throw in the rest: SELECT * FROM users INNER JOIN timesheet ON username WHERE username = '$session->username

    and it doesn't work. I'm doing a basic row count on the result of the query using this: $num=mysql_numrows($result); but it's giving me an error that it's not a valid result resource.

    Thanks for the help. I know this will click soon and I'll be fine. Just need to get through this thick skull of mine.
     
  16. trule macrumors 6502

    Joined:
    Mar 16, 2007
    #17
    read the manual.

    Google on MySQL and JOIN, the manual is quite clear.
     
  17. plinden macrumors 68040

    plinden

    Joined:
    Apr 8, 2004
    #18
    It's a polite way of saying RTFM - http://www.gaarde.org/Acronyms/?lookup=RTFM

    You need to understand what ON is doing - think of the syntax of your clause:
    JOIN timesheet ON 'username'

    What do you think the word following ON is referring to? What do things inside quotes in SQL mean? And so, how can you correct the syntax?
     
  18. SrWebDeveloper macrumors 68000

    SrWebDeveloper

    Joined:
    Dec 7, 2007
    Location:
    Alexandria, VA, USA
    #19
    Of course on here, RTFM means "read the friendly manual" ;)

    Moving on, this is for the OP...

    Ya know, my posts often go unnoticed here, but dangit, I am going to re-post it again because it's SO important, so once more:

    • Use the JOIN keyword to link (ie. "join") primary keys and foreign keys.
    • Use the WHERE clause to limit your result set to only the records you are interested in.
    What I'm getting at is one needs to know the basics of database structure, i.e. which fields are primary keys and have relationships with other fields in other tables. The "on" to this user might not make sense because nobody bothered to say it joins two tables with a common field of the same name which is either a primary or foreign key. And the OP needs to know what the heck a key is, both primary and foreign, and how relationships work and what cartesian products are. If one knows the basic rules, then as one learns the syntax requirements it'll all make sense. I have a feeling the OP is memorizing commands and trying them verbatim, not understanding the context.

    OP, get the syntax right using phpMyAdmin or some utility to test queries before you start complicating things and embedding PHP variables. Matter of fact, phpMyAdmin generates PHP code you can customize easily, once you get the basics down.

    This thread seems to be going in circles, so I felt the need to post this. This is intended only for the OP, not the gurus who posted already.

    -jim
     
  19. trule macrumors 6502

    Joined:
    Mar 16, 2007
    #20
    I read your posts :eek:

    You don't need primary/foreign keys to use JOIN however for performance reasons it does make sense to INDEX columns used in JOIN's and this can include primary keys which are essentially an Index (usually on the Id column, by convention).

    Foreign keys are used for maintaining data integrity when deleting/updating etc. At least in MySQL. But that's a more advanced topic.
     
  20. Dimwhit thread starter macrumors 68000

    Dimwhit

    Joined:
    Apr 10, 2007
    #21
    Now that I didn't know.

    I did see your post, and it makes sense. I understand (sort of) the concept behind it, it's the actual php syntax that I just can't get to work. And I'm having trouble grasping the logic behind the different JOINs (INNER, LEFT, etc.).

    But I'll check out phpMyAdmin. I didn't realize it can generate php code once the syntax is there. That's pretty cool.
     
  21. SrWebDeveloper macrumors 68000

    SrWebDeveloper

    Joined:
    Dec 7, 2007
    Location:
    Alexandria, VA, USA
    #22
    PhpMyAdmin won't write your entire script for you, mostly connection settings and it converts queries you run into basic PHP/MySQL statements. Clearly you still need to know both PHP and MySQL to customize the code it generates for your specific purposes. BTW, for your needs an inner join seems to be what you need.

    But if I was not clear enough, you must take the time on your own to learn about the differences in joins and the basic concepts of keys, etc. before you start PHP coding.

    Help on MySQL inner join and example PHP included (good link for you)
    Help on joins in general (left vs. right. vs. inner vs. outer, advanced)

    You clearly need to read the friendly manuals, the advice we're giving you here will make a whole lot more sense later after you follow at least the two links above. No pain, no gain!

    -jim
     
  22. SrWebDeveloper macrumors 68000

    SrWebDeveloper

    Joined:
    Dec 7, 2007
    Location:
    Alexandria, VA, USA
    #23
    Yes, yes, which is why I said "This came from one of the excellent links provided by NoNameBrand" - I was using it in context to this discussion where someone did mention keys. That's all. I appreciate you adding this, but the OP might be so dizzy they could seriously just blow up into a million pieces at this point, heh. :p

    -jim
     
  23. Dimwhit thread starter macrumors 68000

    Dimwhit

    Joined:
    Apr 10, 2007
    #24
    I agree on all counts. I've actually read the first link you gave a number of times. I think I may have finally figured it out. I was having issues with my WHERE clause to limit the results. Running queries in phpMyAdmin really helped quite a bit.

    So thanks for your help. I am going to spend more time running through the tutorials.
     
  24. SrWebDeveloper macrumors 68000

    SrWebDeveloper

    Joined:
    Dec 7, 2007
    Location:
    Alexandria, VA, USA

Share This Page