MYSQL, PHP and HTML SELECT - adding data to table with relationships

Question

I am not best at PHP and this job I need to do is PHP MUST BE, so I am asking for little help as I am still getting into the PHP.

Here are my tables:

CREATE TABLE `posts` (
      `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
      `user_id` int(11) DEFAULT NULL,
      `title` varchar(255) NOT NULL,
      `slug` varchar(255) NOT NULL UNIQUE,
      `body` text NOT NULL,
      FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) );

CREATE TABLE `topics` (
      `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
      `name` varchar(255) NOT NULL,
      `slug` varchar(255) NOT NULL UNIQUE );

CREATE TABLE `post_topic` (
      `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
      `post_id` int(11) DEFAULT NULL UNIQUE,
      `topic_id` int(11) DEFAULT NULL,
      FOREIGN KEY (`post_id`) REFERENCES `posts` (`id`),
      FOREIGN KEY (`topic_id`) REFERENCES `topics` (`id`) );

There is ON UPDATE NO ACTION... but I left it out to keep it clean.

Now I added Admin page where I can add new posts and script that let me insert title, slug and body of the post

Here is PHP:

$id = "0";
$user_id = 3;
$title = "";
$slug = "";
$body = "";

if (isset($_POST['submit'])) {
    $title = $_POST['title'];
    $slug = $_POST['slug'];
    $body = $_POST['body'];

    mysqli_query($db, "INSERT INTO posts (title, slug, body) VALUES ('$title', '$slug','$body')");
}

And here is HTML form

<form method="post" action="posts.php">

    <input type="text" name="title" placeholder="Title">
    <input type="text" name="slug" placeholder="Slug">
    <textarea name="body" rows="3" placeholder="Text"></textarea>

    <button type="submit" name="submit">Submit</button>

</form>

Also there is config.php file with DB connection and this part of my code works fine.

Now how can I add category using selection box to the post? For example I have three categories Sport, Tech and News and on each post I want to be able to select a category that later can be found under categoryes.

I tried few thing but it just adds new ID to the post_topic or topics table.

Edited

This is how inserts should be for example:

INSERT INTO `posts` (`id`, `user_id`, `title`, `slug`, `body`) VALUES (1, 1, 'First post', 'first-post','First post text') 
INSERT INTO `topics` (`id`, `name`, `slug`) VALUES (1, 'Tech', 'tech')  
INSERT INTO `post_topic` (`id`, `post_id`, `topic_id`) VALUES (1, 1, 1)

@Dharman i know, I am learning it from novice to ninja, this is something I need to do quickly so its just simple CMS with procedural PHP without security focus. — alensone, Jun 30 '19 at 00:55
Just to clarify, do you want to add a new topic? Or do you want to add a new post that's associated with an existing topic? — showdev, Jun 30 '19 at 01:01
@showdev Adding new post and choose one of three topics for it, so I can filter post later by these three topics. — alensone, Jun 30 '19 at 01:03
I don't understand why would you want to learn how to do it wrong? Learn how to do it properly with prepared statements from the start. — Dharman, Jun 30 '19 at 01:30

showdev · Answer 1 · 2019-06-30T01:43:06.677

I suggest populating an HTML <select> input with all the possible topic entries from the database. Then, when you submit the form, associate the post with the selected topic by adding a record to post_topic.

Here's a basic example using mysqli procedural style, since that seems to be what you're using.

Query all available topics

$sql = "SELECT * FROM `topics` WHERE 1 ORDER BY `name` ASC;";
$topicsQ = mysqli_query($db, $sql);

Populate the <select> with topics

<form method="post" action="posts.php">

    <input type="text" name="title" placeholder="Title">
    <input type="text" name="slug" placeholder="Slug">
    <textarea name="body" rows="3" placeholder="Text"></textarea>

    <select name="topic_id"><?php

      while ($topic = mysqli_fetch_row($topicsQ)) {

        ?><option value="<?=$topic['id']?>"><?=$topic['name']?></option><?php

      }

    ?></select>

    <button type="submit" name="submit">Submit</button>

</form>

Insert the post

$user_id = 1; // this might come from whichever user is logged in, from the form, etc.

$title = $_POST['title'];
$slug = $_POST['slug'];
$body = $_POST['body'];
$topic_id = $_POST['topic_id'];

$sql = "INSERT INTO `posts` (`user_id`, `title`, `slug`, `body`) VALUES (?,?,?,?);";
$stmt = mysqli_prepare($db, $sql);
mysqli_stmt_bind_param($stmt, 'isss', $user_id, $title, $slug, $body);
mysqli_stmt_execute($stmt);

// get the id of the inserted post
$post_id = mysqli_insert_id($db);

Associate new post with selected topic

$sql = "INSERT INTO `post_topic` (`post_id`, `topic_id`) VALUES (?,?);";
$stmt = mysqli_prepare($db, $sql);
mysqli_stmt_bind_param($stmt, 'ii', $post_id, $topic_id);
mysqli_stmt_execute($stmt);

How did this answer sneak in after I already hammered it as a duplicate? @show this question has many pre-existing answers on Stackoverflow. — mickmackusa, Jun 30 '19 at 01:42
I'm not sure! Maybe because I'd already started the answer before it was closed? I didn't notice it had been closed until after I submitted my post. The other posts referenced seem to adequately explain all relevant concepts. — showdev, Jun 30 '19 at 02:03
Just so you know, it is not my downvote. It is probably just an effort to allow the page to be Roomba'ed without human review. You have clearly put effort into your post. If you can find an old post to transfer it to (where your advice will not be redundant), I recommend that you shift it. — mickmackusa, Jul 11 '19 at 07:37
It seems fairly localized to this particular question, and answers on the other posts cover things thoroughly. No worries. Thanks for the feedback. — showdev, Jul 11 '19 at 07:50

MYSQL, PHP and HTML SELECT - adding data to table with relationships

Edited

1 Answers1