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 can do it all in one SQL command using CTEs.
Assuming Postgres 9.6 and this classic many-to-many schema (since you did not provide it):
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)
)
, 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;
dbfiddle here
This creates any tags that do not exist yet on the fly.
The text representation of a Postgres array looks like this: {tag1, tag2, tag3}.
If the input array is guaranteed to have distinct tags, you can remove DISTINCT from the CTE input_tags.
Detailed explanation:
If you have concurrent writes you may have to do more. Consider the second link in particular.