Page 1 of 1

update specific fields in mysql

Posted: Tue Apr 17, 2007 8:36 am
by php4user2007
Hi,

I would like to know if there is an easy way to let php update a mysql database only if values have been assigned to the specific variables that go into each field.

For instance if I would like to update a table in mysql with 10 fields I would create a form on a website with 10 input fields and write a php script that will insert all of the 10 variables into the 10 fields. However, I want only the fields that contain values entered by the user to be updated; i.e., the fields that the user did not enter any new varialbes for should not be altered.

Is there a way to do that or will I have to write 10 if statements and make the update of each field conditional whether the user input a value in the corresponding field in the form.

thanks,


Philip

Posted: Tue Apr 17, 2007 11:03 am
by RobertGonzalez
So if the value of the field is currently hector and the user doesn't change it, you want it to stay hector? Is that what you are asking?

Posted: Tue Apr 17, 2007 11:19 am
by mikeeeeeeey
let the form hold the existing data,

that way the user can then use the form to change whatever they want, the form will then overwrite the existing data with the form contents.

hence, information that has changed will be changed and the rest will stay the same.

alternatively, look at hidden form inputs and a foreach() if you'd prefer to do this without outputting lots of data.

hope it helps!

Posted: Tue Apr 17, 2007 3:35 pm
by guitarlvr
If you only want to input the data into the database that the user has inputed and leave everything else alone, you can build the query string dynamically. Below is an example of a script I did that builds a query string dynamically. It takes values put into fields and searches the database for values pertaining to the variables received from the form. Rather selecting you would be inserting:

Code: Select all

if (isset($_POST['submit'])) {
		require_once ('../mysql_connect.php');
		
		//Build query
		$query = "SELECT * FROM users WHERE ";
		if (!empty($_POST['first_name'])) {
				$first_name = $_POST['first_name'];
				$num = ($num + 1);
				$query .= "first_name='$first_name' ";
			} else {
				$query = $query;
				$first_name = FALSE;
			}
		if (!empty($_POST['last_name'])) {
				$last_name = $_POST['last_name'];
				$num = ($num + 1);
					if ($num >= 2) {
						$query .= "AND last_name='$last_name' ";
					} else {
						$query .= "last_name='$last_name' ";		
					}			
			} else {
				$last_name = FALSE;
		}
		if (!empty($_POST['email'])) {
				$email = $_POST['email'];
				$num = ($num + 1);
					if ($num >= 2) {
						$query .= "AND email='$email'";
					} else {
						$query .= "email='$email'";		
					}			
			} else {
				$email = FALSE;
		}
		if (($first_name  == FALSE) && ($last_name == FALSE) && ($email == FALSE)) {
				echo '<p><font color="red" size="+1">You did not enter any search criteria.</font></p>';
			} else {
		$result = mysql_query($query);
		if (mysql_num_rows($result) >= 1) {
				echo '<table border="1" cellpadding="5" align="center">';
				echo "<tr><th>Name</th><th>email</th></tr>";
				while ($row = mysql_fetch_array($result)) {
					echo '<tr><td align="center">';
					echo $row['first_name'] . " " . $row['last_name'];
					echo '</td><td align="center">';
					echo '<a href="mailto:' . $row['email'] . '">' . $row['email'] . '</a>';
					echo "</td></tr>";
					}
				echo '</table>';
			
		mysql_close();
		include ('./includes/footer.php');
		exit();
		} else {
			echo '<p><font color="red" size="+1">Your search did not match any of our records.</font></p>';
		}
	}
}
I'm fairly new to php so there might be some errors but the script does work as i wanted it to.

Hope that helps.

Wayne

Posted: Tue Apr 17, 2007 3:53 pm
by RobertGonzalez
This is my usual logic...
  • Fetch the data to edit and read it into an array (or a series of individual vars if that is your preference)
  • Use that data to fill the form the user is editing
  • On submit (checking for POST vars sent), assign the value of each preset variable to the posted data
  • Validate/sanitize/filter your vars
  • If there are no errors, use the clean posted data (which occupies the preset vars at the point) in your update query
  • Otherwise use that same data to refill the form without any changes to the core data
This process allows for easy to maintain data from the client to the server, allows for easy filling of forms, easy updates and easy error messaging while still keeping their posted information in the form for them to edit if necessary.

Posted: Tue Apr 17, 2007 4:16 pm
by guitarlvr
Everyah, Is post vars sent a function? i searched on google for it but couldnt come up with anything.

Thanks,

Wayne

Posted: Tue Apr 17, 2007 4:34 pm
by John Cartwright
What?

Posted: Tue Apr 17, 2007 4:42 pm
by RobertGonzalez
What I meant was:

Code: Select all

<?php
if (isset($_POST['some_form_field_name']))
{
    // do some stuff
}
?>