help with PHP and foreign keys

[heroprotagonist]

Hello all,

 

I am trying to accomplish what I perceive as a simple task but I am having some trouble.

I have a db with several tables and I am trying to get some data from one table to the next so that I can reference one of two fields later if I need to. By way of example I will give you info on two of my tables. One is sort of a general information field. It is called tbl_gen_data. The other is called tbl_shrinkage_data. The first table has 8 fields labeled id, name, material, date, lab_report_no, lot_no, finish, yards and id is the key field. The second table has far more fields but for example purposes this table also has an id and lab_report_no field. What I need to accomplish is from the little report I generate I need clickable links to other entry screens that have pulled the id or lab_report_field and populated that data into the other table so that I can then continue entering more data that is specific to that table but has some duplicate field that I can use as a foreign key so later if I want to display this information in a report I have something to reference in a query with a join statement.

 

So here is the code for my report page and then here is the code for the page I am having and issue with.

 

//code for report.php This works fine..

 

<?php
if(!isset($cmd)) 
$id=$_GET['id'];
include 'config.php';
include 'opendb.php';

mysql_select_db($dbname) or die( "Unable to select database");
$query=("SELECT * FROM `tbl_gen_data` ORDER BY `lab_report_no` ASC LIMIT 0, 30 ");
$result=mysql_query($query);

$num=mysql_numrows($result);

mysql_close();

echo "<b><center>Database Output</center></b><br><br>";
?>
<center>
<table width="738" border="0" cellpadding="2" cellspacing="2" bgcolor="#D4DDFF">
<tr>
<th><font face="Arial, Helvetica, sans-serif">ID</font></th>
<th><font face="Arial, Helvetica, sans-serif">Name</font></th>
<th><font face="Arial, Helvetica, sans-serif">Material</font></th>
<th><font face="Arial, Helvetica, sans-serif">Date</font></th>
<th><font face="Arial, Helvetica, sans-serif">Lab Report No</font></th>
<th><font face="Arial, Helvetica, sans-serif">Lot No</font></th>
<th><font face="Arial, Helvetica, sans-serif">Finish</font></th>
<th><font face="Arial, Helvetica, sans-serif">Yards</font></th>
</tr>

<?
$i=0;
while ($i < $num) {

$id=mysql_result($result,$i,"id");
$name=mysql_result($result,$i,"name");
$material=mysql_result($result,$i,"material");
$date=mysql_result($result,$i,"date");
$lab_report_no=mysql_result($result,$i,"lab_report_no");
$lot_no=mysql_result($result,$i,"lot_no");
$finish=mysql_result($result,$i,"finish");
$yards=mysql_result($result,$i,"yards");
?>
<tr>
<td><font face="Arial, Helvetica, sans-serif"><? echo $id; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo $name; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo $material; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo $date; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo $lab_report_no; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo $lot_no; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo $finish; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo $yards; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo "<a href='updateview3.php?cmd=edit&id=$id'>$lab_report_no - Edit</a>";?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo "<a href='updateview_shrink4.php?cmd=edit&id=$id'>$lab_report_no - Edit Shrink</a>";?></font></td>
</tr>

<?

$i++;
}
echo "</table>";
echo "</div>";
echo "<hr>";
?>

 

//code that has issues... update_shrink4.php

 

<?php

include 'config.php';
include 'opendb.php';




$query=" SELECT * FROM `tbl_shrinkage_data` WHERE id='$id'";

$result=mysql_query($query);
$num=mysql_numrows($result);
//mysql_close();

$i=0;
while ($i < $num) {

$lab_report_no=mysql_result($result,$i,"lab_report_no");

++$i;
}

if (empty ($lab_report_no)) {

$sql = " INSERT INTO `labdb`.`tbl_shrinkage_data`(lab_report_no)
SELECT `tbl_gen_data`.`lab_report_no`
FROM `labdb`.`tbl_report_data`
WHERE id='$id'";


} else {
header("Location: shrink_entry.php"); 
echo "ERROR: ".mysql_error();
}

?>

 

Thanks for any help.

 

[radi8]

Do a join on the 2 tables:

select a.value, b.value
from table1 a inner join table2 b
on (a.key1 = b.key1 and a.key2=b.key2)
where condition1 = value

[heroprotagonist]

Thanks for the quick reply! However I was afraid I might not have been clear about what my issue was with the second page of code..

 

