Trouble inserting data

[lucy]

Hi. I am trying to insert data into a table called CUSTOMER. This is all captured from a form. The input of this data goes into the CUSTOMER table, and the customer is assigned a 'cid' which is the primary key and auto incrementing.

 

I also insert data into the SERVICE table, which is yet again captured from the input of the same form.

 

The problem begins when i try to link the 'cid' (which has just been auto assigned to the customer), to the 'cid' in the SERVICE table.

 

Basically, i want to be able to show which services a customer has bought.

 

I have made 'cid' in the SERVICE table a foreign key, which references CUSTOMER(cid).

 

The only data that are showing in the 'cid' field of SERVICE is 0 in every entry, and not the correct 'cid' for the customer which has just made the purchase.

 

Any help will be readily accepted.

 

Thanks,

Lucy

[JustLikeIcarus]

Are you using the mysql last_insert_id() function when inserting the record into the joining table? 

[lucy]

I didnt know it existed. Am i meant to put that in the default value, for the SERVICES table of 'cid', or do i use it somehow with my php form?

 

Thanks,

Lucy

[JustLikeIcarus]

It will return the autoincrement value of the last insert. So once you insert your record into customer you can use it like

insert into services NULL, last_insert_id(), whatever.........

 

However if you are going to be inserting multiple records into the next table you need to set a var = last_insert_id.  You can use php's mysql_insert_id() function to give you the id you just inserted into customer, set a variable to that value then use it in your other insert statements.

[lucy]

I put the following function in, as you said:

 

mysql_query("INSERT INTO customer ( title, name, housenumber, add1, add2, pc, email, tel) 
				 VALUES ('$_POST[title]','$_POST[customer_name]','$_POST[customer_house_number]','$_POST[customer_road_name]', '$_POST[customer_city]', '$_POST[customer_postcode]', '$_POST[email]', '$_POST[c_tel]' )") 
		or die('Error: ' . mysql_error());

	mysql_query("INSERT INTO services (NULL, last_insert_id(), number_of_owners, owner_name, house_number, road_name, city, county, postcode, email, nature_of_work, month, year, uptoboundary, askpermission, underpin, description) 
				 VALUES ('$_POST[number_of_owners]', '$_POST[owner_name]', '$_POST[house_number]', '$_POST[road_name]', '$_POST[city]', '$_POST[county]', '$_POST[postcode]', '$_POST[email]', '$_POST[nature_of_work]', '$_POST[month]','$_POST[year]', '$_POST[boundry_with_neighbour]', '$_POST[ask_for_permission]','$_POST[underpin_foundations]', '$_POST[description_of_works]' )") 
		or die('Error: ' . mysql_error());

 

but its threw me an error:

Error: 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 'NULL, last_insert_id(), number_of_owners, owner_name, house_number, road_name, c' at line 1

 

Does last_insert_id() simply return the last auto-incremented filed which was created, even if it was in a different table?

 

Thanks a lot,

Lucy

[JustLikeIcarus]

It returns the autoincrement id of the last insert that occured for the session.  your code should read

mysql_query("INSERT INTO customer ( title, name, housenumber, add1, add2, pc, email, tel)
                VALUES ('$_POST[title]','$_POST[customer_name]','$_POST[customer_house_number]','$_POST[customer_road_name]', '$_POST[customer_city]', '$_POST[customer_postcode]', '$_POST[email]', '$_POST[c_tel]' )")
         or die('Error: ' . mysql_error());
         
      mysql_query("INSERT INTO services (cid, number_of_owners, owner_name, house_number, road_name, city, county, postcode, email, nature_of_work, month, year, uptoboundary, askpermission, underpin, description)
                VALUES ( last_insert_id(),  '$_POST[number_of_owners]', '$_POST[owner_name]', '$_POST[house_number]', '$_POST[road_name]', '$_POST[city]', '$_POST[county]', '$_POST[postcode]', '$_POST[email]', '$_POST[nature_of_work]', '$_POST[month]','$_POST[year]', '$_POST[boundry_with_neighbour]', '$_POST[ask_for_permission]','$_POST[underpin_foundations]', '$_POST[description_of_works]' )")
         or die('Error: ' . mysql_error());

 

Now a word of warning. You NEVER want to use $_POST in any sql statement.  Because you have no idea what the person submitting the form has sent you.

[lucy]

Thanks, that works a treat!

 

Do you suggest using get? would i simply replace post, for get?

 

I am running javascript validation (i know it can be turned off), as well as php validation to try ensure what the user is sending me is what i want.

 

Thanks for all the help

Lucy

 

 

[JustLikeIcarus]

No get is actually worse because you dont have to submit a form to send a get var.  You can continue to use post but I would perform checks on the post variables to verify they contain what you want. At the very least you should pass the variables into mysql_real_escape_string http://us3.php.net/mysql_real_escape_string

 

 

[lucy]

Thanks il look into it

 

Could i do something like this, to retain the cid, and the pid, which are both auto-incrementing primary keys:

mysql_query("INSERT INTO customer ( title, name, housenumber, add1, add2, pc, email, tel)
                VALUES ('$_POST[title]','$_POST[customer_name]','$_POST[customer_house_number]','$_POST[customer_road_name]', '$_POST[customer_city]', '$_POST[customer_postcode]', '$_POST[email]', '$_POST[c_tel]' )")
         or die('Error: ' . mysql_error());
         
	 $cid = mysql_insert_id();

      mysql_query("INSERT INTO services(cid, number_of_owners, number_of_neighbours, directlyattached, special_circumstances, owner_name, house_number, road_name, city, county, postcode, email, nature_of_work, month, year, uptoboundary, askpermission, underpin, description)
                VALUES ( $cid,  '$_POST[number_of_owners]', '$_POST[no_of_neighbours]', '$_POST[neighbour_attached]', '$_POST[owner_name]', '$_POST[special_circumstance]','$_POST[house_number]', '$_POST[road_name]', '$_POST[city]', '$_POST[county]', '$_POST[postcode]', '$_POST[email]', '$_POST[nature_of_work]', '$_POST[month]','$_POST[year]', '$_POST[boundry_with_neighbour]', '$_POST[ask_for_permission]','$_POST[underpin_foundations]', '$_POST[description_of_works]' )")
         or die('Error: ' . mysql_error());

	 $pid = mysql_insert_id();

 

N.B. They are both different values, so i would be expecting the cid of the latest customer, and the pid of the latest service.

[JustLikeIcarus]

 

N.B. They are both different values, so i would be expecting the cid of the latest customer, and the pid of the latest service.

 

Yeah that should do exactly what you want.

[lucy]

Thanks a lot for all your help