Multiple inserts under one query

[woodburndesigns]

Hello,

I am trying to setup a query that will work well with transactions so I would like to put multiple inserts into different tables in one query. I know this is possible in mysql because I have run a similar query in phpmyadmin using teh ";" as the delimiter.

My current code is as such:

$query = "INSERT INTO nanny_registration (
						date_registered,
						date_expire,
						first_name,
						last_name,
						previous_name,
						title,
						dob,
						street_address,
						city,
						state,
						zip_code,
						primary_phone,
						secondary_phone,
						email,
						hashed_password,
						preferred_method,
						employment_type,
						hourly_wages,
						weekly_wages,
						two_years_childcare_experience,
						avmonth,
						avday,
						avyear,
						cities_towns,
						drivers_license,
						own_transportation,
						household_duties,
						ability_perform_duties,
						limitations,
						sun_availability,
						mon_availability,
						tues_availability,
						wed_availability,
						thurs_availability,
						fri_availability,
						sat_availability,
						exp1_st_mo,
						exp1_end_mo,
						exp1_st_yr,
						exp1_end_yr,
						exp1_description,
						exp2_st_mo,
						exp2_end_mo,
						exp2_st_yr,
						exp2_end_yr,
						exp2_description,
						exp3_st_mo,
						exp3_end_mo,
						exp3_st_yr,
						exp3_end_yr,
						exp3_description,
						exp4_st_mo,
						exp4_end_mo,
						exp4_st_yr,
						exp4_end_yr,
						exp4_description,
						ref1_name,
						ref1_city,
						ref1_state,
						ref1_phone,
						ref1_email,
						ref2_name,
						ref2_city,
						ref2_state,
						ref2_phone,
						ref2_email,
						ref3_name,
						ref3_city,
						ref3_state,
						ref3_phone,
						ref3_email,
						ref4_name,
						ref4_city,
						ref4_state,
						ref4_phone,
						ref4_email,
						willing_cpr,
						have_children,
						ages,
						special_needs_exp,
						special_needs_details,
						infant_care_exp,
						child_care_style,
						why_nanny,
						discipline,
						comfort,
						own_childhood,
						personal_description,
						convicted_crime,
						participate_drug_test,
						additional_check,
						smoker,
						smoker_refrain,
						accept_terms,
						how_did_you_hear_about_us
					) VALUES (
						'{$date_registered}',
						'{$date_expire}',
						'{$first_name}',
						'{$last_name}',
						'{$previous_name}',
						'{$title}',
						'{$dob}',
						'{$street_address}',
						'{$city}',
						'{$state}',
						'{$zip_code}',
						'{$primary_phone}',
						'{$secondary_phone}',
						'{$email}',
						'{$hashed_password}',
						'{$preferred_method}',
						'{$employment_type}',
						'{$hourly_wages}',
						'{$weekly_wages}',
						'{$two_years_childcare_experience}',
						'{$avmonth}',
						'{$avday}',
						'{$avyear}',
						'{$cities_towns}',
						'{$drivers_license}',
						'{$own_transportation}',
						'{$household_duties}',
						'{$ability_perform_duties}',
						'{$limitations}',
						'{$sun_availability}',
						'{$mon_availability}',
						'{$tues_availability}',
						'{$wed_availability}',
						'{$thurs_availability}',
						'{$fri_availability}',
						'{$sat_availability}',
						'{$exp1_st_mo}',
						'{$exp1_end_mo}',
						'{$exp1_st_yr}',
						'{$exp1_end_yr}',
						'{$exp1_description}',
						'{$exp2_st_mo}',
						'{$exp2_end_mo}',
						'{$exp2_st_yr}',
						'{$exp2_end_yr}',
						'{$exp2_description}',
						'{$exp3_st_mo}',
						'{$exp3_end_mo}',
						'{$exp3_st_yr}',
						'{$exp3_end_yr}',
						'{$exp3_description}',
						'{$exp4_st_mo}',
						'{$exp4_end_mo}',
						'{$exp4_st_yr}',
						'{$exp4_end_yr}',
						'{$exp4_description}',
						'{$ref1_name}',
						'{$ref1_city}',
						'{$ref1_state}',
						'{$ref1_phone}',
						'{$ref1_email}',
						'{$ref2_name}',
						'{$ref2_city}',
						'{$ref2_state}',
						'{$ref2_phone}',
						'{$ref2_email}',
						'{$ref3_name}',
						'{$ref3_city}',
						'{$ref3_state}',
						'{$ref3_phone}',
						'{$ref3_email}',
						'{$ref4_name}',
						'{$ref4_city}',
						'{$ref4_state}',
						'{$ref4_phone}',
						'{$ref4_email}',
						'{$willing_cpr}',
						'{$have_children}',
						'{$ages}',
						'{$special_needs_exp}',
						'{$special_needs_details}',
						'{$infant_care_exp}',
						'{$child_care_style}',
						'{$why_nanny}',
						'{$discipline}',
						'{$comfort}',
						'{$own_childhood}',
						'{$personal_description}',
						'{$convicted_crime}',
						'{$participate_drug_test}',
						'{$additional_check}',
						'{$smoker}',
						'{$smoker_refrain}',
						'{$accept_terms}',
						'{$how_did_you_hear_about_us}');
						INSERT INTO nanny_areas (nanny_ID, workable_area_ID) VALUES (1,2)";					
				$nanny_reg = mysql_query($query, $connection);

