By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,943 Members | 1,875 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,943 IT Pros & Developers. It's quick & easy.

Update Doesn't Work

P: 22
My UPDATE script just doesn't work. I just couldn't find what was wrong, every variable is declared. Please find what's wrong with my code. Thanks.

***THIS IS THE CODE FROM WHERE THE EDITING WILL TAKE PLACE***
Expand|Select|Wrap|Line Numbers
  1. <?php require_once("includes/db_connect.php"); ?>
  2. <?php require_once("includes/functions.php"); ?>
  3. <?php
  4.  
  5.     $equipid = mysqli_real_escape_string($db_cxn, trim(strip_tags($_POST['equipid']))); //EquipID variable
  6.  
  7.     $query = "SELECT * FROM tblequipmentmasterlist ";
  8.     $query .= "WHERE equipid =" . $equipid;
  9.     $query .= " LIMIT 1";
  10.  
  11.     $query_result = mysqli_query($db_cxn, $query);
  12.  
  13.     while ($row = mysqli_fetch_array ($query_result)) {
  14.             //Variables to be used to output on the textfields
  15.                 $equipname = $row['equipname'];
  16.                 $manufacturer = $row['manufacturer'];
  17.                 $serno = $row['serno'];
  18.                 $modelno = $row['modelno'];
  19.                 $capacity = $row['capacity'];
  20.                 $curloc = $row['curlocation'];
  21.                 $equipcode = $row['equipcode'];
  22.                 $seqno = $row['seqno'];
  23.                 $adddate = $row['adddate'];
  24.              }
  25.  
  26. ?>
  27. <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
  28. <html xmlns="http://www.w3.org/1999/xhtml">
  29. <head>
  30. <title>EST-Equipment Intranet-Calibration-Update Equipment Record</title>
  31. <meta http-equiv="Content-Language" content="English" />
  32. <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
  33. <link rel="stylesheet" type="text/css" href="css/style.css" media="screen" />
  34. <style type="text/css">
  35. <!--
  36. .style1 {
  37.     color: #000000;
  38.     font-size: medium;
  39. }
  40.  
  41. #wrap #content .right .newequipment {
  42.     font-family: Verdana, Arial, Helvetica, sans-serif;
  43.     font-size: 14px;
  44.     font-style: normal;
  45.     font-weight: lighter;
  46.     text-transform: capitalize;
  47.     text-align: left;
  48.     text-indent: 5px;
  49.     margin: 10px;
  50.     padding: 10px;
  51.     width: 350px;
  52. }
  53. -->
  54. </style>
  55.  
  56. <?php include("includes/calheader.php"); ?>
  57.  
  58. <h2>Update Equipment Information Form</h2>
  59. <form action = "processform_update.php" method = "POST">
  60. <div class="DataInput">
  61.   <table width="707" border="0" cellpadding="0" cellspacing="0" id="Equipname6">
  62.     <tr>
  63.       <td width="200" class="style1">Equipment ID No.</td>
  64.       <td width="507"><input name="equipid" type="text" id="equipid" tabindex="" value="<?php echo $equipid; ?>" size="7" />
  65.      </tr>
  66.   </table>
  67. </div>
  68. <div class="DataInput">
  69.  <table width="707" border="0" cellpadding="0" cellspacing="0" id="Equipname">
  70.     <tr>
  71.       <td width="200" class="style1">Equipment Name</td>
  72.       <td width="507">
  73.         <input name="equipname" type="text" id="equipname" tabindex="1" value="<?php echo $equipname; ?>" size="40" />
  74.       </td>
  75.     </tr>
  76.   </table>
  77. </div>
  78. <div class="DataInput">
  79.  <table width="708" border="0" cellpadding="0" cellspacing="0" id="Equipname2">
  80.     <tr>
  81.       <td width="200" class="style1">Manufacturer</td>
  82.       <td width="508">
  83.         <input name="manufacturer" type="text" id="manufacturer" tabindex="2" value="<?php echo $manufacturer; ?>" size="40" />
  84.       </td>
  85.     </tr>
  86.   </table>
  87. </div>
  88. <div class="DataInput">
  89.  <table width="561" border="0" cellpadding="0" cellspacing="0" id="Equipname3">
  90.     <tr>
  91.       <td width="200" class="style1">Serial No. (if any)</td>
  92.       <td width="361">
  93.       <input name="serno" type="text" id="serno" tabindex="3" value="<?php echo $serno; ?>" size="30" />
  94.       </td>
  95.     </tr>
  96.   </table>
  97. </div>
  98. <div class="DataInput">
  99.  <table width="561" border="0" cellpadding="0" cellspacing="0" id="Equipname4">
  100.     <tr>
  101.       <td width="200" class="style1">Model No. (if any)</td>
  102.       <td width="361">
  103.       <input name="modelno" type="text" id="modelno" tabindex="4" value="<?php echo $modelno; ?>" size="30" />
  104.       </td>
  105.     </tr>
  106.   </table>
  107. </div>
  108. <div class="DataInput">
  109.   <label class="style6"></label>
  110.   <table width="707" border="0" cellpadding="0" cellspacing="0" id="Equipname5">
  111.     <tr>
  112.       <td width="200" class="style1">Capacity / Range</td>
  113.       <td width="507">
  114.       <input name="capacity" type="text" id="capacity" tabindex="5" value="<?php echo $capacity; ?>" size="40" />
  115.       </td>
  116.     </tr>
  117.   </table>
  118. </div>
  119. <div class="DataInput">
  120.     <label class="style6"></label>
  121.   <table width="707" border="0" cellpadding="0" cellspacing="0" id="Equipname6">
  122.     <tr>
  123.       <td width="200" class="style1">Current Location</td>
  124.       <td width="507">
  125.       <input name="curlocation" type="text" id="curlocation" value="<?php echo $curloc; ?>" tabindex="6" size="20" />
  126.       <a href='locationcode.php' target='_blank' class='style7'>...Click here for definition...</a> 
  127.       </td>
  128.     </tr>
  129.   </table>
  130. </div>
  131. <div class="DataInput">
  132.     <label class="style6"></label>
  133.   <table width="707" border="0" cellpadding="0" cellspacing="0" id="Equipname5">
  134.     <tr>
  135.       <td width="200" class="style1">Equipment Code</td>
  136.       <td width="507">
  137.       <input name="equipcode" type="text" id="equipcode" value="<?php echo $equipcode; ?>" tabindex="7" size="20" />
  138.       <a href='equipmentcode.php' target='_blank' class='style7'>...Click here for definition...</a> 
  139.       </td>
  140.     </tr>
  141.   </table>
  142. </div>
  143. <div class="DataInput">
  144.  <table width="561" border="0" cellpadding="0" cellspacing="0" id="Equipname8">
  145.     <tr>
  146.       <td width="200" class="style1">Sequence Number</td>
  147.       <td width="361">
  148.       <input name="seqno" type="text" id="seqno" tabindex="8" size="7" maxlength="7" value="<?php echo $seqno; ?>" />
  149.       </td>
  150.     </tr>
  151.   </table>
  152. </div>
  153. <div class="DataInput">
  154.  <label class="style6"></label>
  155.   <table width="707" border="0" cellpadding="0" cellspacing="0" id="Equipname5">
  156.     <tr>
  157.       <td width="200" class="style1">Date Added</td>
  158.       <td width="507">
  159.       <input name="adddate" type="text" id="adddate" tabindex="9" value="<?php echo $adddate; ?>" size="20" /> 
  160.       <strong>YYYY-MM-DD</strong> </td>
  161.     </tr>
  162.   </table>
  163. </div>
  164. <p>&nbsp;</p>
  165. <div class="DataInput">
  166. <input type="submit" name="submit" id="submit" value="Submit" />
  167. </div>
  168. </form>
  169. <p><br />
  170. <br />
  171.   <br />
  172.   <br />
  173.   <br />
  174.   </div>
  175.  
  176. <?php require("includes/footer.php"); ?>
  177.  
