i have problem i am stuck on how to only allow a person to login and access only information in the data base that they are allowed. I have set up a login with there username, password, email and a post code. i want the person with that unique post code to be the only one to access the results for that postcode. This is using to tables, 1 for the login details and the other with the form data the user inputs. i realize i have to use joins this is no drama its just getting the selected info.
Any help would be great, i am a newbie at this. i did think of using sessions but unsure of them.

HI

are those postcodes really unique? <img src=../images/dmxzone/forum/icon_smile.gif border=0 align=middle> I ask because you sound like you want to restrict access to the data but where I live the postcode applies to the apartment block, which is 14 floors . . . so we would all be able to access everyones data ?

Would you consider generating a unique identifier, say something like the md5 digest of the current time and username concatonated, then storing that in your table for that user. Then when they login you can retrieve that and use it as a key on the second table.
Maybe that's over the top, you could just put the user's name in the second table.

There's a really good login/user management tutorial and example system here

evolt.org/node/60384

It's really worth working through how that works and understanding it before you design what you want your system to do.

Thank you for your reply, what im trying to achieve is that 1 person would be allocated access to the view all items listed under that postcode and any new listing with that particular post code would go into the person assigned admin area, when they are signed up they will get a user name, password, and post code allocated, if you get my idea, im a tad stumped and have tryed may ways i know and are not getting any where.

HI
thanks for the clarification, I hope I have understood - there are property listings each of which has a postcode and that is assigned to an administrator. When an administrator logs in they have access to all the properties within that postcode.

When a new property is added its postcode is automatically assigned to the administrator for that postcode.

Can an administrator have access to more than one postcode?

Does the property owner get a different level of access to the administrator?

Sorry to be picky - I need to get the requirement clear <img src=../images/dmxzone/forum/icon_smile.gif border=0 align=middle>

Thanks for the quick reply. Yes the Administrator will be able to view all listings. the way i was thinking was the property owner would have there own level with there own postcode viewable and to them alone, so no other property owner can view there listings. i have set up two tables 1. users with a ID username password and postcode, 2. ID userID name postcode etc iwanted to join table 1 to 2 to organise the listings, but it wont work, do you havea ny idea?

I'll start by describing my system which is somewhat similar. I have properties and property managers. My login system allows logged-in users to have an access level in the range 1 to 9. When someone visits the site and logs in their access level is saved in a php session, then each page has a little test at the head that checks whether or not this person is a property manager, if not they are redirected to the login page. That's how access to the pages is controlled. Other users who are just searching for properties don't get an access level.

Now the database, there is a property manager table that has the unique id of the manager, plus all their details, like name, address, nickname, tel, fax etc.

Then the properties table, this has the unique id, name of property, address, postcode etc, plus a field that holds the name of the property manager.

Now, when a property manager logs in, using their login name and password they get a php session, their access level and other things go into that session. Then as they visit pages in the management area each page checks the session to see if they should have access and only shows them the relevant pages. I am the site admin, so I have access level 9, property managers have level 6.

Getting properties - so, my property manager wants to see all the properties that are under their management, I search the properties table for all the properties that contain that manager's name in the manager field.

SELECT p_name, p_id, p_town, p_postcode, p_description FROM properties WHERE p_userid = 'fred' ORDER BY p_name ASC

that gives a list of all the properties managed by this manager, so I show them

As each manger adds new properties their name goes into that p_userid field.

I think in your case you want managers to handle whole postcodes, so I would have another table, only two fields, manager id and postcode. Then you could access that and look which postcodes each manager could access. As a property was added you would look for its postcode in that table, pick up the manger id and put it into the appropriate field in the property record. This also lets you move postcodes between managers and add and delete them as necessary.

Hope that helps - check out many to many relationships too

Hi Alan, Sorry but still having problems,
Here is the SQL
SELECT `user`.username, `user`.postcode, `form`.ID, `form`.userID, `form`.added_by, `form`.email, `form`.phone, `form`.mobile, `form`.address, `form`.suburb, `form`.postcode, `form`.`state`
FROM `user`, `form`
WHERE `form`.`ID` = `user`.username

