[SOLVED] Update only certain SQL field

[MadFly]

hiI am trying to get a page where i can update only a certain field in the SQL database.I would like to be able to select the client's name, then what needs to be updated, and thenlastly, to what it must be updated. Like say i choose client1, then age, and then insert 35 into the text area, the php file updateclient.php should only update Client1's age to 35.Here is the form i currently have.

	<form action="updateclient.php" method="post"><table border="0"><tr><td>Choose Client to Update:</td><?php $con = mysql_connect("localhost","user","password");if (!$con)  {  die('Could not connect: ' . mysql_error());  }mysql_select_db("my_db", $con);$res02 = mysql_query("SELECT * FROM Clients");?><td><select name="clients"><option value="selected">Client Names...</option><?php while( $row = mysql_fetch_row( $res02 )) {						$sel = ( Clients === $row['FirstName'] ) ? "id='sel' selected" : "";   	 printf ( " <option %s value='%s'>%s</option>\n", $sel, $row[0] , $row[1].' '. $row[2]);					  }; 					  					  mysql_close($con);					  ?>				  </select>	  </td></tr>/// The above code will get all client names automatically from sql database<tr><td>What must be Updated:</td><td><select><option value="selected">Choose...</option><option>Last Name</option><option>Age</option><option>Address</option><option>Email</option><option>Telephoon</option><option>City</option></select></td></tr><tr><td>Update to:</td><td><textarea name="comments" cols="40" rows="5">Put new data here...</textarea></td></tr><tr><td>Password</td><td><input type="password" name="txtPass" /></td></tr><tr><td></td><td><input type="submit" value="Update Data"/>  <input type="reset" value="Oops"></td><td></td></tr></table></form>

What should the SQL query be to update only what i selected? below is my updateclient.php file...

<?php$txtPass = $_POST['txtPass'];if ($txtPass == "MYsecretPASSWORD") {  $con = mysql_connect("localhost","user","password");  if (!$con)  {	die('Could not connect: ' . mysql_error());  }  mysql_select_db("my_db", $con);$sql = "UPDATE Kliente SET `FirstName` = '$_POST[firstname]', `LastName` = '$_POST[lastname]', `Age` = '$_POST[age]', `Adres` = '$_POST[adres]', `City` = '$_POST[city]', `Email` = '$_POST[email]', `Telefoon` = '$_POST[telefoon]' WHERE `kliente`.`FirstName` = '$_POST[firstname]';";  if (!mysql_query($sql,$con))  {	die('Error: ' . mysql_error());  }  else {	echo "Dankie! <br />The client has been updated.<br /><br />Go back to <a href=\"index.html\">Index</a>.";  }  mysql_close($con);}else {  echo "You entered the wrong or no password! Try again <a href=\"updateclient.html\">again</a> or go away! .";}?>

I am having trouble with the

$sql = "UPDATE Kliente SET `FirstName` = '$_POST[firstname]', `LastName` = '$_POST[lastname]', `Age` = '$_POST[age]', `Adres` = '$_POST[adres]', `City` = '$_POST[city]', `Email` = '$_POST[email]', `Telefoon` = '$_POST[telefoon]' WHERE `kliente`.`FirstName` = '$_POST[firstname]';";

part of it. coz this $sql will update all selected parts.

[real_illusions]

Just remove the bits you dont want to update, simple :)If you just want to update the age:$sql = "UPDATE Kliente SET `Age` = '$_POST[age]' WHERE `FirstName` = '$_POST[firstname]';";

[Synook]

Well, just take out some of the assignments in the SET clause.Edit: yeah, what real_illusions said.

[MadFly]

Ok, i can understand that.But then what if i then select the email from the list of things to be updated?isn't there a way to only update what is selected? with something more general at the SET `Age` partBecause i understand it that if i use $sql = "UPDATE Kliente SET `Age` = '$_POST[age]' WHERE `FirstName` = '$_POST[firstname]';"; it will update the age of firstname.But if i select another user to update his city perhaps?

[dsonesuk]

