[RESOLVED] Insert/Update PHP Form

[glima]

Hey guys I'm still learning PHP and I have thousands of doubts :S sorry 🙁... I was creating a form to update information.. and I was using UPDATE command on MYSQL... but then when there is no data already created it does not work, so I search for a while on Google on the subject and found a command on MYSQL (ON DUPLICATE KEY UPDATE...) but I cant define a unique key... or I might be doing it wrong :S because it inserts the value and create the duplicate key 😛 instead of updating the current record. The fact is that I dont know how to do a form like that... one that INSERT new data when there is no record, or that UPDATE where the record already exists... Can you guys please help me? How a form like that would work with PHP?

Thank you guys a lot!
Gill.

[Piranha]

My suggestion is that you run two different queries, one for insert and one for update.

Basically you put a hidden value in the form. It could be a row of it own (like "action") or a special id dedicated to new rows (like "-1"). When you handle the data from the form you simply use a if-statement to choose what action to take.

[Piranha]

Double post

[djjjozsi]

before insert the data, lets select and count how many in that table.

<a href="index.php">Back to List</a><br>
<br>
<?php

include("connect.php");

function check_data($content,$table,$field)
{
  if(empty($content))
      return(3);
  else
  {
   $sql="SELECT * FROM $table WHERE $field='".mysql_real_escape_string($content)."'";
     if (mysql_num_rows(mysql_query($sql))>0)
        return(2);
     else
        return(1);
  }
}

 /*lets count from file table your_message field the your_message from the form... */
switch (check_data($_POST['your_message'],"file","your_message")    ) 
{
case 3:
print "Message field is empty...";    break;

case 2:
print "Message field has already a data you post...";    break;

case 1:   

$id = (int)($_POST['id']);
$your_message = mysql_real_escape_string(stripslashes($_POST['your_message']));

$update = "UPDATE file SET
your_message = '$your_message' WHERE id='$id' ";
$rsUpdate = mysql_query($update);
if ($rsUpdate)
{
echo "Update successful."; 
} 
mysql_close();

break;
}  //end of switch case

?>

[Piranha]

djjjozsi;10893044 wrote:

before insert the data, lets select and count how many in that table.

What if there is another insert at the same time? Or a delete? Never trust queries that depend on each other, it can really make a mess of things.

[djjjozsi]

i Don't want to make this example too complicated.
to start somewhere not needed to build a complex stuff.

To set a table's field into a Unique:

If the field is already done:
ALTER TABLE users_private_informations ADD UNIQUE (
emails
)

If you want to create a table, and add Unique property to the selected field(s) :

CREATE TABLE t_users (
id int(6) NOT NULL auto_increment,
username varchar(255) NOT NULL default '',
UNIQUE KEY username (username),
,PRIMARY KEY (id),
UNIQUE KEY id (id)
) TYPE=MyISAM AUTO_INCREMENT=1;

[glima]

Hey guys, first of all thank you so much for helping me. I was working on this form today and I came a partial solution... it might not the most clean :s or inteligent but when we are learning we tend to complicate things a bit 😛

So let me tell you what I achieved so far.
In this form I update 3 different tables, for the 1st one that stores user infos I succeed on the script, it has 2 queries one to UPDATE, if the number of rows affected on the query is = 0 then it executes the INSERT INTO query. I created an array with the POST variables and assigned the dbColNames to each array position.

Let me explain better how this form works.. it will upload user information but some other info (the ones that got me stuck) are more complicated for me to figure it out how to upload them to the DB.

The user can assign as many ethnicities to a person as the forms allows it (up to 7 on check boxes) and up to 5 languagues (select menus). And thats the part that I'm stuck.
On the ethnicity part the form only uploads one... and on languages the same, both information might or might not be on the post... but I already declared them on the conditions for executing the mysql scripts, otherwise they wouldn't be processed if they where sent.

=========================================================
I'm basically using this structure:

$fieldList (dbColName =>$Post['varName'], dbColName =>$Post['varName']....)
foreach($fieldList as $field => $value){
UPDATE tbName SET $field = '".$value."' WHERE userID=$uid;
if no rows affected
INSERT INTO tbName($field) value($value);

}

The problem is on the ethnicity field all the array name assignment has to be the same because it is the dbColName... and because of that it only processes one, at least i think its cuz of that :$, and the same problem on Languages.
if there was a possibility for me to do this :

=========================================================
$fieldList ($Post['varName1'], $Post['varName2'],... $_Post['varName7'])
foreach($fieldList as $field => $value){
rename $field to ethnicityID
UPDATE
if no rows affected
INSERT
}

This way it would work as the script above and ouwld just replace the name of the field on the query...

