[RESOLVED] updateing a mysql database

[damasterjo]

http://www.questfor3d.com/phptest/showdb.php

please try to edit one of these entrys, it does not work, please advise on what may be wrong.

attached is the file that is "supposed" to be doing the updating...

[kows]

first of all, you shouldn't EVER publicly give out MySQL server information, including the server name/username/password. that's a humongous security risk, and it's now possible that someone's going to check that script out and screw with your stuff if your MySQL server allows connections from unprivileged IP addresses.

for your actual problem: you just aren't querying the database correctly. an UPDATE query does not have the same syntax as an INSERT query, but rather a more similar syntax to the SELECT query. you have:

UPDATE Pizza SET VALUES ('$NamePizza','$Discription','$MPRICE','$LPRICE','$XLPRICE' WHERE ID='$IndexVal')

this is wrong. it should be something more like this (change your field names accordingly):

UPDATE Pizza SET pizzaname='$NamePizza', description='$Description', price_medium='$MPRICE', price_large='$LPRICE', price_xlarge='$XLPRICE' WHERE ID='$IndexVal'

If you would like a quick walkthrough on MySQL syntax and basic commands, you can look here to a post I made last October. You could always search Google for more help, too.

[damasterjo]

$query = "UPDATE Pizza SET Name='$NamePizza',Discription='$Discription',MPrice='$MPRICE',LPrice='$LPRICE',XLPrice='$XLPRICE' WHERE ID='$IndexVal'";

that is what i have now and it dosent work

[damasterjo]

here are the files... plese help. i havent touched this code in so long, and it never worked, all field are correct i believe and i am connecting with proper access codes and such

[kows]

in changedb.php, you have a syntax error (at least in the attachment you posted, although this might be only there because you removed the host stuff). you have:

@
mysql_connect($hostname, $username, $password);

if your script on the server has the line break after the AT sign ('@'), it will probably not work.

other than that, I can't really see anything obvious as to why it wouldn't work. Add "or die()" statements to other function calls relating to MySQL, coupled with a call to mysql_error() to print the error it is returning as well. example:

@mysql_connect($hostname,$username,$password) or die("couldn't connect to MySQL server!<br />" . mysql_error());

...........

mysql_query($query) or die("query error!<br />" . mysql_error()); 

this will usually tell you if the query fails, and if it does, will print what MySQL says. if the query fails, some of your syntax is wrong. this means either you just have something in the wrong order (doesn't look like it could be that), didn't escape some characters in your variables, or your field or table names were typed incorrectly/don't exist.

[damasterjo]

there is totally no error, i dont see any problem, any other advice?

[kows]

remove the javascript for now so that it doesn't redirect you (and use the header() function to redirect anyway -- it's much easier and doesn't require your user to have javascript enabled). there has to be an error somewhere.

echo out $query and comment out mysql_query altogether. copy and paste the output when echoing that into a mysql console directly or into a mysql manager like phpmyadmin and make sure the query executes successfully. if not, see what's wrong with it.

[damasterjo]

$query = "UPDATE Pizza SET Name='$NamePizza',Discription='$Discription',MPrice='$MPRICE',LPrice='$LPRICE',XLPrice='$XLPRICE' WHERE ID='$IndexVal'";

ok thats what i had, i changed IndexVal to an acutual number and it worked, so why is indexval not read as a number?
$query = "UPDATE Pizza SET

Name='$NamePizza',Discription='$Discription',MPrice='$MPRICE',LPrice='$LPRICE',XLPrice='$XLPRICE' WHERE ID=3";

cause THAT works...

[kows]

like I said, echo the variable out and make sure it's holding the right value when you're querying the database. if you did this, you'd realize that you're not even passing the "IndexVar" POST variable in the first place.

add a hidden input field on the edit form that has a name of "IndexVal" and a value of "$i" or $_POST['IndexVal'], inside showdb.php.

[damasterjo]

dude i love you! thanks!