if i get this right select first name<form action="repeatbg.php" method="post"><select name="clients"><option value="Peter">Peter</option><option value="John">John</option><option value="Paul">Paul</option><option value="David">David</option><option value="Paul">Paul</option><option value="Jason">Jason</option><option value="Scott">Scott</option></select>select field name<select name="fieldname"><option value="selected">Choose...</option><option value="LastName">Last Name</option><option value="Age">Age</option><option value="Address">Address</option><option value="Email">Email</option><option value="Telefoon">Telefoon</option><option value="City">City</option></select>use textarea to amend select field valuethen submitthen use$sql=$_POST['fieldname']."= '".$_POST['comments']; //fieldname and value to replace with$sql="UPDATE Kliente SET ".$sql."' WHERE kliente.FirstName = '".$_POST['clients']."';"; //complete sql which should update the specific field

[MadFly]

hmmmm sounds good.Will give that a try quickly and let you know if it worked.Update: I get this error. just a minor adjustment need in WHERE CLAUSEError: Unknown column 'kliente.FirstName' in 'where clause'u think it can work if i remove the kliente (clients in english)

[MadFly]

what would the php code be, if my client list that i am using are displaying the firstname and the lastname?WHERE FirstName,LastName = '".$_POST['clients']."';"; //complete sqlinstead of your original phpWHERE kliente.FirstName = '".$_POST['clients']."';"; //complete sql?

[dsonesuk]

you only use klient.FirstName (unless it is a alias) usually when referencing more than one table which have identical field names, so is not required if referencing only a single table only.'klient' should match exactly the table name you are using.I noticed "SELECT * FROM Clients"if this is the table you are using to update then try:$sql=$_POST['fieldname']."= '".$_POST['comments'];$sql="UPDATE Clients SET ".$sql."' WHERE FirstName = '".$_POST['clients']."'";

[MadFly]

Upon using $sql="UPDATE Kliente SET ".$sql."' WHERE `FirstName` AND `LastName` = ... (seeing as the client list shows the first and lastname)it goes through, but the age does not update if i selected it from the list.will try the other way you mentioned just be4 this post

[dsonesuk]

what would the php code be, if my client list that i am using are displaying the firstname and the lastname?

it would be better to use a id ref (hopefully you have id (unique integer number) to refence) for the value for the option, rather than firstname, lastname, this would be easy to reference, using text you have to allow for 'John Smith' ' 'john smith', and main major problem is, if duplicate 'john smith' are found, it update them all;<option value="78">john smith</option>

[MadFly]

ah ok, i see.But then how do i utilize that in the php sql query?Then it looks like there is no other way, but to use the personID (which is set as primary, and auto_inc) and then update the list as soon as there is a new client on the database.

[dsonesuk]

once you changed the option values to reference personID field values, changefrom:$sql="UPDATE Clients SET ".$sql."' WHERE FirstName = '".$_POST['clients']."'";to:$sql="UPDATE Clients SET ".$sql."' WHERE personID = '".$_POST['clients']."'";EDIT: you might want to add $sql =""; at the top of to clear the $sql variable, as it might continue adding from previous posts$sql ="";$sql=$_POST['fieldname']."= '".$_POST['comments'];$sql="UPDATE Clients SET ".$sql."' WHERE personID = '".$_POST['clients']."'";

[MadFly]

rightthe following is now my sql query in the update.php

$sql=$_POST['fieldname']."= '".$_POST['comments']; //fieldname and value to replace with$sql="UPDATE Clients SET ".$sql."' WHERE personID = '".$_POST['clients']."'"; //complete sql

And still it goes through, saying that the client has been updated. BUt when i check, nothing has been updated.

[dsonesuk]

OK! have you got this function to run the querymysql_query($sql);i can see you have code to validate this, but not to actually run this?

[MadFly]

Here is the whole code of my update.php

