I have a script that is built to update data in a MySQL table. My problem is, I'm unable to simplify the MySQL UPDATE command into one line or else previously stored data becomes "corrupt" (the row updates the column 'name' with the number 0). Here is the script and HTML page that goes along with it. <?php // Edit Account Form if(isset($_POST['edit_submit'])) { require("db_connect.inc.php"); $name = mysql_real_escape_string(trim($_POST['name'])); $company = mysql_real_escape_string(trim($_POST['company'])); $number = trim($_POST['number']); $location = mysql_real_escape_string(trim($_POST['location'])); $edit_error = array(); $edit_error_msg = ""; if(empty($name)) { $edit_error['name'] = "Plese enter your full name."; } else if(!eregi('^[[:alpha:] \'\-]+$', $name)) { $edit_error['name'] = "Please use valid characters in your full name."; } if(!empty($company)) { if(!eregi('^[[:alpha:] \'\.\,\-]+$', $company)){ $edit_error['company'] = "Please use valid characters in the name of your company."; } } if(empty($number)) { $edit_error['number'] = "Please enter your phone number."; } else if(!eregi('^[0-9]{3}-[0-9]{3}-[0-9]{4}$', $number)) { $edit_error['number'] = "Please use valid characters for your phone number."; } if(!empty($location)) { if(!eregi('^[[:alpha:] \,\-]+$', $location)) { $edit_error['location'] = "Please use valid characters in the location name."; } } if(empty($edit_error)) { $change_query = 'UPDATE users SET name = "' . $name . '" WHERE id = "' . $_SESSION['id'] . '"'; $change_result = mysql_query($change_query) or die('error:' . mysql_error()); $change_query2 = 'UPDATE users SET company = "' . $company . '" WHERE id = "' . $_SESSION['id'] . '"'; $change_result2 = mysql_query($change_query2) or die('error:' . mysql_error()); $change_query3 = 'UPDATE users SET number = "' . $number . '" WHERE id = "' . $_SESSION['id'] . '"'; $change_result3 = mysql_query($change_query3) or die('error:' . mysql_error()); $change_query4 = 'UPDATE users SET location = "' . $location . '" WHERE id = "' . $_SESSION['id'] . '"'; $change_result4 = mysql_query($change_query4) or die('error:' . mysql_error()); if(mysql_affected_rows() == 1) { $_SESSION['name'] = $name; $_SESSION['company'] = $company; $_SESSION['number'] = $number; $_SESSION['location'] = $location; } } else { $edit_error_msg = "<ul>\n"; foreach($edit_error as $msg) { $edit_error_msg .= "<li>$msg</li>\n"; } $edit_error_msg .= "</ul>\n"; } } ?> PHP: <?php include("header.inc.php") ?> <?php include("includes/edit.inc.php") ?> <div id="secondary"> <h1>Edit My Account</h1> <p class="p_length">Update your account with the most relevant information so that we can provide the best support for you.</p> <?php if (isset($edit_error_msg)) { echo "<span class='error'>$edit_error_msg</span>\n"; } ?> <form method="post" action="<?php $_SERVER['PHP_SELF'] ?>"> <fieldset> <ol> <li> <label for="name">Full Name</label> <input type="text" name="name" value="<?php if(isset($_SESSION['name'])){ echo $_SESSION['name']; } ?>" /> </li> <li> <label for="company">Company</label> <input type="text" name="company" value="<?php if(isset($_SESSION['company'])){ echo $_SESSION['company']; } ?>" /> </li> <li> <label for="location">Location</label> <input type="text" name="location" value="<?php if(isset($_SESSION['location'])){ echo $_SESSION['location']; } ?>" /> </li> <li> <label for="number">Phone Number</label> <input type="text" name="number" value="<?php if(isset($_SESSION['number'])){ echo $_SESSION['number']; } ?>"> </li> </ol> <fieldset> <input type="submit" name="edit_submit" id="submit" value="Edit Account"> </fieldset> </fieldset> </form> </div> <div class="push"> </div> </div> <?php include("footer.inc.php") ?> HTML:
Why you can't add it into 1 mysql update query ? // This is just an example $var1 = "variable 1"; $var2 = "variable 2"; mysql_query("UPDATE something SET field1='$var1' AND field2='$var2'"); PHP: Try this : $session_id = $_SESSION['id']; $change_query = "UPDATE users SET name ='$name' WHERE id = '$session_id'"; mysql_query($change_query); PHP:
I understand but, I don't have the session's ID stored in the table, but I do have a unique ID for each row, which is what I am using instead. It also turns out, I can update and see the changes reflected on my fields only when the 'Location' field has been entered. Otherwise, nothing is reflected in the HTML, but the updates are changed in the table.
1. You should validate before applying mysql_real_escape_string, because mysql_real_escape_string will add a backslash if there is an apostrophe, causing your validation to fail on "John O'Flaherty" 2. You should use preg instead of ereg which is being removed from PHP. 3. Why don't you do all the updates at once? It will run faster: update table set a='A', b='B', c='C' where x=X Code (markup): 4. Also, doing all updates at once will fix your bug. mysql_affected_rows() reports on the most recent query only. If location doesn't get changed, then no rows are affected as far as mysql_affected_rows() is concerned. Putting all the updates in a single query will solve this.
I agree with 1 and 2, but if you read at the top, I said that the one of the columns would reset itself to 0 when I tried putting it all into one line.......
All I can say is, having multiple queries and then setting your session variables conditionally on the result of a single call to mysql_affected_rows() is definitely what's causing your current bug. It's also standard practice to update multiple fields at once, so if it isn't working, post the code that didn't work and we can probably find the problem. Done properly, it will work, and it will solve your problem, and it will make your code more efficient, and it will teach you the right way to do things in the future.