***THIS IS THE UPDATE PROCESSING CODE***

Expand|Select|Wrap|Line Numbers
  1. <?php include("includes/db_connect.php"); ?>
  2. <?php require_once("includes/functions.php"); ?>
  3.  
  4. <?php
  5.  
  6.     $equipid = mysqli_real_escape_string($db_cxn, trim(strip_tags($_POST['equipid'])));
  7.     $equipname = mysqli_real_escape_string($db_cxn, trim(htmlspecialchars($_POST['equipname'])));
  8.     $manufacturer = mysqli_real_escape_string($db_cxn, trim(htmlspecialchars($_POST['manufacturer'])));
  9.     $serno = mysqli_real_escape_string($db_cxn, trim(htmlspecialchars($_POST['serno'])));
  10.     $modelno = mysqli_real_escape_string($db_cxn, trim(htmlspecialchars($_POST['modelno'])));
  11.     $capacity = mysqli_real_escape_string($db_cxn, trim(htmlspecialchars($_POST['capacity'])));
  12.     $curloc = mysqli_real_escape_string($db_cxn, trim(strip_tags($_POST['curlocation'])));
  13.     $equipcode = mysqli_real_escape_string($db_cxn, trim(strip_tags($_POST['equipcode'])));
  14.     $seqno = mysqli_real_escape_string($db_cxn, trim(strip_tags($_POST['seqno'])));    
  15.     $addDate = mysqli_real_escape_string($db_cxn, trim(strip_tags($_POST['adddate'])));
  16.  
  17.  
  18.     $query_update = "UPDATE tblequipmentmasterlist SET ";
  19.     $query_update .= "equipname = '{$equipname}', ";
  20.     $query_update .= "manufacturer = '{$manufacturer}', ";
  21.     $query_update .= "serno = '{$serno}', ";
  22.     $query_update .= "modelno = '{$modelno}', "; 
  23.     $query_update .= "capacity = '{$capacity}', "; 
  24.     $query_update .= "curlocation = '{$curloc}', "; 
  25.     $query_update .= "equipcode = '{$equipcode}', "; 
  26.     $query_update .= "seqno = '{$seqno}', "; 
  27.     $query_update .= "adddate = '{$addDate}' ";
  28.     $query_update .= "WHERE 'tblequipmentmasterlist'.'equipid' = {$equipid} LIMIT 1";
  29.  
  30.  
  31.     if (mysqli_affected_rows() == 1) {
  32.         header ("LOCATION : updatedata.php");
  33.     } else {
  34.         print_r($_POST);
  35.         var_dump($_POST);
  36.         echo "<p>Update failed : <br />". $query_update.  " <br/>Please contact the site administrator.</p>";
  37.         echo "<p>" . mysqli_error($db_cxn) . "</p>";
  38.     }
  39.  
  40. ?>
  41.  
  42. <?php 
  43.     if (isset($db_cxn)) {
  44.         mysqli_close($db_cxn); 
  45.     }
  46. ?>
  47.  
