[SOLVED] PHP FORM UPDATE MYSQL DATABASE

[avincent]

I have been trying to get this to work for a while now and cant figure it out. I am trying to display records from a database where the condition is the field "completed" is " ". After they are displayed I want the user to be able to check the box next to one of them and hit submit. After the submit button is clicked it will update the database the value of "yes" in the completed field. Below is my code:

---------------------------------------------------

 

<?php

//Create registration form (register.php)

include "db_connect.php";

 

if(!isset($_POST['submit']))

{

echo "

<fieldset><legend><h2>Trouble Ticket List</h2></legend><br>

<form action=\"#\" method=\"post\" >

 

        <table width=\"980\" class=\"ticket-table\">

        <tr>

<th><strong>Name:</strong></th>

<th><strong>Phone:</strong></th>

<th><strong>Domain:</strong></th>

<th><strong>Email:</strong></th>

<th><strong>Comments:</strong></th>

<th><strong>Completed:</strong></th>

</tr>

 

";

 

$sql = "SELECT * FROM `online-form` WHERE `completed` = ''";

      $query = mysql_query($sql) or die(mysql_error());

      while ($row = mysql_fetch_array($query))

              {

  $ID= $row['ID'];

              $name= $row["name"];

  $phone= $row["phone"];

  $email= $row["email"];

  $domain= $row["domain-name"];

              $comments= $row["comments"];

  $completed= $row["completed"];

   

?>

            <tr>

                <td>

                <br /><?php echo "$name" ?>

                </td>

                <td>

                </label><br /><?php echo "$phone" ?>

                </td>

                <td>

                </label><br /><?php echo "$domain" ?>

                </td>

                <td>

                </label><br /><?php echo "$email" ?>

                </td>

                <td>

                <br /><?php echo "$comments" ?>

          </td>

                <td>

                <input type="checkbox" name="completed" id="completed" value="yes" />

                    <input type="hidden" name="<?php echo $ID ?>" value="<?php echo $ID ?>" />

                </td>

        </tr>

         

<?php

} ?>

 

        <tr>

          <td><input type="submit" name="submit" id="submit" value="Submit" /></td>

        </tr>

        </table>

</form>

<?php

}else{

?>

 

<?php

$sql = "SELECT * FROM `online-form` WHERE `completed` = ''";

      $query = mysql_query($sql) or die(mysql_error());

  $row = mysql_fetch_array($query);

 

  $ID= $_POST['ID']; 

  echo $ID;

  $completed = $_POST['completed'];

 

$errors = array();

 

  $sql = "UPDATE `online-form` SET completed = 'yes' WHERE ID = '$ID'";

 

  $result=mysql_query($sql) or die(mysql_error() . '<br />' . $sql);

  echo "The User has been updated.<A HREF=\"javascript:history.go(-1)\">Go Back to List</A> ";

 

}?>

[DavidAM]

[*]Always put your code in [ code ] tags (without the spaces) so it is displayed properly;

[*]Make sure you have error reporting turned on -- error_reporting(E_ALL); -- at the beginning of your scripts;

[*]You did not state a problem.  What is your code doing?  Do you get any error messages?

 

From a cursory glance, you are creating multiple checkbox fields with the same name ("completed").  You will not be able to tell which one is checked when the user submits the form.  The hidden fields will not help since ALL of them will be posted regardless of whether the associated checkbox is checked or not.  Actually, they are only associated in our minds, the browser makes no such association.

 

To make this work, remove the hidden fields (you don't need them) and change the checkbox to:

<input type="checkbox" name="completed[<?php echo $ID;?>]" value="yes" />

This will allow the user to check any number of checkboxes.  And will produce an array, when submitted, of $_POST['completed'][##] = 'yes' where the key ("##") will be the ID of the line checked.  You can walk through this array, and update multiple records with one submit.

 

If you want to prevent the user from checking multiple boxes, you will have to use either Javascript (which will be a pain) or use an INPUT TYPE="radio" button.  In this case, all of the radios have to have the same name (as they are now) but you can specify the value to be the ID.  You still do not need the hidden fields.

[avincent]

I understand what you mean about passing an array, but if I change the input tag to what you suggested what should my submission look like? This is what I have right now:

----------------------------------------------------

<?php

}else{

  $completed = $_POST['completed[]'];

 

  $sql = "UPDATE `online-form` SET completed = " . $completed;

 

  $result=mysql_query($sql) or die(mysql_error() . '<br />' . $sql);

  echo "The User has been updated.<A HREF=\"javascript:history.go(-1)\">Go Back to List</A> ";

 

}?>

------------------------------------------------------

 

but when I use it  I get the error:

 

Notice: Undefined index: completed[] in C:\wamp\www\digitaleel\inc\client-resources\jude.php on line 91

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 '' at line 1

UPDATE `online-form` SET completed =

[DavidAM]

Let's say your query returns three rows, with ID's of 4, 9, and 16.  The checkboxes will have HTML names of:

name="completed[4]"
name="completed[9]"
name="completed[16]"

 

If we check the first two boxes, then the posted array will look like this (I'm at work and do not have a PHP engine to play on, so pardon any typos):

$_POST => array
  ( completed => array
    ( 4 => yes,
      9 => yes
    )
  )

(well, ok, it will also have the submit button in it, but you know that already)

 

so, we can walk this array like this:

foreach ($_POST['completed'] as $ID => $value) {
  echo 'User Checked the box for ' . $ID . PHP_EOL;
}

We don't really care what $value is because checkboxes do NOT get posted unless they are checked.  So the fact that it is there means it was checked.  This will output:

User Checked the box for 4
User Checked the box for 9

 

Now the easiest way to do the update you need would be something like this:

$IDs = '';
if (isset($_POST['completed'])) {  // Nothing to do if nothing is checked
  foreach ($_POST['completed'] as $ID => $Value) {
    $IDs .= (empty($IDs) ? '' : ',') . $ID;
  }
  // Now $IDs is a string like "4,9"
  $sql = 'UPDATE `online-form` SET completed = "yes" WHERE ID IN (' . $IDs . ')';

[avincent]

Awsome!!  You have been a big help and saved me a whole bunch of time. Thank you very much!