Errors when modifying Record Update form code
3 posts by 2 authors in: Forums > CMS Builder
Last Post: January 28, 2019 (RSS)
Hi all,
I have an older record update form that used the following code to update a record in a multi-record section:
The update code is called if a check finds a match for for an email address in one of the table's records.
<?php
$query = "UPDATE `{$TABLE_PREFIX}publcity_listings` SET
source = '".mysql_escape( @$_REQUEST['source'] )."',
media_type = '".mysql_escape( @$_REQUEST['media_type'] )."',
locale = '".mysql_escape( $_REQUEST['locale'] )."',
updatedByUserNum = '0',
updatedDate = NOW()
WHERE email_address = '".mysql_escape( @$_REQUEST['email_address'] )."'";
mysql_query($query) or die("MySQL Error:<br/>\n". htmlspecialchars(mysql_error()) . "\n");
$userNum = mysql_insert_id();
?>
I’m trying to update the code so it works with current mysql standards with this code:
<?php
$email = @$_REQUEST['email_address'];
$tablename = 'publcity_listings';
$colsToValues = array();
$colsToValues['source'] = $_REQUEST['source'];
$colsToValues['media_type'] = $_REQUEST['media_type'];
$colsToValues['locale'] = $_REQUEST['locale'];
$colsToValues['description'] = $_REQUEST['description'];
$colsToValues['updatedByUserNum'] = 0;
mysql_update($tablename, $email, $colsToValues, true);
?>
But the code throws the following errors.
Warning: Invalid argument supplied for foreach() in /home4/zcfzmsmy/public_html/jazzonjstreet/cmsAdmin/lib/mysql_functions.php on line 652 MySQL Error: 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 `num` = 0 AND (`source` = '7' AND `media_type` = '9' AND `locale` = '2' AN' at line 1 - in publicity-addA.php on line 154 by mysql_update()
SQL syntax is a mystery to me.
Any thoughts?
Thanks,
Jerry Kornbluth
Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php
By daniel - January 28, 2019
Hi Jerry,
The correct usage of the mysql_update() function is:
mysql_update($tableName, $recordNum, $customWhere, $colsToValues);Since you're using the record's email rather than num to update, you'll want to use null for the $recordNum, and pass the email to $customWhere.
mysql_update($tableName, null, ['email_address' => $email], $colsToValues);Hope that helps! Let me know if you have any other questions.
Thanks,
Technical Lead
interactivetools.com
Thanks Daniel,
I understand it better now and got it working with a few modifications.
For anyone who has the same concerns, here's what I used for the update record code:
<?php $where = "email_address = '$email_address'"?>
<?php
$tableName = 'publcity_listings';
$colsToValues = array();
$colsToValues['first_name'] = $_REQUEST['first_name'];
$colsToValues['last_name'] = $_REQUEST['last_name'];
$colsToValues['email_address'] = $_REQUEST['email_address'];
$colsToValues['street_address'] = $_REQUEST['street_address'];
$colsToValues['city'] = $_REQUEST['city'];
$colsToValues['state'] = $_REQUEST['state'];
$colsToValues['zip'] = $_REQUEST['zip'];
$colsToValues['phone'] = $_REQUEST['phone'];
$colsToValues['cell'] = $_REQUEST['cell'];
$colsToValues['description'] = $_REQUEST['description'];
mysql_update($tableName, null, $where, $colsToValues);
?>
<?php
// on success
$first_name = @$_REQUEST['first_name'] ;
$last_name = @$_REQUEST['last_name'] ;
$_REQUEST = array(); // clear the form values
$errorsAndAlerts = "The contact information for $first_name $last_name has been updated.<br>You can now enter another email address.";
And here's what I used for the insert record code:
$tableName = 'publcity_listings';
$colsToValues = array();
$colsToValues['createdDate='] = 'NOW()';
$colsToValues['updatedDate='] = 'NOW()';
$colsToValues['createdByUserNum'] = 0;
$colsToValues['updatedByUserNum'] = 0;
$colsToValues['first_name'] = $_REQUEST['first_name'];
$colsToValues['last_name'] = $_REQUEST['last_name'];
$colsToValues['email_address'] = $_REQUEST['email_address'];
$colsToValues['street_address'] = $_REQUEST['street_address'];
$colsToValues['city'] = $_REQUEST['city'];
$colsToValues['state'] = $_REQUEST['state'];
$colsToValues['zip'] = $_REQUEST['zip'];
$colsToValues['phone'] = $_REQUEST['phone'];
$colsToValues['description'] = $_REQUEST['description'];
$userNum = mysql_insert($tableName, $colsToValues, true);
// On Success
$first_name = $_REQUEST['first_name'];
$last_name = $_REQUEST['last_name'] ;
$_REQUEST = array(); // clear form values
$errorsAndAlerts = "A record for $first_name $last_name has been successfully created.<br>You can now enter another email address.";
All of the $_REQUEST fields reference fields in the insert/update form.
There's a much more complete recipe in the CMSB Cookbook.
Best,
Jerry Kornbluth
Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php