PHP Mysql - insert into if there is no data, and update if there is data issue

[jodarecode]

I've tried and nothing

$query = "INSERT INTO formdata (userid, fieldid, value, saveas) VALUES (userid, fieldid, value, saveas) ON DUPLICATE KEY UPDATE value = VALUES(value), saveas = VALUES(saveas)";
foreach($_POST as $fieldid => $value){
    if(in_array($fieldid,$fieldstoinsert)){
        if(trim($value)!=""){
                    $query.= "('$userid', '$fieldid', '$value', '$saveas'),";
        }
    }
} 

 

 

The below seems to be closer to what I'm trying to do:

$fieldstoinsert=array('purchaser', 'orderno', 'contact', 'shipto', 'street', 'city', 'state', 'zip', 'orderdate', 'reqshipdate', 'shipviaUPS', 'shipviaTC', 'shiptotel', 'dealertel', 'mobiletel', 'specinstr', 'blumotion', 'casetape', 'caseconst', 'drwbox', 'state', 'RosBox', 't2', 't3', 't4', 't5', 't6', 't7', 't8');

$query = "REPLACE INTO formdata (userid, fieldid, value, saveas) VALUES WHERE saveas='$saveas'";
foreach($_POST as $fieldid => $value){ 
    if(in_array($fieldid,$fieldstoinsert)){ 
        if(trim($value)!=""){ 
          $query.= "('$userid', '$fieldid', '$value', '$saveas'),"; 
        } 
    } 
} 

$query=substr($query,0,-1); 

 

The way its set up when REPLACE was INSERT before, saved perfectly.

now with REPLACE it still saves but REPLACE is ignored and is inserted instead. How would I make it if WHERE saveas='$saveas' is to replace any duplicates matching the rows with the same saveas value, and insert if there is no existing value?

 

I do not want to use UPDATE, I wanted to do insert into if there is no data, and update if there is data

[drisate]

Why not make stuff simpler ...

 

// Check if the row exist
$check = @current(@mysql_fetch_assoc(@mysql_query("SELECT count(userid) FROM formdata where userid='$userid'")));

if ($check!="0"){
// The row exist, perform update
$UPDATE = mysql_query("UPDATE [...]") or die (mysql_error());

}else{
// The row does not exist, perform insert into
$INSERT = mysql_query("INSERT INTO [...]") or die (mysql_error());

}

[jodarecode]

Ok, It seems to insert but still not updating,

 

Keep in mind it saves just fine with the INSERT code alone and updates just fine with the UPDATE code alone but building it in this format, I may have made some errors, please excuse the newbieness!

 

I don't see why the below doesn't work as they seem to work individually.

 

$fieldstoinsert=array('purchaser', 'orderno', 'contact', 'shipto', 'street', 'city', 'state', 'zip', 'orderdate', 'reqshipdate', 'shipviaUPS', 'shipviaTC', 'shiptotel', 'dealertel', 'mobiletel', 'specinstr', 'blumotion', 'casetape', 'caseconst', 'drwbox', 'state', 'RosBox', 't2', 't3', 't4', 't5', 't6', 't7', 't8');
// Check if the row exist
$check = @current(@mysql_fetch_assoc(@mysql_query("SELECT count(userid) FROM formdata where userid='$userid'")));

if ($check!="0"){
// The row exist, perform update

foreach($_POST as $fieldid => $value){
    if(in_array($fieldid,$fieldstoinsert)){
        if(trim($value)!=""){
           mysql_query("UPDATE formdata SET value = '$value', saveas = '$saveas' WHERE saveas = '$saveas'");
        }
    }
}

}else{
// The row does not exist, perform insert into

$query = "INSERT INTO formdata (userid, fieldid, value, saveas) VALUES";
foreach($_POST as $fieldid => $value){ 
    if(in_array($fieldid,$fieldstoinsert)){ 
        if(trim($value)!=""){ 
          $query.= "('$userid', '$fieldid', '$value', '$saveas'),"; 
        } 
    } 
} 
$query=substr($query,0,-1); 


}

[jodarecode]

The below seems to work with already inserted fields but will not insert if there is a new value

 

// Check if the row exist
$check = @current(@mysql_fetch_assoc(@mysql_query("SELECT count(userid) FROM formdata where userid='$userid'")));

if ($check!="0"){
// The row exist, perform update

foreach($_POST as $fieldid => $value){
    if(in_array($fieldid,$fieldstoinsert)){
        if(trim($value)!=""){
                    mysql_query("UPDATE formdata SET value = '$value' WHERE fieldid = '$fieldid'");
        }
    }
} 

}else{
// The row does not exist, perform insert into

$query = "INSERT INTO formdata (userid, fieldid, value, saveas) VALUES";
foreach($_POST as $fieldid => $value){ 
    if(in_array($fieldid,$fieldstoinsert)){ 
        if(trim($value)!=""){ 
          $query.= "('$userid', '$fieldid', '$value', '$saveas'),"; 
        } 
    } 
} 
$query=substr($query,0,-1); 


}

 

