Insert Array into one record in Database; Not multiple records 2

[iteach2]

Hi Folks,


I have a table called DEPT. It has 24 fields. I have 24 possible form variables that can be stored in my form array. I am attempting to loop through my form array, and each insert 'Y' into each database field that matches my form variables name.

What I have is inserting but if I have 8 form variables, 8 new records are created, If all 24 variables are selected the 24 new records are created...etc.

What "should" be happening is 1 record being created with 8 or 24 field fields getting the 'Y' inserted.

Also if this helps I am using and auto increment id. The id is incremented using mysql_insert_id.

here is my code:

	session_start();
	
	print_r($_SESSION);
	
	 $fname = $_SESSION['fname'];
	 $title_dept = $_SESSION['title_dept'];
     $lname = $_SESSION['lname']; 
	 $cname = $_SESSION['cname'];
	$praddress = $_SESSION['praddress'];
	$city = $_SESSION['city'];
	$state = $_SESSION['state'];
	$zip = $_SESSION['zip'];
	$email = $_SESSION['email'];
	$url = $_SESSION['url'];
	$phone = $_SESSION['phone'];
	$fax =  $_SESSION['fax'];
	$iama = $_SESSION['iam'];	
	$pres_email = $_SESSION['pres_email'];
	$pres_fname = $_SESSION['pres_fname'];
	$pres_lname = $_SESSION['pres_lname'];
	$pres_phone = $_SESSION['pres_phone'];
	$pres_ext = $_SESSION['pres_ext'];
	$sales_email = $_SESSION['sales_email'];
	$sales_fname = $_SESSION['sales_fname'];
	$sales_lname = $_SESSION['sales_lname'];
	$sales_phone = $_SESSION['sales_phone'];
	$sales_ext = $_SESSION['sales_ext'];
	$mktg_email = $_SESSION['mktg_email'];
	$mktg_fname = $_SESSION['mktg_fname'];
	$mktg_lname = $_SESSION['mktg_lname'];
	$mktg_phone = $_SESSION['mktg_phone'];
	$mktg_ext = $_SESSION['mktg_ext'];
	$credit_email = $_SESSION['credit_email'];
	$credit_fname = $_SESSION['credit_fname'];
	$credit_lname = $_SESSION['credit_lname'];
	$credit_phone = $_SESSION['credit_phone'];
	$credit_address = $_SESSION['credit_address'];
	$credit_city = $_SESSION['credit_city'];
	$credit_state = $_SESSION['credit_state'];
	$credit_zip = $_SESSION['credit_zip'];
	$ship_email = $_SESSION['ship_email'];
	$ship_fname = $_SESSION['ship_fname'];
	$ship_lname = $_SESSION['ship_lname'];
	$ship_phone = $_SESSION['ship_phone'];
	$ship_ext = $_SESSION['ship_ext']; 
	$ship_address = $_SESSION['ship_address']; 
	$ship_city = $_SESSION['ship_city']; 
	$ship_state = $_SESSION['ship_state']; 
	$ship_zip = $_SESSION['ship_zip'];
	$depts = $_SESSION['dept']; 
	
	$con = mysql_connect($host,$dbuser,$password)or die(mysql_error());
	
	mysql_select_db($dbname, $con) or die(mysql_error());
	
	$con2 = mysql_connect($host,$dbuser,$password)or die(mysql_error());
	
	mysql_select_db($dbname, $con2) or die(mysql_error());
	
	$uid = mysql_insert_id($con);
	
	$uid2 = mysql_insert_id($con);
	

		
    $SQL = "INSERT INTO users (uid,fname,title_dept,cname,praddress,city,state,zip,email,url," .
	
	       "phone,fax,iama,pres_email,pres_fname,pres_phone,pres_ext,sales_email," .
		   
		   "sales_fname,sales_lname,sales_phone,sales_ext,mktg_email,mktg_fname,mktg_lname," .
		   
		   "mktg_phone,mktg_ext,credit_email,credit_fname,credit_lname,credit_phone,credit_address," .
		   
		   "credit_city,credit_state,credit_zip,ship_email,ship_fname,ship_lname,ship_phone,ship_ext," . 
		   
		   "ship_address,ship_city,ship_state,ship_zip)". 
		   
		   "VALUES('$uid','$fname','$title_dept','$cname','$praddress','$city','$state','$zip','$email','$url'," .
		   
	       "'$phone','$fax','$iama','$pres_email','$pres_fname','$pres_phone','$pres_ext','$sales_email'," .
		   
		   "'$sales_fname','$sales_lname','$sales_phone','$sales_ext','$mktg_email','$mktg_fname','$mktg_lname'," .
		   
		   "'$mktg_phone','$mktg_ext','$credit_email','$credit_fname','$credit_lname','$credit_phone','$credit_address'," .
		   
		   "'$credit_city','$credit_state','$credit_zip','$ship_email','$ship_fname','$ship_lname','$ship_phone','$ship_ext'," . 
		   
		   "'$ship_address','$ship_city','$ship_state','$ship_zip')"; 
		   
	mysql_query($SQL) or die(mysql_error());  	   
		   
   
      
  foreach ($depts as $key => $val) 
 { 
 $SQL2 = "INSERT INTO dept (uid,$val) VALUES ($uid2,'Y')";	
 mysql_query($SQL2) or die(mysql_error());  
 } 



echo"all done";

 

[Foamcow]

You need to use UPDATE instead of INSERT once the row is created.
Or use a single INSERT statement that enters all the data in one go.

I think you might also want to reconsider your database/table design.

--
Tek-Tips Forums is Member Supported. Click Here to donate

<honk>*:O)</honk>

Tyres: Mine's a pint of the black stuff.
Mike: You can't drink a pint of Bovril.

 

[Foamcow]

I've only really glanced at your code above rather than unpick it, but the second INSERT should be more like

UPDATE dept SET(uid=$uid2,val='Y') WHERE ...

That WHERE clause is the critical part, you need to be able to identify the row you are updating - normally via a unique id.

--
Tek-Tips Forums is Member Supported. Click Here to donate

<honk>*:O)</honk>

Tyres: Mine's a pint of the black stuff.
Mike: You can't drink a pint of Bovril.

 

[Olavxxx]

Hi,

It looks like you came from VB / ASP, where you have to do the & _ for next line.

Btw.. When inserting, you have to use the
mysqli_insert_id() after the first insert, not before!


You can then UPDATE WHERE blah = $foo, based in the insert id.

I usually use:

http://no.php.net/manual/en/function.mysql-insert-id.php

Olav Alexander Mjelde

http://www.volvo-power.net

Admin & Webmaster

 

[iteach2]

Heres what I cam up with:

mysql_query($SQL) or die(mysql_error());

		$uid2 = mysql_insert_id();

			$SQL3="INSERT INTO dept(uid) VALUES ('$uid2')";

  	 mysql_query($SQL3) or die(mysql_error());

 	 foreach ($depts as $key => $val)
		 {

 			$SQL2 = "UPDATE dept SET uid='$uid2', $val='Y' WHERE uid = '$uid2'";
			mysql_query($SQL2) or die(mysql_error());


		 }