A community in which webmasters can ask for help with topics such as PHP coding , MySQL , IT jobs, web design, IT security.
Current location:homephp forumphp talk in 2009 yearsql - updating without overriding existing information - page 1
User InfoPosts
sql - updating without overriding existing information#1
I have the following conditional code, which will insert a new record if a record with the appropriate username does not exist, and update the record if it does exist. This works fine.

However, at the moment, if I insert a new record, and only insert the firstname and lastname, and maybe address details with say phone information being left blank this is fine. If I then wish to update the record with just the phone record, the name and address information gets replaced with nothing.

What I would like to know, is if it is possibly to have an easy way to populate my php html form for updating the information, with the content of the field I will be updating? I am using

 <input type="text" name="uniquename" />


To obtain user input, which is then passed to a javascript function which is then passed back to the below php code. If this is not possible, is there an easy way to work some sql/php magic, to only update a field corresponding the userinput which is not empty?

$usernameQuery = "select username from USERS where username = (" . $con->escape_string($username) . "("; 

$xblah = $con->query($usernameQuery);
while ($row = mysqli_fetch_assoc($xblah))
{
$checkUsername = $row[(username(];
}

if ($checkUsername == null) {

$userQuery = "INSERT INTO USERS VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
if ($userInfo = $con->prepare($userQuery)) {
$userInfo->bind_param("ssssssssssssssssssss", $username, $firstname, $lastname, $flaggedauctions, $lastauction, $street1, $city1, $postcode1, $street2, $city2, $postcode2, $phone, $mobilephone, $fax, $email, $website, $bank, $banknumber, $accountnumber, $comments);
$userInfo->execute();
$userInfo->close();
echo "true";
} else {
echo "false";
}
print_r($con->error);
}

else if ($checkUsername == $username) {

$userQuery = "UPDATE USERS SET firstname = ?, lastname = ?, flaggedauctions = ?, lastauction = ?, street1 = ?, city1 = ?, postcode1 = ?, street2 = ?, city2 = ?, postcode2 = ?, phone = ?, mobilephone = ?, fax = ?, email = ?, website = ?, bank = ?, banknumber = ?, accoutnumber = ? WHERE username = ?";
if ($userInfo = $con->prepare($userQuery)) {
$userInfo->bind_param("sssssssssssssssssss", $firstname, $lastname, $flaggedauctions, $lastauction, $street1, $city1, $postcode1, $street2, $city2, $postcode2, $phone, $mobilephone, $fax, $email, $website, $bank, $banknumber, $accountnumber, $username);
$userInfo->execute();
$userInfo->close();
echo "true";
} else {
echo "false";
}
print_r($con->error);
}

posted date: 2009-04-15 19:32:00


Re: sql - updating without overriding existing information#2
I had made out the solution of this problem. click to view my topic...

hope that hepls.

posted date: 2009-04-15 19:32:01


Re: sql - updating without overriding existing information#3
You just have to build your update statement and bind parameter dynamically.e.g.$userQuery = "UPDATE ";if (!empty($firstname)) { $userQuery += "firstname = ?,";}... if (!empty($firstname)) { $bindArgs[] = $firstname;}...so your query won(t update columns that are not supposed to change.

posted date: 2009-04-15 19:53:00


Re: sql - updating without overriding existing information#4
Hmm, thanks. What is happening with bindArgs?

posted date: 2009-04-15 20:17:00


Re: sql - updating without overriding existing information#5
so they are two seperate solutions? I would just have bindARgs for each variable?

posted date: 2009-04-15 20:47:00


Re: sql - updating without overriding existing information#6
What you do is that you build up your $bindArgs array then pass it into the bind_param function this way: $userInfo->bind_param("sssssssssssssssssss", $bindArgs) like I said my PHP skill is a bit rusty but I think this works because variable-length argument list function should accept array too.

posted date: 2009-04-15 20:49:00


Re: sql - updating without overriding existing information#7
More generally, note that:It(s wise to specify your column names in INSERT ... VALUES because otherwise if someone adds a column you(ll be annoyed and if someone reorders the columns you(ll be confused.There(s a race condition between your select and your update ... another process could insert/delete a row while you(re still thinking, causing your insert/update to fail Consider either:INSERT ... ON DUPLICATE KEY UPDATE ...REPLACE INTO ...

posted date: 2009-04-15 22:35:00


select page: « 1 »
Copyright ©2008-2017 www.momige.com, all rights reserved.