Insert list id and item

Question

I'm a bit lost and cant find anything to guide me in the right direction so maybe some of you got a suggestion. Im trying to do add items in a list.

First of all I have a user table which contains the users id. This id is put as a session so when the user creates adds a list their id is inserted in the list table, so I know which user the list belolngs to. The list table aslo contains a listname and an unique id which is gained from auto increment.

List:

id(from user)     ai        Listname
   1              1         List one
   1              2         List two
   3              3         List one

My next table is to add items in the list. It contains the ai from list, and its own auto increment and the name on the item.

ai(from list)   itemAi       itemName
  ?               ?            ? 

What I dont get is to insert it. Lets say I created a list a its in my table and I insert an item name and itemAi gets automaticly set, how do I set which list I'm currently in? Do i set it as a session or what's the normal approach?

Update:

$name = 'Test';
$sql = $con->prepare('INSERT INTO list (name) VALUES (?)');
$sql->bind_param("s",$name);
$sql->execute();
$sql->close();

$user = $_SESSION['user_id'];

$qq = $con->prepare('INSERT INTO user_list (user_id, list_id) VALUES (?,?)');
$qq->bind_param("ss", $user,????);
$qq->execute(); 
$qq->close();
$con->close();

Answer 1

I'd try to normalize your tables into logical concepts: user, list, item etc. Then, use join tables to create the many-to-many relationships between them.

Tables:

user: id (auto-increment), first name, last name, ...

list: id (auto-increment), name

item: id (auto-increment), name

user_list: user_id (FK to user.id), list_id (FK to list.id)

user_list_item: user_id (FK to user.id), list_id (FK to list.id), item_id (FK to item.id)

*FK = foreign key. There are ways to shorten this up a bit, but I'd go for the explicit structure here to keep things clean and separated.

Then, in PHP you keep track of your user and the list they're currently working on in the session.

<?php

// After logging in
$_SESSION['user_id'] = ...;

// Choose which list the user is working on
$_SESSION['list_id'] = ...;

If they haven't chosen a list to work with, force them to. This is part of the validation.

Now every time the user wants to make changes, use those session values. Your business logic would be:

<?php
// Associate a list with a user
INSERT INTO `user_list` (user_id, list_id) VALUES (?, ?)
  ?: $_SESSION['user_id']
  ?: $_SESSION['list_id']

// Disassociate a list from a user
DELETE FROM `user_list` WHERE user_id = ? AND list_id = ?
  ?: $_SESSION['user_id']
  ?: $_SESSION['list_id']

// Insert a new item into a user's list
$itemId = INSERT INTO `item` (name) VALUES (?)
  ?: 'Bread'
INSERT INTO `user_list_item` (user_id, list_id, item_id) VALUES (?, ?, ?)
  ?: $_SESSION['user_id']
  ?: $_SESSION['list_id']
  ?: $itemId

// Clear our the items from a specific list
DELETE FROM `user_list_item` WHERE user_id = ? AND list_id = ?
  ?: $_SESSION['user_id']
  ?: $_SESSION['list_id']

// Empty all items for a given user, but keep the lists intact.
DELETE FROM `user_list_item` WHERE user_id = ?
  ?: $_SESSION['user_id']

// Remove a specific item from a user's list
DELETE FROM `user_list_item` WHERE user_id = ? AND list_id = ? AND item_id = ?
  ?: $_SESSION['user_id']
  ?: $_SESSION['list_id']
  ?: $itemId

Lets say the user inserts a new list and I insert its name and an id on the list gets A.i. Then at the same time I have to insert the session user_id and the list_id in user_list, the user id is set from session, but how do I get the list_id on the new created list?

Good question. Most RDBMs give you a function to get the last inserted ID. PDO specifically exposes it via lastInsertId.

In this case, you'd issue 2 insert statements. The first one will create the new list and the second will associate the list with the user by grabbing the ID of the newly created list and the current user ID from the session.

<?php

// Create a new list and assign it to a user

// The form was submitted
if ($_SERVER['REQUEST_METHOD'] === 'POST') {

    // Connection to your DB. 
    // See https://phpdelusions.net/pdo#dsn for a decent tutorial on PDO. 
    $pdo = new PDO(...);

    // Get the name from the submitted form
    $listName = $_POST['name'];

    // Create the list and get the ID of the newly created list.
    $stmt = $pdo->prepare('INSERT INTO `list` (name) VALUES (?)');
    $stmt->execute([$listName]);
    $listId = $pdo->lastInsertId();

    // Now, associate the new list with the user. 
    $stmt = $pdo->prepare('INSERT INTO `user_list` (user_id, list_id) VALUES (?, ?)');
    $stmt->execute([$_SESSION['user_id'], $listId]);
}