restricting user to certain data in a database?

[sudduth66]

just wandering if this can be done and if so do you know were i can read about this or have any suggestion?

 

i have a database with all are clients data that we store in it. I can now let them log into a php search screen that they can pull data from and see what we have stored. I would like to restrict what info they can pull by the user name they log in with, so they are not pulling other client info when they do the search.  the user database and company database are two different ones.

 

they search by box number and i would like to restrict what it pulls up by company name depending on if the user logged in has rites to that company info.

 

so if jane doe logged in and she was a member of soso company when she did a box search she would only see what was from soso company instead of how it is now it pulls from soso, DD, and all the company's.

 

 

Thanks in advance

 

 

 

 

[JohnOP]

<?php
$sql = mysql_query("SELECT * FROM table_name WHERE username= 'jane' AND company = 'soso'");

[dougjohnson]

Nested select statements might work.  Depending on what data is stored in which database, you could select (connect to this database) from the user db, get jane doe's company, then select from the company db (connect to this database) and display all of her specific company data.  Again, I don't know the structure of your database's or what a "box number" is.

[Drummin]

Well then you should have a clientid in your table(s) and only allow access to when client id's match. Don't rely on user name alone when it comes to company records.  Have the clientid in the user table and where records are stored.

[madkow]

I imagine you have something like this:

 

CLIENT TABLE

ID  NAME

--  ------

1    soso

2    test

3    test2

 

USER TABLE

ID  NAME  CLIENT (if you don't have a client field add one)

--  ------    --------

1    user1  1

2    user2  2

3    user3  3

 

DATA table will be a table where your clients data is stored.  It should have a client id field in it that tells you that the data is for a particular client.

Here is your sql

SELECT DATA.DATA 
FROM 
  DATA,
  USER
WHERE DATA.CLIENT_ID = USER.CLIENT_ID
    AND  USER.USER_ID = $id

 

Hope that helps

[sudduth66]

ok i am lost here is what i have and the error i am getting now.

 

my tables are as such

 

members

id client_id username password

 

storage

 

id client_id customer box_number

 

box number being what i am looking for just not wanting all the customers to be able to pull other box numbers.

 

 

 

 

 

 

 

Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given in C:\inetpub\wwwroot\search.php on line 18

No Boxes available please use your back button to select a new box.

 

 

 

Thank You again.

 

[attachment deleted by admin]

[Drummin]

See if this works for you

	  mysql_connect ("localhost", "****","*******")  or die (mysql_error());
      mysql_select_db ("Customers");
      mysql_select_db ("Client");       
       $term = $_POST['term'];    
    $sql = mysql_query("SELECT * FROM storage WHERE box_number = '$term' AND member.Client_ID = storage.Client_ID ");
   if (mysql_num_rows($sql) > 0)
{   
    while ($row = mysql_fetch_array($sql))
    {
echo "box_num: ".$row['box_number']."";
    echo "<br/> dept: ".$row['Department']."";
    echo "<br/> company: ".$row['Company']."";
    echo "<br/> status: ".$row['status']."";
    echo "<br/> location: ".$row['location']."";
    echo "<br/> description: ".$row['box_desc']."";
    }
}
else
{
   echo "No Boxes available please use your back button to select a new box.";
}