MySQL Query Structure

[paseo]

Hi,

To give a brief background, I have a work form which will display a list of available times from a MySQL DB.

What i want is if someone submits the query and is displayed the list of times available, and they select a time, I want that time to NOT be displayed again if the same user submits a query again but i DO want that same time to be abailable to everyone else who queries.

I hope im being clear...;) Is there a way to append lets say a username to a field in the times table when the user selects a time and have the query strucuted in a way to select * from the times table in which username is NOT the username who is running the query the next time they come around? I'm not even sure if that is the best way to approach this but this is the only idea that comes to mind and the problem im running into is that data cannot be appended to a field in the table but rather replaces whats already there...

I hope im not being to difficult in my wording because ive read it over and it may sound confusing:) All i want, again, is for information to be displayed once, and when it is selected, to not be displayed again but ONLY for that user...

Any help / ideas is GREATLY appreciated!

[cameraman]

I would make another table with the person's identifier and the time reserved. Then when you're displaying available times to that particular user, you can suppress ones in the table, either with one query that looks at both tables or if you prefer simpler queries, get the available times out with one query into an array, and remove them from the array with a second query (not magical - you'd do a while and look for each reserved time in the array and remove it).

[paseo]

I actually did create another table and what i did was post a copy of the entire row (from an array as in INSERT operation) into this new table. The part that i lack is how to structure a single query to perform against multiple tables...IE, SELECT * FROM table1, table2 where (QUERY) WHILE...and this is where im having trouble...WHILE ? Its supposed to be something to compare if the two tables have the same values.

[cameraman]

You need to have a unique value that's common to both tables, doesn't have to be the same field name - like one could be user_id and one could be id. It doesn't have to be numeric.
table1
user_id, name, address, city

table2
user_id, reservation

SELECT name, address, reservation FROM table1, table2 WHERE name='John Smith' AND table1.user_id=table2.user_id

There's examples in the mysql manual and probably the forum's library.

[paseo]

table1.user_id=table2.user_id

Thats what i needed! Thanks Camerman!

[paseo]

Hey, back again :). The table1.field = table2.field works great except for the fact that it will not distinguish based upon a user. When i mapped it all out logically, it doesnt fit our requirement...When a user comes to the query page to find a timeslot, they run the query and are displayed a list of times. When they select a time, this time needs to be available for everybody else when they search but NOT for the user who already selected it.

Ill give an example, UserA comes in and selects 12:00PM. When UserA comes again and runs the query, 12:00 PM should NOT be an available option but 12:00PM SHOULD be available for any other user (UserB, UserC, etc).

The way i have it setup is for when UserA comes and reserves 12:00PM, this information is copied into another table (reserved). The information copied is the ID of the time in the (times) table, the time from the (times) table, and the username from the session variable. Logically, i am up against a wall to determine how to filter the query results based upon username if no user information is contained within the (times) table. The username who selected the id is ONLY available in the (reserved) table. The three fields in my (reserved) table are (id, times_id, time, username) where id is unique to the reserved table, times_id is the id of the time in the times table, time is the time selected, and username is the person who selected the time. Any ideas on how to logically structure this?

Is it possible to something like Select * from times, reserved where reserved.userName NOT EQ session.userName?
THANKS!

[cameraman]

My brain's not firing on all cylinders quite yet, but try playing with this - it's from the mysql manual:
SELECT * FROM t1 WHERE NOT EXISTS (SELECT id FROM t2 WHERE t1.id=t2.id)

the manual says this is an equivalent statement:
SELECT table1.* FROM table1 LEFT JOIN table2 ON table1.id=table2.id
WHERE table2.id IS NULL

but the first form is easier for me to understand; I've done that one before, but haven't used the second.

[paseo]

But i am still puzzled on how to base the query off of jhaving the username only in table 2....

SELECT * FROM times WHERE NOT EXISTS (SELECT times_id FROM reserved WHERE session.username not eq reserved.username)

Something like that?

I am selecting everything from the times table EXCEPT for anything in the reserved table with username equal to the session.username? ...how does it know how to compare the id's between? Im soo confused...;) The only shared value between the two tables is the ID (in times table) and TIMES_ID (in reserved table) fields...these are both identical in value...nothing else can really be used...

[paseo]

I think i almost got it but my syntax is incorrect (as usual..;)...this is what I have that should make sense...

SELECT * FROM times WHERE NOT EXISTS (SELECT * FROM reserved WHERE times.id = times_id AND username = session.username

But even with this way, how does the query know which rows not to display (which id's to exclued)..Does the id field get brought back from times_id?

Once again, times.id refers to the id field in table times. times_id refers to the times_id field in the reserved table which corresponds to the id field in the times table (same values...). username refers to the username field in the reserved table.

[cameraman]

You can get more than 2 tables involved, but how about:
SELECT * FROM times WHERE NOT EXISTS (SELECT * FROM reserved WHERE times.id = times_id AND username ='John Smith')
so you're checking it against a specific user name that is the person currently executing the query.
'WHERE NOT EXISTS' is telling it which ones to not display - if there's a record in reserved that matches the times.id and username, it (the record in times) won't be displayed because the WHERE is false.