Restricting database records based on allowed "cities" or "counties" fields

[vestport]

Hello,

 

First of all I do not consider myself fluent in PHP. I use a PHP code generator called PHPMaker. I have a database that for simplicity sake has records that contain the following fields:

 

Person

Address

City

State

Zip

County

Information

 

What I want to do in a perfect world is to restrict access to this data (from a larger database) based on the "City" or the "County" that the user has been authorized for so that only authorized city data is available to display, search etc.

 

In other words out of the following cities: New York, Syracuse, Buffalo, Pelham I want to just let the user access records for "Syracuse".

Now another user may need access to "Syracuse" and "Buffalo" or any combination. There are in reality dozens of different cities and records for the same cities that use the "City" field

 

PHPMaker and many other code generators that I have seen do not allow this sort of customization. The max functionality of security levels is either to have the user own the record by themselves or levels of access where each level has access to more and more data.

 

Now with that said a friend of mine told me that he heard of a way to customize access based on the users login. That sounds interesting as I could potentially say something like:

 

 

If user = Dave then allow "City" , Buffalo, Syracuse

If user = John then allow "City" , Buffalo, New York

etc.

 

My fear is that I will need to break out into some custom PHP code. I plan on using Joomla CMS for my front end at least to the point that after sign up it directs folks to the actual PHP MySql database generated with PHPMaker.

 

I can custom modify the SQL queries within the application and have fore some custom stuff and could put some conditions there? I am just not seasoned in this and reaching out to the group for some direction, possibilities and or solutions. Do I need a query builder? Not sure, as this is not my forte'.

 

 

Thanks in advance for any replies!

 

 

Art

[Barand]

Sounds  like you need an "allowedcities" table with a row for each city each user can access

 

user      city

bob      new york

bob    buffalo

dave      buffalo

 

etc

[vestport]

Thanks Barand!

 

Now then do I do a join I assume? And join cities from the new table to cities in the main database?

 

Like this?

 

Main Table                      User Table

Address                          Username--------referenced from main php code or somehow to join or verify authorized users

City---------------------------City

State

Zip

etc.

 

Like that?

 

 

 

 

Also, do I need to delimit cities in the new table with a comma if I want users to have access to several cities?

 

Like:

 

user      city

bob      new york, bufffalo, hampton

bob    buffalo, syracuse

dave      buffalo, new york

 

?????

 

 

If that is all that it is then sounds fairly easy. I have a graphical join window that lets amature PHP guys like me to do joins and don't mind putting names and cities manually in the new table.

 

Right now it is insane as the only way I can do it is by replicating the whole database and sorting only certain cities per user before it loads. This foolish hack also requires a whole new customization by PHPMaker too.

 

I guess the real problem is how to map the actual user to username to the user logged in or user table right? And that is probably accessible from the php code generated. I think I may be able to do this with one of the configurations. I will experiment.

 

 

Does this sound reasonable?

 

 

Thanks,

 

 

Art

[Barand]

To be able to join on both city and user you will need records containing one user and one city, no comma delimited lists

[ignace]

Are you sure you really need to match a user for each given city? What are your criteria to allow user A to search for cities B, C, and D and user E for cities C, D, and F? Is it possible you could also just group them, so that user's of group A can search in cities B, C, and D and that users in group E can search in cities C, D, and F?

 

PS:

Using a tool like PHPMaker to generate the skeleton of your project is great, like UML tools assist in generating Java code. But not for programming an enitre app.. If this is for some client then possibly at some point you will have to hand-edit the code and PHPMaker will complain (or worse overwrite) your changes.

 

PHP/programming is not so hard, MANY have gone before you!

[vestport]

Hello Ignace,

 

My criteria is that each user can have any combination of cities A-Z. There is no set group or level with a list of certain cities for each. The only thing I have done that is sort of successful so far is to use:

 

SELECT * from foo
WHERE FIND_IN_SET(Cities, REPLACE('New York, Syracuse, Buffalo', ' ', '')) != 0;

 

 

