PHP/MySQL help with JOIN

[Dimwhit]

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?

[Aea]

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 :)

[NoNameBrand]

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.

[davethewave]

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.

this is a working but bad practice.
take a look on a free sql course, there are plenty on the web.
bye DV

[angelwatt]

Here's a few tutorials to see if they help the ideas sink in. I have a MySQL cookbook from O'reilly (http://www.amazon.com/MySQL-Cookbook-Paul-DuBois/dp/0596001452), which is pretty good. Here's a link to it from Google books (http://books.google.com/books?id=aC3blgT3D3YC&dq=mysql+cookbook&printsec=frontcover&source=bn&hl=en&ei=5GuVSb69Cp6Dtwftl5CcCw&sa=X&oi=book_result&resnum=4&ct=result#PPP1,M1).

http://www.devshed.com/c/a/MySQL/MySQL-Table-Joins/
http://www.tizag.com/mysqlTutorial/mysqlleftjoin.php
http://php.about.com/od/learnmysql/ss/mysql_joins.htm
http://phpweby.com/tutorials/mysql/32

[NoNameBrand]

this is a working but bad practice.

Hmm after a quick Google, I see that you're right. I learned most of my MySQL in the 3.5x.y days and almost all the examples used that syntax (e.g., see angelwatt's links - they all use syntax like mine for basic joins).

Useful reads:
http://lists.mysql.com/mysql/201078
http://stackoverflow.com/questions/128965/sql-mysql-is-there-something-wrong-about-joins-without-the-join-keyword

[Dimwhit]

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. :)

[SrWebDeveloper]

this is a working but bad practice.
take a look on a free sql course, there are plenty on the web.
bye DV

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

[SrWebDeveloper]

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. :)

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

[Dimwhit]

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

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?).

[trule]

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?).

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.

[Dimwhit]

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.

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?

[trule]

So with that JOIN query, I should be able to pull from both table like normal?

Yes.

[Dimwhit]

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?

[trule]

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?

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.

[Dimwhit]

and once that's working add the JOIN. And RTM for your database...you will find the answer pretty fast.

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.

[trule]

Not sure what RTM means. Could you clarify that?


read the manual.

Google on MySQL and JOIN, the manual is quite clear.

[plinden]

Not sure what RTM means. Could you clarify that?

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


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

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

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?

[SrWebDeveloper]

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

[trule]

Use the JOIN keyword to link (ie. "join") primary keys and foreign keys.


I read your posts :o

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.

[Dimwhit]

Matter of fact, phpMyAdmin generates PHP code you can customize easily, once you get the basics down.

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.

[SrWebDeveloper]

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 (http://www.tizag.com/mysqlTutorial/mysqljoins.php) (good link for you)
Help on joins in general (http://www.wellho.net/mouth/158_MySQL-LEFT-JOIN-and-RIGHT-JOIN-INNER-JOIN-and-OUTER-JOIN.html) (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

[SrWebDeveloper]

I read your posts :o

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.

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

[Dimwhit]

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 (http://www.tizag.com/mysqlTutorial/mysqljoins.php) (good link for you)
Help on joins in general (http://www.wellho.net/mouth/158_MySQL-LEFT-JOIN-and-RIGHT-JOIN-INNER-JOIN-and-OUTER-JOIN.html) (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

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.

[SrWebDeveloper]

My pleasure, enjoy. :D

[wdonegan]

Might want to use a left join to ensure you get user info returned even if there is no timesheet info for the user.

<?php

$sqlquery =

'Select
users.*,
timesheet.*
From users
Left Join timesheet ON users.username = timesheet.username
Where users.username = ' . $_SESSION['username']

?>