Oct 3 '11 #1

✓ answered by Rabbit

After you build the string, you need to execute it using the mysql_query($query_update) function.

Share this Question
Share on Google+
15 Replies


Rabbit
Expert Mod 10K+
P: 12,364
If you're trying to qualify a table/field/schema name, I think you have to use ` and not '.
Oct 3 '11 #2

P: 22
I tried revising the code of my PROCESSING page as per your suggestion, and still the same problem occurred. Please kindly check my code. Also, I've attached an image showing the error.

Expand|Select|Wrap|Line Numbers
  1. <?php include("includes/db_connect.php"); ?>
  2. <?php require_once("includes/functions.php"); ?>
  3.  
  4. <?php
  5.  
  6.     $equipid = mysqli_real_escape_string($db_cxn, trim(strip_tags($_POST['equipid'])));
  7.     $equipname = mysqli_real_escape_string($db_cxn, trim(htmlspecialchars($_POST['equipname'])));
  8.     $manufacturer = mysqli_real_escape_string($db_cxn, trim(htmlspecialchars($_POST['manufacturer'])));
  9.     $serno = mysqli_real_escape_string($db_cxn, trim(htmlspecialchars($_POST['serno'])));
  10.     $modelno = mysqli_real_escape_string($db_cxn, trim(htmlspecialchars($_POST['modelno'])));
  11.     $capacity = mysqli_real_escape_string($db_cxn, trim(htmlspecialchars($_POST['capacity'])));
  12.     $curloc = mysqli_real_escape_string($db_cxn, trim(strip_tags($_POST['curlocation'])));
  13.     $equipcode = mysqli_real_escape_string($db_cxn, trim(strip_tags($_POST['equipcode'])));
  14.     $seqno = mysqli_real_escape_string($db_cxn, trim(strip_tags($_POST['seqno'])));    
  15.     $addDate = mysqli_real_escape_string($db_cxn, trim(strip_tags($_POST['adddate'])));
  16.  
  17.  
  18.     $query_update = "UPDATE `tblequipmentmasterlist` SET ";
  19.     $query_update .= "`equipname` = '{$equipname}', ";
  20.     $query_update .= "`manufacturer` = '{$manufacturer}', ";
  21.     $query_update .= "`serno` = '{$serno}', ";
  22.     $query_update .= "`modelno` = '{$modelno}', "; 
  23.     $query_update .= "`capacity` = '{$capacity}', "; 
  24.     $query_update .= "`curlocation` = '{$curloc}', "; 
  25.     $query_update .= "`equipcode` = '{$equipcode}', "; 
  26.     $query_update .= "`seqno` = '{$seqno}', "; 
  27.     $query_update .= "`adddate` = '{$addDate}' ";
  28.     $query_update .= "WHERE `tblequipmentmasterlist`.`equipid` = {$equipid} LIMIT 1";
  29.  
  30.  
  31.     if (mysqli_affected_rows() == 1) {
  32.         header ("LOCATION : updatedata.php");
  33.     } else {
  34.         print_r($_POST);
  35.         var_dump($_POST);
  36.         echo "<p>Update failed : <br />". $query_update.  " <br/>Please contact the site administrator.</p>";
  37.         echo "<p>" . mysqli_error($db_cxn) . "</p>";
  38.     }
  39.  
  40. ?>
  41.  
  42. <?php 
  43.     if (isset($db_cxn)) {
  44.         mysqli_close($db_cxn); 
  45.     }
  46. ?>
  47.  
