Level 73
You can't use a one-to-many relationship for that, you need to use a many-to-many relationship with a pivot table category_post. Oh, and the foreign key should always be the same data type as the primary key that it references.
Oct 1, 2022
15
Level 4
Problem inserting multiple categories in posts table
It's not laravel, creating an oop blog project. I have categories and posts tables, categories table id is the foreign key of the posts table. I want to insert multiple selected categories' id in the posts table column ("category_id" type INT). how can I do that?
category_id column in the posts table:
category_id INT NOT NULL UNIQUE,
FOREIGN KEY (category_id) REFERENCES categories(id)
STORE METHOD:
// Store post
public function store()
{
$data['category_id'] = implode(',', Request::values()['category_id']);
dd($data);
// Insert post query
$query = sprintf(
"INSERT INTO %s (%s) VALUES(%s)",
"posts",
implode(', ', array_keys($data)),
":" . implode(', :', array_keys($data))
);
try {
$stm = pdo()->prepare($query);
$stm->execute($data);
} catch (PDOException $e) {
$this->jsonEncod(false, $e->getMessage());
}
}
IF I DO die & dump:
array(4) {
["title"]=>
string(20) "Culpa aut ullam nobi"
["slug"]=>
string(19) "dignissimos-quos-ut"
["body"]=>
string(21) "Distinctio Sit aut JJ"
["category_id"]=>
string(3) "1,2"
}
ERROR I GOT:
"SQLSTATE[01000]: Warning: 1265 Data truncated for column 'category_id' at row 1"
Thank you
Level 4
@Tray2 what should be the type of column category_id and post_id in the pivot table? Thank you
Level 73
@feralam Create them like this if you are using Laravel 9.
$table->foreignIdFor(Category::class);
$table->foreignIdFor(Post::class);
And I think you would do well by reading the part on foreign keys in the docs
https://laravel.com/docs/9.x/migrations#foreign-key-constraints
These posts might also be a good idea to read.
https://tray2.se/posts/database-design
https://tray2.se/posts/database-design-part-2
https://tray2.se/posts/properly-formed-foreign-keys-are-your-best-friends
Level 4
@Tray2 I am not using laravel, doing core OOP, and MVC-based blog project. is it ok, do I need to add constraints in the pivot table "category_post"?
category_id BIGINT NOT NULL UNIQUE,
post_id BIGINT NOT NULL UNIQUE,
Level 20
@feralam yeah! thats something ,but u have to changed your data type bcz it has a small range and the data is truncate form the data type you have provided!
Level 73
@feralam If the id column in those tables have the same data type that should work, but you can't have the unique constraint on them like that, together the should be unique, not on their own.
Level 4
@Tray2 ID column in those tables have INT data type, let me know if I am doing anything wrong in "category_post" pivot table
id INT AUTO_INCREMENT PRIMARY KEY,
category_id INT NOT NULL,
post_id INT NOT NULL,
UNIQUE(category_id, post_id),
FOREIGN KEY (category_id) REFERENCES categories (id)
ON DELETE CASCADE,
FOREIGN KEY (post_id) REFERENCES posts (id)
ON DELETE CASCADE
1 like
Level 20
@feralam fine :)
Level 73
@feralam That looks good to me.
Level 4
@Tray2 now the question is what will be the code or how I can insert post_id and category_id in the pivot table when creating a new post? Thank you
Level 73
@feralam You insert the post and retrieve the the id for the post, then you use that id and then you loop trough each of the tags and insert them into the pivot table.
Level 4
@Tray2 Let me try. Thank you
Level 4
@Tray2 It's working, am I doing right or is there any other best way to do it?
public function sendToStoreCatPost($category_ids)
{
$select = "SELECT id from posts ORDER BY id DESC LIMIT 1";
$stm = pdo()->prepare($select);
$stm->execute();
$result = $stm->fetch(PDO::FETCH_OBJ);
foreach ($category_ids as $category_id) {
$data = [
"category_id" => $category_id,
"post_id" => $result->id,
];
$query = sprintf(
"INSERT INTO %s (%s) VALUES(%s)",
"category_post",
implode(', ', array_keys($data)),
":" . implode(', :', array_keys($data))
);
$stm = pdo()->prepare($query);
$stm->execute($data);
}
}
Level 73
@feralam I think it looks pretty good.
Level 4
@Tray2 Got it the lastInsertId(), Thank you
Please or to participate in this conversation.