Page 1 of 1

Newbie needs help on modifying records in a table

Posted: Thu Apr 02, 2009 6:54 am
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

Re: Newbie needs help on modifying records in a table

Posted: Thu Apr 02, 2009 7:18 am
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.

Re: Newbie needs help on modifying records in a table

Posted: Thu Apr 02, 2009 7:39 am
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?

Re: Newbie needs help on modifying records in a table

Posted: Thu Apr 02, 2009 8:22 am
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).

Re: Newbie needs help on modifying records in a table

Posted: Thu Apr 02, 2009 8:57 am
by DanielDaBello
Thanks for the help, I'll give it a try and let you know how I get on.

Re: Newbie needs help on modifying records in a table

Posted: Thu Apr 02, 2009 9:50 am
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));

Re: Newbie needs help on modifying records in a table

Posted: Thu Apr 02, 2009 10:04 am
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)

Re: Newbie needs help on modifying records in a table

Posted: Thu Apr 02, 2009 12:07 pm
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.

Re: Newbie needs help on modifying records in a table

Posted: Thu Apr 02, 2009 12:25 pm
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?

Re: Newbie needs help on modifying records in a table

Posted: Fri Apr 03, 2009 9:33 am
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!!

Re: Newbie needs help on modifying records in a table

Posted: Sat Apr 04, 2009 9:22 am
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.

Re: Newbie needs help on modifying records in a table

Posted: Tue Apr 07, 2009 6:08 am
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?

Re: Newbie needs help on modifying records in a table

Posted: Tue Apr 07, 2009 6:46 am
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 :)

Re: Newbie needs help on modifying records in a table

Posted: Tue Apr 07, 2009 7:03 am
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>