I have the following tables in my database:
CREATE TABLE subjects (
subject_id int(11) NOT NULL AUTO_INCREMENT,
subject text,
PRIMARY KEY (subject_id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;
CREATE TABLE users (
id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
username varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE users_subjects (
users_subjects_id int(11) NOT NULL AUTO_INCREMENT,
user_id_fk int(11),
subject_id_fk int(11),
FOREIGN KEY(user_id_fk) REFERENCES users(id),
FOREIGN KEY(subject_id_fk) REFERENCES subjects(subject_id),
PRIMARY KEY (users_subjects_id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;
In the table 'users_subjects' I’m trying to relate the 'subjects' and 'users' tables. All the data in the tables are entered from my index.php.
I introduce the subject_name from my index.php and every time I enter a new one, checkboxes like these are created in the part where the user is added:
This is the code to enter the user, where checkboxes are formed every time a subject is introduced (index.php):
<form method="post" action="register.php">
<?php include('errors.php'); ?>
<div class="input-group">
<label>User</label>
<input type="text" name="username" value="">
</div>
<div class="input-group">
<label>Subjects</label>
</div>
<div>
<?php
$sql = "SELECT subject FROM subjects"; /*Select from table name: subjects*/
$result = $conn->query($sql); /*Check connection*/
if($result)
{
foreach($result as $row)
{
echo "<input type='checkbox' name='subject' value='" . htmlspecialchars($row['subject']) . "' /> <label>" . $row['subject'] . " </label><br>";
}
}
?>
</div>
<div class="input-group">
<button type="submit" class="btn" name="reg_user">Add new user</button>
</div>
</form>
I have managed to enter the user name in the 'users' table.
The problem I have is that I don’t know how to shore the checkboxes data in the 'users_subjects' table. I'm stuck and I can’t get it solved. Can somebody help me?
This is the code I’ve done for ‘register.php’:
<?php
$username = "";
$subject = "";
$errors = array();
include('Conexion.php');
if (isset($_POST['reg_user'])) {
// receive all input values from the form
$username = mysqli_real_escape_string($conn, $_POST['username']);
$subject = mysqli_real_escape_string($conn, $_POST['subject']);
if (empty($username)) { array_push($errors, "Username is required"); }
if (empty($subject)) { array_push($errors, "Subject is required"); }
$user_check_query = "SELECT * FROM users WHERE username='$username' LIMIT 1";
$result = mysqli_query($conn, $user_check_query);
$user = mysqli_fetch_assoc($result);
if ($user) { // if user exists
if ($user['username'] === $username) {
array_push($errors, "Username already exists");
}
}
// Register user if there are no errors in the form
if (count($errors) == 0) {
$query = "INSERT INTO users (username)
VALUES('$username')";
mysqli_query($conn, $query);
$insert_id = mysqli_insert_id($conn);
$subject=implode(',',$_POST['subject']);
//Count subjects and checks if the subject exists
for($i=0; $i<count($subject); $i++) {
$query = "SELECT subject_id FROM subject where subject='$subject[$i]'";
$result = $conn->query($query); /*Check connection*/
if ($result->num_rows > 0) {
$row = $result->fetch_assoc();
$subject_id = $row["subject_id"];
$query = "INSERT INTO users_subjects (user_id_fk, subject_id_fk)
VALUES('$insert_id', '$subject_id')";
mysqli_query($conn, $query);
} else {
/*???*/
}
}
header('location: indexAdmin.php');
}
}
?>
