更新mysql查询坏了

Question

My entire query was working fine, I changed the php dropdown part to get the list of asset numbers from the database, weirdly after those changes the update part does not want to work anymore... my apache2 error log complains about the undefined index who_out. I don't know what to change to get it working again...

Here is the current part that updates the asset with the necessary info.

$sql1=$_POST['fieldname']." AND '".$_POST['comments'];

if (strpos($_POST['comments'],'OUT') !== false) {
    $sql2 = "UPDATE data SET ".$sql1."', dt_out = '$date', who_out = '".$_POST['who_out']."' WHERE data_id = '".$_POST['reference']."'";
} else {
    $sql2="UPDATE data SET ".$sql1."' WHERE data_id = '".$_POST['reference']."'";
}

$result = mysql_query($sql2,$con);

anyone able to assist me with what needs to change for this to work? let me know if you need more info...

the original dropdown looked like this:

<td>Software Profile:</td>
<?php 
$con = mysql_connect($host, $db_user, $db_pass);
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db($db, $con);

$res02 = mysql_query("SELECT * FROM profiles");?>

<td>
<select name="swp">
<option selected="selected">Choose Profile</option>
<option></option>
<?php while( $row = mysql_fetch_row( $res02 )) {                    
    $sel = ( data === $row['profile_id'] ) ? "id='sel' selected" : "";   
     printf ( " <option %s value='%s'>%s</option>
", $sel, $row[1] , $row[1]);  //die data wat select en gedisplay word.
                      }; 

                      mysql_close($con);



?> 
</select>

the new drop down looks like this:

<td>Software Profile:</td>

<td>
        <select name="swp">
        <option selected>Select Profile...</option>
        <option value="no profile selected"></option>
            <?php
            $link=mysql_connect($host, $db_user, $db_pass) or die ("Error connecting to mysql server: ".mysql_error());
            mysql_select_db($db, $link) or die ("Error selecting specified database on mysql server: ".mysql_error());

            $query="SELECT profile_id, profile FROM profiles";
            $result=mysql_query($query) or die ("Query to get data from Profiles Table failed: ".mysql_error());

while ($row=mysql_fetch_array($result)) {
$profile=$row["profile"];
$profile_id=$row["profile_id"];
    echo "<option value=\"$profile\">$profile</option>";
}

            ?>

        </select>

and here is the form used to update a asset number if it has a status of !=OUT

<h2>Update Details:</h2>
<form action="updateref.php" method="post">
<table border="frame" align="center">
    <tr>
<td>Which Asset #:</td>
<td>
<select name="reference">
        <option selected>Select Asset #</option>
        <option></option>
            <?php
            $link=mysql_connect($host, $db_user, $db_pass) or die ("Error connecting to mysql server: ".mysql_error());
            mysql_select_db($db, $link) or die ("Error selecting specified database on mysql server: ".mysql_error());

            $query="SELECT * FROM data WHERE status != 'OUT'";
            $result=mysql_query($query) or die ("Query to get data from Profiles Table failed: ".mysql_error());

while ($row=mysql_fetch_array($result)) {
$data_id=$row["data_id"];
$asset=$row["asset"];
    echo "<option value=\"$asset\">$asset</option>";
} 
            ?>

        </select>
      </td>
    </tr>
<tr>
<td>What must be updated:</td>
<td>
<select name="fieldname">
<option selected="selected">Select Option</option>
<option value="asset">Asset Number</option>
<option value="make_model">Make Model</option>
<option value="os">Operating System</option>
<option value="office">Office</option>
<option value="swp">Software Profile</option>
<option value="ea">Extra Apps</option>
<option value="status">Status</option>
</select>
</td>
</tr>

<tr>
<td>Change to:</td>
<td>
<input type="text" name="comments" required></input>
</td>
</tr>

<tr>
<td>

</td>
<td>
<button id='sblogloginbtn' type="submit"><b>Update</b></button>  <button id='sblogloginbtn' type="reset" ><b>Reset</b></button>
</td>
</tr>
</table>
</form>

how do i safetify the following code in the udpate.php page?

$sql1=$_POST['fieldname']."='".$_POST['comments'];


$unsafe_variable = $_POST["user-input"]
$safe_variable = mysql_real_escape_string($unsafe_variable);




$who_out = $_POST['who_out'];
$reference = $_POST['reference'];
if (strpos($_POST['comments'],'OUT') !== false) {
    $sql2 = "UPDATE data SET ".$sql1."', dt_out = '$date', who_out = '$who_out' WHERE asset = '$reference'";
} else {
    $sql2="UPDATE data SET ".$sql1."' WHERE asset = '$reference'";
}

$result = mysql_query($sql2,$con);

Answer 1

As i can not know what values your variables contain i can not help you very much.

Make a echo $sql2; at the end to show what the final query looks like. Then either you see the problem yourself or we can help - maybe one of your POST vars contains no or a wrong value.

ps: NEVER use POST directly in a query, always validate the input first. Read about "SQL Injection"...