Problems with mysql update and insert 1

[xor]

I'm trying to do an update and insert, in both cases I've got to do a select on the table first, which works. But when I come to do the update and insert queries they don't do anything.

I'm calling mysql_connect each time (any problem with doing this? my host doesn't have persistent connections). I've had a bit of a similar problem on other pages.
eg.
$select = "SELECT Hits FROM postcardhits WHERE PhotoID = '$photoID'";
@ $db = mysql_connect("localhost", "xxx", "xxx&quot
or die ('I cannot connect to the database because: ' . mysql_error());
$result = mysql_db_query("xxx", $select);
//this code above seems to be working.

// if the number of rows is GT 0... and it is...
$row=mysql_fetch_object($result);
$hits=$row->Hits;
$newHits=$row->Hits + 1;
$sql="UPDATE postcardhits SET Hits = '".$newHits."' WHERE PhotoID ='$photoID'";
@ $db = mysql_connect("localhost", "xxx", "xxx&quot
or die ('I cannot connect to the database because: ' . mysql_error());
$result=mysql_query($sql);

NOTE: I originally had '$newHits', until I saw an example in a book in the form '".$newHits."', so I've tried both forms -- no results either way.

I've checked the $sql string, everything is coming out just fine, I can copy and paste it into phpmyadmin and it runs the SQL just fine. But from my PHP page it does nothing.

I seem to sometimes (not all the time) have a problem when I am making more than one call to the database on the same page. But it's nothing consistent, for example in this set I've got 2 calls to the DB, the first is fine, 2nd doesn't work. In another set I've got the first 2 calls working, 3rd does nothing.

Anyone know what's going on here?


The New Zealand Site

http://www.thenewzealandsite.com

 

[vbkris]

after the update stmt:
$result=mysql_query($sql) or die(mysql_error());

this will tell u whether the erro is in the SQL.


Known is handfull, Unknown is worldfull

 

[xor]

Thanks vbkris!
I used that kind of debugging in other places, but I didn't know you could use it for the mysql_query as well. Well, that does sort of explain, this is what I get...

Access denied for user: blah blah
and the details are correct for the user and database.

But why is it giving me a hard time? Guess I should check my permissions and see that I've got update and insert priviledges.

hmmm, time to get out the old MySQL documentation. If anyone knows the commands and feels like letting me know I'd appreciate it

Thanks!

The New Zealand Site

http://www.thenewzealandsite.com

 

[vbkris]

will do!!

Known is handfull, Unknown is worldfull

 

[xor]

Ok I looked it up, this is the easy way, from the mysql console...

mysql> GRANT SELECT, INSERT, UPDATE, DELETE
ON dbname.* TO username@localhost
IDENTIFIED BY 'password';

You don't have to restart MySQL to see the changes, they will take effect immediately with the 'GRANT' command



The New Zealand Site

http://www.thenewzealandsite.com

 

[sleipnir214]

xor:
Even if you manipulate the MySQL permission tables directly, you don't have to restart MySQL for the changes to take effect.

Just issue a "flush privileges" query.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!