Now out of a field "Cities" the above will indeed show only the 3 out of dozens in the field. I need to do something similar per user and without manual input in the code. I don't mind having to manually input each in phpmyadmin or something similar however.

 

What I am using is PHPMaker so the ideal thing is to use a "Server Event" like Recordset_Searching.

 

I am told by PHPMaker support that I can:

 

function Recordset_Selecting(&$filter) {
ew_AddFilter($filter, "(Field1 = 1234)"); // Add your own filter expression    
} In fact, we even have provided example in previous reply:

ew_AddFilter($filter, "`MyList`.`City` IN (" . CurrentUserInfo("cities") . ")");

 

I am just not that seasoned to be able to implement that. I tried many things but the furthest I got was the "FIND_IN_SET" above. That really doesn't cut it as I need this to work with the users table too.

 

Thanks for your reply!

 

Art

 

 

 

[vestport]

Okay, I got this to work last night and unfortunately during security configs blew it away.

 

What was working or as close as I can remember was a "Server Event" as follows:

 

// Recordset Selecting event
function Recordset_Selecting(&$filter) {
ew_AddFilter($filter, "`MyMainTable`.`City` IN (" . CurrentUserInfo("Cities") . ")");   
} 

 

Now after trying to get this to work again, all I get is the following error:

 

Failed to execute SQL: SELECT * FROM `MyMainTable` WHERE `Users`.`Cities` IN () LIMIT 20 OFFSET 0. Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') LIMIT 20 OFFSET 0' at line 1

No records found


(mysqlt): SELECT COUNT(*) FROM `MyMainTable` WHERE `Users`.`Cities` IN ()
Error (1064): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1


(mysqlt): SELECT * FROM `MyMainTable` WHERE `Users`.`Cities` IN ()
Error (1064): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1


(mysqlt): SELECT * FROM `MyMainTable` WHERE `Users`.`Cities` IN () LIMIT 20 OFFSET 0
Error (1064): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') LIMIT 20 OFFSET 0' at line 1

 

Any ideas?

 

Again, the "Server Event" is a phpmaker function.

 

Thanks,

 

Art

 

[vestport]

Forgot to mention my user table name is "Users". I honestly forget if I put that into:

 

(" . CurrentUserInfo("Cities") . ")");

 

somewhere and now it is not reading cities because it is not concatenated right (tablename.fileldname) and maybe that is my problem?

 

 

Art

[Illusion]

From error report......there are two problems

 

(i) CurrentUserInfo("Cities") not fetching any data into query

 