Where am i going wrong.

Wat are you using as, param to retrieve the user details? Are you using a session or passing data through url?

What you have to do is retrieve the matching data by joining the tables user inner join
<pre id=code><font face=courier size=2 id=code>
SELECT `user`.username, `user`.postcode, `form`.ID, `form`.userID, `form`.added_by, `form`.email, `form`.phone, `form`.mobile, `form`.address, `form`.suburb, `form`.postcode, `form`.`state`
FROM `user` inner join `form` on `user`.ID = `form`.ID
WHERE `form`.ID = $param
</font id=code></pre id=code>
$param will be the param you are using to identify the user that has just login.

I was planniing on using sessions, but unsure on how to set it up.. Also i sort of have it working

This shows the results but will not only show the results provided when the admin logs in to view there postcode provided

SELECT user.userID, user.username, user.level, form.ID, form.added_by, form.suburb, form.postcode, form.state, form.datetime
FROM user JOIN form
WHERE (user.level = form.postcode)
ORDER BY form.postcode DESC

i am using sessions to login, how do i add postcode?

Don't use postcode use ID instead this is unique. I really don't understand the concept of using a postcode to identify a user. Its risky because if someone changes address then u'll have his record again.

I have tried ID to idetify it but it only gets the records from the table users not users and form

So each user is allow to access only some or all the info in the database is that right? Can you send the table/s structure for all the table/s that u'r using please?

Edited by - roders22 on 14 Mar 2007 11:40:08

"SELECT user.userID, user.username, user.level, form.ID, form.added_by, form.suburb, form.postcode, form.state, form.datetime FROM user LEFT JOIN form ON (user.level = form.postcode) ORDER BY form.ID DESC"

The table structure not the query.

table user - ID, Name, Cmpany, Phone, username, password, email, postcode
table form - ID, added_by, email, phone, mobile, address, suburb, postcode, state, home_style, home_age, home_size_home, lot_size, construction_type, home_condition, bedroom, bathroom, datetime

SELECT `user`.username, `user`.postcode, `form`.ID, `form`.userID, `form`.added_by, `form`.email, `form`.phone, `form`.mobile, `form`.address, `form`.suburb, `form`.postcode, `form`.`state`
FROM `user` inner join `form` on `user`.postcode = `form`.postcode

Have applied that but it still shows all the results, not just the ones that are in the `user`.postcode, i need the one that has been put in that field by the admin to only show when someone logs in to check the results for there postcode

just add a where clause to it.
The query i gave you is just to join to table together for it to return matching postcode. Now you have to use the param that will identify the user who's logged in, like user id or something. The problem is am getting very confused with what you're looking to do and what data you're using to identify the user who's logged in.

Sorry about the confusion, ill try to explain.
i need the person that the admin puts into table users they will be assigned a postcode eg:0800 when they login they will see all results that have the postcode of 0800 through the table form or if assigned 2 post codes eg 0800, 0801 that person will be the only person to view that postcode/s if you would like my email address to discuss of the forum its

Then set as session the user_id. Then on each page use this user_id to retrieve the postcode in the user table that matches with it and then use this postcode to list the postcode in the form table. Its simple.

