[RESOLVED] database not updating

linda_thomas

I'm very new to php so I may have made a mistake somewhere though I can't see where! For some reason on this one page the update_handler isn't updating the database. Yet it's exactly the same code as on three other pages, and they work just fine. Can anyone spot anything that would be stoping this?

<?php
include_once '../inc/hearse.inc';

$pl_id=$_POST['pl_id'];
$pl_cat=$_POST['pl_cat'];
$pl_desc=$_POST['pl_desc'];
$pl_price=$_POST['pl_price'];

@mysql_connect($host,$username,$password) or die("Unable to connect with server!");
@mysql_select_db($database)or die("Unable to connect to database, please try later!");

$query = "UPDATE pricelist SET pl_cat='$pl_cat',pl_desc='$pl_desc',pl_price='$pl_price'WHERE pl_id = $pl_id";
mysql_query($query);
    header("Location: pl_updated.php");

mysql_close();
?>

Many thanks in advance for any help given!
Linda

NogDog

To get some debug info, try changing the query section to :

$result = mysql_query($query);
if(!$result)
{
  die("Query failed: $query - " . mysql_error());
}
elseif(mysql_affected_rows() == 0)
{
  die("Query syntax OK, but no rows updated: $query");
}

For the production version you'll probably want error-handling that does not display queries to the browser, but for now this should let you know if the problem has something to do with the query.

ClarkF1

You might need a space between ' and WHERE

Also use mysql_real_escape_string() on all the $_POST variables to protect from SQL injection

linda_thomas

Thank you very much for replying to my post, I tried all methods suggested, but it didn't resolve anything. So after much frustration, I saved the handler as old, then retyped it. (which I have included below), and it worked! The thing is I can not for the life of me see any differences! The changes suggested by Clark (Clark have I done this right? I'm very new to this so forgive me if it's the wrong) which were made after the re-write, they are the same. It's very strange.

<?php
include_once '../inc/hearse.inc';

$pl_id=($POST['pl_id']);
$pl_cat=($POST['pl_cat']);
$pl_desc=($POST['pl_desc']);
$pl_price=($POST['pl_price']);

@mysql_connect($host,$user,$password) or die("Unable to connect to server!");
@mysql_select_db($database) or die ("Unable to connect to database, please try later!");

$query = "UPDATE pricelist SET pl_cat='$pl_cat',pl_desc='$pl_desc',pl_price='$pl_price'WHERE pl_id = $pl_id";
mysql_query($query);
header("Location: pl_updated.php");

mysql_close();
?>

Once again thank you for your help!
Linda

PS I have one other very quick qestion, (should I start another thread?) I was wondering how I would go about being able to use quotes and apostrophes in a post form? Or is this even possible? At the moment i have to use the back slash to include them in the data. I have done a search for this, but am not to sure what words I should be using to do the search. As I've only come up with articles on their use in php. Any links would be greatfully received!

ClarkF1

It should look like this:


<?php
include_once '../inc/hearse.inc';

$pl_id=mysql_real_escape_string($_POST['pl_id']);
$pl_cat=mysql_real_escape_string($_POST['pl_cat']);
$pl_desc=mysql_real_escape_string($_POST['pl_desc']);
$pl_price=mysql_real_escape_string($_POST['pl_price']);

@mysql_connect($host,$user,$password) or die("Unable to connect to server!");
@mysql_select_db($database) or die ("Unable to connect to database, please try later!");

$query = "UPDATE pricelist SET pl_cat='$pl_cat',pl_desc='$pl_desc',pl_price='$pl_price' WHERE pl_id = $pl_id";
mysql_query($query);
header("Location: pl_updated.php");

mysql_close();
?>

mysql_real_escape_string will add the slashes for you so you don't need to add them in the form.

It also means you don't have to remove them when retrieving data from the database

linda_thomas

That's great, thank you so very much. There is so much to learn, and teaching myself php/mysql at home (when I manage to get five minutes p&q that is!), means that you lack the ability to ask others question. Thank you for clarifying that for me.

I hate to take up to much of your time, but if you could just briefly explain the above! Does the mysql_real_escape_string mean that by adding this, would be attackers would not be able to use malicious script? So by protect the database and those that use the page? Is that about right?

I must say I'm very glad I have found this forum. I have a feeling I may be calling on it frequently!

Prior to asking questions here, I have asked questions on other sites. And sadly most of the answers where a case of go do some more reading! No other information what so ever. This made it very difficult to resolve issues that I was having at the time, as I didn't know what I should be entering into the search engine to get the answers. Hence why I asked in these forums to start with.

Best wishes,
Linda

linda_thomas

I have edited the code as you suggested, but I get errors. Errors included below:

Warning: mysql_real_escape_string(): Access denied for user 'ODBC'@'localhost' (using password: NO) in ADDRESSREMOVED on line 4

Warning: mysql_real_escape_string(): A link to the server could not be established in ADDRESSREMOVED on line 4

Warning: mysql_real_escape_string(): Access denied for user 'ODBC'@'localhost' (using password: NO) in ADDRESSREMOVED on line 5

Warning: mysql_real_escape_string(): A link to the server could not be established in ADDRESSREMOVED on line 5

Warning: mysql_real_escape_string(): Access denied for user 'ODBC'@'localhost' (using password: NO) in ADDRESSREMOVED on line 6

Warning: mysql_real_escape_string(): A link to the server could not be established in ADDRESSREMOVED on line 6

Warning: mysql_real_escape_string(): Access denied for user 'ODBC'@'localhost' (using password: NO) in ADDRESSREMOVED on line 7

Warning: mysql_real_escape_string(): A link to the server could not be established in ADDRESSREMOVED on line 7

If I take it back out it works fine! Any ideas?

Best wishes,
Linda

cahva

Put those escapes AFTER the connection. Mysql_real_escape_string needs the connection first.

include_once '../inc/hearse.inc';

@mysql_connect($host,$user,$password) or die("Unable to connect to server!");
@mysql_select_db($database) or die ("Unable to connect to database, please try later!"); 

$pl_id=mysql_real_escape_string($_POST['pl_id']);
$pl_cat=mysql_real_escape_string($_POST['pl_cat']);
$pl_desc=mysql_real_escape_string($_POST['pl_desc']);
$pl_price=mysql_real_escape_string($_POST['pl_price']);
...

linda_thomas

Woowww you guys are amazing!!! It should have been so obvious, but sadly to moi it wasn't lol.... This is like learning Italian all over again heheeee but harder. Let's hope I do better with this than I did with the Italian. At least mysql wont be offended when I tell it, it's grandmother has died lol....

That's great, thank you very much to all! There is just so much to learn, I have a feeling this is going to take me years! I thought it was great when I learned html in a few weeks. But here I am, 3weeks into php/mysql and I'm no further forward! BUT where there's a will and all that.

Thanks again!
Best wishes and have a great day,
Linda