how do i use data from one database, to....

[MSUK1]

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?

[Exo]

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

[Muddy_Funster]

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.

[zenlord]

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.

[MSUK1]

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

 

 

 

[MSUK1]

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

[zenlord]

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.

[MSUK1]

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?