Display a foreign key value

Parkie02 · December 3, 2013

Can somebody maybe help me. I have got a complaint and complaint_details table. Complaint_nr is my primary key in complaint and foreign key in complaint_details. How can i show the complaint_nr in my complaint_details table. I have created the tables and in both there are a field for complaint_nr. It is an auto increment in complaint and also the primary key. And in complaint_details it is an int field and the foreign key.

Here is my code. At $query3 I try to insert data into complaint_details.

<?php
session_start();

if(!$_SESSION['email'])
{
	header("location:login.php");
}
?>

<html>
	<head>
		<title> 
			Complaint
		</title>
	</head>
	
<body>
<form method='post' action='complaint.php'>
	<table width='800' border='10' align='left'>
	<tr>
		<td align='center' colspan='5'><h1>Complaint Form</h1></td>
	</tr>

	<tr>
		<td>Comany's Name:</td>
		<td><input type='text' name='comp_name' /></td>
	</tr>
	
	<tr>
		<td>Registration Number:</td>
		<td><input type='text' name='reg_nr' /></td>
	</tr>
	
	<tr>
		<td>Email:</td>
		<td><input type='text' name='comp_email' /></td>
	</tr>
	
	<tr>
		<td>Contact Person:</td>
		<td><input type='text' name='comp_contact_person' /></td>
	</tr>
	
	<tr>
		<td>Contact Number:</td>
		<td><input type='text' name='comp_contactnr' /></td>
	</tr>
	
	<tr>
		<td>Details of complain:</td>
		<td><input type='text' name='complain_details' /></td>
	</tr>
	
	<tr>
		<td>Order Number:</td>
		<td><input type='text' name='order_nr' /></td>
	</tr>
	
	<tr>
		<td>Order Description:</td>
		<td><input type='text' name='order_desc' /></td>
	</tr>
	
	<tr>
		<td>Order Date:</td>
		<td><input type='date' name='order_date' /></td>
	</tr>
	
	<tr>
		<td>Delivery Date:</td>
		<td><input type='date' name='delivery_date' /></td>
	</tr>
	
	<tr>
		<td>Invoice Date:</td>
		<td><input type='date' name='invoice_date' /></td>
	</tr>
	
	<tr>
		<td>Delivery Number:</td>
		<td><input type='text' name='delivery_nr' /></td>
	</tr>
	
	<tr>
		<td>Order Amount:</td>
		<td><input type='number' step='0.01' name='order_amount' /></td>
	</tr>
	
	<tr>
		<td>Amount Already Paid:</td>
		<td><input type='number' step='0.01' name='amount_paid' /></td>
	</tr>
	
	<tr>
		<td>Amount Outstanding:</td>
		<td><input type='number' step='0.01' name='amount_outstanding' /></td>
	</tr>
	
	<tr>
		<td colspan='5' align='center'><input type='submit' name='add_debtor' value='Submit' /></td>
	</tr>
	
	
	</table>
</form>
</body>
</html>
<?php
mysql_connect("localhost","root","mj2015");
mysql_select_db("whodidntpay");