Attached Images
File Type: jpg update_error.jpg (53.1 KB, 107 views)
Oct 4 '11 #3

Rabbit
Expert Mod 10K+
P: 12,364
I can't really see what the error says, it's too small. Have you tried outputting the SQL and running it manually through the DBMS, which I'm assuming is MySQL?
Oct 4 '11 #4

P: 22
If you mean if I tried updating via the MySQL console, YES, I tried that and it works fine. I was able to update the database manually. I'm sorry about the image, I just can't have a larger image due to the forum restrictions (500 x 600) so I have to resize it before posting it here. Please find again the image on this link:

http://i729.photobucket.com/albums/w...pdateError.jpg



Also, I made some revision with the mysqli_num_rows function. Please find the code here:

Expand|Select|Wrap|Line Numbers
  1. <?php include("includes/db_connect.php"); ?>
  2. <?php require_once("includes/functions.php"); ?>
  3.  
  4. <?php
  5.  
  6.     $equipid = mysqli_real_escape_string($db_cxn, trim(strip_tags($_POST['equipid'])));
  7.     $equipname = mysqli_real_escape_string($db_cxn, trim(htmlspecialchars($_POST['equipname'])));
  8.     $manufacturer = mysqli_real_escape_string($db_cxn, trim(htmlspecialchars($_POST['manufacturer'])));
  9.     $serno = mysqli_real_escape_string($db_cxn, trim(htmlspecialchars($_POST['serno'])));
  10.     $modelno = mysqli_real_escape_string($db_cxn, trim(htmlspecialchars($_POST['modelno'])));
  11.     $capacity = mysqli_real_escape_string($db_cxn, trim(htmlspecialchars($_POST['capacity'])));
  12.     $curloc = mysqli_real_escape_string($db_cxn, trim(strip_tags($_POST['curlocation'])));
  13.     $equipcode = mysqli_real_escape_string($db_cxn, trim(strip_tags($_POST['equipcode'])));
  14.     $seqno = mysqli_real_escape_string($db_cxn, trim(strip_tags($_POST['seqno'])));    
  15.     $addDate = mysqli_real_escape_string($db_cxn, trim(strip_tags($_POST['adddate'])));
  16.  
  17.  
  18.     $query_update = "UPDATE `tblequipmentmasterlist` SET ";
  19.     $query_update .= "`equipname` = '{$equipname}', ";
  20.     $query_update .= "`manufacturer` = '{$manufacturer}', ";
  21.     $query_update .= "`serno` = '{$serno}', ";
  22.     $query_update .= "`modelno` = '{$modelno}', "; 
  23.     $query_update .= "`capacity` = '{$capacity}', "; 
  24.     $query_update .= "`curlocation` = '{$curloc}', "; 
  25.     $query_update .= "`equipcode` = '{$equipcode}', "; 
  26.     $query_update .= "`seqno` = '{$seqno}', "; 
  27.     $query_update .= "`adddate` = '{$addDate}' ";
  28.     $query_update .= "WHERE `tblequipmentmasterlist`.`equipid` = {$equipid} LIMIT 1";
  29.  
  30.  
  31.     if (mysqli_affected_rows($db_cxn) == 1) {
  32.         header ("LOCATION : updatedata.php"); //Record was successfully updated
  33.     } else {
  34.         print_r($_POST);
  35.         var_dump($_POST);
  36.         echo "<p>Update failed : <br />". $query_update.  " <br/>Please contact the site administrator.</p>";
  37.         echo "<p>" . mysqli_error($db_cxn) . "</p>";
  38.     }
  39.  
  40. ?>
  41.  
  42. <?php 
  43.     if (isset($db_cxn)) {
  44.         mysqli_close($db_cxn); 
  45.     }
  46. ?>
