Jump to content


Relational Databases

  • Please log in to reply
3 replies to this topic

#1 kenwvs

  • Members
  • PipPipPip
  • Advanced Member
  • 194 posts

Posted 07 September 2006 - 05:59 AM

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 TABLE    where TABLE is the workorder, parts or employees tables, or is it the workorder_parts, etc. table.

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:

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());

#2 drranch

  • Members
  • PipPipPip
  • Advanced Member
  • 78 posts

Posted 07 September 2006 - 06:32 AM

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'.

#3 fenway

  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 07 September 2006 - 12:44 PM

As for relating the records, simply pull back LAST_INSERT_ID().
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#4 obsidian

  • Staff Alumni
  • Advanced Member
  • 3,202 posts
  • LocationSeattle, WA

Posted 07 September 2006 - 12:47 PM

As for relating the records, simply pull back LAST_INSERT_ID().

or, you could use the PHP function mysql_insert_id(). same result
You can't win, you can't lose, you can't break even... you can't even get out of the game.

while (count($life->getQuestions()) > 0)
{   $life->study(); } ?>
  LINKS: PHP: Manual MySQL: Manual PostgreSQL: Manual (X)HTML: Validate It! CSS: A List Apart | IE bug fixes | Zen Garden | Validate It! JavaScript: Reference Cards RegEx: Everything RegEx

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users