Hi, I need some help if possible.

I have a mysql table called 'users' which holds user details such as (Name, username, password, id etc). The id value is auto-imcrementing. What I am trying to do is create a user login which will allow users access to the records in another database.

I have used the 'User login' server behaviour which works ok and takes you to a records display page. What I'm having trouble with it also taking the 'id' value with it so it displays the records of that particular person. Could someoone please help and advise me. I'm very new to php and struggling a bit.

Is there an extension or some other means of doing this.

Thanks

Simon

Hi,

Correct me if I am wrong . . .

user details are in a table, and you want to access another table within the same database.

If that is the case then you could use the id from the first table as a foreign key in the second table, then you can do a JOIN in sql which links the two tables together, it also means you can have more than one record in the second table belonging to the same user.

A good example might be people and cars, one person can own zero, one or more cars. A car can only be owned by one person. Then you can have a table of owners that has a key, say owner_id, then you put owner_id into the table of cars against each car that an owner owns. A join pulls the data out of both tables.

Thats basically it. I have all the details in a tables called users. Then a central tables called records. What I want to do is when the user has logged in it displays their records in a table with a repeat region. I have tried using a session variable called MM_username and filter the recordset on the session variable but I get nothing displaying. I even added a test entry in the records table and added an extra field called username containing 'simon' but using the session variable from my username login I could still get nothing to display. Very confusing.

Please help, as it's one big head ache.

Thanks

Simon

I hope this will not confuse further but here goes . . . you will have to have it in SQL for reasons I'll explain later

this is an example from something I am working on at the moment, using time zones for different users, I want to have all the time information, that would be equivalent to your records, your student would equate to my manager.

My managers table: - nothing special about this
CREATE TABLE `mgrs` (
`mgrs_firstName` varchar(14) NOT NULL default '',
`mgrs_lastName` varchar(16) NOT NULL default '',
`mgrs_nickname` varchar(12) NOT NULL default '',
`mgrs_address_1` varchar(36) NOT NULL default '',
`mgrs_address_2` varchar(36) NOT NULL default '',
`mgrs_town` varchar(30) NOT NULL default '',
`mgrs_county` varchar(24) NOT NULL default '',
`mgrs_postcode` varchar(8) NOT NULL default '',
`mgrs_CountRy` varchar(14) NOT NULL default '',
`mgrs_alt_email` varchar(32) NOT NULL default '',
`mgrs_tel` varchar(20) NOT NULL default '',
`mgrs_userid` varchar(30) NOT NULL default '',
`mgrs_last_good_login` datetime NOT NULL default '2007-01-01 00:00:00',
`mgrs_tzkey` tinyint(3) NOT NULL default '1',
PRIMARY KEY (`mgrs_userid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='Manager contact details';

the item of interest if mgrs_tzkey this is a 'foreign key' see next table

CREATE TABLE `tz` (
`tz_id` tinyint(2) NOT NULL auto_increment,
`abbreviation` varchar(4) NOT NULL default '',
`zone` varchar(32) NOT NULL default '',
`area` varchar(24) NOT NULL default '',
`offset` float NOT NULL default '0',
PRIMARY KEY (`tz_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='time zone information';

tz holds an id for the timezone, abbrevation - eg UTC, GMT, MDT, zone is the full name, Greenwich Mean Time, Mountain Daylight Time; area says something like Europe, Canada, N America; offset is the number of hours + or - from UTC.

I only want to hold this data once, so it's in a table of its own.

Now, let's say manager admin is in a time zone that is on mountain daylight time, which is where I just happen to be, here's the row for that entry from the tz table:

23 MDT Mountain Daylight Time North America -6

the id is 23, which is the important item, that 23 goes into the field mgrs_tzkey in table mgrs

Now comes the bit where you make mysql do the work for you. When you want the time offest for the manager whose user id is admin or whatever you make the sql query the first table, extract that mgrs_tzkey, then go to the second table, look up the row whose id is whatever number was in mgrs_tz_key and return the offset to you.

This is what the query looks like . . . .

SELECT offset FROM mgrs LEFT JOIN tz ON mgrs.mgrs_tzkey = tz.tz_id WHERE mgrs_userid = 'admin'

for this example the returned value is -6 as expected. You would have different table names and fields. Your repeating region would show all the records that were returned by the select with the join in it.

You can get the full syntax for the join from the mysql documentation, there is a shortcut form where you don't have to be quite so explicit but I always like to have it written out in full because at some time in the future I will have to come back and remember how this things worked.

Getting those foreign keys into the first table can be a bit tricky, you have to build something like a select dropdown box that picks up the information from the second table and puts the id into the value part of the list. I think there is an example in the DW tutorial.

I am not experienced in doing these joins in DW, I am fairly sure that you have to type the sql into the advanced query box, that's why you get sql from me, <img src=../images/dmxzone/forum/icon_smile.gif border=0 align=middle>.

I said I would explain why, DW tends to pass things using GET parameters - attached to the end of the url, that works fine, but it exposes that information to the whole world. So, someone could change (even manually by typing the url with parameters) and get a different record from the one that was intended. I pass the userid via the session variables and then pick up the data and keep it in an object - this is a massive amount of hand coding but I think it's worth it.

Hope that helps - another thing to check out is one to many relationships.

Thanks, that great and solved my problem.

Simon