Update a record based on matching field value in form

5 posts by 2 authors in: Forums > CMS Builder
Last Post: December 15, 2011   (RSS)

By gkornbluth - December 14, 2011

Hi All,

Well, obviously I’m going about this wrong, because it doesn’t work... (And Ill probably feel pretty silly once I find out the solution)

I’m trying to update a record in the table customer_uploads only if the value of a variable ($activation_code) matches the value in the existing record’s activation_code field. I thought that the code below would do that, but instead it updates all the records.

Here’s what I have. (There are other variables and fields but I left them out for clarity.)

Hope someone is willing to help.

Thanks,

Jerry Kornbluth

$activation_code=$_POST['activation_code'];


// load records
list($customer_uploadsRecords, $customer_uploadsMetaData) = getRecords(array(
'tableName' => 'customer_uploads',
'where' => 'activation_code = "$activation_code"',
));

mysql_query("UPDATE `{$TABLE_PREFIX}customer_uploads` SET

email = '".$email."',
first_name = '".$first_name."',
last_name = '".$last_name1."',
street_address = '".$street_address."',

or die("MySQL Error updating Record:<br/>\n". htmlspecialchars(mysql_error()) . "\n");
$userNum = mysql_insert_id();
@saveUploadFromFilepath('customer_uploads', 'uploads', $userNum, '123456789' , $path_of_uploaded_file);

$errors='Thank You<br /><br />Your Order Has Been Successfully Placed.';
}

?>
The first CMS Builder reference book is now available on-line!







Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php

Re: [gkornbluth] Update a record based on matching field value in form

By Jason - December 15, 2011 - edited: December 15, 2011

Hi Jerry,

In order to not have the query update all records, you need to add a WHERE clause to your update query.

For example, if you wanted to update ALL records with that given activation code, you can try this:

mysql_query("UPDATE `{$TABLE_PREFIX}customer_uploads` SET
email = '".$email."',
first_name = '".$first_name."',
last_name = '".$last_name1."',
street_address = '".$street_address."'
WHERE activation_code = '".mysql_escape($activation_code)."'")

or die("MySQL Error updating Record:<br/>\n". htmlspecialchars(mysql_error()) . "\n");

Hope this helps
---------------------------------------------------
Jason Sauchuk - Project Manager
interactivetools.com

Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/

Re: [Jason] Update a record based on matching field value in form

By gkornbluth - December 15, 2011

Hi Jason,

I thought I would have to put in a where clause but I kept getting syntax errors every time I tried to put one in.
I'm still getting a syntax error:
MySQL Error updating Record:
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 'WHERE activation_code = 'T87345'' at line 19


Here's the actual code that's generating the error (with or without the load records call):

//load records
list($customer_uploadsRecords, $customer_uploadsMetaData) = getRecords(array(
'tableName' => 'customer_uploads',

));

mysql_query("UPDATE `{$TABLE_PREFIX}customer_uploads` SET

email = '".$email."',
first_name = '".$first_name."',
last_name = '".$last_name1."',
street_address = '".$street_address."',
city = '".$city."',
state = '".$state."',
zip = '".$zip."',

sales_id = '".$sales_id."',
createdDate = NOW(),
updatedDate = NOW(),
createdByUserNum = '0',
updatedByUserNum = '0',
WHERE activation_code = '".mysql_escape($activation_code)."'")
or die("MySQL Error updating Record:<br/>\n". htmlspecialchars(mysql_error()) . "\n");
$userNum = mysql_insert_id();
@saveUploadFromFilepath('customer_uploads', 'uploads', $userNum, '123456789' , $path_of_uploaded_file);
$errors='Thank You<br /><br />Your Portrait Order Has Been Successfully Placed.';
}

?>

ANd I've attached the complete file
The first CMS Builder reference book is now available on-line!







Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php
Attachments:

ordernow3.php 12K

Re: [gkornbluth] Update a record based on matching field value in form

By Jason - December 15, 2011

Hi Jerry,

Try removing the last comma in your field list, the one on this line:

updatedByUserNum = '0',

That should take care of it. I don't know how many times I got caught with that one myself.
---------------------------------------------------
Jason Sauchuk - Project Manager
interactivetools.com

Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/