help using mysql_insert_id();

[theG-Scott]

I have two tables in my db. One table contains portfolio images with a primay key set to AI. The other table contains all of the thumbnails that need to be associated with each portfolio image with no primary key. I'm trying to grab the last inserted id of the portfolio image and enter it into my table of thumbnails so each group of thumbnails is associated to the correct portfolio image by the id. I'm using the following code:

if (isset($_POST['btnSubmit'])) {

//...........

foreach ( $_FILES['small_tn_']['name'] as $key=>$value1 )
	{
		$smallImage=$_FILES['small_tn_']['name'][$key];
		$largeImage=$_FILES['large_image_']['name'][$key];
		$overlay_id= mysql_insert_id();
		$sql_overlay = sprintf("INSERT INTO 'overlay'('overlay_id','small_tn','large_img') VALUES ('%s','%s','%s')",$overlay_id,$smallImage, $largeImage );  
		$db->query($sql_overlay);
	}
}
//............
}

...to insert values from a form with dynamic fields into my db. When I don't have this line included (along with the corresponding variables):

$overlay_id= mysql_insert_id();

...everything submits fine with the exception of the id of course. When that line of code is included (along with the corresponding variables): I receive this error:

Query failed: INSERT INTO 'overlay'('overlay_id','small_tn','large_img') VALUES ('82','imagine_tn01.jpg','imagine-full.jpg')

"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''overlay'('overlay_id','small_tn','large_img') VALUES ('82','imagine_tn01.jpg','' at line 1"

Could somebody please give me insight as to what I'm doing wrong? I'm a newb so I'm learning as much as I can. I hope I explained everything clearly.

Thanks for the help...

[sudeepjd]

You should not put the field names or table names in quotes. In SQL field names are variables not string literals. Only string literals should be put in quotes. So remove the quotes around the field names and the table name and it should work. $sql_overlay = sprintf("INSERT INTO overlay (overlay_id,small_tn,large_img) VALUES ('%s','%s','%s')",$overlay_id,$smallImage, $largeImage ); As per coding practices however, it is better to always encapsulate your field names. This can be done by using the backquotes (`). (The button before the 1 key on the keyboard). So this would make your statement. $sql_overlay = sprintf("INSERT INTO `overlay` (`overlay_id`,`small_tn`,`large_img`) VALUES ('%s','%s','%s')",$overlay_id,$smallImage, $largeImage ); All the best.

[theG-Scott]

Hey, thanks for the reply...that seemed to do the trick. The only issue I'm having now is that it only inserted the id that was pulled using mysql_insert_id once and then returned 0's for the rest (see attached). I need the id that is pulled to be assigned to each row for the group of images in the array submitted from the form if that makes sense.

I'm slowly learning so thank you for explaining why you did what you did.

[sudeepjd]

That is because the mysql_insert_id() pulls the last insert_id. You are currently in a for each loop and are inserting into a table which does not have an auto_increment i.e., as you say no primary key.

In order to get around this move the $overlay_id= mysql_insert_id(); before the foreach loop. I am assuming the value that the $overlay_id would be the same for all the INSERT queries for the current execution of the foreach loop. This will prevent the $overlay_id resetting itself.

[theG-Scott]

That was it, everything is working perfectly now! Thanks again for you help, I wish you the best.