Jump to content


This topic is now archived and is closed to further replies.


Relational Databases

Recommended Posts

I have a designed a form that people will fill out to generate a work order for a piece of equipment when it fails.

I have created relational (I think) databases, where the tables are all normalized, but am not sure how to tie it all together now.

As an example I have the following tables, with their purpose:

workorder - majority of the data goes in this table
parts - the parts number and parts descriptions go in this table
employees - this table contains the employees data and the employee who generated the workorder go in here

workorder_parts - to tie the workorder and parts tables together
workorder_employee - to tie the workorder and employee tables together

My host does not offer innodb table type so I need to do this manually

When I create the query to INSERT the data to the DB table, am I using INSERT INTO [color=red]TABLE[/color]    where [color=red]TABLE is the workorder, parts or employees tables, or is it the workorder_parts, etc. table.[/color]

I am thinking it is the main (parent) tables, but I don't understand how I will tie them together.  As an example, if I use the parts table, how do I tie it together so that the parts number/description in the parts table are referenced to the workorder number they were generated from?

Here is the code I am using:

[code]if (!isset($_POST['Submit']))
$work = $_POST["work"];
$sched = $_POST["sched"];
$name = $_POST["tech"];
$site = $_POST["site"];
$serial = $_POST["serial"];
$hours = $_POST["hours"];
$starts = $_POST["starts"];
$issue = $_POST["issue"];
$severity = $_POST["severe"];
$resolution = $_POST["resolve"];
$assistance = $_POST["assist"];
$safety = $_POST["safe"];
    $number = $_POST["number"];
    $description = $_POST["description"];

if (!empty($_POST['number']))
  foreach($_POST['description'] as $key=>$description) {
  $description_esc = mysql_real_escape_string($description);
  $number = intval($_POST['number'][$key]);
  $query = "INSERT INTO parts (description,number) VALUES ("
    . "'{$description_esc}', "
    . "{$number} )";
mysql_query($query) or die(mysql_error().$query);

mysql_query ("Insert into `workorder`(work, sched, site, serial, hours,
starts, issue, severity, resolution, assistance, safety) VALUES
('$work', '$sched', '$site', '$serial', '$hours', '$starts',
'$issue', '$severity', '$resolution', '$assistance', '$safety')")or die(mysql_error());

mysql_query ("Insert into `employees` (name) VALUES ('$name')")or die(mysql_error());

Share this post

Link to post
Share on other sites
you would setup the following in your database...

One to many relationship -

Employee would be one and the workorder would be many.
So each employe can have many workorder records associated with them.

Table structure
make sure that your workorder table has a field for the employee name and set it as the 'key' for that table.  This will prevent more than one employee getting added to this table.

I don't think you want to do the one to many relationships with the parts/workorder section as many parts can be associated with many workorders and vice versa.

What program are you using to upload your tables with? I use Plesk and the 'key' terminology is 'unique'.

Share this post

Link to post
Share on other sites
As for relating the records, simply pull back LAST_INSERT_ID().

Share this post

Link to post
Share on other sites
[quote author=fenway link=topic=107201.msg429836#msg429836 date=1157633058]
As for relating the records, simply pull back LAST_INSERT_ID().

or, you could use the PHP function mysql_insert_id(). same result

Share this post

Link to post
Share on other sites


Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.