[benracer]

just out of interest why one?

[woodburndesigns]

I am trying to setup transactions and it seemed it would be easier to understand if I could do it all in a single query rather than breaking it up.

[benracer]

In my opinon its far easier to break it up,

$user = "xxx";
$pass = "xxx";
$db = "xxx";
$link =  mysql_connect( "host", $user, $pass );

mysql_select_db( $db, $link ) or die ( "Couldn't open $db: ".mysql_error() );

$query = "INSERT INTO x (name)VALUES('$name')";
mysql_query( $query, $link) or die( mysql_error() );

$query2 = "INSERT INTO x (name)VALUES('$name')";
mysql_query( $query2, $link) or die( mysql_error() );

ect...

[woodburndesigns]

So in setting up the transactions would you put the code "start transaction" at the beginning of the first query and "commit" at the end of the 2nd query? Does php read the two separate queries in line like that?

Thank you for all of your help.

[benracer]

for the example above like this...

$user = "xxx"; 
$pass = "xxx"; 
$db = "xxx"; 
$link =  mysql_connect( "host", $user, $pass ); 

mysql_select_db( $db, $link ) or die ( "Couldn't open $db: ".mysql_error() ); 

$query = "INSERT INTO nanny_registration ( 
                        date_registered, 
                        date_expire, 
                        first_name, 
                        last_name, 
                        previous_name, 
                        title, 
                        dob, 
                        street_address, 
                        city, 
                        state, 
                        zip_code, 
                        primary_phone, 
                        secondary_phone, 
                        email, 
                        hashed_password, 
                        preferred_method, 
                        employment_type, 
                        hourly_wages, 
                        weekly_wages, 
                        two_years_childcare_experience, 
                        avmonth, 
                        avday, 
                        avyear, 
                        cities_towns, 
                        drivers_license, 
                        own_transportation, 
                        household_duties, 
                        ability_perform_duties, 
                        limitations, 
                        sun_availability, 
                        mon_availability, 
                        tues_availability, 
                        wed_availability, 
                        thurs_availability, 
                        fri_availability, 
                        sat_availability, 
                        exp1_st_mo, 
                        exp1_end_mo, 
                        exp1_st_yr, 
                        exp1_end_yr, 
                        exp1_description, 
                        exp2_st_mo, 
                        exp2_end_mo, 
                        exp2_st_yr, 
                        exp2_end_yr, 
                        exp2_description, 
                        exp3_st_mo, 
                        exp3_end_mo, 
                        exp3_st_yr, 
                        exp3_end_yr, 
                        exp3_description, 
                        exp4_st_mo, 
                        exp4_end_mo, 
                        exp4_st_yr, 
                        exp4_end_yr, 
                        exp4_description, 
                        ref1_name, 
                        ref1_city, 
                        ref1_state, 
                        ref1_phone, 
                        ref1_email, 
                        ref2_name, 
                        ref2_city, 
                        ref2_state, 
                        ref2_phone, 
                        ref2_email, 
                        ref3_name, 
                        ref3_city, 
                        ref3_state, 
                        ref3_phone, 
                        ref3_email, 
                        ref4_name, 
                        ref4_city, 
                        ref4_state, 
                        ref4_phone, 
                        ref4_email, 
                        willing_cpr, 
                        have_children, 
                        ages, 
                        special_needs_exp, 
                        special_needs_details, 
                        infant_care_exp, 
                        child_care_style, 
                        why_nanny, 
                        discipline, 
                        comfort, 
                        own_childhood, 
                        personal_description, 
                        convicted_crime, 
                        participate_drug_test, 
                        additional_check, 
                        smoker, 
                        smoker_refrain, 
                        accept_terms, 
                        how_did_you_hear_about_us 
                    ) VALUES ( 
                        '{$date_registered}', 
                        '{$date_expire}', 
                        '{$first_name}', 
                        '{$last_name}', 
                        '{$previous_name}', 
                        '{$title}', 
                        '{$dob}', 
                        '{$street_address}', 
                        '{$city}', 
                        '{$state}', 
                        '{$zip_code}', 
                        '{$primary_phone}', 
                        '{$secondary_phone}', 
                        '{$email}', 
                        '{$hashed_password}', 
                        '{$preferred_method}', 
                        '{$employment_type}', 
                        '{$hourly_wages}', 
                        '{$weekly_wages}', 
                        '{$two_years_childcare_experience}', 
                        '{$avmonth}', 
                        '{$avday}', 
                        '{$avyear}', 
                        '{$cities_towns}', 
                        '{$drivers_license}', 
                        '{$own_transportation}', 
                        '{$household_duties}', 
                        '{$ability_perform_duties}', 
                        '{$limitations}', 
                        '{$sun_availability}', 
                        '{$mon_availability}', 
                        '{$tues_availability}', 
                        '{$wed_availability}', 
                        '{$thurs_availability}', 
                        '{$fri_availability}', 
                        '{$sat_availability}', 
                        '{$exp1_st_mo}', 
                        '{$exp1_end_mo}', 
                        '{$exp1_st_yr}', 
                        '{$exp1_end_yr}', 
                        '{$exp1_description}', 
                        '{$exp2_st_mo}', 
                        '{$exp2_end_mo}', 
                        '{$exp2_st_yr}', 
                        '{$exp2_end_yr}', 
                        '{$exp2_description}', 
                        '{$exp3_st_mo}', 
                        '{$exp3_end_mo}', 
                        '{$exp3_st_yr}', 
                        '{$exp3_end_yr}', 
                        '{$exp3_description}', 
                        '{$exp4_st_mo}', 
                        '{$exp4_end_mo}', 
                        '{$exp4_st_yr}', 
                        '{$exp4_end_yr}', 
                        '{$exp4_description}', 
                        '{$ref1_name}', 
                        '{$ref1_city}', 
                        '{$ref1_state}', 
                        '{$ref1_phone}', 
                        '{$ref1_email}', 
                        '{$ref2_name}', 
                        '{$ref2_city}', 
                        '{$ref2_state}', 
                        '{$ref2_phone}', 
                        '{$ref2_email}', 
                        '{$ref3_name}', 
                        '{$ref3_city}', 
                        '{$ref3_state}', 
                        '{$ref3_phone}', 
                        '{$ref3_email}', 
                        '{$ref4_name}', 
                        '{$ref4_city}', 
                        '{$ref4_state}', 
                        '{$ref4_phone}', 
                        '{$ref4_email}', 
                        '{$willing_cpr}', 
                        '{$have_children}', 
                        '{$ages}', 
                        '{$special_needs_exp}', 
                        '{$special_needs_details}', 
                        '{$infant_care_exp}', 
                        '{$child_care_style}', 
                        '{$why_nanny}', 
                        '{$discipline}', 
                        '{$comfort}', 
                        '{$own_childhood}', 
                        '{$personal_description}', 
                        '{$convicted_crime}', 
                        '{$participate_drug_test}', 
                        '{$additional_check}', 
                        '{$smoker}', 
                        '{$smoker_refrain}', 
                        '{$accept_terms}', 
                        '{$how_did_you_hear_about_us}')"; 

