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.

You need to have a primary key (ID) on the questions table and the tags table. Then have a third table containing just question and tag IDs. — miken32, Apr 08 '17 at 20:41
I have it. I omitted that because It's pretty clear on the question I'm using that approach — vftw, Apr 08 '17 at 20:44
After an insert you can use PDO's lastInsertId function to retrieve the primary key of the row you just inserted. You can then add data to the next tables without having to do a seperate query to fetch the primary key first. http://php.net/manual/en/pdo.lastinsertid.php — Andrew Larsen, Apr 08 '17 at 20:48
It would help *a lot* if you provided actual table definitions (`CREATE TABLE` statements) and your version of Postgres. All you need is a *single* SQL statement. — Erwin Brandstetter, Apr 09 '17 at 13:42

Erwin Brandstetter · Answer 1 · 2022-09-21T19:26:53.433

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:

If there can be concurrent writes, consider:

How to use RETURNING with ON CONFLICT in PostgreSQL?

Multiple INSERTS into one table and many to many table

1 Answers1

Linked