problems with UPDATE

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
cosmicdog
Forum Newbie
Posts: 5
Joined: Mon Jun 18, 2007 4:47 am

problems with UPDATE

Post 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.
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Re: problems with UPDATE

Post 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";
cosmicdog
Forum Newbie
Posts: 5
Joined: Mon Jun 18, 2007 4:47 am

Post 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.
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

Have you printed the query? What does it print?
cosmicdog
Forum Newbie
Posts: 5
Joined: Mon Jun 18, 2007 4:47 am

Post 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;
User avatar
Kadanis
Forum Contributor
Posts: 180
Joined: Tue Jun 20, 2006 8:55 am
Location: Dorset, UK
Contact:

Post 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());
cosmicdog
Forum Newbie
Posts: 5
Joined: Mon Jun 18, 2007 4:47 am

Thank you - it's fixed

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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...
cosmicdog
Forum Newbie
Posts: 5
Joined: Mon Jun 18, 2007 4:47 am

Post 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?
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

You can guarantee $_GET['id'] (i.e. user input) is a valid number?
No, you can't ;)
bdlang
Forum Contributor
Posts: 395
Joined: Tue May 16, 2006 8:46 pm
Location: Ventura, CA US

Post 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
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post by superdezign »

When it has to be a number, typecasting will suffice.
Post Reply