Newbie needs help on modifying records in a table

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
DanielDaBello
Forum Newbie
Posts: 8
Joined: Thu Apr 02, 2009 6:36 am

Newbie needs help on modifying records in a table

Post by DanielDaBello »

Hi I'm pretty new to PHP.
I have some tables that I need to be able to modify, one contains user id, name, dob, etc, another contains location information. To modify a record I'm using a form with all the fields listed and as it stands I can modify all the fields in one record across the tables, but I get stuck when, for example I only need to update the the dob or change a surname. I leave the rest of the form blank, which then updates my tables with a blank.
I've looked for hours online and I can't seem to get anywhere, any help on how to only update fields with data in them would be really appreciated.
Thanks
DDB
User avatar
Apollo
Forum Regular
Posts: 794
Joined: Wed Apr 30, 2008 2:34 am

Re: Newbie needs help on modifying records in a table

Post by Apollo »

Code: Select all

UPDATE fancytable SET dob='1-4-1982' WHERE id=846173;
This will only update the dob for the person with id 846173, and leave his other details unchanged.
DanielDaBello
Forum Newbie
Posts: 8
Joined: Thu Apr 02, 2009 6:36 am

Re: Newbie needs help on modifying records in a table

Post by DanielDaBello »

Thanks Apollo

I get that, but the problem is that the end user is just going to click on a link to modify a record, which is going to show a form with id, first name, surname, dob, sex, location etc, now if they only put 846173 against id & 1-4-1982 against dob, then click submit, all the other fields become blank. Is there a way round that?
User avatar
Apollo
Forum Regular
Posts: 794
Joined: Wed Apr 30, 2008 2:34 am

Re: Newbie needs help on modifying records in a table

Post by Apollo »

Create the SET part of your query dynamically, including only the non-empty values. For example:

Code: Select all

$fields = array('dob', 'firstName', 'sex', /* etc */ );
$changes = array();
foreach($fields as $field)
{
    $x = $_POST['field'];
    if ($x) $changes[] = $field . "='" . mysql_real_escape_string($x) . "'"; // e.g. results in "firstName='Joe'" if ($_POST['firstName']=='Joe')
}
if (count($changes))
{
    $id = intval($_POST['id']);
    $query = 'UPDATE table SET '.implode(', ',$changes).' WHERE id='.$id;
}
Note that this requires the form elements to have the same names as the columns in your SQL table (or you have to convert them in the $changes[] = $field ... part).
DanielDaBello
Forum Newbie
Posts: 8
Joined: Thu Apr 02, 2009 6:36 am

Re: Newbie needs help on modifying records in a table

Post by DanielDaBello »

Thanks for the help, I'll give it a try and let you know how I get on.
DanielDaBello
Forum Newbie
Posts: 8
Joined: Thu Apr 02, 2009 6:36 am

Re: Newbie needs help on modifying records in a table

Post by DanielDaBello »

okay I've tried that and it doesn't seem to modify anything, probably a lack of understanding on my part1!!
this is the code I used:-
//modify the animal table
$fields = array('animalId', 'animalName', 'animalSpecies', 'animalBreed', 'animalDob', 'animalSex', 'animalSecurity', 'animalComments');
$changes = array();
foreach($fields as $field)
{
$x = $_POST['field'];
if ($x) $changes[] = $field . "='" . mysql_real_escape_string($x) . "'";
}
if (count($changes))
{
$animalId = intval($_POST['animalId']);
$query = 'UPDATE animal SET '.implode(', ',$changes).' WHERE animalId='.$animalId;
}

Don't know if it helps, but this was my previous code:-
$add_animal_sql = "UPDATE animal SET animalId = '".$_POST["animalId"]."', animalName = '".$_POST["animalName"]. "', animalSpecies = '".$_POST["animalSpecies"]."', animalBreed = '".$_POST["animalBreed"]."', animalDob = '". $_POST["animalDob"]."', animalSex = '".$_POST["animalSex"]."', animalSecurity = '".$_POST["animalSecurity"]."', animalComments = '".$_POST["animalComments"]."' WHERE animalId = '".$_POST["sel_animalId"]."'";
$add_animal_res = mysqli_query($mysqli, $add_animal_sql) or die(mysqli_error($mysqli));
User avatar
Apollo
Forum Regular
Posts: 794
Joined: Wed Apr 30, 2008 2:34 am

Re: Newbie needs help on modifying records in a table