$log = $_SESSION['email'];

	if(isset($_POST['add_debtor']))
	{
		$comp_name = $_POST['comp_name'];
		$comp_regnr = $_POST['reg_nr'];
		$comp_email = $_POST['comp_email'];
		$comp_contact_person = $_POST['comp_contact_person'];
		$comp_contact_nr = $_POST['comp_contactnr'];
		$complain_det = $_POST['complain_details'];
		$ordernr = $_POST['order_nr'];
		$orderdesc = $_POST['order_desc'];
		$orderdate = $_POST['order_date'];
		$deliverydate = $_POST['delivery_date'];
		$invoicedate = $_POST['invoice_date'];
		$deliverynr = $_POST['delivery_nr'];
		$orderamount = $_POST['order_amount'];
		$paidamount = $_POST['amount_paid'];
		$outstandamount = $_POST['amount_outstanding'];
		
		if($comp_name=='')
		{
			echo "<script>alert('Please Enter Company Name')</script>";
			exit();
		}
		
		if($comp_regnr=='')
		{
			echo "<script>alert('Please Enter Company Registration Number')</script>";
			exit();
		}
		
		if($comp_email=='')
		{
			echo "<script>alert('Please Enter Company Email')</script>";
			exit();
		}
		
		if($comp_contact_person=='')
		{
			echo "<script>alert('Please Enter Contact Person')</script>";
			exit();
		}
		
		if($comp_contact_nr=='')
		{
			echo "<script>alert('Please Enter Contact Number')</script>";
			exit();
		}
		
		if($complain_det=='')
		{
			echo "<script>alert('Please Enter Complain Details')</script>";
			exit();
		}
		
		if($ordernr=='')
		{
			echo "<script>alert('Please Enter Order Number')</script>";
			exit();
		}
		
		if($orderdesc=='')
		{
			echo "<script>alert('Please Enter Order Description')</script>";
			exit();
		}
		
		if($orderdate=='')
		{
			echo "<script>alert('Please Enter Order Date')</script>";
			exit();
		}
		
		if($deliverydate=='')
		{
			echo "<script>alert('Please Enter Delivery Date')</script>";
			exit();
		}
		
		if($invoicedate=='')
		{
			echo "<script>alert('Please Enter Invoice Date')</script>";
			exit();
		}
		
		if($deliverynr=='')
		{
			echo "<script>alert('Please Enter Delivery Number')</script>";
			exit();
		}
		
		if($orderamount=='')
		{
			echo "<script>alert('Please Enter Order Amount')</script>";
			exit();
		}
		
		if($paidamount=='')
		{
			echo "<script>alert('Please Enter Amount Paid')</script>";
			exit();
		}
		
		if($outstandamount=='')
		{
			echo "<script>alert('Please Enter The Amount Outstanding')</script>";
			exit();
		}
		
		$num_debt = "select * from debtor where d_name= '$comp_name' AND registration_nr= '$comp_regnr'";
		$run = mysql_query($num_debt);
		$num_comp = mysql_num_rows($run);
		
		if ($num_comp ==0)
		$query = "insert into debtor(d_name,registration_nr,email,contact_person,contact_number,companies_not_paid,amount_not_paid) 
		values ('$comp_name','$comp_regnr','$comp_email','$comp_contact_person','$comp_contact_nr','1','$outstandamount')";
		if ($num_comp ==1)
		$query = "update debtor set companies_not_paid = companies_not_paid + 1, amount_not_paid = amount_not_paid + '$outstandamount' where d_name='$comp_name'";
		
		if (mysql_query($query))
		{
			echo "<script>alert('Company Added Successful')</script>";
		}
		
		$complain_id = "select complainant_id from complainant where email = '$log'";
		$run2=mysql_query($complain_id);
		
		$row = mysql_fetch_assoc($run2); 	
		$query2 = "insert into complaint(complain,d_name,complainant_id) values ('$complain_det','$comp_name','{$row['complainant_id']}')";
		
		if (mysql_query($query2))
		{
			echo "<script>alert('Complaint Added Successful')</script>";
		}
	
		$query3 = "insert into complaint_details(order_nr,order_description,order_date,delivery_date,invoice_date,delivery_nr,order_amount,amount_paid,amount_outstanding)
		values ('$ordernr','$orderdesc','$orderdate','$deliverydate','$invoicedate','$deliverynr','$orderamount','$paidamount','$outstandamount')";
		
	}	

?>

Any help will be appreciated

Parkie02 · December 3, 2013

The table complaint_details consists of everything stated in query 3 and also details_id that is the primary key and also auto increment and then also complaint_nr that is the foreign key that I dont know how to add

BrodaNoel · December 3, 2013

http://php.net/manual/en/function.mysql-insert-id.php

int mysql_insert_id ([ resource $link_identifier = NULL ] )

Retrieves the ID generated for an AUTO_INCREMENT column by the previous query (usually INSERT).

Example:

<?php
$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');
if (!$link) {
die('Could not connect: ' . mysql_error());
}
mysql_select_db('mydb');

mysql_query("INSERT INTO mytable (product) values ('kossu')");
printf("Last inserted record has id %d\n", mysql_insert_id());
?>

Warning This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQL extension should be used. See also MySQL: choosing an API guide and related FAQ for more information.

And... Please, read about: SQL Inyection.

Parkie02 · December 3, 2013

Can you maybe help me apply it directly on my problem. So with this query3 insert I want to insert all of the values below plus the details_id that is automatically added because of the auto increment and then also complaint_id that I want to add that is the foreign key for this table and primary key for the complaint table.