mysql_query( $query, $link) or die( mysql_error() ); 

$query2 = "INSERT INTO nanny_areas (nanny_ID, workable_area_ID) VALUES (1,2)"; 
mysql_query( $query2, $link) or die( mysql_error() ); 

if you are getting the data from a form use...

. $_GET['zip_code'] . instead of {$zip_code}

[laserlight]

woodburndesigns, you need to redesign your database schema. Read a tutorial on database normalisation, e.g., Database Normalization and Design Techniques.

[woodburndesigns]

Laserlight, how would you recommend setting up the database instead? Currently I have a 3 or 4 many to many relationship tables that contain various items such as workable areas, special needs experience and so forth. All of those many to many tables use the foreign key from the main nanny registration table.

Originally I was taking those workable areas and inserting them directly into the main nanny registration table as a comma delimited string. But was told that wasn't taking advantage of the power of relational databases.

[laserlight]

You probably do not need a main nanny registration table but can join the other tables.

[woodburndesigns]

I have included part of the database schema for you to check out. Could you give me some clues on how this could be more efficient. I want to do this the proper way and was under the impression I was doing that. If not please let me know what I need to change.

--
-- Table structure for table `nanny_areas`
--

CREATE TABLE `nanny_areas` (
  `nanny_ID` int(10) NOT NULL,
  `workable_area_ID` int(10) NOT NULL,
  KEY `nanny_ID` (`nanny_ID`),
  KEY `workable_area_ID` (`workable_area_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='many to many: list of nanny''s workable areas';

-- --------------------------------------------------------

--
-- Table structure for table `nanny_certification`
--

CREATE TABLE `nanny_certification` (
  `nanny_ID` int(10) NOT NULL,
  `certification_ID` int(10) NOT NULL,
  KEY `nanny_ID` (`nanny_ID`),
  KEY `certification_ID` (`certification_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `nanny_duties`
--

CREATE TABLE `nanny_duties` (
  `nanny_ID` int(10) NOT NULL,
  `household_duty_ID` int(10) NOT NULL,
  KEY `nanny_ID` (`nanny_ID`),
  KEY `household_duty_ID` (`household_duty_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `nanny_registration`
--

CREATE TABLE `nanny_registration` (
  `nanny_ID` int(10) NOT NULL auto_increment,
  `date_registered` date NOT NULL,
  `date_expire` date default NULL,
  `date_updated` date default NULL,
  `first_name` varchar(50) NOT NULL,
  `last_name` varchar(50) NOT NULL,
  `previous_name` varchar(50) default NULL,
  `title` varchar(5) default NULL,
  `dob` date NOT NULL,
  `street_address` varchar(100) NOT NULL,
  `city` varchar(50) NOT NULL,
  `state` varchar(2) NOT NULL,
  `zip_code` int(5) NOT NULL,
  `primary_phone` varchar(14) NOT NULL,
  `secondary_phone` varchar(14) default NULL,
  `email` varchar(50) NOT NULL,
  `hashed_password` varchar(40) NOT NULL,
  `preferred_method` varchar(5) NOT NULL,
  `employment_type` int(2) NOT NULL,
  `hourly_wages` varchar(10) NOT NULL,
  `weekly_wages` varchar(10) NOT NULL,
  `two_years_childcare_experience` varchar(3) NOT NULL,
  `avmonth` int(2) NOT NULL,
  `avday` int(2) NOT NULL,
  `avyear` int(4) NOT NULL,
  `cities_towns` text,
  `drivers_license` varchar(3) NOT NULL,
  `own_transportation` varchar(3) NOT NULL,
  `household_duties` varchar(100) default NULL,
  `ability_perform_duties` varchar(3) NOT NULL,
  `limitations` text,
  `sun_availability` varchar(30) NOT NULL,
  `mon_availability` varchar(30) NOT NULL,
  `tues_availability` varchar(30) NOT NULL,
  `wed_availability` varchar(30) NOT NULL,
  `thurs_availability` varchar(30) NOT NULL,
  `fri_availability` varchar(30) NOT NULL,
  `sat_availability` varchar(30) NOT NULL,
  `exp1_st_mo` int(2) NOT NULL,
  `exp1_end_mo` int(2) NOT NULL,
  `exp1_st_yr` int(4) NOT NULL,
  `exp1_end_yr` int(4) NOT NULL,
  `exp1_description` text NOT NULL,
  `exp2_st_mo` int(2) default NULL,
  `exp2_end_mo` int(2) default NULL,
  `exp2_st_yr` int(4) default NULL,
  `exp2_end_yr` int(4) default NULL,
  `exp2_description` text,
  `exp3_st_mo` int(2) default NULL,
  `exp3_end_mo` int(2) default NULL,
  `exp3_st_yr` int(4) default NULL,
  `exp3_end_yr` int(4) default NULL,
  `exp3_description` text,
  `exp4_st_mo` int(2) default NULL,
  `exp4_end_mo` int(2) default NULL,
  `exp4_st_yr` int(4) default NULL,
  `exp4_end_yr` int(4) default NULL,
  `exp4_description` text,
  `ref1_name` varchar(100) NOT NULL,
  `ref1_year` int(4) NOT NULL,
  `ref1_city` varchar(50) NOT NULL,
  `ref1_state` varchar(2) NOT NULL,
  `ref1_phone` varchar(14) NOT NULL,
  `ref1_email` varchar(50) default NULL,
  `ref2_name` varchar(100) default NULL,
  `ref2_year` int(4) default NULL,
  `ref2_city` varchar(50) default NULL,
  `ref2_state` varchar(2) default NULL,
  `ref2_phone` varchar(14) default NULL,
  `ref2_email` varchar(50) default NULL,
  `ref3_name` varchar(100) default NULL,
  `ref3_year` int(4) default NULL,
  `ref3_city` varchar(50) default NULL,
  `ref3_state` varchar(2) default NULL,
  `ref3_phone` varchar(14) default NULL,
  `ref3_email` varchar(50) default NULL,
  `ref4_name` varchar(100) default NULL,
  `ref4_year` int(4) default NULL,
  `ref4_city` varchar(50) default NULL,
  `ref4_state` varchar(2) default NULL,
  `ref4_phone` varchar(14) default NULL,
  `ref4_email` varchar(50) default NULL,
  `willing_cpr` varchar(3) NOT NULL,
  `have_children` varchar(3) NOT NULL,
  `ages` text,
  `special_needs_exp` varchar(100) default NULL,
  `special_needs_details` text,
  `infant_care_exp` varchar(3) NOT NULL,
  `child_care_style` text NOT NULL,
  `why_nanny` text NOT NULL,
  `discipline` text NOT NULL,
  `comfort` text NOT NULL,
  `own_childhood` text NOT NULL,
  `personal_description` text NOT NULL,
  `convicted_crime` varchar(3) NOT NULL,
  `participate_drug_test` varchar(3) NOT NULL,
  `additional_check` varchar(3) NOT NULL,
  `smoker` varchar(3) NOT NULL,
  `smoker_refrain` varchar(3) default NULL,
  `accept_terms` varchar(45) NOT NULL,
  `how_did_you_hear_about_us` int(2) NOT NULL,
  `photo` varchar(100) default NULL,
  `photo_approved` int(11) NOT NULL default '0',
  `profile_visible` tinyint(1) NOT NULL default '1',
  `profile_status` char(1) NOT NULL default '1',
  PRIMARY KEY  (`nanny_ID`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=23 ;

-- --------------------------------------------------------

--
-- Table structure for table `nanny_review`
--

CREATE TABLE `nanny_review` (
  `nanny_ID` int(10) NOT NULL,
  `parent_ID` int(10) NOT NULL,
  `review_score` int(3) NOT NULL,
  KEY `nanny_ID` (`nanny_ID`),
  KEY `parent_ID` (`parent_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Store and Calculate Nanny Reviews';

-- --------------------------------------------------------

--
-- Table structure for table `nanny_special_needs`
--

CREATE TABLE `nanny_special_needs` (
  `nanny_ID` int(10) NOT NULL,
  `special_need_ID` int(10) NOT NULL,
  KEY `nanny_ID` (`nanny_ID`),
  KEY `special_need_ID` (`special_need_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `parent_favorites`
--

CREATE TABLE `parent_favorites` (
  `parent_ID` int(10) NOT NULL,
  `nanny_ID` int(10) NOT NULL,
  KEY `parent_ID` (`parent_ID`),
  KEY `nanny_ID` (`nanny_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Constraints for table `nanny_areas`
--
ALTER TABLE `nanny_areas`
  ADD CONSTRAINT `nanny_areas_ibfk_2` FOREIGN KEY (`nanny_ID`) REFERENCES `nanny_registration` (`nanny_ID`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `nanny_areas_ibfk_1` FOREIGN KEY (`workable_area_ID`) REFERENCES `workable_areas` (`workable_area_ID`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `nanny_certification`
--
ALTER TABLE `nanny_certification`
  ADD CONSTRAINT `nanny_certification_ibfk_4` FOREIGN KEY (`certification_ID`) REFERENCES `certifications` (`certification_ID`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `nanny_certification_ibfk_3` FOREIGN KEY (`nanny_ID`) REFERENCES `nanny_registration` (`nanny_ID`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `nanny_duties`
--
ALTER TABLE `nanny_duties`
  ADD CONSTRAINT `nanny_duties_ibfk_2` FOREIGN KEY (`household_duty_ID`) REFERENCES `household_duties` (`household_duty_ID`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `nanny_duties_ibfk_1` FOREIGN KEY (`nanny_ID`) REFERENCES `nanny_registration` (`nanny_ID`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `nanny_review`
--
ALTER TABLE `nanny_review`
  ADD CONSTRAINT `nanny_review_ibfk_2` FOREIGN KEY (`parent_ID`) REFERENCES `parent_registration` (`parent_ID`),
  ADD CONSTRAINT `nanny_review_ibfk_1` FOREIGN KEY (`nanny_ID`) REFERENCES `nanny_registration` (`nanny_ID`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `nanny_special_needs`
--
ALTER TABLE `nanny_special_needs`
  ADD CONSTRAINT `nanny_special_needs_ibfk_2` FOREIGN KEY (`special_need_ID`) REFERENCES `special_needs` (`special_need_ID`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `nanny_special_needs_ibfk_1` FOREIGN KEY (`nanny_ID`) REFERENCES `nanny_registration` (`nanny_ID`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `parent_favorites`
--
ALTER TABLE `parent_favorites`
  ADD CONSTRAINT `parent_favorites_ibfk_2` FOREIGN KEY (`nanny_ID`) REFERENCES `nanny_registration` (`nanny_ID`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `parent_favorites_ibfk_1` FOREIGN KEY (`parent_ID`) REFERENCES `parent_registration` (`parent_ID`) ON DELETE CASCADE ON UPDATE CASCADE;