PDA

View Full Version : inserting data into 2 tables


Chris b
06-12-2006, 03:50 PM
I have one database which has 2 tables.

The first table is for project description content and the second is to store multiple image information associated to each project.

How can I insert the project description and pass the newly created projectID (auto_number) to a second insert page from which I can add the images?


TABLE `projects` (
`auto_id` smallint(6) NOT NULL auto_increment,
`sort_order` smallint(2) NOT NULL default '0',
`visible` char(1) NOT NULL default 'Y',
`project` varchar(100) default NULL,
`address` tinytext NOT NULL,
`project_type` tinytext NOT NULL,
`challenge` mediumtext NOT NULL,
`solution` mediumtext NOT NULL,
`thumbnail_image` varchar(100) NOT NULL default '',
`link` tinytext NOT NULL,
PRIMARY KEY (`auto_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


TABLE `images` (
`auto_id` smallint(6) NOT NULL auto_increment,
`projectID` varchar(6) NOT NULL default '',
`sort_order` smallint(2) NOT NULL default '0',
`visible` char(1) NOT NULL default '',
`image` varchar(100) NOT NULL default '',
PRIMARY KEY (`auto_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 ;



Thanks,
Chris

nanny
06-16-2006, 12:26 AM
Hi Chris
You can use the php method of mysql_insert_id()
What it does is if there is an auto_increment id in your insert record - it records it and allows you to use it in the next record by giving it a value.
e.g. if you do an insert record for projects at the end of the insert record you will see something like this:
mysql_select_db($database_config, $config);
$Result1 = mysql_query($insertSQL, $config) or die(mysql_error());

Straight after this you will need to do this:$autoid = mysql_insert_id();
So it will now look like this:
mysql_select_db($database_config, $config);
$Result1 = mysql_query($insertSQL, $config) or die(mysql_error());
$autoid = mysql_insert_id();

I think you can then do a left join or inner join in the recordset for the next insert record for images and use the autoid as a hidden field and give it a value of the $autoid

I had the same trouble but found that I used the mysql_insert_id(); and the recordset together and it seems to work. If you do a google search on the mysql_inser_id(); you might get a better answer.
Cheers
Nanny