Start Free TrialLog in
Avatar of jws2bay
jws2bay

asked on 

Update record in php/mySQL

I am working with php/mySQL servers.  I have a page which I have fields to match the contents of a record in my table.  I want to allow the user to edit the contents and update the record.  I have used a session var. to setup the record, and this works fine.  When I attempt to submit the form and update the record it seems to bounce back to the same page.  I  work in dreamweaver, but this site doesn't allow me to use the dreamweaver tools, so I have been trying to hand code the page. I have attached my current code.
I am also sending out emails when the page is submitted.

 Any help is appreciated.

$editFormAction = $_SERVER['PHP_SELF'];
if (isset($_SERVER['QUERY_STRING'])) {
  $editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);
}
 
if ((isset($_POST["MM_update"])) && ($_POST["MM_update"] == "form1")) {
  $updateSQL = sprintf("UPDATE Contractors SET First_Name=%s, Last_Name=%s, Company=%s, Street=%s, Street_2=%s, City=%s, State_or_Province=%s, Postal_Code=%s, Country=%s, Email_Address=%s, Phone=%s, Fax=%s, License_Number=%s, License_State=%s, Area_of_Expertise=%s, Area_Codes_Supported=%s, Referrals_Requested=%s, Show_Information=%s, WHERE Username=%s",
                       GetSQLValueString($_POST['First_Name'], "text"),
                       GetSQLValueString($_POST['Last_Name'], "text"),
                       GetSQLValueString($_POST['Company'], "text"),
                       GetSQLValueString($_POST['Street'], "text"),
                       GetSQLValueString($_POST['Street_2'], "text"),
                       GetSQLValueString($_POST['City'], "text"),
                       GetSQLValueString($_POST['State'], "text"),
                       GetSQLValueString($_POST['Postal_Code'], "text"),
                       GetSQLValueString($_POST['Country'], "text"),
                       GetSQLValueString($_POST['Email'], "text"),
                       GetSQLValueString($_POST['Phone'], "text"),
                       GetSQLValueString($_POST['Fax'], "text"),
                       GetSQLValueString($_POST['License_no'], "text"),
                       GetSQLValueString($_POST['License_state'], "text"),
                       GetSQLValueString($_POST['Expertise'], "text"),
                       GetSQLValueString($_POST['Area_Codes'], "text"),
                       GetSQLValueString(isset($_POST['Referals']) ? "true" : "", "defined","1","0"),
                       GetSQLValueString(isset($_POST['Show_information']) ? "true" : "", "defined","1","0"),
                       GetSQLValueString($_POST['username'], "text"));
 
  mysql_select_db($database_squirrelcart, $squirrelcart);
  $Result1 = mysql_query($insertSQL, $squirrelcart) or die(mysql_error());
 
  $updateGoTo = "WL1_Reg_Post_Update.php";
  if (isset($_SERVER['QUERY_STRING'])) {
    $updateGoTo .= (strpos($updateGoTo, '?')) ? "&" : "?";
    $updateGoTo .= $_SERVER['QUERY_STRING'];
  }
  header(sprintf("Location: %s", $updateGoTo));
}
 
?>
 
 
<?php
mysql_select_db($database_squirrelcart, $squirrelcart);
$query_State = "SELECT * FROM States";
$State = mysql_query($query_State, $squirrelcart) or die(mysql_error());
$row_State = mysql_fetch_assoc($State);
$totalRows_State = mysql_num_rows($State);
 
$colname_Contractor = "-1";
if (isset($_SESSION['MM_Username'])) {
  $colname_Contractor = $_SESSION['MM_Username'];
}
mysql_select_db($database_squirrelcart, $squirrelcart);
$query_Contractor = sprintf("SELECT * FROM Contractors WHERE username = %s", GetSQLValueString($colname_Contractor, "text"));
$Contractor = mysql_query($query_Contractor, $squirrelcart) or die(mysql_error());
$row_Contractor = mysql_fetch_assoc($Contractor);
$totalRows_Contractor = mysql_num_rows($Contractor);
?>
 
<?php require_once("WA_Universal_Email/Mail_PHP.php"); ?>
<?php require_once("WA_Universal_Email/MailFormatting_PHP.php"); ?>
<?php
if ((($_SERVER["REQUEST_METHOD"] == "POST") && (isset($_SERVER["HTTP_REFERER"]) && strpos($_SERVER["HTTP_REFERER"], $_SERVER["SERVER_NAME"].$_SERVER["PHP_SELF"]) > 0) && isset($_POST)))     {
  //WA Universal Email object="Mail"
  //Send Loop Once Per Entry
  $RecipientEmail = "".((isset($_POST["Email"]))?$_POST["Email"]:"")  ."";include("WA_Universal_Email/WAUE_WL1_Reg_New_User3_1.php");
  $RecipientEmail = "jerry@eshowerdoor.com";include("WA_Universal_Email/WAUE_WL1_Reg_New_User3_1.php");
}
?>

