Click here to Skip to main content
15,068,678 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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');

// 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 

$rs = mysqli_query($con, $sql1); mysqli_query($con, $sql2); mysqli_query($con,$insertqry);




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

?>
Posted
Comments
Richard Deeming 2-Aug-21 9:36am
   
That's three tables, not three dbs. :)

You haven't defined any foreign keys. And you're inserting 0 into the primary key for each table, so you'll only ever be able to insert a single row.
Kenneth Mukiria 2-Aug-21 9:43am
   
Sorry about that, I meant tables.

I do need help on defining the foreign keys.
Also what should I use instead of 0?
Richard Deeming 2-Aug-21 10:03am
   
Instead of 0, use an AUTO_INCREMENT[^] field. Then you don't need to insert a value for the primary key at all - the database will automatically insert the next value for you.

It looks like MySQL also allows you to explicitly insert 0 into the primary key field, so you wouldn't need to change that part of your code at all.

You would need to use LAST_INSERT_ID[^] or mysql_insert_id[^] to retrieve the value that was just inserted in order to reference the record via a foreign key.
Kenneth Mukiria 2-Aug-21 10:27am
   
would you mind sharing your code for reference
thank you

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900