Basically I need to INSERT and/or UPDATE,

not if not UPDATE, INSERT or not if not INSERT then UPDATE

[9three]

Did you set it to auto increment?

 

Try putting in your SQL statement into phpmyadmin manually and see if it gives you an error.

[jodarecode]

This is what it spit out

 

There seems to be an error in your SQL query. The MySQL server error output below, if there is any, may also help you in diagnosing the problem

ERROR: Unknown Punctuation String @ 1
STR: //
SQL: // Check if the row exist
$check = @current(@mysql_fetch_assoc(@mysql_query("SELECT count(userid) FROM formdata where userid='$userid'")));// Check if the row exist
$check = @current(@mysql_fetch_assoc(@mysql_query("SELECT count(userid) FROM formdata where userid='$userid'")));// Check if the row exist
$check = @current(@mysql_fetch_assoc(@mysql_query("SELECT count(userid) FROM formdata where userid='$userid'")));// Check if the row exist
$check = @current(@mysql_fetch_assoc(@mysql_query("SELECT count(userid) FROM formdata where userid='$userid'")));// Check if the row exist
$check = @current(@mysql_fetch_assoc(@mysql_query("SELECT count(userid) FROM formdata where userid='$userid'")));// Check if the row exist
$check = @current(@mysql_fetch_assoc(@mysql_query("SELECT count(userid) FROM formdata where userid='$userid'")));// Check if the row exist
$check = @current(@mysql_fetch_assoc(@mysql_query("SELECT count(userid) FROM formdata where userid='$userid'")));


SQL query: Documentation

// Check if the row exist $check = @current(@mysql_fetch_assoc(@mysql_query("SELECT count(userid) FROM formdata where userid='$userid'")));

MySQL said: Documentation
#1064 - 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 '// Check if the row exist
$check = @current(@mysql_fetch_assoc(@mysql_query("SE' at line 1 

 

Basically I need to INSERT and/or UPDATE,

not: if not UPDATE, INSERT or not if not INSERT then UPDATE

 

 

[jodarecode]

My bad, forgot to take out the comments...HAHA!

 

I'll repost

[drisate]

I am not sure abbout this part

$_POST as $fieldid => $value

 

can you show us your table structure? and perform a

 

echo "<pre>";

print_r($fieldid);

echo "</pre>";

[jodarecode]

Did you set it to auto increment?

 

Try putting in your SQL statement into phpmyadmin manually and see if it gives you an error.

 

The saveas is the name referenced to each field, there is no auto increment except for the userid which is auto increment for users who register.

 

the mysql spits out with out the comments

Error

SQL query: Documentation

$check = @current (
@mysql_fetch_assoc (
@mysql_query (
"SELECT count(userid) FROM formdata where saveas='$saveas'"
)
)
);

MySQL said: Documentation
#1064 - 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 '$check = @current(@mysql_fetch_assoc(@mysql_query("SELECT count(userid) FROM for' at line 1 

[PFMaBiSmAd]

You can replace all that code with one query - http://dev.mysql.com/doc/refman/5.1/en/insert-on-duplicate.html

 

And please don't use the @ in any code. It just hides the problems that are occurring that are preventing your code from doing what you want it to.

[jodarecode]

table structure as follows,

 

[pre]

Table name: formdata

 

userid | fieldid | value | saveas

[/pre]

I've tried INSERT ON DUPLICATE and cant seem to get it to work.

Original code which successfully works great for inserting:

$fieldstoinsert=array('purchaser', 'orderno', 'contact', 'shipto', 'street', 'city', 'state', 'zip', 'orderdate', 'reqshipdate', 'shipviaUPS', 'shipviaTC', 'shiptotel', 'dealertel', 'mobiletel', 'specinstr', 'blumotion', 'casetape', 'caseconst', 'drwbox', 'state', 'RosBox', 't2', 't3', 't4', 't5', 't6', 't7', 't8');


$query = "INSERT INTO formdata (userid, fieldid, value, saveas) VALUES";
foreach($_POST as $fieldid => $value){
    if(in_array($fieldid,$fieldstoinsert)){
        if(trim($value)!=""){
          $query.= "('$userid', '$fieldid', '$value', '$saveas'),";
        }
    }
}  
$query=substr($query,0,-1);

 

I've tried UPDATE which works great for updating

foreach($_POST as $fieldid => $value){
    if(in_array($fieldid,$fieldstoinsert)){
        if(trim($value)!=""){
                    mysql_query("UPDATE formdata SET userid = '$userid', fieldid = '$fieldid', value = '$value', saveas = '$saveas' WHERE saveas = '$saveas'");
        }
    }
} 

 

I just need to insert if no row exists and/or update if row exists.

I've tried INSERT ON DUPLICATE but not sure how to get it working. everybody keeps telling me different.

The data is put into the $fieldstoinsert=array and according to the table at top, It INSERTS and UPDATES just fine, just not at the same time