jodarecode Posted March 17, 2009 Share Posted March 17, 2009 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 Quote Link to comment https://forums.phpfreaks.com/topic/149890-php-mysql-insert-into-if-there-is-no-data-and-update-if-there-is-data-issue/ Share on other sites More sharing options...
drisate Posted March 17, 2009 Share Posted March 17, 2009 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()); } Quote Link to comment https://forums.phpfreaks.com/topic/149890-php-mysql-insert-into-if-there-is-no-data-and-update-if-there-is-data-issue/#findComment-787179 Share on other sites More sharing options...
jodarecode Posted March 17, 2009 Author Share Posted March 17, 2009 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); } Quote Link to comment https://forums.phpfreaks.com/topic/149890-php-mysql-insert-into-if-there-is-no-data-and-update-if-there-is-data-issue/#findComment-787191 Share on other sites More sharing options...
jodarecode Posted March 17, 2009 Author Share Posted March 17, 2009 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 Quote Link to comment https://forums.phpfreaks.com/topic/149890-php-mysql-insert-into-if-there-is-no-data-and-update-if-there-is-data-issue/#findComment-787195 Share on other sites More sharing options...
9three Posted March 17, 2009 Share Posted March 17, 2009 Did you set it to auto increment? Try putting in your SQL statement into phpmyadmin manually and see if it gives you an error. Quote Link to comment https://forums.phpfreaks.com/topic/149890-php-mysql-insert-into-if-there-is-no-data-and-update-if-there-is-data-issue/#findComment-787196 Share on other sites More sharing options...
jodarecode Posted March 17, 2009 Author Share Posted March 17, 2009 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 Quote Link to comment https://forums.phpfreaks.com/topic/149890-php-mysql-insert-into-if-there-is-no-data-and-update-if-there-is-data-issue/#findComment-787200 Share on other sites More sharing options...
jodarecode Posted March 17, 2009 Author Share Posted March 17, 2009 My bad, forgot to take out the comments...HAHA! I'll repost Quote Link to comment https://forums.phpfreaks.com/topic/149890-php-mysql-insert-into-if-there-is-no-data-and-update-if-there-is-data-issue/#findComment-787203 Share on other sites More sharing options...
drisate Posted March 17, 2009 Share Posted March 17, 2009 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>"; Quote Link to comment https://forums.phpfreaks.com/topic/149890-php-mysql-insert-into-if-there-is-no-data-and-update-if-there-is-data-issue/#findComment-787204 Share on other sites More sharing options...
jodarecode Posted March 17, 2009 Author Share Posted March 17, 2009 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 Quote Link to comment https://forums.phpfreaks.com/topic/149890-php-mysql-insert-into-if-there-is-no-data-and-update-if-there-is-data-issue/#findComment-787205 Share on other sites More sharing options...
PFMaBiSmAd Posted March 17, 2009 Share Posted March 17, 2009 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. Quote Link to comment https://forums.phpfreaks.com/topic/149890-php-mysql-insert-into-if-there-is-no-data-and-update-if-there-is-data-issue/#findComment-787233 Share on other sites More sharing options...
jodarecode Posted March 18, 2009 Author Share Posted March 18, 2009 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 Quote Link to comment https://forums.phpfreaks.com/topic/149890-php-mysql-insert-into-if-there-is-no-data-and-update-if-there-is-data-issue/#findComment-787677 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.