In_array question

[g00gle]

Hi guys, have a quick question for ya'll.

Is it possible to make an in_array from a SQL table field where the data is stored?

 

I've tried something like this:

$result = mysqli_query($GLOBALS["___mysqli_ston"], "SELECT allowid FROM articles WHERE id =".sqlesc($id)) or sqlerr(__FILE__, __LINE__);
$cats = array();
while ($row = mysqli_fetch_array($result)){
$cats[] = $row['allowid'];
}
if (!(in_array($CURUSER['id'],$cats))){
stderr("Error", "Special access is required !");
}

Where the allowid table field contains numbers separated by a comma corresponding to each userid. If the userid doesn't match one of those numbers they shouldn't get access. This is what i am trying to do instead of having a separate table with different rows in it.
 

 

[Barand]

SQL was designed for normalized data held in separate tables. Storing comma-separated data i a single field is a definite no-no, use that separate table with an article id and an allowed id in each row.

 

All you then need do to find if access is allowed is

SELECT COUNT(*) as count
FROM article_access
WHERE articleid = ? AND allowid = ?

If the count is 0 it ain't allowed.

 

Job done.

Edited December 2, 2017 by Barand

[g00gle]

In that other table i already have articledid userid and even username... that is ok. But the access is given from the user's edit page and i want to change this.

What i wanted to do was to insert the access for the article from the article's edit page itself.

When i write articles just have an option to select which user/userid will get access.

Thank you for the reply

Edited December 2, 2017 by g00gle

[Barand]

You should hold data, like username, in one place only. The whole idea of a relational database is non-duplication of data. The username would be held in a user table whose key is the id. That way, if an item of data changes then it need only be updated in one place. Ids are the only things that should appear in more than one table (to maintain the data relationships). If you need to output the username you would use a relational JOIN to the user tale to get the matching record.

 

If in your edit page you have article id = 123 and users 100, 101, 102 are allowed access then

INSERT INTO article_access (articleid, userid) 
VALUES
(123, 100), (123, 101), (123, 102);

which will insert the required records with a single insert query.

 

Another approach is to assign users to groups and then specify the group(s) that can access an article, if that is an approach that fits your situation.

[g00gle]

Yes it has groups, but some articles require access based by name/userid.

That insert looks interesting but wouldn't i need to do somekind of join with the articles table and the users table so that i could get the proper values?

[Barand]

Yes, you would need the joins when presenting data to users so they can see meaningful user and article names. But you only do it on an "as required" basis.

EG

SELECT article_name
     , username
FROM article_access
    JOIN user USING (userid)
    JOIN article USING (articleid)
ORDER BY article_name

There are my SQL tutorials here http://www.barringtondrew.co.uk/index.php?page=3 which should help you. Edited December 2, 2017 by Barand

[g00gle]

But when i do the insert i dont need them ?

For example how do i insert access for username/userid from the edit page of an article with id=$id ? I mean if i am on edit page of an article that articleid is auto-recognized and all i need to do is insert the userid and name. Articleid will just get inserted based on what article i am editing

Won't i need some kind of form on the edit page itself?

Edited December 2, 2017 by g00gle

[Barand]

How were you populating your comma-separated list from the edit page without a form?

[Barand]

This brings up another advantage of the relational approach. With you comma-separated list you could only which users were allowed to access an article. Now you can just as easily see which articles a user can access. To do that before you would have needed a separated version of the lists, duplicating the data.

 

This then gives you two approaches, both using the same data

 

- on the article edit page select which users can access it

- on the user edit page select which articles they can access

[g00gle]

On the edit user page it is already the option to select which articles they can access or not

The entire idea of changing this to the edit page of the article was that it would be easier to add access just when doing another article instead of going to the user page.

Also i wanted to do something like if i post an article, give access to members, then i post another article that should be like a continuation of the previous one they will get access automatically to the "continuation" because they had it on the previous one.

Ohh dear it started to sound so complicated...

[Barand]

On the edit user page it is already the option to select which articles they can access or not

The entire idea of changing this to the edit page of the article was that it would be easier to add access just when doing another article instead of going to the user page.

 

Those two do not have to mutually exclusive. You could have both methods, use whichever is more convenient at the time.

 

It sounds like you are now introducing the concept of "article_group", where a user has access to group of articles. So instead of giving a user acces to a individual article, you give them access to the group.

 

You may also require the concept of "user_group", so allocating a member to a group gives them access to all the articles that the user group can access.

[mac_gyver]

In that other table i already have articledid userid and even username... that is ok. But the access is given from the user's edit page and i want to change this.

