Php PDO MySQL Table joins

Question

I am scratching my head over something and for the life of me cannot figure it out?

I am building a little app at work which is like a mini CMS. Here is how it works.

User situation:

User logs in and can see a list of categories they have access to (issue#1) they click into that category and there will be a list of docs which are associated to that category(issue#2) they click onto that doc and they can see the doc information (the doc/docs I am referring to are just rows stores in a database and work fine).

issue#1 - I am not sure how to restrict users from seeing certain categories or controlling the access they have to those categories.

issue#2 - I have worked out how to assign a 'doc' to a category but only 1 and what I need is for the 'doc' to have the ability to be part of multiple groups if required.

Here is how I have set this all up:

On the category view page I grab the data to show the categories like so:

"SELECT * FROM cat_list ORDER BY cat_title ASC" - I need to restrict which categories users see.

When you click a category this is how I query the DB:

"SELECT * FROM cat_list WHERE cat_id = ?"

I am getting the cat id from the url so I know I am in that category.

Below this I am doing another query to show only the docs that belong to that category (which works)

"SELECT doc_list.doc_title, doc_list.cat_no, doc_id FROM doc_list WHERE cat_no = ?" - But the issue is in the doc_list table I have a columns called cat_no which is the id of the cat and I can only store 1 at a time where as the docs maybe a aprt of multiple categories.

I hope this makes sense?

Here is my database setup:

Catagory Table:

CREATE TABLE `cat_list` (
`cat_id` int(11) NOT NULL,
  `cat_title` varchar(32) NOT NULL
) ENGINE=InnoDB  DEFAULT CHARSET=utf16 AUTO_INCREMENT=5 ;

Document Table:

CREATE TABLE `doc_list` (
`doc_id` int(11) NOT NULL,
  `doc_title` varchar(50) NOT NULL,
  `doc_content` text NOT NULL,
  `doc_created` datetime NOT NULL,
  `doc_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `user_id` int(11) NOT NULL,
  `cat_no` int(11) NOT NULL
) ENGINE=InnoDB  DEFAULT CHARSET=utf16 AUTO_INCREMENT=122 ;

User table:

CREATE TABLE `user_login` (
`id` int(11) NOT NULL,
  `username` text NOT NULL,
  `firstname` varchar(30) NOT NULL,
  `lastname` varchar(50) NOT NULL,
  `password` varchar(64) NOT NULL,
  `psalt` text NOT NULL,
  `role` int(11) NOT NULL
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=12 ;

UPDATE

<?php require_once '../db_con.php'; 

if(!empty($_GET['cat_id'])){
    $cat = intval($_GET['cat_id']);
try{
    $results = $dbh->prepare("SELECT * FROM doc_list WHERE FIND_IN_SET(4, cat_no)"); // I need that number in the FIND_IN_SET to refelct the category I am in
    $results->bindParam(1, $cat);
    $results->execute();

    } catch(Exception $e) {
    echo $e->getMessage();
    die();
    }

    $doc = $results->fetchAll(PDO::FETCH_ASSOC); 
    if($doc == FALSE){

        echo '<div class="container">';
        echo "<img src='../img/404.jpg' style='margin: 40px auto; display: block;' />";
        echo "<h1 style='margin: 40px auto; display: block; text-align: center;' />Oh Crumbs! You upset the bubba!</h1>";
        echo '<a href="userList.php"  style="margin: 40px auto; display: block; text-align: center;">Get me outta here!</a>';
        echo'</div>';
        die();
    }
}

?>

As you can above I can return the values of the docs in those categories but I need it to take the id of the category I am in and show the results as at the moment it works by just assigning a static number as seen above (4, cat_no)" I need where it says number for to be a reference to the cat_id of the page I am on

Please see comments to the question from @prembaranwal who helped me answer my own question — Adam Mann Pro
– Adam Mann Pro, Commented May 13, 2015 at 10:58

Prem Baranwal · Accepted Answer · 2015-05-13 10:10:09Z

2

issue#1 - I am not sure how to restrict users from seeing certain categories or controlling the access they have to those categories. Answer: Create some roles for the user and assign those roles to each user. According to the role of the user, you can restrict users to see a particular category.

issue#2 - I have worked out how to assign a 'doc' to a category but only 1 and what I need is for the 'doc' to have the ability to be part of multiple groups if required. Answer: In your doc_list table, change cat_no data type as text and define all categories values as comma separated. For example: For doc1, cat_no will be 1,2,3 (where 1 2 and 3 are different category ids). Then you can use FIND_IN_SET of mysql to find out the docs for a category or a group of categories.

answered May 13, 2015 at 10:10

Prem Baranwal

3691 gold badge3 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Adam Mann Pro Over a year ago

Hi @prembarabwal could you show me an example how to use the FIND_IN_SET from my code above

Prem Baranwal Over a year ago

For example, In your doc_list table, doc_id will be 1 and cat_no will be 5,7,8. That means, doc with id 1 is attached to 3 categories whose ids are 5,7 and 8. Now, you want to fetch the docs with category_id 7. Your sql will be: SELECT * FROM doc_list WHERE FIND_IN_SET(7, cat_no)

Adam Mann Pro Over a year ago

@prebaranwal I have figured out by doing this: $results = $dbh->prepare("SELECT * FROM doc_list WHERE FIND_IN_SET(?, cat_no)");

Adam Mann Pro · Accepted Answer · 2015-05-13 10:59:27Z

2

Please see the comments from @prembaranwal who helped me answer my own question

answered May 13, 2015 at 10:59

Adam Mann Pro

2751 gold badge3 silver badges13 bronze badges

Comments

massie · Accepted Answer · 2015-05-13 10:08:10Z

1

I think you want to be able to use documents on multiple categories.

Then you must add an extra table between doc_list and cat_list, say:

category_documents (table) with columns:
cd_id (pk)
cat_id (key of cat_list)
doc_id (key of doc_list)

You now can use a document on multiple categories.

Now drop column cat_no in doc_list table.

answered May 13, 2015 at 10:08

massie

5562 silver badges10 bronze badges

Collectives™ on Stack Overflow

Php PDO MySQL Table joins

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related