Open in new window

Adobe Dreamweaver
Avatar of undefined
Last Comment
jws2bay
Avatar of Jason C. Levine
Jason C. Levine
Flag of United States of America image
>> When I attempt to submit the form and update the record it seems to bounce back to the same page.

According to the code, when you submit the form you should get redirected to:

WL1_Reg_Post_Update.php

Is that what is happening?
Avatar of jws2bay
jws2bay

ASKER

No  - it comes back to the same page.

WL1_Reg_Update.php

It doesn't seem to be doing the update.  If I make changes the fields are changed back to the original contents when it refresh the page.
SOLUTION
Avatar of Jason C. Levine
Jason C. Levine
Flag of United States of America image
Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of jws2bay
jws2bay

ASKER

I am populating the form using a recordset which finds the record using a session var which has the username which is unique in the table, but is not the primary key for the table.  The primary key for the table is the record number.  I have added a field in the form for the record number, and have changed the update statement so that the "WHERE" is the record number.  Modified code is attached.

The page still acts the same.  Does not update the data, comes back to the same page, but does send the emails.
$editFormAction = $_SERVER['PHP_SELF'];
if (isset($_SERVER['QUERY_STRING'])) {
  $editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);
}
 
if ((isset($_POST["MM_update"])) && ($_POST["MM_update"] == "form1")) {
  $updateSQL = sprintf("UPDATE Contractors SET First_Name=%s, Last_Name=%s, Company=%s, Street=%s, Street_2=%s, City=%s, State_or_Province=%s, Postal_Code=%s, Country=%s, Email_Address=%s, Phone=%s, Fax=%s, License_Number=%s, License_State=%s, Area_of_Expertise=%s, Area_Codes_Supported=%s, Referrals_Requested=%s, Show_Information=%s, Username=%s, WHERE record_number=%s",
					   GetSQLValueString($_POST['First_Name'], "text"),
                       GetSQLValueString($_POST['Last_Name'], "text"),
                       GetSQLValueString($_POST['Company'], "text"),
                       GetSQLValueString($_POST['Street'], "text"),
                       GetSQLValueString($_POST['Street_2'], "text"),
                       GetSQLValueString($_POST['City'], "text"),
                       GetSQLValueString($_POST['State'], "text"),
                       GetSQLValueString($_POST['Postal_Code'], "text"),
                       GetSQLValueString($_POST['Country'], "text"),
                       GetSQLValueString($_POST['Email'], "text"),
                       GetSQLValueString($_POST['Phone'], "text"),
                       GetSQLValueString($_POST['Fax'], "text"),
                       GetSQLValueString($_POST['License_no'], "text"),
                       GetSQLValueString($_POST['License_state'], "text"),
                       GetSQLValueString($_POST['Expertise'], "text"),
                       GetSQLValueString($_POST['Area_Codes'], "text"),
                       GetSQLValueString(isset($_POST['Referals']) ? "true" : "", "defined","1","0"),
                       GetSQLValueString(isset($_POST['Show_information']) ? "true" : "", "defined","1","0"),
                       GetSQLValueString($_POST['username'], "text"),
					   GetSQLValueString($_POST['record_no'], "text"));
 
  mysql_select_db($database_squirrelcart, $squirrelcart);
  $Result1 = mysql_query($insertSQL, $squirrelcart) or die(mysql_error());
 
  $updateGoTo = "WL1_Reg_Post_Update.php";
  if (isset($_SERVER['QUERY_STRING'])) {
    $updateGoTo .= (strpos($updateGoTo, '?')) ? "&" : "?";
    $updateGoTo .= $_SERVER['QUERY_STRING'];
  }
  header(sprintf("Location: %s", $updateGoTo));
}
 
?>
 
 
<?php
mysql_select_db($database_squirrelcart, $squirrelcart);
$query_State = "SELECT * FROM States";
$State = mysql_query($query_State, $squirrelcart) or die(mysql_error());
$row_State = mysql_fetch_assoc($State);
$totalRows_State = mysql_num_rows($State);
 