$query3 = "insert into complaint_details(order_nr,order_description,order_date,delivery_date,invoice_date,delivery_nr,order_amount,amount_paid,amount_outstanding)
values ('$ordernr','$orderdesc','$orderdate','$deliverydate','$invoicedate','$deliverynr','$orderamount','$paidamount','$outstandamount')";

Barand · December 3, 2013



$query2 = "insert into complaint(complain,d_name,complainant_id) values ('$complain_det','$comp_name','{$row['complainant_id']}')";


mysql_query($query2);


$the_auto_id_that_was_just_created = mysql_insert_id();


// now use that value as the foreign key when you add the detail record

Parkie02 · December 4, 2013

Thank you. Can somebody tell me why there is now added two complaint records to the complaint table and how I can fix it. Here is the new code

<?php
session_start();

if(!$_SESSION['email'])
{
	header("location:login.php");
}
?>

<html>
	<head>
		<title> 
			Complaint
		</title>
	</head>
	
<body>
<form method='post' action='complaint.php'>
	<table width='800' border='10' align='left'>
	<tr>
		<td align='center' colspan='5'><h1>Complaint Form</h1></td>
	</tr>

	<tr>
		<td>Comany's Name:</td>
		<td><input type='text' name='comp_name' /></td>
	</tr>
	
	<tr>
		<td>Registration Number:</td>
		<td><input type='text' name='reg_nr' /></td>
	</tr>
	
	<tr>
		<td>Email:</td>
		<td><input type='text' name='comp_email' /></td>
	</tr>
	
	<tr>
		<td>Contact Person:</td>
		<td><input type='text' name='comp_contact_person' /></td>
	</tr>
	
	<tr>
		<td>Contact Number:</td>
		<td><input type='text' name='comp_contactnr' /></td>
	</tr>
	
	<tr>
		<td>Details of complain:</td>
		<td><input type='text' name='complain_details' /></td>
	</tr>
	
	<tr>
		<td>Order Number:</td>
		<td><input type='text' name='order_nr' /></td>
	</tr>
	
	<tr>
		<td>Order Description:</td>
		<td><input type='text' name='order_desc' /></td>
	</tr>
	
	<tr>
		<td>Order Date:</td>
		<td><input type='date' name='order_date' /></td>
	</tr>
	
	<tr>
		<td>Delivery Date:</td>
		<td><input type='date' name='delivery_date' /></td>
	</tr>
	
	<tr>
		<td>Invoice Date:</td>
		<td><input type='date' name='invoice_date' /></td>
	</tr>
	
	<tr>
		<td>Delivery Number:</td>
		<td><input type='text' name='delivery_nr' /></td>
	</tr>
	
	<tr>
		<td>Order Amount:</td>
		<td><input type='number' step='0.01' name='order_amount' /></td>
	</tr>
	
	<tr>
		<td>Amount Already Paid:</td>
		<td><input type='number' step='0.01' name='amount_paid' /></td>
	</tr>
	
	<tr>
		<td>Amount Outstanding:</td>
		<td><input type='number' step='0.01' name='amount_outstanding' /></td>
	</tr>
	
	<tr>
		<td colspan='5' align='center'><input type='submit' name='add_debtor' value='Submit' /></td>
	</tr>
	
	
	</table>
</form>
</body>
</html>
<?php
mysql_connect("localhost","root","mj2015");
mysql_select_db("whodidntpay");

