[solved] mysql_db_query problem with UPDATE (via PHP)

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
User avatar
robster
Forum Contributor
Posts: 360
Joined: Wed Jul 16, 2003 8:28 am
Location: Sunshine Coast, Australia

[solved] mysql_db_query problem with UPDATE (via PHP)

Post by robster »

Assuming i am connected to mysql and my variables are loaded appropriately, can anyone see why this doens't work?
(It does NOT error, it just keeps on going to the next part of the script and does not update the database).

Code: Select all

$Update = mysql_db_query ($dbname, "UPDATE user_info SET photo='$photos_remaining' WHERE id='$id'") or die('Subtracting 1 from photos remaining failed: ' . mysql_error());
Any advice appreciated,

Rob
Last edited by robster on Wed Sep 28, 2005 3:07 am, edited 3 times in total.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

What is the error?
User avatar
robster
Forum Contributor
Posts: 360
Joined: Wed Jul 16, 2003 8:28 am
Location: Sunshine Coast, Australia

Post by robster »

there is none, that's the thing! So bizzarre.

Basically, field 'photo' is currently set to 28. When I run the script, it sets $photos_remaining to (photo - 1). I've tested this with an echo "$photos_remaining"; and it shows 27 just before that line is run. The line then runs and it stays at 28 :(

No error, no change, script continues.


(shrug)
Charles256
DevNet Resident
Posts: 1375
Joined: Fri Sep 16, 2005 9:06 pm

Post by Charles256 »

hm.. this may not help but sometimes it does... on the variable do something like

Code: Select all

"whatever='".$variable."'"
sometimes doing stuff that way helps..no it doesn't make any sense to me but i don't argue with results
User avatar
robster
Forum Contributor
Posts: 360
Joined: Wed Jul 16, 2003 8:28 am
Location: Sunshine Coast, Australia

Post by robster »

I don't quite understand that, did you mean:

Code: Select all

"$whatever='".$variable."'"
But I can't see that working either... (sorry, it's just not sinking into this head ;))
User avatar
robster
Forum Contributor
Posts: 360
Joined: Wed Jul 16, 2003 8:28 am
Location: Sunshine Coast, Australia

Post by robster »

turns out the problem was the $id, it was the id of the image, not the user... I fixed it and it worked. Strange though, I would have thought it would error.

Image id was 80, user id was 1. There is no user with an id of 80 on this test machine, so I would have expected a bail... strange indeed.

Thanks again though for your help.

Rob
User avatar
robster
Forum Contributor
Posts: 360
Joined: Wed Jul 16, 2003 8:28 am
Location: Sunshine Coast, Australia

Post by robster »

This is just.... rediculous!!!!!! :(

Same problem, only this time the vars ARE correct, I've checked, double, tripple checked, wasted half an afternoon :(

Code: Select all

$Update = mysql_db_query ($dbname, "UPDATE user_photos SET primary = '$primary' WHERE id = '$photo_id'") or die('<b>Updating primary photo failed: </b>' . mysql_error());
It errors with:

Code: Select all

Updating primary photo failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'primary = '1' WHERE id = '86'' at line 1
User avatar
robster
Forum Contributor
Posts: 360
Joined: Wed Jul 16, 2003 8:28 am
Location: Sunshine Coast, Australia

Post by robster »

ok too strange, if I change this:

Code: Select all

$Update = mysql_db_query ($dbname, "UPDATE user_photos SET primary = '$primary' WHERE id = '$photo_id'") or die('<b>Updating primary photo failed: </b>' . mysql_error());
to this:

Code: Select all

$Update = mysql_db_query ($dbname, "UPDATE user_photos SET id= '$primary' WHERE id = '$photo_id'") or die('<b>Updating primary photo failed: </b>' . mysql_error());
it changes the 'id' but it will not change the 'primary' on the original piece of code!!!

id and primary are both of type INT and I can't see why one would change and not the other?!

Any ideas?
jollyjumper
Forum Contributor
Posts: 107
Joined: Sat Jan 25, 2003 11:03 am

Post by jollyjumper »

Hi Robster,

It's probably because primary is a reserved word in mysql for setting the primary key of the table. I guess if you change the fieldname to myprimary it will work.

Greetings Jolly.
User avatar
robster
Forum Contributor
Posts: 360
Joined: Wed Jul 16, 2003 8:28 am
Location: Sunshine Coast, Australia

Post by robster »

ARRRRRRRGGGGGHHHHHH!!!

:)

Thank you SO much, this is not the first time I've done that (with other reserved words though).

Thanks again !!

8)
jollyjumper
Forum Contributor
Posts: 107
Joined: Sat Jan 25, 2003 11:03 am

Post by jollyjumper »

You're welcome. Glad I could help.

I think everyone has had something like this in the past. I once had something like this with javascript. I had a form and a textbox with the name action, and then try to change form.action.value. This didn't work off course because action is a property of form so it didn't refer to my textbox, but before I noticed that I had wasted a lot of time :-)

Hmm, maybe a nice idea for a new topic, your most idiot mistake or something like that :-)

goodluck with your script and have a nice day.
User avatar
robster
Forum Contributor
Posts: 360
Joined: Wed Jul 16, 2003 8:28 am
Location: Sunshine Coast, Australia

Post by robster »

perhaps it could go into a FAQ (Fully Absurd Questions).

I bet it happens to so many people at some stage.
jollyjumper
Forum Contributor
Posts: 107
Joined: Sat Jan 25, 2003 11:03 am

Post by jollyjumper »

"Fully Absurd Questions" :lol: :lol:

nice one, I'm going to use that one day.
Post Reply