$colname_Contractor = "-1";
if (isset($_SESSION['MM_Username'])) {
  $colname_Contractor = $_SESSION['MM_Username'];
}
mysql_select_db($database_squirrelcart, $squirrelcart);
$query_Contractor = sprintf("SELECT * FROM Contractors WHERE username = %s", GetSQLValueString($colname_Contractor, "text"));
$Contractor = mysql_query($query_Contractor, $squirrelcart) or die(mysql_error());
$row_Contractor = mysql_fetch_assoc($Contractor);
$totalRows_Contractor = mysql_num_rows($Contractor);
?>
 
<?php require_once("WA_Universal_Email/Mail_PHP.php"); ?>
<?php require_once("WA_Universal_Email/MailFormatting_PHP.php"); ?>
<?php
if ((($_SERVER["REQUEST_METHOD"] == "POST") && (isset($_SERVER["HTTP_REFERER"]) && strpos($_SERVER["HTTP_REFERER"], $_SERVER["SERVER_NAME"].$_SERVER["PHP_SELF"]) > 0) && isset($_POST)))     {
  //WA Universal Email object="Mail"
  //Send Loop Once Per Entry
  $RecipientEmail = "".((isset($_POST["Email"]))?$_POST["Email"]:"")  ."";include("WA_Universal_Email/WAUE_WL1_Reg_New_User3_1.php");
  $RecipientEmail = "jerry@eshowerdoor.com";include("WA_Universal_Email/WAUE_WL1_Reg_New_User3_1.php");
}
?>

Open in new window

Avatar of Jason C. Levine
Jason C. Levine
Flag of United States of America image
Does the form field actually get the record number when you access the page?

I think I may need to look at the form source code...
Avatar of jws2bay
jws2bay

ASKER

Yes it is filling in the correct record number.

Go to
http://www.eshowerdoor.com/WL1_Reg_login.php

login using  
Username  = Test_User
Password = 888login

on the next page you should see the user = Micky Mouse in the upper right of the page.  In the first section on the left side click on the link.

Update Registry Data

This takes you to the page we are working on.  Change a field and click the REVISE button to see what happens.


ASKER CERTIFIED SOLUTION
Avatar of Jason C. Levine
Jason C. Levine
Flag of United States of America image
Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of jws2bay
jws2bay

ASKER

Your right about starting with the input page.  I made the change, and it doesn't come back to the same page any more, but I get an error message.

Query was empty
Avatar of Jason C. Levine
Jason C. Levine
Flag of United States of America image
Okay.  Is record_no a text field or an integer.  Because here you are telling DW it is text:

GetSQLValueString($_POST['record_no'], "text"));
Avatar of jws2bay
jws2bay

ASKER

Your right in that the record number is an interger.  I made the change from "text" to "int", but I still get the same results.
Avatar of jws2bay
jws2bay

ASKER

I found another error caused from starting with an insert form.

 mysql_select_db($database_squirrelcart, $squirrelcart);
  $Result1 = mysql_query($insertSQL, $squirrelcart) or die(mysql_error());

should be an update not insert

 mysql_select_db($database_squirrelcart, $squirrelcart);
  $Result1 = mysql_query($updateSQL, $squirrelcart) or die(mysql_error());

Now I get new error message.

"You have an error in your SQL syntax near 'WHERE record_number=41' at line 1

Do I need to do something more than call the field  "int"?

Avatar of Jason C. Levine
Jason C. Levine
Flag of United States of America image
um.

MySQL may not like the underscore in the column.  Edit the query to enclose that column name in backticks.

Also post the whole page code again so I can test.
Avatar of jws2bay
jws2bay

ASKER

Problem solved.  Had an extra camma in the update statement.

Works fine.  Thanks for the help.

Adobe Dreamweaver
Adobe Dreamweaver

Adobe Dreamweaver is a web development tool featuring both WYSIWYG and code editors that allow the user to quickly and easily build websites in a wide variety of languages, including ASP, ASP.NET, ColdFusion and PHP. It also offers support for CSS and JavaScript.

11K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts

  • "Invaluable tool for our organization"

    Josh Regalski

  • "Your help has saved me hundreds of hours of internet surfing."

    @fblack61

  • "Just a dang good service!"

    @golferski

  • "Worth every penny."

    Steve Hougom

  • "It’s been a life saver."

    Maria Halt

  • "Best support site I’ve seen!"

    Bob Schneider

  • "I would be lost without them."

    @fblack61

  • "Saved my job multiple times."

    Walt Forbes

  • "I love this site."

    Maria Duwe

  • "Friendly respectful help."

    Andrew Kowtalo

  • "Such top-notch experts."

    @magento

  • "The best money I have ever spent."

    @rwheeler23

  • "Beyond any comprehensible value"

    George Morris

  • "Invaluable tool for our organization"

    Josh Regalski

Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo
© 1996-2023 Experts Exchange, LLC. All rights reserved. Covered by US Patent