//
$editFormAction = $_SERVER['PHP_SELF'];
if (isset($_SERVER['QUERY_STRING'])) {
  $editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);
}
if ((isset($_POST["MM_update"])) && ($_POST["MM_update"] == "personalFRM")) {

$uservars = array(userMoto=>$_POST['userMoto'], user_heightFeet=>$_POST['user_heightFeet'], 
user_heightInch=>$_POST['user_heightInch'], user_bodyTypeID=>$_POST['user_bodyTypeID'], 
user_eyeColorID=>$_POST['user_eyeColorID'], user_hairColorID=>$_POST['user_hairColorID'],
 user_orientationID=>$_POST['user_orientationID'], user_dateUpdated=>$_POST['updatedate']);		
	 //update values on the user_info table
		foreach($uservars as $field => $value){ 
		print "$field : $value<br>";
		$user_update_qr = "UPDATE user_registered SET $field='".$value."' WHERE user_userID='".$uid."'";
		//print "$user_update_qr<br/>";
		mysql_select_db($database_dbConnect, $dbConnect);
  		$Result1 = mysql_query($user_update_qr, $dbConnect) or die(mysql_error());
		if ($Result1 == 0){
		//for each field not updated it will insert a new row on the table
		$user_insert_qr = mysql_query("INSERT INTO user_ registered (user_userID, '".$field."') Values('".$uid."', '".$value."')");
		print "$user_insert_qr<br/>";
		}
	}

//array to gather all the fields related to the ethnicity table
$ethvars = array(ethnicityID=>$_POST['eth_asian'], ethnicityID=>$_POST['eth_black'], ethnicityID=>$_POST['eth_lantin'], 
ethnicityID=>$_POST['eth_indian'], ethnicityID=>$_POST['eth_meastern'], 
ethnicityID=>$_POST['eth_namerican'], ethnicityID=>$_POST['eth_white']);	
		//update values on the user_ethnicities table
		foreach($ethvars as $efield => $evalue){
		if (!empty($evalue)){
			print "$efield : $evalue <br>";
			$eth_update_qr = "UPDATE user_ethnicities SET $efield='".$evalue."' WHERE user_userID='".$uid."'";
			print "$eth_update_qr<br/>";
 			mysql_select_db($database_dbConnect, $dbConnect);
			$Result2 = mysql_query($eth_update_qr, $dbConnect) or die(mysql_error());
			if ($Result2 == 0){
			//for each field not updated it will insert a new row on the table
			$eth_insert_qr = mysql_query("INSERT INTO efield (user_userID, '".$efield."') Values('".$uid."', '".$evalue."')");}
			}else {print $efield; unset($efield);}
		}
/*		
 //array to gather all the fields related to the language table
$langvars = array(language_langID=>$_POST['langs1'], language_langID=>$_POST['langs2'], language_langID=>$_POST['langs3'], 
language_langID=>$_POST['langs4'], 
language_langID=>$_POST['langs5']);		

foreach($langvars as $field => $value){ 
	print "$field : $value<br>";
	$lang_update_qr = "UPDATE user_langs SET $field='".$value."' WHERE user_registeredUserID='".$uid."'";
	print "$lang_update_qr<br/>";
	mysql_select_db($database_dbConnect, $dbConnect);
	$Result3 = mysql_query($lang_update_qr, $dbConnect) or die(mysql_error());
	if ($Result3 == 0){
	//for each field not updated it will insert a new row on the table
	$lang_insert_qr = mysql_query("INSERT INTO user_langs (user_registeredUserID, '".$field."') Values('".$uid."', '".$value."')");
	print "$field : $value <br/>";
	}
} */	

}

Sorry for writing that much
and thank you guys again.
BTW, i put some print_r (s) in the middle of the script just to check the query itgenerated.

[glima]

Hey guys thanks for the help I solved the problem yesterday, heres the code is sum1 had the same problem and need help:

if ((isset($_POST["MM_update"])) && ($_POST["MM_update"] == "personalFRM")) {      //since I had to update 3 diferent tables in specific form I started the name 
       //of each field with an identifyer: user_userID, user_name, etc...
	$pattern1 = '/user/';
	$pattern2 = '/eth/';
	$pattern3 = '/lang/';
	$pattern4 = '/MM_update/';
	$pattern5 = '/Submit/';

foreach ($_POST as $key => $value) {
//get the name of each form field and use it as the query field, 
   //so the form fields name should be the same as the db_field_names.
    $$key = $_POST[$key];

if (preg_match($pattern1, $key, $matches, PREG_OFFSET_CAPTURE)){
	$tbname = "user_registered";
	$update_qr = "INSERT INTO $tbname(user_userID, $key) VALUES('".$uid."', '".$value."') ON DUPLICATE KEY UPDATE $key='".$value."'";
}elseif(preg_match($pattern2, $key, $matches, PREG_OFFSET_CAPTURE)){
	$tbname = "user_ethnicities";
	$key = "ethnicityID";
	if($value < 10){
	$update_qr = "INSERT INTO $tbname(user_userID, $key) VALUES('".$uid."', '".$value."') ON DUPLICATE KEY UPDATE $key='".$value."'";
	}elseif($value > 10){
	$value = $value - 10;
	$update_qr = "DELETE FROM $tbname WHERE user_userID='".$uid."'  AND $key='".$value."'";}
}elseif (preg_match($pattern3, $key, $matches, PREG_OFFSET_CAPTURE)){
	$tbname = "user_langs";
	$key = "language_langID";
	if (empty($value)){;
	}elseif($value < 10){
	$update_qr = "INSERT INTO $tbname(user_userID, $key) VALUES('".$uid."', '".$value."') ON DUPLICATE KEY UPDATE $key='".$value."'";
	}elseif($value > 100){
	$value = $value - 100;
	$update_qr = "DELETE FROM $tbname WHERE user_userID='".$uid."'  AND $key='".$value."'";
	}
}elseif (preg_match($pattern4, $key, $matches, PREG_OFFSET_CAPTURE)){
	break;
}elseif (preg_match($pattern5, $key, $matches, PREG_OFFSET_CAPTURE)){
	break;
}
	//print "$update_qr<br/>";
 	mysql_select_db($database_dbConnect, $dbConnect);
	mysql_query($update_qr, $dbConnect) or die(mysql_error());
	$result = mysql_affected_rows();
	//print $result;

	}
}