Sidebar Sidebar
Become a MacRumors Supporter for $50/year with no ads, ability to filter front page stories, and private forums.

PHP/MySQL help with JOIN

Dimwhit

Dimwhit

macrumors 68020
Original poster
Apr 10, 2007
2,069
299
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?
 
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 :)
 
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.
 
NoNameBrand said:
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.
Click to expand...

this is a working but bad practice.
take a look on a free sql course, there are plenty on the web.
bye DV
 
As an Amazon Associate, MacRumors earns a commission from qualifying purchases made through links in this post.
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. :)
 
Dimwhit said:
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. :)
Click to expand...

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
 
Aea said:
Edit: Post your schema & tables / columns you want to join if you're having issues.
Click to expand...

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?).
 
Dimwhit said:
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?).
Click to expand...

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.
 
trule said:
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 am AutoIncrement. Just a tip, it comes in handy later on.
Click to expand...

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?
 
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?
 
Dimwhit said:
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?
Click to expand...

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.
 
trule said:
and once that's working add the JOIN. And RTM for your database...you will find the answer pretty fast.
Click to expand...

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.
 
Dimwhit said:
Not sure what RTM means. Could you clarify that?
Click to expand...

It's a polite way of saying RTFM - http://www.gaarde.org/Acronyms/?lookup=RTFM

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

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

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?
 
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
 
SrWebDeveloper said:
Use the JOIN keyword to link (ie. "join") primary keys and foreign keys.
Click to expand...

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.
 
SrWebDeveloper said:
Matter of fact, phpMyAdmin generates PHP code you can customize easily, once you get the basics down.
Click to expand...

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.
 
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
 
trule said:
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.
Click to expand...

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
 
SrWebDeveloper said:
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
Click to expand...

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.
 
You must log in or register to reply here.
Register on MacRumors! This sidebar will go away, and you'll see fewer ads.
Top Bottom
Back