Page 1 of 1

problems with UPDATE

Posted: Mon Jun 18, 2007 5:00 am
by cosmicdog
Hello, I'm new to the boards and fairly new to SQL. So in short, my problem is that every time I try to update a record, it doesn't update it, but it creates a new record. I've pulled the SQL code from phpmyadmin and it still doesn't work. So I kind of doubt that it's the query. All the same, here it is.

Code: Select all

$query = 'UPDATE `dogParks` SET `name` = $name, `description` = $description, `city` = $city, `state` = $state, `zip` = $zip, `website` = $website, `offleash` = $offleash WHERE `key` = $_GET['id'] LIMIT 1;';
I've fought with this problem to the point where I don't even know if I understand it anymore. Every other SQL thing I've done has been pretty straight forward and made sense. This just doesn't make any sense to me.

I have a link on my report page to pass the primary key to another php page which queries the database and returns the data for the record and populates a form. Work's just fine. Submit the form, passes the primary key again and calls the update code above. Creates a new record instead of updating it. I'm going to assume the code above is correct and that there's something else I am missing. What could be causing this? I would be very relieved to find out I'm making a noobie mistake. Thanks in advance.

Re: problems with UPDATE

Posted: Mon Jun 18, 2007 5:14 am
by volka
cosmicdog wrote:So in short, my problem is that every time I try to update a record, it doesn't update it, but it creates a new record.
Then you're looking at the wrong piece of code. An UPDATE statement never creates a new record, an INSERT statment does.
Anyway, print and check the query statement

Code: Select all

echo '<div>Debug: ', htmlentities($query), "</div>\n";

Posted: Mon Jun 18, 2007 5:26 am
by cosmicdog
Of course I was....... sheesh.... and now it's still not updating, but nothing's happening now. This really shouldn't be difficult.

Posted: Mon Jun 18, 2007 5:30 am
by volka
Have you printed the query? What does it print?

Posted: Mon Jun 18, 2007 5:34 am
by cosmicdog
says this:

Debug: UPDATE `dogParks` SET `name` = Culver City Dog Park, `description` = Great local dog park, `city` = Culver, `state` = 0, `zip` = 0, `website` = http://www.culvercitydogpark.com WHERE key = 15 LIMIT 1;

Posted: Mon Jun 18, 2007 6:16 am
by Kadanis
You need to put single quotes around your field data and curled braces around the array variable if you are going to parse it inside a string variable. MySQL (and SQL in general) doesn't like string variables if they are not enclosed in single quotes. That's why you must escape data before the query so that any single quotes don't effect the final outcome.

Code: Select all

$query = "UPDATE `dogParks` SET `name` = '$name', `description` = '$description', `city` = '$city', `state` = '$state', `zip` = '$zip', `website` = '$website', `offleash` = '$offleash' WHERE `key` = '{$_GET['id']}' LIMIT 1;";
A handy way to see what SQL doesn't like with your query is to add a die statement. Just remember to remove them when the site goes live.

For example:

Code: Select all

mysql_query($query) or die (mysql_error());

Thank you - it's fixed

Posted: Mon Jun 18, 2007 11:42 am
by cosmicdog
Thanks guys. Apparently I was right, noobie mistake, just not understanding the syntax well enough. Guess that's what happens when you learn on the run. Thanks again.

Posted: Mon Jun 18, 2007 3:20 pm
by feyd
I do hope there is some form of escaping (and filtering) going on with the $_GET variable being used in this query before it's executed/used...

Posted: Mon Jun 18, 2007 5:52 pm
by cosmicdog
It's the primary key. There wouldn't need to be any escaping when it's guaranteed to be a number, would there?

Posted: Mon Jun 18, 2007 6:20 pm
by volka
You can guarantee $_GET['id'] (i.e. user input) is a valid number?
No, you can't ;)

Posted: Mon Jun 18, 2007 9:28 pm
by bdlang
cosmicdog wrote:It's the primary key. There wouldn't need to be any escaping when it's guaranteed to be a number, would there?
Here's what I'd do with that unchecked $_GET['id] string (don't allow your query to run, just check the input values):

yourscript.php?id=1%20%4F%52%20%31%3D%31%3B

Posted: Mon Jun 18, 2007 10:06 pm
by superdezign
When it has to be a number, typecasting will suffice.