Newbie needs help on modifying records in a table
Moderator: General Moderators
-
DanielDaBello
- Forum Newbie
- Posts: 8
- Joined: Thu Apr 02, 2009 6:36 am
Newbie needs help on modifying records in a table
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
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
Code: Select all
UPDATE fancytable SET dob='1-4-1982' WHERE id=846173;-
DanielDaBello
- Forum Newbie
- Posts: 8
- Joined: Thu Apr 02, 2009 6:36 am
Re: Newbie needs help on modifying records in a table
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?
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
Create the SET part of your query dynamically, including only the non-empty values. For example:
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).
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;
}-
DanielDaBello
- Forum Newbie
- Posts: 8
- Joined: Thu Apr 02, 2009 6:36 am
Re: Newbie needs help on modifying records in a table
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
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));
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
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)
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
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.
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
Yes, that should do it. Note that you shouldn't mix up mysql_* and mysqli_* functions.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));
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
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!!
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
If you addbelow the code posted earlier, then it should print the query. Debug further from there.
Code: Select all
print($query);-
DanielDaBello
- Forum Newbie
- Posts: 8
- Joined: Thu Apr 02, 2009 6:36 am
Re: Newbie needs help on modifying records in a table
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?
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
Uhmm.. what?DanielDaBello wrote:Would the fact that after the query, I run it through an html scrip stop the print fN displaying?
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
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>
//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>