Oct 4 '11 #5

Rabbit
Expert Mod 10K+
P: 12,364
When you say that you tried updating via the MySQL console, what does that mean? What SQL string did you use? Did you output the SQL string from PHP and then use that? Or did you manually type it in?
Oct 6 '11 #6

P: 22
I only tried a manual update of my database using the MySQL console to check if the commands are right and working if encoded directly in MySQL. My MySQL version has GUI (I'm using WAMP), so I can also directly check if my webpage did changed my database. Right now, I don't really know what's the problem with my code, the data variables from GETDATA page is being transferred to my PROCESSING page, as the print_r and var_dump shows from the imaged linked, but the data stops there, it doesn't get into my database, whatsoever.
Oct 7 '11 #7

Rabbit
Expert Mod 10K+
P: 12,364
Instead of the manual update, try outputting the string from PHP and then pasting that into MySQL to see if it works.
Oct 7 '11 #8

P: 22
You mean the whole UPDATE string from my PHP and then pasting it to the MySQL sql query? Ok, I'll try it, thanks for the advice, hope this helps...
Oct 7 '11 #9

Rabbit
Expert Mod 10K+
P: 12,364
Yep, I'm thinking there might be an issue with the string creation. Outputting it and using what the PHP engine uses will either confirm or deny it. And then we can proceed from there.
Oct 7 '11 #10

P: 22
Please find the linked images. As per your suggestion, I copied the PHP string and then pasted it on the MySQL sql query, and it successfully updated my dbase.

***The data to be be sent as an update***


***Update failed, copied the query string***


***Database doesn't show any update activity at all***


***Pasted the copied string***


***Query was a success!***


***Update was reflected on the main dbase***

Oct 8 '11 #11

Rabbit
Expert Mod 10K+
P: 12,364
I can't believe I missed this but you didn't actually run the update query in your code. You create the string but then you don't execute it.
Oct 9 '11 #12

P: 22
Could you please elaborate?
Oct 9 '11 #13

Rabbit
Expert Mod 10K+
P: 12,364
After you build the string, you need to execute it using the mysql_query($query_update) function.
Oct 9 '11 #14

P: 22
Wow, thanks dude, that surely solves the UPDATE problem. But, I can't make my redirect to work. When a successful update was performed, it should redirect to UPDATEDATA.PHP. What happens is it only opens a blank PROCESSFORM_UPDATE.PHP page.

Expand|Select|Wrap|Line Numbers
  1. <?php include("includes/db_connect.php"); ?>
  2. <?php require_once("includes/functions.php"); ?>
  3.  
  4. <?php
  5.  
  6.     $equipid = mysqli_real_escape_string($db_cxn, trim(strip_tags($_POST['equipid'])));
  7.     $equipname = mysqli_real_escape_string($db_cxn, trim(htmlspecialchars($_POST['equipname'])));
  8.     $manufacturer = mysqli_real_escape_string($db_cxn, trim(htmlspecialchars($_POST['manufacturer'])));
  9.     $serno = mysqli_real_escape_string($db_cxn, trim(htmlspecialchars($_POST['serno'])));
  10.     $modelno = mysqli_real_escape_string($db_cxn, trim(htmlspecialchars($_POST['modelno'])));
  11.     $capacity = mysqli_real_escape_string($db_cxn, trim(htmlspecialchars($_POST['capacity'])));
  12.     $curloc = mysqli_real_escape_string($db_cxn, trim(strip_tags($_POST['curlocation'])));
  13.     $equipcode = mysqli_real_escape_string($db_cxn, trim(strip_tags($_POST['equipcode'])));
  14.     $seqno = mysqli_real_escape_string($db_cxn, trim(strip_tags($_POST['seqno'])));    
  15.     $addDate = mysqli_real_escape_string($db_cxn, trim(strip_tags($_POST['adddate'])));
  16.  
  17.  
  18.     $query_update = "UPDATE `tblequipmentmasterlist` SET ";
  19.     $query_update .= "`equipname` = '{$equipname}', ";
  20.     $query_update .= "`manufacturer` = '{$manufacturer}', ";
  21.     $query_update .= "`serno` = '{$serno}', ";
  22.     $query_update .= "`modelno` = '{$modelno}', "; 
  23.     $query_update .= "`capacity` = '{$capacity}', "; 
  24.     $query_update .= "`curlocation` = '{$curloc}', "; 
  25.     $query_update .= "`equipcode` = '{$equipcode}', "; 
  26.     $query_update .= "`seqno` = '{$seqno}', "; 
  27.     $query_update .= "`adddate` = '{$addDate}' ";
  28.     $query_update .= "WHERE `tblequipmentmasterlist`.`equipid` = {$equipid} LIMIT 1";
  29.  
  30.     $result = mysqli_query($db_cxn, $query_update);
  31.  
  32.     if (mysqli_affected_rows($db_cxn) == 1) {
  33.         header ("LOCATION : updatedata.php"); //Record was successfully updated
  34.     } else {
  35.         print_r($_POST);
  36.         var_dump($_POST);
  37.         echo "<p>Update failed : <br />". $query_update.  " <br/>Please contact the site administrator.</p>";
  38.         echo "<p>" . mysqli_error($db_cxn) . "</p>";
  39.     }
  40.  
  41. ?>
  42.  
  43. <?php 
  44.     if (isset($db_cxn)) {
  45.         mysqli_close($db_cxn); 
  46.     }
  47. ?>
  48.  
Another thing, how do I make a text field uneditable? I tried DISABLING my EQUIPID text field, but then I always encounter an UNDEFINED INDEX error. I would like my EQUIPID to be uneditable to prevent errors.
Oct 9 '11 #15

Rabbit
Expert Mod 10K+
P: 12,364
I don't know about making it disabled but you can just make it a hidden field.

As for the redirect, can you make a new thread for that? We like to have one question per thread here.
Oct 9 '11 #16

Post your reply

Sign in to post your reply or Sign up for a free account.