Jump to content


Photo

insert_last-id() or something else better?


  • Please log in to reply
3 replies to this topic

#1 kenwvs

kenwvs
  • Members
  • PipPipPip
  • Advanced Member
  • 194 posts

Posted 07 October 2006 - 05:00 PM

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.

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

	}
}

this next one is for the images

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());
			}
Thanks,

Ken

#2 fenway

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

Posted 09 October 2006 - 05:45 AM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 kenwvs

kenwvs
  • Members
  • PipPipPip
  • Advanced Member
  • 194 posts

Posted 09 October 2006 - 07:37 AM

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

#4 fenway

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

Posted 09 October 2006 - 04:56 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users