<?php$txtPass = $_POST['txtPass'];if ($txtPass == "password") {  $con = mysql_connect("localhost","user","pass");  if (!$con)  {	die('Could not connect: ' . mysql_error());  }  mysql_select_db("my_db", $con); $sql=$_POST['fieldname']."= '".$_POST['comments']; //fieldname and value to replace with$sql="UPDATE Kliente SET ".$sql."' WHERE personID = '".$_POST['clients']."'"; //complete sql if (!mysql_query($sql,$con))  {	die('Error: ' . mysql_error());  }  else {	echo "Thanks! <br />the client is updated. <br /><br />go back to <a href=\"index.html\">Index</a>.";  }  mysql_close($con);}else {  echo "you entered wrong pass! try <a href=\"opdateerklient.html\">again</a> or go away! .";}?>

i see it just below the $sql="UPDATE... line$sql="UPDATE Kliente SET ".$sql."' WHERE personID = '".$_POST['clients']."'"; //complete sql if (!mysql_query($sql,$con))isn't the problem that there is 2 $sql lines?would it work if i made 1st $sql query something like $sql1 and the 2nd one $sql2 ?

[dsonesuk]

if (!mysql_query($sql,$con)) just checks if it can connect, and if not give a error.its not performing mysql_query($sql,$con) to update the table, so because its passed the if condition, it goes to the else condition and display 'thank you' message even though it has not updated anything.

[MadFly]

I see. so where do i then put the mysql_query($sql,$con) ?Below or above the actual $sql lines? OR in the middle perhaps?

[ShadowMage]

You'd put it after you declare your $sql variable. (The second time) So:

$sql=$_POST['fieldname']."= '".$_POST['comments']; //fieldname and value to replace with$sql="UPDATE Kliente SET ".$sql."' WHERE personID = '".$_POST['clients']."'"; //complete sql$result = mysql_query($sql,$con);

Then change your test for the if statement:Instead of:if (!mysql_query($sql,$con))you'd use:if (!$result)

[dsonesuk]

always after, it needs the update, table, where conditions within in the $sql variable to perform its task, if not the error would show, as it receive only the first incomplete $sql.it would be better to add it to the else statement else {mysql_query($sql,$con); echo "Thanks! <br />the client is updated. <br /><br />go back to <a href=\"index.html\">Index</a>."; }so if the results provides a valid connection without error, it will begin update, and thank you, if not if it was run before the if and else, it will display error as it tried to carry out invalid sql, through mysql_query($sql,$con); and then again when it runs through the if (!mysql_query($sql,$con)) condition.OK! I've managed test a similar script with if (!mysql_query($sql,$con)) condition and it works fine! so ignore above. try and echo the $sql it should show something similar to thisUPDATE mytable SET Age = '45' WHERE personID = '17'also have added the name and values to the field name dropdown<select name="fieldname"><option value="selected">Choose...</option><option value="LastName">Last Name</option><option value="Age">Age</option>the values should match exactly the table fieldnamesor php dynamically using$sql="SELECT LastName, Age, Address, Email, Telefoon, City FROM Clients"; $query = mysql_query($sql);echo '<select name="fieldname">'."\n";echo '<option selected="selected">Choose...</option>'."\n";for ($i = 0; $i < mysql_num_fields($query); $i++){echo '<option value="'.mysql_field_name($query, $i).'">'.mysql_field_name($query, $i).'</option>'."\n";}echo '</select>'."\n";

[MadFly]

You'd put it after you declare your $sql variable. (The second time) So:

$sql=$_POST['fieldname']."= '".$_POST['comments']; //fieldname and value to replace with$sql="UPDATE Kliente SET ".$sql."' WHERE personID = '".$_POST['clients']."'"; //complete sql$result = mysql_query($sql,$con);

Then change your test for the if statement:Instead of:if (!mysql_query($sql,$con))you'd use:if (!$result)

After using this it works perfectly, it now allows me to update the selected field of a certain user.To select the user i am using that php code that automatically gets all client names from the sql database. Over the weekend i figured that, that code IS using the personID as a reference, so I should be able to use it. And with the added modifications to update.php it works perfectly now.thanks a million for all your helpings!