[SOLVED] Data from table based on variable

[Ell20]

Hi,

 

Im making a website where users sign up to a club each user has a user_id and each club has a club_id. On the first page after logging in I want to be able to include a standard welcome message which will say something like "Welcome to $clubname".

 

Then once the admin signs in he then has the ability to edit the post and write some information on the club and save it.

 

However within the website there are lots of different clubs so I need the message that applies to a particular club to only be visible if the user is linked to the club.

 

My database has 3 tables, 1 for users, 1 for clubs and 1 for welcome.

 

Can anyone give me some help as to how to do this as I am still very new to php.

 

If you need any extra information then just ask.

 

Thanks for your help

 

Elliot

 

 

[hostfreak]

Create a field in the "users" table called "club_id". Then for whatever club the user is a member of, store the "id" from the "clubs" table in the newly created "club_id" field in the "users" table. Make sense?

 

Then you can just check to see what the specific "club_id" for a user matches to the "id" in the clubs table and display your message the same way (checking what "club_id" in "messages" matches the "id" in clubs..

 

So to give an example:

 

table: users

username | club_id | id

 

table: clubs

clubname : id

 

table: message

message | club_id | id

[Ell20]

I have already done the top bit that you mentioned.

 

Would it be possible for you to give me some idea as to how to do the second part?

 

Cheers

[hostfreak]

If your database is setup like that, then something like this should do the trick:

 

<?php

    $club_id = mysql_query("SELECT club_id FROM users WHERE username = '$username'")
        OR DIE(mysql_error());
    $row = mysql_fetch_assoc($user_id);
    $club_id = $row['id'];
    
    $clubname = mysql_query("SELECT clubname FROM clubs WHERE id = '$club_id'")
        OR DIE(mysql_error());
    $row = mysql_fetch_assoc($clubname);
    $clubname = $row['clubname'];
    
    $message = mysql_query("SELECT message FROM welcome WHERE club_id = '$club_id'")
        OR DIE(mysql_error());
    $row = mysql_fetch_assoc($message);
    $welcome = $row['message'];
    
    echo 'You are linked to ' . $clubname . '<br />' . $welcome . '<br />';

?>

 

Of course I am not sure how you have your system setup (sessions or what not). If so though and you have a username session variable, that will work.

[Ell20]

Thanks for the reply.

 

My session is actually setup through user_id which is in the users table.

 

Ive had ago at using your table but I think I may have made a mistake as it dosent work.

 

Tables:

 

club: club_id, clubn, user_id

users: username, user_id, club_id

welcome: welcome_id, club_id, welcome

 

Thanks

[hostfreak]

So you have a variable user_id, correct? Which is set upon login for each specific user? If so, just change the first sql statment to:

 

    $club_id = mysql_query("SELECT club_id FROM users WHERE user_id = '$user_id'")
        OR DIE(mysql_error());

 

Then change the rest of the naming around if needed in the other sql statements to match your table/field names.

[Ell20]

I have a variable called user_id but its not set upon login, its set upon register but it is linked to the session if that makes sense?

 

Here is the error:

An error occured in script c:\program files\easyphp1-8\www\html\main.php on line 25: Undefined variable: user_id

 

Cheers

[hostfreak]

Yeah I see. You will need something that is set upon login, to be able to pull specific data for a user. I would recommend setting the user_id upon login, since it will be unique. Unlike the username whereas many people could possibly have the same username (unless you don't allow that). If that is the case, the username or user_id would work fine.

[Ell20]

Erm ok now im confused!

 

I dont allow 2 people to have the same username and the user_id is always unique as it is set to auto_increment in the database so each user is given a different ID when they register.

 

At the moment if i typed something like:

 

echo " $_SESSION['first_name']" it comes up with the first name of the user that is logged in, can this not be used for doing this?

[Ell20]

Think I may have done it:

 

$club_id = mysql_query("SELECT club_id FROM users WHERE user_id = '{$_SESSION['user_id']}'")

[hostfreak]

Yeah, that is what I was saying. I just wasn't sure if you had it assigned to a variable or were just using the session superglobal array

[corbin]

You could (depending on the database schema) combine all of the queries into one using just the user id as a where clause:

 

SELECT w.message, c.clubname, u.club_id FROM users u LEFT JOIN clubs c ON c.id = u.club_id LEFT JOIN message m ON m.club_id = u.club_id WHERE u.club_id != ''