Jump to content


Photo

update statement


  • Please log in to reply
4 replies to this topic

#1 witham

witham
  • Members
  • PipPipPip
  • Advanced Member
  • 86 posts

Posted 17 August 2006 - 08:30 PM

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'.

$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>


These are passed to another page that has this statement:

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

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!!!!

#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 18 August 2006 - 04:05 PM

"and PRODID = prodid" is always going to return true... where's the variable?
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 witham

witham
  • Members
  • PipPipPip
  • Advanced Member
  • 86 posts

Posted 18 August 2006 - 05:22 PM

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?

#4 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 18 August 2006 - 06:06 PM

It just sounded like to meant to update a subset of rows, but the prodequiv where clause was necessary but not sufficient...
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#5 witham

witham
  • Members
  • PipPipPip
  • Advanced Member
  • 86 posts

Posted 18 August 2006 - 06:14 PM

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.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users