Php mysql: I would like to create 3 tables connected by foreign keys and have the end user input the data from one form

MySQL database
#1

I would like to create 3 tables connected by foreign keys and have the end user input the data from one form

The tables are client, work, and accessories

The sql file is below

CREATE TABLE `Work` (
  `WorkId` int(11) NOT NULL AUTO_INCREMENT,
  `WrkWork` varchar(100) NOT NULL,
	primary key(WorkId)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `Accessories` (
  `AccId` int(11) NOT NULL AUTO_INCREMENT,
  `product_name` varchar(20) NOT NULL,
  `product_price` float NOT NULL,
  `product_quantity` int(11) NOT NULL,
	primary key(AccId)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `Client` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `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,
  `AccId` int(11) NOT NULL,
  `WorkId` int(11) NOT NULL,
  primary key(Id), foreign key(AccId) references Accessories(AccId), 
  foreign key(WorkId) references Work(WorkId)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

The html form is below

<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="txtWork" value="one">One<br />
    	<input type="checkbox" name="txtWork" value="two">Two<br />
    	<input type="checkbox" name="txtWork" value="three">Three<br />
    	<input type="checkbox" name="txtWork" 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="AccId[]" 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="AccId[]" 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="AccId[]" 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>
	<label>
	    <input type="submit" name="Submit" id="Submit" value="Submit">
	</label>

</form>

And the php file for saving the data is below

<?php

$con = mysqli_connect('localhost', 'root', 'password','database');

// get the post records
$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];
	}
}

// database insert SQL code
$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')";

// insert in database 

$rs1 = mysqli_query($con, $sql1);
$rs2 = mysqli_query($con, $sql2);
$rs3 = mysqli_query($con,$insertqry);

if($rs1 AND $rs2 AND $rs3) 
{
	/*echo "Contact Records Inserted";*/
	 header("location: index.php");
}

?>

I am not sure where I am going wrong

#2

Welcome to the site!

Please post all code inside the code-tags. OR, place it between three back-tick marks. ( ` no spaces)
This will make our understanding your code much easier! Thanks.

Now you showed a lot of code but did not ask any questions. First, you can not do three queries the way you did. Each of them are separate queries. Therefore, you would need to alter that last part to something loosely like this:

$rs1 = mysqli_query($con, $sql1);
$rs2 = mysqli_query($con, $sql2);
$rs3 = mysqli_query($con,$insertqry);

if($rs1 AND $rs2 AND $rs3) {

But, this does not really work well since if it fails you do not know what went wrong. Normally, you would run the first query, check if it fails. If it fails you would give a notice to what went wrong. Then, if it passes, continue to query 2 and again check if that fails. If the first query fails, you do not need to do the next two queries. Does that make sense? Think about your logic, rewrite your code and post it correctly between tags or back-ticks and we will help further.

#3

Thank you. I have corrected the tags

#4

That is not the problem. You can continue here. I was just explaining things you need to know. Thanks!

Sponsor our Newsletter | Privacy Policy | Terms of Service