Jump to content

Archived

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

kenwvs

insert_last-id() or something else better?

Recommended Posts

I have developed a form that employees will fill out and then the results are saved in the mysql database.  I have a table called workorder, which stores most of the results.  I also have a table called parts, where the parts required are stored and an image table, where the images for this workorder are saved.

I also have a workorder_image and workorder_parts table, to tie everything together.  I am not sure how to get the information into the JOIN tables.  The workorder, image and parts table all use a unique key, auto incrementing and I thought it would be these numbers that would tie it all together.  There could be up to 4 rows of images and 7 rows of parts for each workorder.  If I am trying to tie the images to the workorder table, would I be better off using the workorder number (which is the Month, Hour, Second format) and the image number, to tie them together?

I hope this makes sense.......it is driving me crazy.  I had it all setup using one table and was advised this was bad as far as database normalization is concerned, so I have changed things around, but have gotten in over my head.  This is my first project using a database.

Here is the queries I am using right now.

[code]{
  if (!empty($val))
  {
  $query = "INSERT IGNORE INTO parts (description,number) VALUES ('" . $description[$key] . "', '" . $val . "')";
mysql_query($query) or die(mysql_error().$query);
}
}


mysql_query("INSERT INTO workorder (work, name, sched, site, serial, hours, starts, issue, severity, resolution, assistance, safety) VALUES ('$work', '$name', '$sched', '$site', '$serial', '$hours', '$starts', '$issue', '$severity', '$resolution', '$assistance', '$safety')") or die(mysql_error());
//mysql_query("INSERT INTO workorder_parts (number, workorder_work_id) VALUES ('number[]', 'last_insert_id')")or die(mysql_error());
mysql_query("INSERT INTO workorder_image(image_image_id,workorder_work_id) VALUES ('$image_id', 'last_insert_id()')")or die(mysql_error());

}
}[/code]

this next one is for the images

[code]if ($_FILES['upload']['error'][$i] == 0)
{
$uploaddir = '/home/forsa7/public_html/GE/GEUploads/';

$uploadfile = $uploaddir . basename($_FILES['upload']['name'][$i]);
$file_name = basename($_FILES['upload']['name'][$i]);

if (!move_uploaded_file($_FILES['upload']['tmp_name'][$i], $uploadfile))
{
    die("<br><br>File could not be uploaded.");
}

// After each file is successfully uploaded, record file name in DB
mysql_query("INSERT INTO `image` (Upload) VALUES ('$file_name')")or die(mysql_error());
}[/code]
Thanks,

Ken

Share this post


Link to post
Share on other sites
I'm not sure I understand the question -- you should, of course, be linking these tables with FKs to the primary key, usually the auto-increment value.

Share this post


Link to post
Share on other sites
I am not understanding how to get the auto-increment value.  As there could be 4 images (or seven parts), if I use the mysql_insert_id(); I will only get the last number, and be missing the ones generated earlier in the same query

Share this post


Link to post
Share on other sites
Well, you need to "keep track" of the most recently added ID in your PHP script, and then pass this as a variable to subsequent INSERT calls.

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.