I would like to create 3 dbs connected by foreign keys and have the end user input the data from one form
Can anyone please help?
The dbs are client, work, and accessories
Below is the form
<form name="form" method="psot" action="save.php">
<h1>Client</h1>
<label>
<input type="text" name="txtName" id="txtName" onfocus="if(this.value=='Name'){this.value='';}" onblur="if(this.value==''){this.value='Name';}" value="Name" title="Name" required="">
</label>
<label>
<input type="date" id="txtDate" name="txtDate">
<?php
$month = date('m');
$day = date('d');
$year = date('Y');
$today = $year . '-' . $month . '-' . $day;
?>
</label>
<label>
<input type="text" name="txtEmail" id="txtEmail" onfocus="if(this.value=='Email Address'){this.value='';}" onblur="if(this.value==''){this.value='Email Address';}" value="Email Address" title="Email Address" required="">
</label>
<label>
<input type="text" name="txtPhone" id="txtPhone" onfocus="if(this.value=='Phone Number'){this.value='';}" onblur="if(this.value==''){this.value='Phone Number';}" value="Phone Number" title="Phone Number" required="">
</label>
<label>
<select name="txtOptions">
<option value="">Select Options</option>
<option value="OptOne">Option one</option>
<option value="OptTwo">Option two</option>
<option value="OptThree">Option three</option>
<option value="OptFour">Option four</option>
</select>
</label>
<h1>Work</h1>
<label>
<input type="checkbox" name="work" value="one">One<br />
<input type="checkbox" name="work" value="two">Two<br />
<input type="checkbox" name="work" value="three">Three<br />
<input type="checkbox" name="work" value="four">Four<br />
</label>
<h1>Accessories</h1>
<table class="table table-bordered">
<thead>
<tr>
<th></th>
<th>Product Name</th>
<th>Price</th>
<th>Quantity</th>
</tr>
</thead>
<tbody>
<tr>
<td><input type="checkbox" name="prodid[]" value="Car"></td>
<td>Accessory One
<input type="hidden" name="prodname[]" value="Car">
</td>
<td><input type="number" name="prod_price[]" class="form-control"></td>
<td><input type="number" name="prod_qty[]" class="form-control"></td>
</tr>
<tr>
<td><input type="checkbox" name="prodid[]" value="Bike"></td>
<td>Accessory Two
<input type="hidden" name="prodname[]" value="Bike">
</td>
<td><input type="number" name="prod_price[]" class="form-control"></td>
<td><input type="number" name="prod_qty[]" class="form-control"></td>
</tr>
<tr>
<td><input type="checkbox" name="prodid[]" value="Accessories"></td>
<td>Accessory Three
<input type="hidden" name="prodname[]" value="Accessories">
</td>
<td><input type="number" name="prod_price[]" class="form-control"></td>
<td><input type="number" name="prod_qty[]" class="form-control"></td>
</tr>
</tbody>
</table>
</form>
What I have tried:
creating the database
CREATE TABLE `Work` (
`WorkId` int(11) NOT NULL,
`WrkWork` varchar(100) NOT NULL,
primary key(JobId)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE `Accessories` (
`AccId` int(11) NOT NULL,
`AccTank` varchar(100) NOT NULL,
primary key(AccId)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE `Client` (
`Id` int(11) NOT NULL,
`CltDate` varchar(11) NOT NULL,
`CltName` varchar(100) NOT NULL,
`CltEmail` varchar(150) NOT NULL,
`CltPhone` varchar(20) NOT NULL,
`CltOptions` varchar(9) NOT NULL,
primary key(Id), foreign key(AccId) references Accessories(AccId),
foreign key(WorkId) references Work(WorkId)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
The file for saving the data: save.php
<?php
$con = mysqli_connect('localhost', 'root', 'password','database');
$txtName = $_POST['txtName'];
$txtDate = $_POST['txtDate'];
$txtEmail = $_POST['txtEmail'];
$txtPhone = $_POST['txtPhone'];
$txtOptions = $_POST['txtOptions'];
$txtWork = $_POST['txtWork'];
$checked_array=$_POST['AccId'];
foreach ($_POST['prodname'] as $key => $value)
{
if(in_array($_POST['prodname'][$key], $checked_array))
{
$prodname=$_POST['prodname'][$key];
$prod_price= $_POST['prod_price'][$key];
$prod_qty= $_POST['prod_qty'][$key];
}
}
$sql1 = "INSERT INTO `ClientDets` (`Id`, `CltName`, `CltDate`, `CltEmail`, `CltPhone`, `CltOptions`)
VALUES ('0', '$txtName', '$txtDate', '$txtEmail', '$txtPhone', 'txtOptions')";
$sql2 = "INSERT INTO `Work` (`WorkId`, `WrkWork`)
VALUES ('0', '$txtWork')";
$insertqry="INSERT INTO `Accessories`( `AccId`, `product_name`, `product_price`, `product_quantity`) VALUES ('0', $prodname','$prod_price','$prod_qty')";
$rs = mysqli_query($con, $sql1); mysqli_query($con, $sql2); mysqli_query($con,$insertqry);
if($rs)
{
header("location: index.php");
}
?>