PHP/MySQL - How to add multiple tags

Question

I have this script that only lets users enter a single tag but I want to let users enter multiple tags that are separated by a comma for example, shoe, shirt, hat, glasses and store each tag in the database.

Can someone please give me a couple of examples of what I need to change in my script in-order to do this.

Here is my MySQL tables below.

CREATE TABLE questions_tags (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
tag_id INT UNSIGNED NOT NULL,
users_questions_id INT UNSIGNED NOT NULL,
PRIMARY KEY (id)
);

CREATE TABLE tags (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
tag VARCHAR(255) NOT NULL,
PRIMARY KEY (id)
);

Here is the script below.

<?php 
require_once ('./mysqli_connect.php');

if (isset($_POST['submitted'])) {

  $mysqli = new mysqli("localhost", "root", "", "sitename");
  $dbc = mysqli_query($mysqli,"SELECT questions_tags.*, tags.* FROM questions_tags, tags");
 if (!$dbc) {
  print mysqli_error($mysqli);
 }

$page = '3';

$tag = mysqli_real_escape_string($mysqli, $_POST['tag']);

$mysqli = new mysqli("localhost", "root", "", "sitename");
$dbc = mysqli_query($mysqli,"SELECT questions_tags.*, tags.* FROM questions_tags INNER JOIN tags ON tags.id = questions_tags.tag_id WHERE questions_tags.users_questions_id='$page'");

if(mysqli_num_rows($dbc) >= 0){

$mysqli = new mysqli("localhost", "root", "", "sitename");
$clean_url = mysqli_real_escape_string($mysqli, $page);

$query1 = "INSERT INTO tags (tag) VALUES ('$tag')";

if (!mysqli_query($mysqli, $query1)) {
 print mysqli_error($mysqli);
 return;
}

$mysqli = new mysqli("localhost", "root", "", "sitename");
$dbc = mysqli_query($mysqli,"SELECT id FROM tags WHERE tag='$tag'");

if (!$dbc) {
 print mysqli_error($mysqli);
}  else {
 while($row = mysqli_fetch_array($dbc)){
  $id = $row["id"];
 }
}

$query2 = "INSERT INTO questions_tags (tag_id, users_questions_id) VALUES ('$id', '$page')";

if (!mysqli_query($mysqli, $query2)) {
 print mysqli_error($mysqli);
 return;
}

echo "$tag has been entered";

 if (!$dbc) {
   print mysqli_error($mysqli);
 }
}
mysqli_close($mysqli);
}
?>

Answer 1

you can use explode()

To get an array of tags seperated by commas

$tag_string = "t1, t2, t3";
$tags = explode(",", $tag_string );
echo $tags[0]; // t1
echo $tags[1]; // t2

Then you can loop through the array to insert into the database

You might also want your Create Query to include UNIQUE

CREATE TABLE tags (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
tag VARCHAR(255) NOT NULL,
PRIMARY KEY (id),
UNIQUE(`tag`)
);

This way you wont have two tags with the same name. Look here for further explanation on the UNIQUE syntax

Here Goes coding without testing xD

//Assuming you have already added the question and the mysql_insert_Id() == 1
//where mysql_insert_Id() is the last id added to the question table

if (isset($_POST['tags'])){
    $tags = explode(",", $_POST['tags']);

    for ($x = 0; $x < count($tags); $x++){

        //Due to unique it will only insert if the tag dosent already exist
        mysql_query("INSERT INTO tag VALUES(NULL, {$tags[x]})");

        //Add the relational Link
        mysql_query("INSERT INTO question_tag VALUES(NULL, (SELECT tags.Id FROM tags WHERE tags.tag = {$tags[x]}), 1)");
    }
}

Answer 2

Avoid subqueries while mysql_insert_id() provide good result for achieving the same.

Another thing - check $_POST['tags'] for a comma before explode() to make sure you will get an array and also check in the loop if trim($tags[$x]) == '' (what can take place if $_POST['tags'] would be, for example: $_POST['tags'] === "tag1," or "tag1, ".

And not connected with question itself, but try to use only one db connection per request (as long as there is no good reason to do it other way).