How to insert data into foreign key from html page

#1

Hi i want to find a way to create a page html that can insert data into table that has foreign key (drop-down value) the value will get from the other primary table so i try with simple table to test but with no luck it show the page but when the submit button enter their are no data insert
can anyone help me please

so first the table i do something like this

Table Name : proj
proj_id int(15) PK + AI
proj_name varchar(30)

Table Name : user

user_id int(15) PK + AI
user_name varchar(30)
proj_id int(15) FK

the code
Folder Name :input.php

<html>
<head>
<title>

INPUT INTO PROJ

</title>

</head>

<body>

<form name="form1" method="post" action="insert.php">

	User ID: <input type="text" name="user_id" size="50"><br>
	


	User Name: <input type="text" name="user_name" size="50"><br>

    Proj ID: <input type="text" name="proj_id" size="50"><br>

<input type="submit" name="button" value="save">
</form>



</body>
</html>

Folder Name : insert.php
<?php

$severname = "localhost";
$username = "root";
$password = "";
$dbname = "proj";


//Create connection


$conn = new mysqli($severname, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 



session_start();
include('input.php');

$user_id=$_POST['user_id'];
$user_name=$_POST['user_name'];
$proj_id=$_POST['proj_id'];


$qry="SELECT * FROM proj WHERE proj_id=$proj_id";
$result = mysqli_query($qry);
$num_rows = mysqli_num_rows($result);

if($num_rows > 0){
    mysqli_query("INSERT INTO user(user_id, user_name, proj_id) VALUES('$user_id', '$user_name', $proj_id)");
}else{
    echo "Team is not valid!!!";
}
mysqli_close($con);
header("location: input.php?remarks=success"); 

mysqli_error($con); 
?>

i try insert data manually from phpmyadmin it work and it event show the drop down list i not sure how to do it with html and php.

i hope someone can tell me what should i do, i really new to this php and mysql. thank you

#2

You need to use prepared statements, especially because this data will be coming from user supplied data.

You need to create a drop down that is populated from the table first.

<select name="project">
    <option value="55">Online Help</option>
    <option value="23">In Person Help</option>
</select>

Now when the form is submitted you are getting the selected value to be inserted into the record, and because it was supplied by the database, it should match the foreign key constraint.

#3

ok thank you. but now i have new question how to create drop down value from data database (phpmyadmin) and insert it into another table

#4

You are just selecting the data from that table, like the id and the name, and building the html.
Then you just take that value and do the insert.

#5

so i use this code but it no show the dropdown value list

<html>
<head>
<title>

INPUT INTO PROJ

</title>

</head>

<body>

<form name="form1" method="post" action="index.php">

	User ID: <input type="text" name="user_id" size="50"><br>
	


	User Name: <input type="text" name="user_name" size="50"><br>


    Proj ID: <input type="text" name="proj_id" size="50"><br>




<select name="proj_id">
<?php 
$severname = "localhost";
$username = "root";
$password = "";
$dbname = "proj";


//Create connection


$conn = new mysqli($severname, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 
$sql = mysqli_query($connection, "SELECT proj_id FROM proj");
while ($row = $sql->fetch_assoc()){
echo "<option value=$proj_id>" . $row['proj_id'] . "</option>";
}
?>
</select>



<input type="submit" name="button" value="save">
</form>



</body>
</html>
#6

ok i manage to make the dropdown list but the value cannot be insert

<html>
<head>
<title>

INPUT INTO PROJ

</title>

</head>

<body>

<form name="form1" method="post" action="index.php">

	User ID: <input type="text" name="user_id" size="50"><br>
	


	User Name: <input type="text" name="user_name" size="50"><br>





Proj ID : <select name="proj_id">
<?php 
$severname = "localhost";
$username = "root";
$password = "";
$dbname = "proj";


//Create connection


$conn = new mysqli($severname, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 
$sql = mysqli_query($conn, "SELECT proj_id FROM proj");
while ($row = $sql->fetch_assoc()){
	
echo "<option value=''>" . $row['proj_id'] . "</option>";
}
?>
</select>

<br>

<input type="submit" name="button" value="save">
</form>



</body>
</html>
#7

You aren’t using a value…

You can also simplify this,

User Name: <input type="text" name="user_name" size="50"><br>

If you use an identifier that is in the database, the user id can be looked up on insert. Say you have a pin number for John Smith, 1234. When John wants to add something, rather than putting in several fields he can enter ‘1234’, now when the record gets inserted you do something like this:

$stmt = $pdo->prepare("INSERT INTO user_proj (proj_id, user_id) VALUES (?,SELECT user_id FROM Users WHERE name = ?))";
$stmt->execute([$_POST['proj_id'], $_POST['user_name]]);

On another note, your table scheme is really bad. You are duplicating data and adding values to a table that should know nothing about those fields.

Table: Projects
id int
name varchar(50)
details varchar(MAX)
date_start datetime
date_end datetime

Table Users
id int
first_name varchar(50)
last_name varchar(50)
email varchar(50)
phone varchar(10)
date_removed default null

Table Project_Users
id int
project_id int
user_id int
date_added
date_removed

Project_Users is a joining table that keeps all you data segregated into their proper tables. This is an example only as I do not know what data you actually need to store, what is important for the tables to know, or what that data needs to be stored as.

#8

thank you i get it now. yup the table those not make sense because i just try first with simple table and relationship to understand the concept first.

Sponsor our Newsletter | Privacy Policy | Terms of Service