$log = $_SESSION['email'];

	if(isset($_POST['add_debtor']))
	{
		$comp_name = $_POST['comp_name'];
		$comp_regnr = $_POST['reg_nr'];
		$comp_email = $_POST['comp_email'];
		$comp_contact_person = $_POST['comp_contact_person'];
		$comp_contact_nr = $_POST['comp_contactnr'];
		$complain_det = $_POST['complain_details'];
		$ordernr = $_POST['order_nr'];
		$orderdesc = $_POST['order_desc'];
		$orderdate = $_POST['order_date'];
		$deliverydate = $_POST['delivery_date'];
		$invoicedate = $_POST['invoice_date'];
		$deliverynr = $_POST['delivery_nr'];
		$orderamount = $_POST['order_amount'];
		$paidamount = $_POST['amount_paid'];
		$outstandamount = $_POST['amount_outstanding'];
		
		if($comp_name=='')
		{
			echo "<script>alert('Please Enter Company Name')</script>";
			exit();
		}
		
		if($comp_regnr=='')
		{
			echo "<script>alert('Please Enter Company Registration Number')</script>";
			exit();
		}
		
		if($comp_email=='')
		{
			echo "<script>alert('Please Enter Company Email')</script>";
			exit();
		}
		
		if($comp_contact_person=='')
		{
			echo "<script>alert('Please Enter Contact Person')</script>";
			exit();
		}
		
		if($comp_contact_nr=='')
		{
			echo "<script>alert('Please Enter Contact Number')</script>";
			exit();
		}
		
		if($complain_det=='')
		{
			echo "<script>alert('Please Enter Complain Details')</script>";
			exit();
		}
		
		if($ordernr=='')
		{
			echo "<script>alert('Please Enter Order Number')</script>";
			exit();
		}
		
		if($orderdesc=='')
		{
			echo "<script>alert('Please Enter Order Description')</script>";
			exit();
		}
		
		if($orderdate=='')
		{
			echo "<script>alert('Please Enter Order Date')</script>";
			exit();
		}
		
		if($deliverydate=='')
		{
			echo "<script>alert('Please Enter Delivery Date')</script>";
			exit();
		}
		
		if($invoicedate=='')
		{
			echo "<script>alert('Please Enter Invoice Date')</script>";
			exit();
		}
		
		if($deliverynr=='')
		{
			echo "<script>alert('Please Enter Delivery Number')</script>";
			exit();
		}
		
		if($orderamount=='')
		{
			echo "<script>alert('Please Enter Order Amount')</script>";
			exit();
		}
		
		if($paidamount=='')
		{
			echo "<script>alert('Please Enter Amount Paid')</script>";
			exit();
		}
		
		if($outstandamount=='')
		{
			echo "<script>alert('Please Enter The Amount Outstanding')</script>";
			exit();
		}
		
		$num_debt = "select * from debtor where d_name= '$comp_name' AND registration_nr= '$comp_regnr'";
		$run = mysql_query($num_debt);
		$num_comp = mysql_num_rows($run);
		
		if ($num_comp ==0)
		$query = "insert into debtor(d_name,registration_nr,email,contact_person,contact_number,companies_not_paid,amount_not_paid) 
		values ('$comp_name','$comp_regnr','$comp_email','$comp_contact_person','$comp_contact_nr','1','$outstandamount')";
		if ($num_comp ==1)
		$query = "update debtor set companies_not_paid = companies_not_paid + 1, amount_not_paid = amount_not_paid + '$outstandamount' where d_name='$comp_name'";
		
		if (mysql_query($query))
		{
			echo "<script>alert('Company Added Successful')</script>";
		}
		
		$complain_id = "select complainant_id from complainant where email = '$log'";
		$run2=mysql_query($complain_id);
		
		$row = mysql_fetch_assoc($run2); 	
		$query2 = "insert into complaint(complain,d_name,complainant_id) values ('$complain_det','$comp_name','{$row['complainant_id']}')";

		mysql_query($query2);

		$the_auto_id_that_was_just_created = mysql_insert_id();

		if (mysql_query($query2))
		{
			echo "<script>alert('Complaint Added Successful')</script>";
		}
	
		$query3 = "insert into complaint_details(order_nr,order_description,order_date,delivery_date,invoice_date,delivery_nr,complaint_nr,order_amount,amount_paid,amount_outstanding)
		values ('$ordernr','$orderdesc','$orderdate','$deliverydate','$invoicedate','$deliverynr','$the_auto_id_that_was_just_created','$orderamount','$paidamount','$outstandamount')";
		
		if (mysql_query($query3))
		{
			echo "<script>alert('Complaint Details Added Successful')</script>";
		}
	}	

?>

Ch0cu3r · December 4, 2013

Because you are running the insert query twice for the complaint table ($query2). This is will cause two entries.

mysql_query($query2);

$the_auto_id_that_was_just_created = mysql_insert_id();

if (mysql_query($query2))
{
	echo "<script>alert('Complaint Added Successful')</script>";
}

Sign In

Display a foreign key value

Recommended Posts

Parkie02

Link to comment

Share on other sites

Parkie02

Link to comment

Share on other sites

BrodaNoel

Link to comment

Share on other sites

Parkie02

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

Parkie02

Link to comment

Share on other sites

Ch0cu3r

Link to comment

Share on other sites

Archived

Browse

Activity

Important Information