Update Query issue

[TheOperative]

I have aan update script that works fine, it updates the row. It is in an admin side of a website and uses checkboxes and text fields for input.

The issue I'm having is that if a second update is made to the row, the previous update gets wiped out.

Is there anyway I can put a conditional either in PHP or sql to say:

UPDATE query only for items checked

Thanks for the help.

[bastien]

tie each checkbox to a particular field, if checked update that field only

[TheOperative]

bastien wrote:

tie each checkbox to a particular field, if checked update that field only

That is what I thought too, can you elaborate and show me with an example, here's my code:

<?php
include "../../common.php";
$loannum = $_GET['id'];
session_start();
session_register("admin");


if (isset($submit))   

{
$query = "UPDATE pipeline SET
                status1='$status1',
                status1date='$status1date',
                status2='$status2',
                status2date='$status2date',
                status3='$status3',
                status3date='$status3date',
                status4='$status4',
                status4date='$status4date',
                status5='$status5',
                status5date='$status5date',
                status6='$status6',
                status6date='$status6date',
                status7='$status7',
                status7date='$status7date',
                status8='$status8',
                status8date='$status8date',
                status9='$status9',
                status9='$date',
                status10='$status10',
                status10date='$status10date'
                WHERE id= ". $_POST['loannum'];

        $result = mysql_query ($query) or die(mysql_error(); // Run the query.

        if ($result) // If it ran OK.
        {
        echo '<h3>Thank you for Submitting to the Pipeline</h3>';
        }
    }     

?> 

<form method="post" action="<?php echo $_SERVER['PHP_SELF'];?>">

<table width="80%" border="1" bordercolor="#999999" align="center">
<tr><td><div align="center"><span class="style1">Update the Pipeline</span></div></td>
</tr>
   <tr>
     <td class="form" width="423"><b>
       <input type="checkbox" name="status1" value="1">Final 1003 Completed</b></td>

     <td class="normal" width="339"> Date:
       <input name="status1date" size="20" value="">(MM/DD/YY) </td>
  </tr>

   <tr>
     <td class="form" width="423"><b>                   
        <input type="checkbox" name="status2" value="1">Ordered Appraisal</b></td>
     <td class="normal" width="339"> Date:
        <input name="status2date" size="20" value=""> (MM/DD/YY) </td>   
   </tr>                      

    <tr>
      <td class="form" width="423"><b>
         <input type="checkbox" name="status3" value="1">Ordered Title, Payoffs, CPL & Wire Instructions</b></td>
      <td class="normal" width="339"> Date:
         <input name="status3date" size="20" value=""> (MM/DD/YY) </td>
    </tr>

     <tr>
      <td class="form" width="423"><b>
         <input type="checkbox" name="status4" value="1"> Ordered Insurance</b></td>
      <td class="normal" width="339"> Date:
         <input name="status4date" size="20" value=""> (MM/DD/YY) </td>
     </tr>                    

     <tr>
      <td class="form" width="423"><b>
        <input type="checkbox" name="status5" value="1">Submitted to Processing</b></td>
      <td class="normal" width="339"> Date:
        <input name="status5date" size="20" value=""> (MM/DD/YY)  </td>
     </tr>

     <tr>
     <td class="form" width="423"><b>
       <input type="checkbox" name="status6" value="1">Submitted to Lender</b></td>
     <td class="normal" width="339"> Date:
       <input name="status6date" size="20" value=""> (MM/DD/YY) </td>
    </tr>

     <tr>
     <td class="form" width="423"><b>
       <input type="checkbox" name="status7" value="1">Approved by Lender</b></td>
     <td class="normal" width="339"> Date:
       <input name="status7date" size="20" value=""> (MM/DD/YY) </td>
    </tr>

     <tr>
     <td class="form" width="423"><b>
       <input type="checkbox" name="status8" value="1">Conditions Met</b></td>
     <td class="normal" width="339"> Date:
       <input name="status8date" size="20" value=""> (MM/DD/YY) </td>
     </tr>

     <tr>
      <td class="form" width="423"><b>
        <input type="checkbox" name="status9" value="1">Review Final HUD</b></td>
      <td class="normal" width="339"> Date:
        <input name="status9date" size="20" value=""> (MM/DD/YY)</td>
    </tr>

     <tr>
      <td class="form" width="423"> <b>
        <input type="checkbox" name="status10" value="1">File Closed</b></td>
     <td class="normal" width="339"> Date:
        <input name="status10date" size="20" value=""> (MM/DD/YY) </td>
     </tr>
                    <tr>
                        <td class="form" width="423">&nbsp;</td>
                        <td class="normal" width="339">&nbsp; </td>
                    </tr>
                    <tr>
                        <td width="423" align="left" class="form"><div align="right">
                            <input type="hidden" name="loannum" value="<?php echo $_GET['id']; ?>">
                            <input type="submit" name="submit" value="submit" style="font-family: Verdana, Arial; font-weight:bold; font-size: 12; background-color=#EFEBE4; color=#000000; border: 1px solid #000000">
                          </div></td>
                        <td class="normal" width="339"><input type="submit" name="Delete" value="Delete" style="font-family: Verdana, Arial; font-weight:bold; font-size: 12; background-color=#EFEBE4; color=#000000; border: 1px solid #000000"></td>
                    </tr>
                    <tr>
                        <td class="form">&nbsp;</td>
                        <td class="normal">&nbsp;</td>
                    </tr>
                  </table>

</form>

[bastien]

You need to create the sql dynamically..checking whether each checkbox has a value and then including the value

//base sql statement
$sql = "update pipeline set ";

if (!empty($_POST['status1']))
  $sql .= " status1 = ".$_POST['status1'].",status1date = '{$_POST['status1date']}' ";
}
if (!empty($_POST['status2']))
  $sql .= " status1 = ".$_POST['status2'].",status1date = '{$_POST['status2date']}' ";
}
if (!empty($_POST['status3']))
  $sql .= " status1 = ".$_POST['status3'].",status1date = '{$_POST['status3date']}' ";
}
if (!empty($_POST['status4']))
  $sql .= " status1 = ".$_POST['status4'].",status1date = '{$_POST['status4date']}' ";
}
//and so on and so forth

//add the where clause
$sql .= " WHERE id= ". $_POST['loannum'];

$result = mysql_query ($query) or die(mysql_error(); // Run the query.

if ($result) // If it ran OK.
{
echo '<h3>Thank you for Submitting to the Pipeline</h3>';
}