Post by Apollo »

To avoid any confusion, do you actually execute the query I create? (i.e. do you perform mysqli_query($query) later on?)

Furthermore, animalId is now in the field list, so it can be changed by the user. Is that your intention? (I'd rather expect the id to be in a hidden field in the form)
DanielDaBello
Forum Newbie
Posts: 8
Joined: Thu Apr 02, 2009 6:36 am

Re: Newbie needs help on modifying records in a table

Post by DanielDaBello »

I wasn't-I'm very new to php!!
Should it be something like this?
$result = mysql_query($query) or die(mysqli_error($mysqli));
Doesn't seem to work though.
As regard to the aminalId, it probably wont be amendable in the end, but I just wanted to get everything working first.
User avatar
Apollo
Forum Regular
Posts: 794
Joined: Wed Apr 30, 2008 2:34 am

Re: Newbie needs help on modifying records in a table

Post by Apollo »

DanielDaBello wrote:I wasn't-I'm very new to php!!
Should it be something like this?
$result = mysql_query($query) or die(mysqli_error($mysqli));
Yes, that should do it. Note that you shouldn't mix up mysql_* and mysqli_* functions.

Since you mentioned it still doesn't seem to work, do you see no changes at all, or is it emptying non-specified fields again? (like before)

To debug, can you print the $query string just before you mysql_query it, and post the output here?
DanielDaBello
Forum Newbie
Posts: 8
Joined: Thu Apr 02, 2009 6:36 am

Re: Newbie needs help on modifying records in a table

Post by DanielDaBello »

Hi, I've been trying to get it to work, but it just doesn't update anything.
Can't even get it to print the string. However, I'm trying to debug, by trial and error will keep you posted!!
User avatar
Apollo
Forum Regular
Posts: 794
Joined: Wed Apr 30, 2008 2:34 am

Re: Newbie needs help on modifying records in a table

Post by Apollo »

If you add

Code: Select all

print($query);
below the code posted earlier, then it should print the query. Debug further from there.
DanielDaBello
Forum Newbie
Posts: 8
Joined: Thu Apr 02, 2009 6:36 am

Re: Newbie needs help on modifying records in a table

Post by DanielDaBello »

Hi
That's exactly what I was trying to do with 'print', but I don't get anything apart from my html. Would the fact that after the query, I run it through an html scrip stop the print fN displaying?
User avatar
Apollo
Forum Regular
Posts: 794
Joined: Wed Apr 30, 2008 2:34 am

Re: Newbie needs help on modifying records in a table

Post by Apollo »

DanielDaBello wrote:Would the fact that after the query, I run it through an html scrip stop the print fN displaying?
Uhmm.. what? :)

Not sure what you mean with that, but it sounds like something you probably want to disable :)
DanielDaBello
Forum Newbie
Posts: 8
Joined: Thu Apr 02, 2009 6:36 am

Re: Newbie needs help on modifying records in a table

Post by DanielDaBello »

I'm not too sure either. I though that maybe the html at the end could stop the print fn working, anyway this is the end portion of my code. When I submit the form it just says that my entry has been updated, but doesn't show the query-any ideas?

//modify the animal table
$fields = array('animalId', 'animalName', 'animalSpecies', 'animalBreed', 'animalDob', 'animalSex', 'animalSecurity', 'animalComments');
$changes = array();
foreach($fields as $field)
{
$x = $_POST['field'];
if ($x) $changes[] = $field . "='" . mysql_real_escape_string($x) . "'";
}
if (count($changes))
{
$animalId = intval($_POST['animalId']);
$query = "UPDATE animal SET = implode(', ',$changes) WHERE animalId = '".$_POST["animalId"]."'";
$result = mysqli_query($query) or die(mysqli_error($mysqli));
print($query);
}

//link to add another animal to the records
mysqli_close($mysqli);
$display_block = "<p>Your entry has been Updated. Would you like to <a href=\"animalModifyEntry.php\">modify another</a>?</p>
<a href=\"ZooMenu.html\">Home</a>";

}
?>

<html>
<head>
<title>Modify an Entry</title>
<link href="ZooStyle2.php" rel="stylesheet" type="text/css">
</head>
<body>
<div id = "main">
<div id = "body">
<div id ="heading">
<h1>Update An Entry</h1>
<br><br>
</div>
<?php echo $display_block;?>
</div>
</div>
</body>
</html>
Post Reply