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

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
– alensone, Commented Jun 30, 2019 at 0:55
@showdev Adding new post and choose one of three topics for it, so I can filter post later by these three topics. — alensone
– alensone, Commented Jun 30, 2019 at 1: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
– Dharman ♦, Commented Jun 30, 2019 at 1:30

showdev · Accepted Answer · 2019-06-30 01:43:06Z

-1

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);

edited Jun 30, 2019 at 1:43

answered Jun 30, 2019 at 1:41

showdev

29.3k37 gold badges61 silver badges82 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

mickmackusa Over a year ago

How did this answer sneak in after I already hammered it as a duplicate? @show this question has many pre-existing answers on Stackoverflow.

showdev Over a year ago

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.

mickmackusa Over a year ago

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.

showdev Over a year ago

It seems fairly localized to this particular question, and answers on the other posts cover things thoroughly. No worries. Thanks for the feedback.

Collectives™ on Stack Overflow

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

Edited

1 Answer 1

4 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Edited

1 Answer 1

4 Comments

Linked

Related