The second page of code is supposed to check and see if the lab_report_no field is empty if so then perform the insert query below which should copy the proper lab report number from the general table to the shrinkage table based on the id fields. Then it returns the lab report number in a new form and the rest of the entry fields are empty. So that I can then enter all of my shrinkage data and use an update query to write the changes.  If it is not empty it should redirect to another page which just displays the information and I can update if I want.

 

Thanks

 

 

 

//code

 

<?php

include 'config.php';
include 'opendb.php';




$query=" SELECT * FROM `tbl_shrinkage_data` WHERE id='$id'";

$result=mysql_query($query);
$num=mysql_numrows($result);
//mysql_close();

$i=0;
while ($i < $num) {

$lab_report_no=mysql_result($result,$i,"lab_report_no");

++$i;
}

if (empty ($lab_report_no)) {

$sql = " INSERT INTO `labdb`.`tbl_shrinkage_data`(lab_report_no)
SELECT `tbl_gen_data`.`lab_report_no`
FROM `labdb`.`tbl_report_data`
WHERE id='$id'";


} else {
header("Location: shrink_entry.php"); 
echo "ERROR: ".mysql_error();
}

?>

[radi8]

you can pass the data 2 ways, through the URL or through SESSIONS, either are ok. In you case, pass it via the URL like:

www.yoururl.com?id=$id&loc=$loc&otherstuff=$otherstuff

 

on the other page, retrieve it with

<?php 
$id=$_GET['id'];
$loc=$_GET['loc'];
$otherstuff=$_GET['otherstuff'];
//then use it in the 
$query=" SELECT * FROM `tbl_shrinkage_data` WHERE id='$id'";
?>

 

is that what you are looking for?

[heroprotagonist]

you can pass the data 2 ways, through the URL or through SESSIONS, either are ok. In you case, pass it via the URL like:

www.yoururl.com?id=$id&loc=$loc&otherstuff=$otherstuff

 

on the other page, retrieve it with

<?php 
$id=$_GET['id'];
$loc=$_GET['loc'];
$otherstuff=$_GET['otherstuff'];
//then use it in the 
$query=" SELECT * FROM `tbl_shrinkage_data` WHERE id='$id'";
?>

 

is that what you are looking for?

 

I tried that in one of my earlier attempts but I ran into an issue when I submit the information. This is an earlier code iteration that I used and never worked. In this attempt I passed the id and lab_report_no varibles from the report page to a page that should echo those fields and display a third. Which it does but when I run a form action on it to collect further data specifically the test1_roll_no and write all three to the tbl_shrinkage_data table it will only post the test1_roll_no field to the database and it is in the wrong field in the database it ends up in the lab_report_no column. The rest of the fields in the database are empty. It should write $id, $lab_report_no, and $test1_roll_no in the proper fields in the tbl_shrinkage_data table in the db.  Here is the code.

 

//code

 

<td><font face="Arial, Helvetica, sans-serif"><? echo "<a href='updateview_shrink5.php?cmd=edit&id=$id&lab_report_no=$lab_report_no'>$lab_report_no - Edit Shrink</a>";?></font></td>

 

//code-updateview_shrink5.php

 

<?php

$id=$_GET['id'];

$lab_report_no=$_GET['lab_report_no'];

 

 

include 'config.php';

include 'opendb.php';

 

?>

<form action="shrink_entry.php" method="post">

      <strong>ID:</strong><input type="text" name="id" value="<? echo $id; ?>">

<strong>Lab Report No:</strong><input type="text" name="lab_report_no"value="<? echo $lab_report_no; ?>">

<strong>Test1 Roll No:</strong><input type="text" name="test1_roll_no">

<hr>

 

 

      <input type="submit" name="submit" value="submit">

 

      </form>

 

//code-shrink_entry.php

 

<?php

$id=$_GET['id'];

$lab_report_no=$_GET['lab_report_no'];

 

include 'config.php';

include 'opendb.php';

 

 

$id=$_POST['$id'];

$lab_report_no=$_POST['$lab_report_no'];

$test1_roll_no=$_POST['test1_roll_no'];

 

 

 

$sql = "INSERT INTO tbl_shrinkage_data VALUES ('$id','$lab_report_no','$test1_roll_no')";

 

 

 

if($result = mysql_query($sql)) {

echo '<h1>Thank you</h1>Your information has been entered into our database<br><br><a href="labdb_entry.php" title="New Entry" target="_self">New Entry</a><br><br><a href="main.php" title="Main" target="_self">Home</a><br><br><img src="images/city.jpg"<br><br><a href="labdb_entry.php" title="New Entry" target="_self">New Entry</a>';

} else {

echo "ERROR: ".mysql_error();

}

 

 

?>