Updating db from (text) input field

[Sub_Seven]

Hello everyone and happy weekend

Turns out I can't update a mysql field from a text input field in a form, I have looked and found two good examples that technically say the same but mine wont work, this is my relevant code:

<?php include($_SERVER['DOCUMENT_ROOT']."/includes/login.php"); ?>
<?php if(isset($_GET['p_id']) && !empty($_GET['p_id'])){
$SqlQuery = "SELECT * FROM `data` WHERE `id` = '".strip_tags(mysql_real_escape_string($_GET['p_id']))."' ";
$SqlSent = mysql_query($SqlQuery) or die(mysql_error());
while($result = mysql_fetch_array($SqlSent)){ ?>
<?php
$i=0;
while ($i < $num) {
$add=mysql_result($result,$i,"pri");
++$i;
}
$u_add=$_POST['add'];
?>
<p><form name="form" method="post">
<input type="text" name="pri" value="<?php echo $add; ?>" />
<input type="submit" name="submit" value="Submit" />
or <a href="/cp">Go back to Control Panel</a>
</form></p>
<?php
$id = $result['id'];
if(isset($_POST['pri'])) {
$a = mysql_query("UPDATE data SET pri = '$add' WHERE id = '$id'");
if($a) {
echo "<br />Price updated successfully! Click <a href='/cp/'>here</a> to go back to <a href='/cp/'>Control Panel</a>";
}else {
echo "<br />error";
}
}
?>
<?php }
}
else{
header("location: /cp/");
exit;
}
?>

I'm not getting an error, it just wont update, now, if I hardcode the value I'd like to update, for example to 500, here:

$a = mysql_query("UPDATE data SET pri = '500' WHERE id = '$id'");

It will update to 500, so that tells me all the connections are fine, the problem has to do with grabbing the data inserted in the input field of the form and adding it to the updating mysql_query.

Could someone please tell me what's wrong with my code? Thank :)

[Matthew1980]

Hi there Sub_Seven,

Well have you actually echoed the sql query to screen to see if the data is being captured and populated before it gets added into the statement?

I would suggest this:-

if(isset($_POST['pri'])) {
echo $sql_query = "UPDATE `data` SET `pri` = '".$add."' WHERE `id` = ".$id." ";
$a = mysql_query($sql_query);
if($a) {

And if the content of id is numerical, you won't need to quote the values - also, the use of data, I'm pretty sure that may be a reserved word [dev.mysql.com] in sql, always good to print that list out for future reference.

You may want want to do this too:-

echo $SqlQuery = "SELECT * FROM `data` WHERE `id` = '".strip_tags(mysql_real_escape_string($_GET['p_id']))."' ";
$SqlSent = mysql_query($SqlQuery) or die(mysql_error());

Again, this will just ensure that your getting the values in the sql that you want when you run the script, just remove the echo when your happy!

Also, when the form is on the screen, check the page source to see if everything is populated in the input tags to make sure that everything is working that side of the script; and I'm pretty sure that you should be $i++; as your wanting to increment, unless I read that wrong..

Form is also defaulting to posting to itself as you haven't defined an action, if your aiming for validation you may just want to include that, but only quickly reading through this, it seems ok - though I could have missed something obvious.

Hope that makes sense.

Cheers,
MRb

[Sub_Seven]

Hey Matt,

Echoing the sql query returns this:

UPDATE `data` SET `pri` = '' WHERE `id` = 1

and looking at the source code the value is empty as well so the problem is that I'm not capturing the data to begin with.

This was wrong $i++; I'm not trying to increment anything, its supposed to be $i; (I guess I was carrying that from the example I saw online.)

Should an action be defined in the form even if you're only updating? because if its not necessary I'd like to keep it as it is (less complicated)

Any ideas? Thanks :)

Oh by the way, I didn't know about reserved words, I saw that page and data doesn't appear in the list, database does though.

[Matthew1980]

>>Should an action be defined in the form even if you're only updating?

Realistically, only if your concerned about W3C validation, but if you don't specify anything the default action is for the script/page/form to post to itself anyway.

Also where is $num being defined, as this is what your specifying in your while() loop, personally I think as this code is redundant, and you could code it a little better just doing something a little more simplified. But firstly, you need to get your values captured and populating the relevant area's of your form... $u_add=$_POST['add']; << this seems to be doing nothing too?

I guess that your just trying to update a price in a database field using some user submitted data, and if it's in the database update with the newer value? Or something like that anyway..

>>Oh by the way, I didn't know about reserved words, I saw that page and data doesn't appear in the list, database does though.

It's good to have a copy of the php reserved word set [php.net] & the mysql reserved word set [dev.mysql.com] too, then at least you can pin it up for quick reference.

Happy coding.

Cheers,
MRb

[Sub_Seven]

Hey Matthew sorry for the late reply,

I got it working after some more research and testing, as you pointed there were several errors on my original script but its all good now, thanks for the help :)