What i wanted to do was to insert the access for the article from the article's edit page itself.

When i write articles just have an option to select which user/userid will get access.

Thank you for the reply

 

i was starting to write a reply to this ^^^, when you posted your new reply above, but the answer is the same. you would provide a section on the article creation/edit page that lists the users, showing their username, with a way, either radio buttons or select/option menus next to each username, of allowing or denying access to that article. when creating a new article, you would pick which users will be allowed access to that article. when editing an existing article or the article access values, you would pre-select the correct radio-buttons/select-option choices based on the saved article_access data.

 

when the article creation/edit page is submitted, you would either insert new data or update existing data.

 

if creating a new article, you would insert it into the articles table and get the last insert id from that query to use as the article id for inserting the article_access data. the radio-buttons or select/option menus would submit the user id and the allow/deny selection. you would insert new rows into the article_access table.

 

if updating the article or article_access data, the form submission would contain the article id and you would use that id to update the article table and the article_access table with the changed data.

 

 

to have user groups and individual user access (which you stated at one point), you would need both group and individual article_access data. your article creation/edit page would basically need to have a way of selecting between three choices for each user - 1) inherit the group access, 2) allow access (if not a member of the group but should be able to access this specific article), or 3) deny access (if a member of the group but should not access this specific article.) when determining access, you would first get the group access value, then override it with any individual allow/deny value.

 

however, if what you really want is just group access without the ability to override (allow or deny) access for individuals, this becomes a simpler 'role' based access system (the above is a resource based access system.) you would create a role named something like 'members; and give it a permission to access (read) articles. you would assign users the role 'members'. to access articles, the user needs to have a role that includes permission to read articles to access the article display page.

 

you would use this same role based system to control who can create/edit articles. you would create a role named something like 'authors', with a permission to create and edit articles. if the currently logged in user has a role that includes permission to create and edit articles, they would be able to access the create/edit articles page.

 

the create/edit articles page would then not need the user access selection section. you would only need to assign the correct role to users, based on whatever condition qualifies a user to have that role.

 

if you search for 'role based access control list' you can find examples on how to do this second method.

Edited December 3, 2017 by mac_gyver

[g00gle]

This is getting us nowhere. I already have what you just said, both of you.

Access per user and access per group. Access per group is done from the article edit page itself and access per user from the user edit page. All i wanted was to change that so maybe if user has access to the x article could have the same access to the y article which is "linked" to the x article so i wont need to give access all over again to an article that is related or is a continuation of x article

 

x - first article

y - second article

y is the continuation of x but is a separate article

access from x gets cloned to y article automatically

[Barand]

The most straightforward way is to indicate on the article edit page that Y is a continuation of X. You then copy Y's userids from the article_access table and insert then again with Y as the articleid (which, again, can all be don with a single query)

EG

INSERT INTO article_access (articleid, userid)
    SELECT Y, userid
    FROM article_access
    WHERE articleid = X

This is getting us nowhere. I already have what you just said, both of you.

Before you throw your toys out of the pram, all we are trying to do is give you other options which may be applicable and therefore make life easier for you.

 

Please let us know if you no longer want help and I can close this thread.

[g00gle]

Well how do i tell the system that 2 articles are linked?

If i make another field in the articles table "linkeid" and there i populate with the articleid in cause?

Furthermore.. if i want to link multiple future articles to the same article how will that work out ?

You say INSERT INTO and i understand that, but how would the system know from which articleid(if they are multiple) in the access table to copy the access ?

without an actual example i cannot understand

Edited December 3, 2017 by g00gle

[Barand]

Well how do i tell the system that 2 articles are linked?

If i make another field in the articles table "linkeid" and there i populate with the articleid in cause?

Furthermore.. if i want to link multiple future articles to the same article how will that work out ?

Adding a linkid is another vaid way of solving the problem though it does make the query more complicated.

 

If you then add a third article Z which is a continuation of Y then the complexity increases again. You really get problems with chains longer than that and you will need a recursive solution.

 

Another way, as mentioned before is article groups. This way X,Y and Z would all have the same groupid which keeps things manageable. Of course if the first article (X) were considered to be the groupid, and Y and Z both linked to X (instead of forming a chain) then that would also work.

You say INSERT INTO and i understand that, but how would the system know from which articleid(if they are multiple) in the access table to copy the access ?

without an actual example i cannot understand

I thought I had just given you an example - X is the id of the original article and Y is the new one which is a continuation of X.

 

Which way is best depends on the complete relationships and constraints between your articles and users, and only you know that at present.

[g00gle]

This topic can be closed. I resolved it. Thank you for all the help and support.