Trouble with UPDATE using php and mysql

Question

I am having difficulty using the UPDATE command on a functioning database.

What I am attempting to do:

On an existing database I want to update a specific field (sdate) using a date picker and have the value be saved into the database.

What is happening:

I am able to access the database (the echo of one of the elements (row[0]) works) but I am not able to get the date picker's value to get saved into the database.

Can someone point me in the right direction please?

Here is the main html code:

<?php include '../include/header.php'; ?>
<?php include '../include/datepicker.php'; ?>
<?php include '../include/format.php'; ?>
<fieldset>
<legend>Presale Units in Stock</legend>
<table border=1>
<tr>
<th>Id</th>
<th>Ship Date</th>
<th>Button?</th>
</tr>
<tr>
<form>
<?php include '../include/junk.presale.mysql.php'; ?>
</table>
<button type="reset" value="Reset">Reset</button>
</form>
</tr>
</fieldset>
<br>
<?php include '../include/footer.php'; ?>

Here is junk.presale.mysql.php:

<?php
// Get database credentials
require 'config.php';
$dbtable = "assembly2";
$col1 = "id";
$col4 = "sdate";
$comm = "SELECT * FROM $dbtable";
/* Create a new mysqli object with database connection parameters */
$conn= new mysqli($dbhost,$dbuser,$dbpass,$dbname);
if(mysqli_connect_errno()) {
    echo "Connection Failed: " . mysqli_connect_errno();
    exit();
}

// Assembly array
if ($result = $conn->query($comm)) {
    /* fetch object array */
    while ($row = $result->fetch_row()) {
        if(($row[5]=="presale")or($row[4]!=0)) {
            echo "<tr>";
            echo "<td>$row[0]</td>";
            echo "<td><input type=\"text\" name=\"sdate\" class=\"datepicker\"></td>";
            echo "<td><input name=\"update\" type=\"submit\" id=\"update\" value=\"Update\"></td>";
            if(isset($_POST["update"])){
                $entry4 = $_POST["sdate"];
                $cmd = "UPDATE $dbtable SET $col4=$entry4 WHERE $col1=$row[0]";

                // use prepared statements to increase security
                if ($stmt = mysqli_prepare($conn,$cmd)){
                    mysqli_stmt_execute($stmt);
                }
                // Close statement and connection
                mysqli_stmt_close($stmt);
            }
            echo "</tr>";
        }
    }
}

/* free result set */
$result->close();

// Close statement and connection
mysqli_close($conn);
?>

Any help is much appreciated!

Sidenote: if you are using variables inside of your query string, you might be missing out on the increased security prepared statements can provide... you might try looking into binding parameters for those values :) — summea
– summea, Commented Apr 9, 2014 at 20:51
Are you getting any errors? Is the date formatted properly to match the column in the DB? — durbnpoisn
– durbnpoisn, Commented Apr 9, 2014 at 20:53
Sidenote: You need to use quotes in $col4=$entry4 as in $col4='$entry4' since you're dealing with a string; most likely. — Funk Forty Niner
– Funk Forty Niner, Commented Apr 9, 2014 at 20:54
durbnpoisn, - I am not getting any errors. Which is suprising because I have been looking for errors to debug this. — user3517003
– user3517003, Commented Apr 9, 2014 at 23:10

Haroon · Accepted Answer · 2014-04-11 17:13:42Z

1

As for as i understand your problem you want to update each row of a table by clicking update button. for this you have to create multiple form not a single form. Your logic is not correct. use the below code a hopes that will solve your problem.

<?php include '../include/header.php'; ?>
<?php include '../include/datepicker.php'; ?>
<?php include '../include/format.php'; ?>
<fieldset>
<legend>Presale Units in Stock</legend>
<table border="1">
 <tr>
  <th>Id</th>
  <th>Ship Date</th>
  <th>Button?</th>
 </tr>
 <?php include '../include/junk.presale.mysql.php'; ?>
  </table>
<br>
<?php include '../include/footer.php'; ?>

Here is you another file.

 // Assembly array
 if ($result = $conn->query($comm)) {
  /* fetch object array */
   while ($row = $result->fetch_row()) {
        if(($row[5]=="presale")or($row[4]!=0)) {
                echo "<tr>";
                echo "<form id=\"form-$row[0]\" name=\"form-name-$row[0]\" method=\"post\">";
                echo "<td>$row[0]</td>";
                echo "<td><input type=\"text\" name=\"sdate\" class=\"datepicker\"></td>";
                echo "<td><input type=\"hidden\" name=\"rec_id\" value=\"$row[0]\"></td>";
                echo "<td><input name=\"update\" type=\"submit\" id=\"update\" 
                                   value=\"Update\"> </td>";
                echo "</form>";
                echo "</tr>";
        }
    }

}
 if(isset($_POST["update"])){
      $entry4 = $_POST["sdate"];
      $rec_id = $_POST["rec_id"];
      $cmd = "UPDATE $dbtable SET $col4='$entry4' WHERE $col1=$rec_id";

      // use prepared statements to increase security
      if ($stmt = mysqli_prepare($conn,$cmd)){
           mysqli_stmt_execute($stmt);
          }
          // Close statement and connection
          mysqli_stmt_close($stmt);
    }

edited Apr 11, 2014 at 17:13

answered Apr 9, 2014 at 20:54

Haroon

5004 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

user3517003 Over a year ago

are the "{}" symbols literally needed or is that for illustration purposes.

user3517003 Over a year ago

I tried the above code but still no luck. The weird thing is I am not getting any error messages to help debug :(

Haroon Over a year ago

Can you please use printf("Errormessage: %s\n", mysqli_error($conn)); to get an error and let me know so that i can help you better..

user3517003 Over a year ago

I am not seeing any errors. I tried to post the code here but I am limited to the characters

Haroon Over a year ago

I just change my complete answer.. please modify you files accordingly and let me know whether it works.. The problem is in your logic.. give a shot to the new code..

|

Collectives™ on Stack Overflow

Trouble with UPDATE using php and mysql

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related