Multiple INSERTS into one table and many to many table

Question

I'm trying to develop a Q&A website in PHP using a PostgreSQL database. I have an action to create a page which has a title, body, category and tags. I managed to insert all those fields however I'm having some issues inserting multiple tag values.

I used this function to get the comma separated values into an array and now I want something that inserts each array element into the database (avoiding repetitions) on table tags and after that insert on my many to many relationship table questiontags:

$tags = explode(',', $_POST['tags']); //Comma separated values to an array

which prints something like this:

Array ( [0] => hello [1] => there [2] => this [3] => is [4] => a [5] => test )

action/create_question.php

$category = get_categoryID_by_name($_POST['category']);

$question = [
    'userid' => auth_user('userid'),
    'body' => $_POST['editor1'],
    'title' => $_POST['title'],
    'categoryid' => $category
];

create_question($question, $tags);

and then my create_question where I should insert the tags.

function create_question($question, $tags) {

    global $conn;
    $query_publications=$conn->prepare("SELECT * FROM insert_into_questions(:body, :userid, :title, :categoryid);
");
    $query_publications->execute($question);
}

I was thinking about doing something like this:

global $conn;

foreach ($tags as $tag) {

    $query_publications=$conn->prepare("INSERT INTO tags(name) VALUES($tag);
");
    $query_publications->execute($question);    
}

But then I'd need the tags id to insert on my many to many table. Do I need to create another procedure, get_tags_id and then get a tag_id array and insert them as I tried for tags? When do I execute the query? After both inserts or in the end of each other?

Sorry for any misused term or for my newbie question. I'm new to PHP and I'm struggling with some new concepts.

Answer 1

Can be done with a single SQL query using CTEs.

Assuming Postgres 9.6 or later and this classic many-to-many schema:

CREATE TABLE questions (
  question_id serial PRIMARY KEY
, title text NOT NULL
, body text
, userid int
, categoryid int
);

CREATE TABLE tags (
  tag_id serial PRIMARY KEY
, tag text NOT NULL UNIQUE);

CREATE TABLE questiontags (
  question_id int REFERENCES questions
, tag_id      int REFERENCES tags
, PRIMARY KEY(question_id, tag_id)
);

To insert a single question with an array of tags:

WITH input_data(body, userid, title, categoryid, tags) AS (
   VALUES (:title, :body, :userid, :tags)  -- all input here
   )
 , input_tags AS (                         -- fold duplicates
      SELECT DISTINCT tag
      FROM   input_data, unnest(tags::text[]) tag
      )
 , q AS (                                  -- insert question
   INSERT INTO questions
         (body, userid, title, categoryid)
   SELECT body, userid, title, categoryid
   FROM   input_data
   RETURNING question_id
   )
 , t AS (                                  -- insert tags
   INSERT INTO tags (tag)
   TABLE  input_tags  -- short for: SELECT * FROM input_tags
   ON     CONFLICT (tag) DO NOTHING        -- only new tags
   RETURNING tag_id
   )
INSERT INTO questiontags (question_id, tag_id)
SELECT q.question_id, t.tag_id
FROM   q, (
   SELECT tag_id
   FROM   t                                -- newly inserted
   UNION  ALL
   SELECT tag_id
   FROM   input_tags JOIN tags USING (tag) -- pre-existing
   ) t;

db<>fiddle here

This creates any tags that did not exist on the fly.

This is an array literal (text representation of a Postgres array:

'{tag1, tag2, tag3}'

If the input array is guaranteed to have distinct tags, remove DISTINCT from the CTE input_tags.

Detailed explanation:

• Insert data in 3 tables at a time using Postgres
• How to implement a many-to-many relationship in PostgreSQL?
• Cannot INSERT: ERROR: array value must start with "{" or dimension information

If there can be concurrent writes, consider:

• How to use RETURNING with ON CONFLICT in PostgreSQL?