PHP/MySQL - Improper stored data when submitted by users?

Question

First let me explain that I have this script that should let users enter multiple tags that are separated by a comma for example, html, css, php, mysql and store each tag in the database and associating it to the question it was tagged too.

But for some reason the tags are not beign associated with the questions_tags table correctly to give you a better explanation of what I talking about and what I want to happen please look at the following table layouts below?

I hope i explained this correctly?

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

Here is what happens below.

Table: questions_tags

id  tag_id  users_questions_id
1  1   3

Table: tags

id  tag
1  html
2  css
3  php
4  mysql

Here is what I want to happen below.

Table: questions_tags

id  tag_id  users_questions_id
1  1   3
2  2   3
3  3   3
4  4   3

Table: tags

id  tag
1  html
2  css
3  php
4  mysql

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 my 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){

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

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

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

$query1 = "INSERT INTO tags (tag) VALUES ('" . $tags[$x] . "')";


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='" . $tags[$x] . "'");

 }
}

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

Your best bet is to:

1. log into PHPMyAdmin on your MySQL server
2. manually add an entry to the questions_tags column
3. copy and paste the MySQL query created by PHPMyAdmin into your php script

voila!

Answer 2

try

INSERT INTO questions_tags (tag_id, users_questions_id) VALUES ($id, $page)