MySQL database relation question

[Guest]

I'm setting up a site where registered users can catalog their Japanese Vinyl toys (i.e. vinyl kaiju). I have 2 tables:
collection
members

members has an ID that is specific to each member (naturally). within collection I have kaijuID and userID.

What I need to figure out is the best way to get the ID from the member table to populate within the collection table, thus allowing each registered user to see just their collection.

I can wrap my head around displaying the collection based on the user ID -- I just can't figure out where to add the user ID information into the collection database.

The site structure has:

login page
member home page
add kaiju
view kaiju
update kaiju
search kaiju

the add / view / update / search pages will all utilize the ID's. Not a big deal since I can have the user ID as a base for the results on these pages. it's the adding a new kaiju part that I can't figure out. Where would this form associate the logged in user so that it silently adds in their ID along with the toy info? A hidden field would be a natural assumption -- would that be part of the submit action?

So basically here's the breakdown:

User A has an ID of 001

User A wants to catalog 2 kaiju: Godzilla and Gamera

When User A submits these two each will need to have the 001 user ID added to the collection table column labeled as userID (which is the same as the column in the member table).

Which will allow the viewed collection to specifically show just member 001's collection.

phew. Any suggestions would be appreciated. I looked through the CS3 documentation and got this far but didn't see anything for relational databases.

Thanks!

[Guest · Accepted solution]

<?php session_start() ?> <---- This goes at the top of all of the pages in code view.

From there, when you create the recordset, use the filter option, for example:

filter: user_name =
Session Variable
user_name

The different session variables that are available to use in this is in the bindings tab, under Sessions.

In each table, you will need to have a field named "user_name". Basically, think of it as a link between the 2.

[Guest]

tried doing that and it wasn't working. When I had added the binding the vinyl I had added in as a test wouldn't display.

What would be ideal is the following:

all new users are assigned a userID in the MEMBERS table. this is an auto-incrementing (primary key) value that is unique to each user.

in the COLLECTIONS table there's a column called userID. when a user is logged in, then any new addition to the COLLECTIONS table will add in their userID on the backend so that each user's entries are tied in to their userID.

I first tried adding in the session_start code on all pages and then the filter to the recordset. rather than using filter: user_name I went with filter: userID. That didn't do anything but killed the display of any new additions to the view page.

I then tried setting up a binding to a hidden field in the add form:
<input name="userID" type="hidden" value="<?php echo $_SESSION['userID']; ?>" />

which might have been successful, although I got a message saying that userID cannot be null, so when I switched it to NULL the entry didn't register anything in the COLLECTION --> userID column but a NULL.

Would part of the problem be that userID in the MEMBERS table is the key? I already have a column in MEMBERS that is usrname -- which would be easy to change in COLLECTIONS (from userID to usrname) although I wasn't sure if that would do anything either way.

Also, since the session isn't labeled, such as
<?php echo $_SESSION['userID']; ?>

then how, based on your helpful suggestion, will the pages know the specific session ID?

so close....

[Guest]

Ok, after reading a bit more into the problem (needing sleep sucks :p),

If indeed the problem is getting the correct userID into the added item, and assuming the recordset name is rsKaiju, I would use a dynamic hidden value in association with filtering the records based on the $_SESSION['userID']

<input name="userID" type="hidden" id="userID" value="<?php echo $row_rsKaiju['userID']; ?>" />

As far as the sessions between the different pages, as long as you add the <?php session_start(); ?> to the top of the code of every page, when you create the recordsets using the userID filter, it should automatically put in:

$colname_rsUsers = "-1";
if (isset($_SESSION['UserID'])) {
$colname_rsUsers = (get_magic_quotes_gpc()) ? $_SESSION['userID'] : addslashes($_SESSION['userID']);
}

along with the mysql commands and the GetSQLValueString conditional.

At least, this is how its working for me.