...

View Full Version : Update mysql php code not working



Jodarecode
03-17-2009, 07:10 PM
Any ideas why the code below is not updating?


$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 = "UPDATE formdata SET `userid` = '$userid', `fieldid` = '$fieldid', `value` = '$value', `saveas` = '$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);

djm0219
03-17-2009, 07:28 PM
The code you posted never does an update. You build up an UPDATE clause but never do anything with it. At least not in what you posted.

Jodarecode
03-17-2009, 07:33 PM
Originally I had


$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);

How would I make it so it updates and saves any new fields to the db?

The code above works perfect for saving to the db and works well retrieving but I cant seem to replace any duplicate values pertaining to saveas value which is the saved form name.

tomws
03-17-2009, 09:00 PM
http://us3.php.net/mysql_query

Jodarecode
03-17-2009, 09:02 PM
Thanks for the link, but im getting ahead of my self now.

This works perfect

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'");
}
}
}

now how can I make these to work together so that it can insert new values as well as update existing values?


$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);

table: formdata
The db structure as shown
userid | fieldid | value | saveas

tomws
03-17-2009, 09:07 PM
Can you clarify your question? Sounds like you're asking how to perform two queries simultaneously, which can't be done.

Jodarecode
03-17-2009, 09:44 PM
According to ON DUPLICATE KEY UPDATE (http://dev.mysql.com/doc/refman/5.1/en/insert-on-duplicate.html) I can but I still cant get it to work. Any ideas?

I've tried using this before and I've tried every combination I could think of and will not save and update, any Ideas what I'm doing wrong?

$query = "INSERT INTO formdata (userid, fieldid, value, saveas) VALUES (saveas) ON DUPLICATE KEY UPDATE 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);

tomws
03-17-2009, 11:04 PM
Well, that's not 2 queries executing simultaneously, but at least I understand what you're trying to do.

It looks like your query is all screwed up. Aren't you seeing any errors? For one thing, the parameter count is mismatched between the field list and the values list.

Another problem is that you're using the UPDATE portion incorrectly. Look at the man page from your link. While I've never used it before, it certainly looks like anything past the keyword phrase is the update query itself, so your current WHERE makes no sense there.

Furthermore, that loop doesn't make sense either. Unless I'm seeing it wrong, it looks like you're just attaching junk at the end of the query that also won't work. Echo the query string to confirm this.

tomws
03-17-2009, 11:11 PM
They don't have anchors on the comments, but look for this one in the top half of the page: Posted by Shane McEwan on October 27 2006 4:09am

Jodarecode
03-17-2009, 11:29 PM
What about doing something like REPLACE



$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?

tomws
03-18-2009, 02:40 AM
I've used REPLACE INTO, and that will work as long as you don't need to update column data based upon previous values (http://www.mysqlperformanceblog.com/2007/01/18/insert-on-duplicate-key-update-and-replace-into/).

Your query there doesn't provide any values to insert/replace into the table, so you're telling it to insert NULL where saveas='$saveas'.

Moreover, I think that "value" is a reserved word in the MySQL syntax, so using that as a fieldname and using it in the query string without backticking it may cause problems. And there's still that loop that's tacking on the extra stuff at the end. Have you echoed the string to see it yet? You still haven't mentioned anything about specific errors you're receiving, either.

Jodarecode
03-18-2009, 05:49 PM
table structure as follows,

Table name: formdata
userid | fieldid | value | saveas

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

tomws
03-18-2009, 06:24 PM
Ok, if it's inserting, then that clarifies that "value" as a field name is at least working in this case. One potential problem out of the way.

I see now where your mistake is - you're trying to just change part of the query above that loop and not changing anything else, so your query ends up being garbage. If you would examine the error messages (as previously requested) and if you would echo the query string (also as previously requested) for either of the ON DUPLICATE KEY UPDATE or REPLACE INTO, you would likely see the problem.

In the case of the error message, it might say something about a problem with your query near blah-blah-blah. That blah-blah-blah is pointing to the location of the error. Echoing the query string would be even better. It might show something like this:

REPLACE INTO formdata (userid, fieldid, value, saveas) VALUES WHERE saveas='someSaveAs'('someUID', 'someFID', 'someValue', 'someSaveAs'),('someUID', 'someFID', 'someValue', 'someSaveAs'),('someUID', 'someFID', 'someValue', 'someSaveAs'),('someUID', 'someFID', 'someValue', 'someSaveAs'),('someUID', 'someFID', 'someValue', 'someSaveAs'),('someUID', 'someFID', 'someValue', 'someSaveAs'),('someUID', 'someFID', 'someValue', 'someSaveAs'),('someUID', 'someFID', 'someValue', 'someSaveAs')

That's not a properly structured query, so it's going to barf on you with the error message(s) that you refuse to post.

For the ON DUPLICATE KEY UPDATE query, follow the man page and build the new query as an extension on the base INSERT query. Try something like this:

$query = /*same base query*/
foreach($_POST as $fieldid => $value){
/*same loop*/
}
$query=substr($query,0,-1); // leave this, too
/*Now, do what the man page clearly shows*/
// assuming the unique key is userid
$query .= " ON DUPLICATE KEY UPDATE fieldid=VALUES(fieldid), value=VALUES(value), saveas=VALUES(saveas)";

Will that work? I don't know. But it looks like it follows the man page and the comment that I mentioned above.

Jodarecode
03-18-2009, 06:54 PM
EDIT: I'm not sure if I'm doing the error checking right, I'm doing some research as I'm still new at this.

Errors as follows from phpmyadmin
The INSERT & UPDATE seem to work but an still an error when I insert it into phpmyadmin

Error:INSERT

Error

SQL query:

$query = "INSERT INTO formdata (userid, fieldid, value, saveas) VALUES";

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 '$query = "INSERT INTO formdata (userid, fieldid, value, saveas) VALUES"' at line 1

Error:UPDATE

Error

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 @ 28
STR: =>
SQL: foreach($_POST as $fieldid => $value){
if(in_array($fieldid,$fieldstoinsert)){
if(trim($value)!=""){
mysql_query("UPDATE formdata SET value = '$value' WHERE fieldid = '$fieldid'");foreach($_POST as $fieldid => $value){
if(in_array($fieldid,$fieldstoinsert)){
if(trim($value)!=""){
mysql_query("UPDATE formdata SET value = '$value' WHERE fieldid = '$fieldid'");foreach($_POST as $fieldid => $value){
if(in_array($fieldid,$fieldstoinsert)){
if(trim($value)!=""){
mysql_query("UPDATE formdata SET value = '$value' WHERE fieldid = '$fieldid'");


SQL query:

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

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 'foreach($_POST as $fieldid => $value){
if(in_array($fieldid,$fieldstoinsert' at line 1

Error:REPLACE

Error

SQL query:

$query = "REPLACE INTO formdata (userid, fieldid, value, saveas) VALUES 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 '$query = "REPLACE INTO formdata (userid, fieldid, value, saveas) VALUES WHERE sa' at line 1

Error:ON DUPLICATE KEY UPDATE

Error

SQL query:

$query = "INSERT INTO formdata (userid, fieldid, value, saveas) VALUES";

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 '$query = "INSERT INTO formdata (userid, fieldid, value, saveas) VALUES"' at line 1

tomws
03-18-2009, 06:58 PM
You can't paste php code into the query window of phpMyAdmin. It needs real SQL. That's the cause of all 4 of those errors.

Jodarecode
03-18-2009, 07:06 PM
I think I know what the issue is

userid: is the users identity for for when they register, if they were the 27th person to register, there userid on all the data is the same "27". userid is always the same and the saveas is only to recognize which data to update pertaining to the userid "27"

fieldid: are the field names that are inserted and/or updated

value: is the values inserted and/or updated

saveas: is the name of the form so the user can update the form

There is no auto increment or index

I hope this made sense

Jodarecode
03-18-2009, 07:11 PM
As per your example

$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); // leave this, too
/*Now, do what the man page clearly shows*/
// assuming the unique key is userid
$query .= " ON DUPLICATE KEY UPDATE fieldid=VALUES(fieldid), value=VALUES(value), saveas=VALUES(saveas)";

echo "$query";

And below is what I get but still doesn't update, just inserts
echo "$query";

INSERT INTO formdata (userid, fieldid, value, saveas) VALUES('27', 'purchaser', 'be', 'test1'),('27', 'orderno', '1234', 'test1'),('27', 'contact', 'sdsddddd', 'test1'),('27', 'shipto', 'sdsd', 'test1') ON DUPLICATE KEY UPDATE fieldid=VALUES(fieldid), value=VALUES(value), saveas=VALUES(saveas)

tomws
03-18-2009, 07:12 PM
If you're storing a user id on registration, it really needs to be an auto-increment or at least flagged as unique. Otherwise, you have no trustworthy key to differentiate users.

Still, that's not the source of the phpMyAdmin error messages.

tomws
03-18-2009, 07:17 PM
And below is what I get but still doesn't update, just inserts


That's expected given your previous response. You have no key field, so the ON DUPLICATE KEY UPDATE will never update. If you want to continue to use a non-unique userid field, which I'd guess nearly everyone would discourage, you'll have to switch to the REPLACE INTO query.

Jodarecode
03-18-2009, 07:36 PM
so, in my case I would need to make the saveas the unique key, lets say user has 2 forms:

for user 27, which is there userid
1st form saved would would be named form1 or whatever which is saveas
2nd form would be named form2 or whatever which is saveas

all data pertaining to 27 for form1 is called back up once form1 is clicked

if I save and/or update, I want to only update form1, not form2 still pertaining to userid 27

Would that make sense?

Or should I create an id field before userid and make unique or primary key with auto increment?

please excuse my newbieness...

tomws
03-18-2009, 08:03 PM
If the table in question is a user registration table, the userid field should be unique and probably primary. Something like an orders table, though, should not have a restriction on the userid to be unique since a user may have more than one order listed in the table. The setup is implementation-dependent.

You can actually get by with using no keys at all, but then you lose the benefits of keys altogether and end up having to hand-code a lot of manual checks. Ugly. That's why keys are helpful.

Jodarecode
03-18-2009, 08:04 PM
Here's an image if it helps, I made userid primary and saveas as the unique key

http://img440.imageshack.us/img440/6447/dbimg1.jpg


But now it only saveas the last field value that was entered

tomws
03-18-2009, 08:08 PM
But now it only saveas the last field value that was entered

What does the current query look like? Show a sample set of the data you're attempting to insert.

Jodarecode
03-18-2009, 08:44 PM
I created an id column before the userid column and made it primary w/auto increment.

ill use 2 fields for now

1st: Purchaser : I'll enter "me"
fieldid is purchaser

1st: Tag : I'll enter "test1"
fieldid is saveas

3rd: Order No : I'll enter "1234"
fieldid is orderno

userid is 27 and saveas is whatever Tag is "test1"



$fieldstoinsert=array('purchaser', 'orderno');

$query = "INSERT INTO formdata (id, 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); // leave this, too
/*Now, do what the man page clearly shows*/
//Primary key is id
$query .= " ON DUPLICATE KEY UPDATE userid=VALUES(userid), fieldid=VALUES(fieldid), value=VALUES(value), saveas=VALUES(saveas)";

echo "$query";

it echos out:

INSERT INTO formdata (id, userid, fieldid, value, saveas) VALUES('', '27', 'purchaser', 'me', 'test1'),('', '27', 'orderno', '1234', 'test1') ON DUPLICATE KEY UPDATE userid=VALUES(userid), fieldid=VALUES(fieldid), value=VALUES(value), saveas=VALUES(saveas)

it will save but still wont update

tomws
03-18-2009, 08:50 PM
1st: Purchaser : I'll enter "me"
fieldid is purchaser

1st: Tag : I'll enter "test1"
fieldid is saveas

3rd: Order No : I'll enter "1234"
fieldid is orderno

userid is 27 and saveas is whatever Tag is "test1"



This doesn't make sense to me. Describe your application. What are you trying to do with it?

Jodarecode
03-18-2009, 09:04 PM
Ok, once user logs in, they have a userid which in this case is 27...so we know that

once on the form page, lets say they want to create a new form(pre-existing template)

lets say there are 3 fields
http://img509.imageshack.us/img509/6656/dbimg2.jpg

1st: lets enter me
2nd: this will be the name of the form once saved "saveas" lets enter test1
3rd: is an order no, lets enter 1234

now user clicks on Save & Update...

it should insert the fieldid, value, and saveas

| id | userid | fieldid | value | saveas |
+-----------------------------------+
| 0 | 27 | purchaser | me | test1 |
| 1 | 27 | orderno | 1234 | test1 |

if they want to update, it should replace fieldid and value and/or insert more if there were more fields.

Does that make sense?

tomws
03-18-2009, 09:49 PM
Ok, once user logs in, they have a userid which in this case is 27...so we know that

once on the form page, lets say they want to create a new form(pre-existing template)

This is a form builder application, then? Or something else?

Jodarecode
03-18-2009, 10:08 PM
no,

There are 3 tables

* formdata
http://img16.imageshack.us/img16/4301/dbimg3.jpg


* formfields
http://img3.imageshack.us/img3/2862/dbimg4.jpg

* users
http://img24.imageshack.us/img24/1276/dbimg5.jpg

This link to my previous post I posted while ago will explain the structure much better.

http://www.codingforums.com/showthread.php?t=141896
or
http://www.webdeveloper.com/forum/showthread.php?t=183148

Jodarecode
03-18-2009, 10:30 PM
the code below saves but only the last field. When I update that field it updates just fine, but its confined to only that field to insert or update.

so, its working but for only one field and not both.


$fieldstoinsert=array('purchaser', 'orderno');

$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);
//Primary key is userid
$query .= " ON DUPLICATE KEY UPDATE fieldid=VALUES(fieldid), value=VALUES(value), saveas=VALUES(saveas)";

echo $query;

INSERT INTO formdata (userid, fieldid, value, saveas) VALUES('27', 'purchaser', 'me', 'test1'),('27', 'orderno', '1234', 'test1') ON DUPLICATE KEY UPDATE fieldid=VALUES(fieldid), value=VALUES(value), saveas=VALUES(saveas)

tomws
03-18-2009, 10:32 PM
Minor issue - you got the names crossed for the users and formdata pictures.
--

Has anyone ever told you it's like pulling teeth to get direct information from you? :( Two points:

1. Are you just collecting data from the same form over and over? If so, why are you not doing this the easy way? Give table formdata the fields you want to collect and then stick the data into that table. Unless I misunderstand, the complexity that you've added will only be useful if you mean to provide the ability to extend the base form with additional fields. Good if you're building in extensibility, but bad if you just want to collect a few fields.

2. Assuming that you need to keep the complexity, you can't set formdata.saveas as a unique key because you're inserting non-unique data. Unique means just that: the same value can't be duplicated in that field in the whole table.

tomws
03-18-2009, 10:39 PM
echo $query;

INSERT INTO formdata (userid, fieldid, value, saveas) VALUES('27', 'purchaser', 'me', 'test1'),('27', 'orderno', '1234', 'test1') ON DUPLICATE KEY UPDATE fieldid=VALUES(fieldid), value=VALUES(value), saveas=VALUES(saveas)

What happens if you plug that query straight into phpMyAdmin?

Jodarecode
03-18-2009, 10:47 PM
Sorry about not getting you the correct information, I'm still a newbie and I hope you can bear with me.

yes this is an extensive form up to 250 fields which saves and retrieves just fine. I just narrowed it down to a couple fields to show in the forum.

I made the userid primary

if I plug
INSERT INTO formdata (userid, fieldid, value, saveas) VALUES('27', 'purchaser', 'me', 'test1'),('27', 'orderno', '1234', 'test1') ON DUPLICATE KEY UPDATE fieldid=VALUES(fieldid), value=VALUES(value), saveas=VALUES(saveas)

I get:http://img8.imageshack.us/img8/559/dbimg6.jpg

and spits outhttp://img24.imageshack.us/img24/647/dbimg7.jpg

no errors

tomws
03-18-2009, 11:12 PM
Then I think it's doing what it should since userid is primary key. It inserted the first value set and then, since the second value set has the same key, it updates what was just inserted. Or else, there is already data in the table with userid=27 and it does two updates, but it's the same concept either way. One row for each userid is all there will be by using that as the primary key.

Given my little understanding of your project, I don't think that's what you want to happen. You at least want to keep a record of several (~250) answers per user. How many complete answer sets do you want to track for each user? One or many?

Jodarecode
03-18-2009, 11:21 PM
Many, that's why I made it so the saveas is the name of the form so they can get to the data coinciding with that form name for that particular user.

I hope that answered your question

tomws
03-18-2009, 11:26 PM
Ah! I think it's finally becoming clear after 30 posts!

And I'm guess that you also want to allow users to modify previous answers, yes? Is that why you're looking to add in the REPLACE or UPDATE?

Jodarecode
03-18-2009, 11:42 PM
Yep

Thats why I was hoping this would work:

$query = "REPLACE 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);

tomws
03-18-2009, 11:46 PM
Got it. Much clearer now. I'm about to leave for the day, so let me shoot this off.

Since you want to allow users to change answers, then the data arrangement is going to complicate that. There can't be any unique or primary key in the current formdata dataset of userid, fieldid, value, and saveas because of the requirements. That screws up grabbing a key as the access point for the combination.

I had a similar situation on an application I was modifying. I needed to access a row from the db to update it, but also didn't have a key that I could grab. My solution (good or bad) was to add another field to the problem table and make it auto-increment primary key. Then when I needed to pull that data for an edit, I also pulled that key, which I called simply "row". On the edit form, I tied in a hidden input with the value of "row" which then allowed me to take any changes in the form back to the correct row in the table.

Where we differ a bit, though, is that my context was an edit page and yours seems to be a combination of insert and edit. Perhaps setting a flag with another hidden input somewhere would allow you to use that idea to combine the two. Not quite sure and don't have time left to consider it right now.

Any of that make sense?

Jodarecode
03-18-2009, 11:49 PM
yes, Thanks a bunch!!
I'll see what I can figure out from here and re-post if more help is needed.

wuyou013
03-19-2009, 05:37 AM
The code you posted never does an update. You build up an UPDATE clause but never do anything with it. At least not in what you posted.

tomws
03-19-2009, 02:50 PM
Actually, he does run the query. It's just not included in the posted code.

Jodarecode
03-19-2009, 07:08 PM
So, I think I figured it out.

To recap:
userid is always the same for the user whos logged in.
fieldid is all the fields in the array in $fieldstoinsert.
value is all the values that will be inserted and/or updated pertaining to the fieldid.
saveas is always the same for the saved form value sets and different for each form saved

So I had an idea and made fieldid primary, I figured since there was nothing with the same record in the column "fieldid", that it might work.

using:

$query = mysql_query("SELECT * from formdata WHERE saveas='$saveas'");

$fieldstoinsert=array('purchaser', 'orderno', 'contact');

$query = "REPLACE 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);

To my surprise it seems to work perfectly, it INSERTs and/or UPDATEs at the same time.

I'm not sure if that was sloppy coding as the rest but it seems to work with no errors.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum