How to associate user_id field in one table with user name in another?

I have the following code which will display all transactions for a given user.  In the transactions table there's a users_id column which has got a bunch of numbers in it which relate to user names in the users table, but how do I write the code without having to manually look up the ??????? user_id in the users table?  Thanks.

$transactions_query = "SELECT * FROM transactions WHERE users_id = ?????? ";
LVL 1
LB1234Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ray PaseurCommented:
Since we do not have the CREATE TABLE statements, this is just a guess, but if the data base was professionally designed it's probably a good guess.

SELECT columns FROM users, transactions WHERE users.users_id = ???? AND transactions.users_id = ????

Don't use SELECT * -- just select the columns you want by name.
0
LB1234Author Commented:
But correct me i'm wrong, wouldn't I still have to know/lookup the user's ID number in the transactions table?
0
Ray PaseurCommented:
That would depend on the input being used to create the query.  Can you please post the script that you have now?  I'll try to see what we can do with it.
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Chris StanyonWebDevCommented:
You would need a JOIN. Something like this:

SELECT transactions.*, users.username FROM transactions LEFT JOIN users ON transactions.users_id = users.users_id WHERE transactions.users_id = xxx

Open in new window

As ray said, though, don't just use the * - be specfic about your columns
0
Ray PaseurCommented:
The SSCCE would show us the CREATE TABLE statements, some test data, and the script that receives the client input and queries the tables.  Then we could suggest things about how to handle the ID numbers throughout the process.  Getting the query right is certainly important, as is the overall design that would allow you to JOIN the tables and return meaningful information after the client submits a form.
0
LB1234Author Commented:
Transactions Tableusers table
What I'm trying to accomplish where the users.user_id = whatever, and login_user_name (which is really just $_POST["$login_user_name"] from my login form) are part of the same record show

"SELECT DATE, amount, vendor, description, TYPE , trip, last_updated, last_updated_by, done, notes\n" from the transactions table where transactions.users_id = users.users_id.

Argh, I hope this is clear.

Ray reading over the SSCCE to try to understand for future posts, to use that format if necessary.
0
LB1234Author Commented:
Basically when the user logs in, I need to show them only their transactions based on their login_user_name.
0
LB1234Author Commented:
I tried this but it didn't work.

$transactions_query = "SELECT DATE, amount, vendor, description, TYPE , trip, last_updated, last_updated_by, done, notes\n"
    . "FROM transactions, users\n"
    . "WHERE users.user_name = {$login_user_name} AND users.users_id = transactions.users_id";

Open in new window

0
Chris StanyonWebDevCommented:
You'll need something like this:

SELECT `date`, `amount`, `vendor`, `description`, `type` , `trip`, `last_updated`, `last_updated_by`, `done`, `notes`
FROM transactions t, users u
WHERE user_name = 'someUser' AND u.users_id = t.users_id

Open in new window

You'll notice I've backticked the field names. Using reserved words for field name can sometimes be problematic if you don't do this - you've used 2: date and type. I've also wrapped your username criteria in quotes as it's a string.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ray PaseurCommented:
There may be more to this question than at first meets the eye.  Please forgive me if I am overcomplicating it.  This part indicates that you would need some kind of client authentication.
... show them only their transactions based on their login_user_name.
This article may be helpful.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_2391-PHP-login-logout-and-easy-access-control.html
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.