MSUK1 Posted February 10, 2011 Share Posted February 10, 2011 Ok so i have a Client System i built where a staff member register a client, and it stores them in a table "user" i now want to add a table "event" and what i want to do is, use data from "user" to store into "event" and then when a client logins in, pull the "user" information and the "event" information from the username... so for example... admin registers client... stores username password email [just as an example] then admin wants to add an event for that user so the admin clicks "add event" a drop down menu pulls ALL the usersnames from "user" (i know how to do that) enters the date of event and time and occasion.. then that is stored in "event" now this is the bit im not sure of.. when the user loggs in. i want to have an area where it will list events in date order for the "username" FROM the "user" table ensuring that you cant see other users dates? Quote Link to comment Share on other sites More sharing options...
Exo Posted February 10, 2011 Share Posted February 10, 2011 Hi, The SQL used would depend on what information you have about the logged in user and what piece of information about the user you stored in the events table. If it were me I would have something like this: User Table - User ID - User name - Password - Email - Etc. Events Table - Event ID - Date - Time - Description - User ID - Etc. sql = select events.* from events, users where events.user_id = users.user_id and users.username = $username However, if you are storing the username against the event you only need to select from the events table. I hope that I have explained this well... Regards Matt Quote Link to comment Share on other sites More sharing options...
Muddy_Funster Posted February 10, 2011 Share Posted February 10, 2011 could you post up your table structures so we have something to work with, oh and please don't use SELECT *, it's really not the best thing to do. Quote Link to comment Share on other sites More sharing options...
zenlord Posted February 10, 2011 Share Posted February 10, 2011 You know how to get all the user names from the table 'users', but you don't know how to get all the event names from the table 'events'? I don't think you have thought it through. Just make a SQL-query SELECT <necessary column names> FROM events WHERE user = $user_id ORDER BY date ASC The $user_id could be put in a session whenever a user logs in on the site, so that you have quick access to the above information. Quote Link to comment Share on other sites More sharing options...
MSUK1 Posted February 10, 2011 Author Share Posted February 10, 2011 thanks guys ok so here we go: we have the DB: server_largemus in that i have active_guests active_users banned_users client_details contactus[/quote client_details holds the information on clients... like so: userid | company | firstname | lastname | email | tel | address |websites | username | password | clientid | userlevel | timestamp | ip userid is set each time the usre loggs in its more like a sessionid the clientid is the unique number for each client ---------------- now witht he events table, i havn't created it yet as i want start it correct.. but the information i want to take is, event name, time, date, occasion, location... thanks for helping out Quote Link to comment Share on other sites More sharing options...
MSUK1 Posted February 10, 2011 Author Share Posted February 10, 2011 You know how to get all the user names from the table 'users', but you don't know how to get all the event names from the table 'events'? I don't think you have thought it through. Just make a SQL-query SELECT <necessary column names> FROM events WHERE user = $user_id ORDER BY date ASC The $user_id could be put in a session whenever a user logs in on the site, so that you have quick access to the above information. no i think you have mis-understood, yes i know how to get the username, and i would theirfor know how to get the event but storing the event, and then retreiving the event information from the username is what i am confused at? if i logg in as "matt" user id "1" i shouldnt be able to see user id "2"'s events sorry if thats confusing Quote Link to comment Share on other sites More sharing options...
zenlord Posted February 10, 2011 Share Posted February 10, 2011 if i logg in as "matt" user id "1" i shouldnt be able to see user id "2"'s events sorry if thats confusing Not confusing at all. Look at the part 'WHERE user = $user_id' in the query I supplied. That is the part that will only return events that are coupled to user with the specified $user_id. If that wasn't clear yet: you should add a column named 'user' to your table 'events' or a column named 'event' to your 'users'-table (one-to-many-relationship), or, if you need to be able to couple more than 1 user to more than 1 event: you should add a table with 2 columns: 1 for 'event_id' and one for 'user_id' in which you can couple more than 1 user to more than 1 event (many-to-many-relationship) If the latter is the solution to your problem, then you weren't really clear on the problem... Zl. Quote Link to comment Share on other sites More sharing options...
MSUK1 Posted February 10, 2011 Author Share Posted February 10, 2011 ok great, and if i wanted to get the data in one query and store it in seperate variables whats the best way to do this? Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.