update statement

[witham]

I am having alot of trouble getting some data to update, the problem I have is that I wish to update one particular  "prodequiv" but I cannot stop them all changing?

I have a page that allows data entry into two variables called 'Equivalent' and 'newequiv'.

[code]
$result  = mysql_query($sqlPRODNAME, $dblink)
or die("<p>Error Processing Query</p><hr /><p>".mysql_error()."</p>\n");



// mysql_fetch_array fetches the results from the query a row at a time and populates the drop down box each time it's called
// the result is returned as an array that can be referenced either by field name or by it's index, "ALL" is hardcoded because
//it is always constant and allows the % query to be executed


echo '<td><select name="Equivalent">';

while ($row = mysql_fetch_array ($result))



// loop through the rows outputing them as select options
  {
echo '<option value="' . $row["PRODEQUIV"] . '">' . $row["PRODEQUIV"] . "</option>";
  }
?>
</select></td></tr>

  <tr>
      <td><b>
New Equivalent Name:</b>
      </td>
      <td><input type text name="newequiv">
      </td>
  </tr>





<!--The other part of the form is hardcoded as the types of lubricant are constant-->


<!--Close the last selection box and put a submit button named "display results from equivalents database" to send the
data to query.php for processing-->
<tr><td colspan = 2 align = center class="style3"><button type="submit">AMEND EQUIVALENT NAME</button></td></tr>[/code]

These are passed to another page that has this statement:

[code]$sqlAMEND = "UPDATE prodname SET
  prodequiv = '$newprod' where
  prodequiv = '$Equivalent'
  and PRODID = prodid;";[/code]

But it just changes every 'prodequiv' entry, I have changed this and stared at it for days but can't get it to work. Could anyone please help!!!!

[fenway]

"and PRODID = prodid" is always going to return true... where's the variable?

[witham]

I think that  '$Equivalent' holds the product equivalent selected from the database by the user and '$newequiv' holds the desired update. So I would think (using my limited knowledge) that what I need to do is:

1). Take the data contained within '$newequiv' (sorry this is posted as '$newprod' mis-type)

2). Take the selected product contained within '$Equivalent'; 

3). Now I need to update the rows where the prodequiv in the database is equal to the data contained within the '$Equivalent' variable;

But I thought that this "where prodequiv = '$Equivalent' would do it?

[fenway]

It just sounded like to meant to update a subset of rows, but the prodequiv where clause was necessary but not sufficient...

[witham]

Done it at last:

UPDATE prodname, produse, man SET
  prodequiv = '$newequiv' where
  '$Equivalent' = prodequiv;

Thanks, sometimes a fresh perspective and all that!!!!!!!!!!!!

cheers for your time.