(i)  You are trying to get records from MyMainTable`  but in where clause you have Users table.  You need a join.

 

[vestport]

Illusion,

 

Thanks for your response. I have included a link to the PHPMaker "Server Events" to help explain how it works with this package. I'm sure the event itself takes care of the rest. I did this successfully last night without a join and foolishly didn't immediately backup the function or I should say I thought I did but I was wrong.

 

Thanks for your reply but still working trying to solve this.

 

 

Art

[vestport]

Illusion, sorry for the second reply. Yes, you are correct about the WHERE CLAUSE. This was before I really understood the problem better. The last post from me and the link are much more on target to solving this as I had the "Server Event" working and that has nothing to do with the WHERE clause above. Sorry to be confusing on this. I am not a seasoned vet at PHP & Mysql.

 

Art

[vestport]

The PHPMaker server event I am using is:

 

// Recordset Searching event
function Recordset_Searching(&$filter) {
    // Enter your code here    
ew_AddFilter($filter, "`MyMainTable`.`City` IN (" . CurrentUserInfo("Cities") . ")");
}

 

I have also tried:

 

// Recordset Searching event
function Recordset_Selecting(&$filter) {
    // Enter your code here    
ew_AddFilter($filter, "`MyMainTable`.`City` IN (" . CurrentUserInfo("Cities") . ")");
}

 

A link to the event syntax can be found here:

 

http://www.hkvstore.com/phpmaker/doc/customscripts.htm

 

 

And the debug error I get is:

 

Failed to execute SQL: SELECT * FROM `MyMainTable` WHERE `MyMainTable`.`City` IN () LIMIT 20 OFFSET 0. Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') LIMIT 20 OFFSET 0' at line 1

No records found


(mysqlt): SELECT COUNT(*) FROM `MyMainTable` WHERE `MyMainTable`.`City` IN ()
Error (1064): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1


(mysqlt): SELECT * FROM `MyMainTable` WHERE `MyMainTable`.`City` IN ()
Error (1064): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1


(mysqlt): SELECT * FROM `MyMainTable` WHERE `MyMainTable`.`City` IN () LIMIT 20 OFFSET 0
Error (1064): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') LIMIT 20 OFFSET 0' at line 1

 

 

I am using mysql  Ver 14.12 Distrib 5.0.95, for redhat-linux-gnu (x86_64)

 

I just don't know what the heck the errors are and how to deal with them. I suspect "'City' IN ()" that whatever is in the "()" which should be my "Cities" list in the "Users" table is not firing from my "Server Event" that I created filtering the cities.

 

 

Any help appreciated.

 

 

Thanks,

 

 

 

Art

 

[Illusion]

Seems like its not mysql problem anymore.

 

You could ask in PHP Code Help forum , probably somebody who has experience in phpmaker can help you.

 

Don't forget to post your CurrentUserInfo function code there.

[vestport]

The developer won't support custom code and the board seems at a snails pace. I have had something on there for days without input from anyone. I am very surprised. That is why I tried to reach out here.

 

Thanks for your responses in any case!

 

 

Art

[ignace]

So now you have payed $200 to find out you still need to learn to program...

[fenway]

You need to debug your subroutine to see why you're getting nothing.

[vestport]

Hello Fenway,

 

Yes, I have debug turned on but am just hacking this as I really don't know what I am doing with PHPMaker. I would bail to just PHP code but I really need this to work with the code generator software.

 

Thanks for your reply.

 

Art

[fenway]

I don't know what you think you mean when you say "debug turned on".  Add print statements.

[vestport]

I mean debug turned on within the PHPMaker application. There is a checkbox to debug.

 

Art

 

[Illusion]

CurrentUserInfo("Cities") function is written by you or code for it is auto generated ? in either case you need to debug that function by adding print statements or with a debugger if you are using any IDE.

 

if you are not able to make it, post that function code here and along with Users table info .

 

[vestport]

Illusion -

 

CurrentUserInfo() function is an internal PHPMaker functions. I have references to this and to the developer examples in this post. Also, the code I put into the "Server Event":

 

 

// Recordset Selecting event
function Recordset_Selecting(&$filter) {
ew_AddFilter($filter, "`MyMainTable`.`City` IN (" . CurrentUserInfo("Cities") . ")");

 

 

is mostly internal PHPMaker functions. The only part that I input was the "MyMainTable", "City" and "Cities" as directly above this line and as suggested by the developer. The problem is that when they sent this I think it was not completely correct and I hacked at it until I got it, then lost the code. I believe I had to add "Users" (for the users table) somewhere to the right of "IN".

 

 

As far as debug goes. There is an option to turn debugging on or off within the program and that is what I am referring to when I mention anything about debug.

 

The tables: Well I think I described them pretty well in this post. The "MyMainTable" has similar to:

 

Name

Address

City

State

Zip

 

for each record and the "Users" table - used for logon security and access to cities has:

 

Username

Password

Cities

etc.

 

The "MyMainTable" has only one city per record. The "Users" table has several cities in the field "Cities" where each city is single quoted and comma delimited like so: ('City1', 'City2', 'City3').

 

 

Thanks to everyone who has hung in there trying to help on this. I really appreciate it!

 

 

Art

[Illusion]

A quick look at documentation shows certain global functions should be used with Advanced Security only.

 

try this

 

function Recordset_Selecting(&$filter) {
$cities=ew_ExecuteScalar("SELECT cities FROM users WHERE username='".CurrentUserName()."'");
ew_AddFilter($filter, "`MyMainTable`.`City` IN (".$cities.")");
}

[fenway]

I mean debug turned on within the PHPMaker application. There is a checkbox to debug.

 

Art

 

You need to be able to debug code yourself -- not with someone else's tools.