i have set a session
session_start();
if(!isset($HTTP_SESSION_VARS['usersession'])){
header("Location: ../login.php";

session_destroy();

this grabs the username and pw and lets them in, sorry not good at sessions. if you can or have the time and can help out would be great

why do you destroy the session???? You only destroy a session when you won't use it anymore, i.e. Logout.
what does $HTTP_SESSION_VARS['usersession'] contains?? What data will it store?

here is the code i use

&lt;?php
// Buzz inet PHPLS01 - Login & Set Session
// Enable Session Support for page
session_start();
?&gt;&lt;?php require_once('Connections/conn_fra.php'); ?&gt;
&lt;?php
// Buzz inet PHPLS01 - Login & Set Session - Recordset
$myUsername_rsLogin = "0";
if (isset($HTTP_POST_VARS['username'])) {
$myUsername_rsLogin = (get_magic_quotes_gpc()) ? $HTTP_POST_VARS['username'] : addslashes($HTTP_POST_VARS['username']);
}
$myPassword_rsLogin = "0";
if (isset($HTTP_POST_VARS['password'])) {
$myPassword_rsLogin = (get_magic_quotes_gpc()) ? $HTTP_POST_VARS['password'] : addslashes($HTTP_POST_VARS['password']);
}
mysql_select_db($database_conn_fra, $conn_fra);
// Verify Login is correct
$query_rsLogin = sprintf("SELECT username, password FROM user WHERE username = '%s' AND password = '%s'", $myUsername_rsLogin,$myPassword_rsLogin);
$rsLogin = mysql_query($query_rsLogin, $conn_fra) or die(mysql_error());
$row_rsLogin = mysql_fetch_assoc($rsLogin);
$totalRows_rsLogin = mysql_num_rows($rsLogin);

mysql_select_db($database_conn_fra, $conn_fra);
$query_Recordset1 = "SELECT * FROM `user`";
$Recordset1 = mysql_query($query_Recordset1, $conn_fra) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
$totalRows_Recordset1 = mysql_num_rows($Recordset1);

// Buzz inet PHPLS01 - Login & Set Session - Main
if($HTTP_POST_VARS['action']=="login"{
if($totalRows_rsLogin==0){
$errorMessage = "Name or Password Incorrect Try Again.";
mysql_free_result($rsLogin);
} else {
mysql_free_result($rsLogin);
session_register("usersession";
$HTTP_SESSION_VARS['usersession'] = $HTTP_POST_VARS['username'];
header("Location: admin/default.php";
}
}

// FELIXONE - 2002 SB by Felice Di Stefano - www.felixone.it
session_start();
if (isset($HTTP_POST_VARS['postcode'])) {$postcode = $HTTP_POST_VARS['postcode'];
session_register("postcode";
}
?&gt;

If you want your script to work regardless of register_globals, you need to instead use the $_SESSION array as $_SESSION entries are automatically registered. If your script uses session_register(), it will not work in environments where the PHP directive register_globals is disabled.
www.php.net/session_register
I made some mods use this for session
<pre id=code><font face=courier size=2 id=code>
// Buzz inet PHPLS01 - Login & Set Session - Main
if($HTTP_POST_VARS['action']=="login"
{
if($totalRows_rsLogin==0){
$errorMessage = "Name or Password Incorrect Try Again.";
mysql_free_result($rsLogin);
} else {
mysql_free_result($rsLogin);
$HTTP_SESSION_VARS['usersession'] = $HTTP_POST_VARS['username'];
header("Location: admin/default.php";
}
}

/*WHAT IS THIS?? IF YOU KNOW WHAT THIS IS USED FOR THEN UNCOMMENT
// FELIXONE - 2002 SB by Felice Di Stefano - www.felixone.it
session_start();
if (isset($HTTP_POST_VARS['postcode'])) {
$postcode = $HTTP_POST_VARS['postcode'];
session_register("postcode";
}
*/
</font id=code></pre id=code>

Now your query can use this
<pre id=code><font face=courier size=2 id=code>
SELECT `user`.username, `user`.postcode, `form`.ID, `form`.userID,
`form`.added_by, `form`.email, `form`.phone, `form`.mobile, `form`.address, `form`.suburb, `form`.postcode, `form`.`state`
FROM `user` inner join `form` on `user`.postcode = `form`.postcode
`user`.username = $HTTP_SESSION_VARS['usersession']
</font id=code></pre id=code>

Sorry to be a pain but i added the code and got the following error


Parse error: parse error, expecting `T_STRING' or `T_VARIABLE' or `T_NUM_STRING' in /home/httpd/vhosts/freerealestateappraisals.com.au/httpdocs/admin/default.php on line 52

Here is line 52 -
$query_Recordset1 = " SELECT `user`.username, `user`.postcode, `form`.ID, `form`.userID, `form`.added_by, `form`.email, `form`.phone, `form`.mobile, `form`.address, `form`.suburb, `form`.postcode, `form`.`state` FROM `user` inner join `form` on `user`.postcode = `form`.postcode `user`.username = $HTTP_SESSION_VARS['usersession']";