Page 1 of 3

Update Row If IP = IP in database

Posted: Sun Oct 22, 2006 2:33 pm
by nickman013
Hello,

I have a comment system that displays comments. It displays a butto, the button is to delete the comment, it is only shown if you wrote that comment.

I want to update a field in my database for the comment the person picked to delete.
The field that is updated is called show. If it equals 1 then the comment will show up, if it equals 0 then the comment will not show up.

I want to make sure that the comment being deleted is by the person who wrote it.

The script I thought would work is below:

Code: Select all

<?
$username2= "muot_report";  
$password2= "password";  
$database2= "muot_report";  
$connection2 = mysql_connect('localhost',$username2,$password2);  
mysql_select_db($database2); 
$id = $_POST['id'];
$ip = $_POST['ip'];
$sql4 = "SELECT * FROM `comments` WHERE ip =".$ip; 
$result4 = mysql_query($sql4) or die(mysql_error());
while($row4 = mysql_fetch_array($result4)) {
if($ip = $row4['ip']){
mysql_query('UPDATE comments set `show` = 0 WHERE `id` ='.$id);
}
}
mysql_close($connection2); 
?>
It gives me,

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 '' at line 1


Basically.

I have a comment system that displays a button to people who wrote the comment, under their comment. when they click the comment, I want it to update a field on thier comment row in my database. I want it to update if it is really them, I wanna check if its them by their email address.

Thank You.

Posted: Sun Oct 22, 2006 2:45 pm
by reecec
where are these values coming from

Code: Select all

$_POST['ip'];

Posted: Sun Oct 22, 2006 2:46 pm
by s.dot
Basing things like this on IP addresses will only give you headaches down the line.

Posted: Sun Oct 22, 2006 2:47 pm
by reecec
anyway i think its here

Code: Select all

if($ip = $row4['ip']){
it need equal to which is ==

Code: Select all

if($ip == $row4['ip']){

Posted: Sun Oct 22, 2006 2:52 pm
by nickman013
Thanks for replying.

The values come from a form on a different page, I echoed the values, they work.

It wasnt the == problem. I still get the error.

Hmm.

Posted: Sun Oct 22, 2006 2:54 pm
by s.dot

Code: Select all

mysql_query('UPDATE comments set `show` = 0 WHERE `id` ='.$id);
to

Code: Select all

mysql_query('UPDATE comments set `show` = 0 WHERE `id` ='.$id) or die(mysql_error());

Posted: Sun Oct 22, 2006 2:55 pm
by nickman013
Same error.

Posted: Sun Oct 22, 2006 2:58 pm
by s.dot
actually, try this

Code: Select all

$sql4 = "SELECT * FROM `comments` WHERE ip = '$ip'";

Posted: Sun Oct 22, 2006 3:01 pm
by nickman013
Thanks for helping... But I still get the same error? I dont understand. Your right it is headaches.

Posted: Sun Oct 22, 2006 3:02 pm
by Flamie
hah scottay you beat me to it :D
comments is a keyword in mysql afaik, you need the ` ` for it indeed =]
edit:
Ok saw another error in there:

Code: Select all

"UPDATE `comments` SET show=0 WHERE `id`='$id'"
this should be your query

Posted: Sun Oct 22, 2006 3:05 pm
by s.dot
Sorry man, i have a bit of a hangover today... i can't even think straight. :cry: I'm still trying to help though. I rewrote your code the way I would write it, we'll see if it helps any.

Code: Select all

<? 
$username2= "muot_report";  
$password2= "password";  
$database2= "muot_report";  
$connection2 = mysql_connect('localhost',$username2,$password2);  
mysql_select_db($database2); 

$id = $_POST['id']; 
$ip = $_POST['ip']; 

$result4 = mysql_query("SELECT * FROM `comments` WHERE `ip` = '$ip'") or die(mysql_error());
while($row4 = mysql_fetch_array($result4))
{
	if($ip == $row4['ip'])
	{
		mysql_query("UPDATE `comments` set `show` = 0 WHERE `id` = $id") or die(mysql_error());
	}
}

mysql_close($connection2); 
?>

Posted: Sun Oct 22, 2006 3:06 pm
by Flamie
anbd your 1st query:
"SELECT * FROM `comments` WHERE ip ='".$ip."'"

edit: that last part is:
' " . $ip . " ' "

Posted: Sun Oct 22, 2006 3:08 pm
by nickman013
Thanks again for helping,,,, but I still get the error.

Code Now

Code: Select all

<? 
$username= "muot_report";  
$password= "password";  
$database= "muot_report";  
$connection = mysql_connect('localhost',$username,$password);  
mysql_select_db($database); 

$id = $_POST['id']; 
$ip = $_POST['ip']; 

$result = mysql_query("SELECT * FROM `comments` WHERE `ip` = '$ip'") or die(mysql_error()); 
while($row = mysql_fetch_array($result)) 
{ 
        if($ip == $row['ip']) 
        { 
                mysql_query("UPDATE `comments` set `show` = 0 WHERE `id` = $id") or die(mysql_error()); 
        } 
} 

mysql_close($connection); 
?>

Posted: Sun Oct 22, 2006 3:09 pm
by s.dot
Is $id an integer? If not, you'll need single quotes around it in your query. '$id'

Posted: Sun Oct 22, 2006 3:10 pm
by nickman013
Yes ID is a number.