PHP OOP - best way to implement 2 classes

Question

I have a simple DB with user data table, a controller data table and user_controller table that have the controllerID and the userId.

In my PHP OOP (first time) implementation i have a class User and a class Controller.

The functionality for the user is simple - he can login and logout and see a table of all his controllers. I have an admin user who can see all users and their controllers, he can add/delete/edit controller to a user and also can add\delete\edit users.

The Login functionality is implemented (DB class, user class and helpers class)

My problem is how and where should i build the functionality to add a controller to a user. Controller has an id (unique), a name(not unique) and password.

User private fields are:

private $_db,
        $_data, //all of this user data
        $_sessionName,
        $_cookieName,
        $_isLoggedIn;

and controller class:

private $_db,
        $_data; //all of this controller data

I tried to implement function Create in controller:

public function create($fields = array()){
        if(!$this->_db->insert('controllers',$fields)){
            throw new Exception("Problem creating new controller");}}

and function createController in the user:

public function addController($pass,$controller_name,$lat='',$lon=''){
    if ($pass && $controller_name){
        $controller = new Controller();

        $salt = Hash::salt(32);

        try{
            $controller->create(array(
                    'pass' => Hash::make($pass,$salt),
                    'salt' => $salt,
                    'controller_name' => $controller_name,
                    'lat' => $lat,
                    'lon' => $lon
                    ));
        //if success then we update user-controller table
            $controller_id = $controller->find($controller_name)->data()->id;
            $userID = $this->data()->ID;
            $fields = array(
                'UserID' => $userID,
                'ControllerID' => $controller_id
                );

            if(!$this->_db->insert('user_controllers',$fields)){
                throw new Exception("Problem creating controller for user");
            }
        }
        catch(Exception $e){
            throw new Exception('There was a problem creating new controller'); 
        }
    } else {
        throw new Exception('No name or password had been given');
    }

Generally it is working. But there have been couple problems:

1. The id of the controller is created automatically by the DB so i dont know it when i create it. I cant later find the controller by name because i can have a lot of controllers with this name

2. I am not sure this is the proper way to handle the relationship between user and controller

3. i have trouble of implementing "show me all your controllers" for the user.

Can you please tell me what is the correct way to implement this relationship and how to make the createController and ShowControllers of the user?

Answer 1

Your structure seems to be correct. You need a users table, a controllers table and a user_controller table as you mentioned which maps the relationships between the two (user has many controllers).

Solutions for your problems:

1. When you insert data, you need to retrieve the inserted ID of the new record to use in your mapping relationship. I'm not sure in your example if you're written this MVC structure yourself or otherwise; it looks a little like CodeIgniter. If it is a CI application, here's an example of how to get the last insert ID. If it's a bespoke application, you can use $mysqli->insert_id (mysqli) or $pdo->lastInsertId(); for PDO.

2. What you're doing is correct.

3. In a "show me all your controllers" for the user scenario, you will query the user_controller table to get a list of all the controller relationships, and you'll use a join (inner might suit, for a mandatory successful join). You specify that you want to return controller.* in your select fields, and you'll end up with a record for each controller and ignore the user controller fields (if you don't need them). An example SQL query might look like this:

   SELECT controller.* FROM user_controller UC
   INNER JOIN controller ON controller.id = user_controller.controller_id
   WHERE user_controller.user_id = [YOUR_USER_ID_HERE]