New to PHP, confused with LEFT JOIN

[jayemsee283]

Hi all. I really love your site...and have learned a good bit from tutorials provided!

I am trying to write a program addition to an application we currently are using. What it needs to do is this in each row:

Display all users name
Show all users total points per category
Show all their total overall points



MySQL info:

The "points" table has a column (points) which is where each entry of points is stored..nothing major there. It also has a column (user_id) which corresponds with the "user" table to get the users (char_name). The "points" table also has and a (custom_event_id) which matches the (point_cat_description_id) of the "point_cat_description" table to get the (name).


I am having headaches over this because I know my answer is simple...I just can't get it right. The SQL query that I have is:

SELECT points, user_id, custom_event_id FROM points
LEFT JOIN user ON points.used_id=user.user_id
LEFT JOIN point_cat_description ON points.custom_event_id=point_cat_description.point _cat_description_id

I think the query is right...or close...I just don't know how to get the values that I need from joining the tables...

Another small problem I am having is deciding if I am going to use a for or do...while loop to display the data (leaning towards a for).

I REALLY appreciate ANY and ALL help, and thank you for helping in advance !

[jayemsee283]

Sorry to bump, but this is driving me a little crazy lol..

[Telos]

You get the values just like you have used the values in join, by prefixing them with the correct table name. You can also use shorter names which makes it a bit clearer like

select u.user_id from users u

(not sure if that's the format for mysql)

For looping through the resultset I would use while but I guess something else works just fine too.

[Tablet205]

This might help

SQL

Join to see stuff on php.

[tamlin]

Originally Posted by Telos

select u.user_id from users u

You don't need to use the table alias until after you declare it.

select u.user_id from users u

would give you the same result as

select user_id from users u

but after you've declared a table alias you must use that in the rest of the query. In other words

select user_id from users u
order by u.user_id

would work, but

select user_id from users u
order by users.user_id

would give you a syntax error. (1109 - Unknown table 'users' in order clause)

[tamlin]

In answer to your first question:

I just don't know how to get the values that I need from joining the tables

you'll need to use Aggregate Functions. Here's a couple of links to get you started:
Roland Bouman's blog: Powerful MySQL Aggregate Functions
MySQL Tutorial - Group By

In answer to your second question:

Another small problem I am having is deciding if I am going to use a for or do...while loop to display the data

it doesn't really matter. Both will give you the result you require and both are equally efficient. Some (very pedantic) programmers might argue that WHILE implies a value that may vary, but either will work just as well as the other. Just use whatever you feel most comfortable with.