Php PDO returning related table data

Question

I am scratching my head over how to get a scenario to work where by I have my users login (which works) when they hit the homepage they will see a list of 'categories' which are pulled from my cat_list table but they only see 'categories' they are associated with. for example, user1 has access to cat1, user2 can access cat2, user3 can access cat1 & cat2 - I need a way to store those cat values and allow the users to see them. so cat1 could have an id of 1 and cat 2 could have an id of 2 but I am not sure how that translates into getting that data with a SQL command.

Here is currently how i am showing the categories.

query("SELECT * FROM cat_list ORDER BY cat_title ASC  ");

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

$docs = $results->fetchAll(PDO::FETCH_ASSOC);

foreach($docs as $docs){  

echo '
    
    
    
    
    
    '.$docs["cat_title"].'
    
    
    
    ';}


?>

Here is how my tables are made up:

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

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
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=12 ;

I am really struggling to understand or figure out the best way to link them and specify who sees what? I have researched JOINS and also relationships between tables with foreign keys or even having a separate new table al together (normalisation) - but form my above I need to get the users $_SESSION id which is passed from the login and then take that value and check to see what categories they can see.

UPDATE

prepare("SELECT * FROM user_login WHERE username=?");
        $sql->execute(array($email));

    while($r=$sql->fetch()){
        $p=$r['password'];
        $p_salt=$r['psalt'];
        $id=$r['id'];
    }

        $site_salt="subinsblogsalt";
        $salted_hash = hash('sha256',$password.$site_salt.$p_salt);

    if($p==$salted_hash){
        $_SESSION['user']=$id;
      $_SESSION['username']=$email;
      $_SESSION['firstname']=$firstname;

        header("Location:home.php");

    }else{
        echo "Username/Password is Incorrect.";
        }
    }
?>

** UPDATE 2**

Here is the home.php file where the categories need to be seen:



query("SELECT * FROM cat_list JOIN user_cats ON cat_id = cat_id WHERE user_id = ? ORDER BY cat_title ASC");




?>

beercohol · Accepted Answer

From the description of your requirement, Users and Categories have a one to many relationship (one user can have access to many categories) - however the user does not actually "own" the category (several users may have access to the same category), so you must have an intermediate table to define the relationship between the two.

This is the bit you are missing!

The table should be something like the following (my mySQL is a bit rusty so forgive me if there's a syntax error):

CREATE TABLE `user_cats` (
  `user_id` int(11) NOT NULL,
  `cat_id` int(11) NOT NULL,
);

How you populate this is up to you! Note that cat_ids for a given user should not be duplicated, else you will get duplicate categories in your list.

Your results query should then look something like this:

$sql=$dbh->prepare(("SELECT A.* FROM cat_list A JOIN user_cats B ON A.cat_id = B.cat_id WHERE B.user_id = ? ORDER BY A.cat_title ASC");
$sql->execute(array($id));

Where $id is your user_id that you get from a successful login.

RESPONSE TO UPDATE 2

Sorry, my mySQL and PHP is a bit rusty, and I was copying and pasting some bits from your code above that you say is working.

Firstly, the "A" and "B" in my query are table aliases - they just make the query easier to read so you can write "A.column_name" instead of "my_long_table_name.column_name". There is an optional "[AS]" which I should probably have included for clarity, but I am used to omitting it. See here for some info about aliases in mySQL: http://www.techonthenet.com/mysql/alias.php

"$id" is the variable from your login code, but of course that will only exist for that page. You're already storing that in a session var, so you just need to retrieve it from the session in your home.php. You seem to have that bit backwards, and are assigning a non-existent variable back to the session.

I would make home.php look more like this:

prepare(("SELECT A.* FROM cat_list AS A JOIN user_cats AS B ON A.cat_id = B.cat_id WHERE B.user_id = ? ORDER BY A.cat_title ASC");
        $sql->execute(array($_SESSION['user']));
    } catch(Exception $e) {
        echo $e->getMessage();
        die();
    }

    $docs = $sql->fetchAll(PDO::FETCH_ASSOC);

    foreach($docs as $doc_item) {  
        echo '
            
            
            
            
            
            '.$doc_item["cat_title"].'
            
            
            
            ';
    }
}

?>

Note that I have changed your iterator variable in the foreach loop to "$doc_item" as it looks like a bug to me to use the same variable as the array!

Php PDO returning related table data

Answers (1)

Related Questions