FRIHOST • FORUMS • FAQ • TOS • BLOGS • DIRECTORY
You are invited to Log in or Register a Frihost Account!

Changing entries in MySQL database

   Frihost Forum Index -> Scripting -> Php and MySQL
 


Saysior
Hello, I have a problem with a code that is meant to allow users to rate content that is loaded from a MySQL database. Since it isn't commented, I should explain most of it here. The page will load content based on the value of the dis that is passed to it via the url. It will then show a the content, and at the bottom I have a form, which is meant to allow readers to rate it, and also fill out a comments sheet that will then be mailed to my address. I've only included one of the php snippets on the page (the one after the form that deals with what the form passes it from $PHP_SELF.

Code:

<?php
$name = $_POST['name'];
$rating = $_POST['rating'];
$readercomment = $_POST['readercomment'];
if ($rating) {
mysql_connect("localhost", "******", "*******") or die(mysql_error()); mysql_select_db("*******") or die(mysql_error());
$data = mysql_query("SELECT * FROM documents WHERE docname='$docname'") or die(mysql_error());
$info = mysql_fetch_array( $data );
$avgrate = $info['avgrate'];
$ratenum = $info['ratenum'];
$docname = $info['docname'];
$newnum = 1 + $ratenum;
$newavg = ($avgrate * $ratenum + $rating)/$newnum;
$title = $info['title'];
$comments = $info['comments'];
$date = $info['date'];
$summary = $info['summary'];
$type = $info['type'];
$content = $info['content'];
mysql_query("DELETE FROM documents WHERE docname='$docname' AND title='$title'") or die(mysql_error());
mysql_query("INSERT INTO documents VALUES ( '$title', '$type', '$date', '$content', '$docname', '$summary', '$comments', '$newavg', '$newnum')") or die(mysql_error());
echo "<br>Thank you, " . $_POST['name'] . ", your rating has been submitted.<br>";
}
if ($readercomment) {
mail('saysior@yahoo.com',$info['title'],$readercomment, "From: " . $name . " And Rated it: " . $rating);
echo "<br>Thank you, " . $name . ", your comment has been submitted.";
}
?>


My problem is that it succeeds in deleting the entry, but it fails to write it back into the database. I am doing it this way because I wasn't able to get the MySql change command to work. I would really appreciate any help on this, because I am absolutely stumped on why it wont function right.
GSIS
Just at first glance I'd assume MySQL is locking the record when it's deleted, then - because it's locked - it can't be rewritten. When the transaction ends the delete is finally committed but because the write failed that can't be committed to the database.

Persevere a bit more with the update keyword, or try setting an autonumbered primary key so that the changed record is, in effect, a new record.

Note - I'm not a MySQL expert but did spend a lot of time working with Oracle databases a few years ago.
Saysior
Okay, I didn't know it would work that way. I'll take another run at the update command. Who knows, maybe it was just some small mistake that I won't make again if I start again from scratch.

Thanks.
alem
as far as i understand from your code, this may work:
Code:
<?php
$name = $_POST['name'];
$name = mysql_real_escape_string($name);
$rating = $_POST['rating'];
$rating = mysql_real_escape_string($rating);
$readercomment = $_POST['readercomment'];
$readercomment = mysql_real_escape_string($readercomment);
if ($rating) {
mysql_connect("localhost", "******", "*******") or die(mysql_error()); mysql_select_db("*******") or die(mysql_error());
$data = mysql_query("SELECT * FROM documents WHERE docname='$docname'") or die(mysql_error());
$info = mysql_fetch_array( $data );
$avgrate = $info['avgrate'];
$ratenum = $info['ratenum'];
$docname = $info['docname'];
$newnum = 1 + $ratenum;
$newavg = ($avgrate * $ratenum + $rating)/$newnum;
$title = $info['title'];
$comments = $info['comments'];
mysql_query("UPDATE documents SET comments='$comments' , avgrate='$newavg', ratenum='$newnum' WHERE  docname='$docname' AND title='$title' ") or die(mysql_error());
echo "<br>Thank you, " . $_POST['name'] . ", your rating has been submitted.<br>";
}
if ($readercomment) {
mail('saysior@yahoo.com',$info['title'],$readercomment, "From: " . $name . " And Rated it: " . $rating);
echo "<br>Thank you, " . $name . ", your comment has been submitted.";
}
?>


also i recommend you to use mysql_real_escape_string function in order to prevent sql injections. if you didn't hear about it, you had better visit this site.
Reply to topic    Frihost Forum Index -> Scripting -> Php and MySQL

FRIHOST HOME | FAQ | TOS | ABOUT US | CONTACT US | SITE MAP
© 2005-2007 Frihost, forums powered by phpBB.