Can someone tell what's wrong in this code

Hello

I have Sql Tabel.
CREATE TABLE comment (
xid int(11) NOT NULL auto_increment,
name varchar(100) NOT NULL default '',
email varchar(100) NOT NULL default '',
font_color varchar(100) NOT NULL default '',
comment mediumtext NOT NULL,
submitter int(11) NOT NULL default '0',
ip varchar(100) NOT NULL default '',
date int(10) NOT NULL default '0',
PRIMARY KEY (xid)
) TYPE=MyISAM;

This for post Comments

Now i use this to try get the total of Comments

$sql =mysql_query("SELECT * FROM comment")
or die("can't select the comment");
$total_comment=mysql_affected_rows($sql);

But i get this error

Warning: mysql_affected_rows(): supplied resource is not a valid MySQL-Link resource in file
i tried to use another function but same error
$total_ehdaa=mysql_num_rows($query);

For the Records . wht i'm trying to do is , delete a Number of Comment in Certin limits

Full code
$limit= "1";


$sql =mysql_query("SELECT * FROM comment")
or die("can't select the comment");
$total_ehdaa=mysql_affected_rows($sql);


$diff= $total_ehdaa - $limit ;

if ($diff<=0) { echo"No old Comment";}else{
$query2= mysql_query("delete from comment LIMIT $diff") or die ("cann't delete");


if (!$query2){
echo"<center>There SOme errors </center><META HTTP-EQUIV=\"Refresh\" Content=3;URL=\"index.php\">";
}else{
echo"<center>Delted!!<br>Will Take you Back to where u come from</center><META HTTP-EQUIV=\"Refresh\" Content=5;URL=\"index.php\">";
}
}

$sql =mysql_query("SELECT * FROM comment")
or die("can't select the comment");
$total_comment = mysql_fetch_array($sql);

Thx Phil

The Error Gon But Now is Fatal Error
Fatal error: Unsupported operand types in delete.php on line 21

and wht is in line 21 is :
$diff= $total_ehdaa - $limit ;

wht i try to do is get the total # of comments then Minus it from whatever $limit set to equal ..

echo out both of these variables and see what you get.

EDIT: wait there

New Update .. it seem it was Right the first i did it but the error was with the Connection to Mysql . i fixed the Connection and now it works . with the same way i posted it first .


the problem Now is that Code suppose to delete the old Comment but it deletes the new Ones .. any idea
Thx Phil

if you have `id` set in your table you could try this:


$limit= "1";


$sql = mysql_query("SELECT * FROM comment order by id DESC") or die("can't select the comment");
$total_ehdaa = mysql_affected_rows($sql);

$diff= $total_ehdaa - $limit ;

if ($diff<=0)
{
echo"No old Comment";
}
else
{
while($array = mysql_fetch_array($sql))
{
$commentID = $array["id"];
$query2= mysql_query("DELETE FROM `comment` WHERE id='$commentID'") or die ("cann't delete");
if (!$query2)
{
echo"<center>There SOme errors </center><META HTTP-EQUIV=\"Refresh\" Content=3;URL=\"index.php\">";
}
else
{
echo"<center>Delted!!<br>Will Take you Back to where u come from</center><META HTTP-EQUIV=\"Refresh\" Content=5;URL=\"index.php\">";
}
break;
}
}

Thx Phil But that didt Work quit well , it deletes One Comment a time .. wht i want is delete all the Old ..and keep only the last New 5 comments..
and had some problem with useing mysql_fetch_array
so i had to use mysql_num_rows
the tabel uses ID i rever to it as xid .. this is my Tabel

CREATE TABLE comment (
xid int(11) NOT NULL auto_increment,
name varchar(100) NOT NULL default '',
comment mediumtext NOT NULL,
submitter int(11) NOT NULL default '0',
ip varchar(100) NOT NULL default '',
date int(10) NOT NULL default '0',
PRIMARY KEY (xid)
) TYPE=MyISAM;

Now the the delete file , and Works But somthing not right , this stupid code i Modfied from the first Code i posted , Deletes Randomly Comments .. Sometime the oldest sometime the Newest .. i do not know wht i'm messing here..

<?php
$con=mysql_connect($host,$dbuser,$dbpass)
or die("CONNECTION FAILED");
mysql_select_db($dbname,$con)
or die("can't conect to DB");

$limit= "5"; // will be left and delete the Oldest

$query =mysql_query("SELECT * FROM comment")
or die("can't select the comment");
$total_ehdaa=mysql_num_rows($query);


$diff= $total_ehdaa - $limit ;

if ($diff<=0) { echo"No Old Comment";}else{
$query2= mysql_query("delete from comment LIMIT $diff") or die ("cann't delete");


if (!$query2){
echo"<center>There is Some error </center><META HTTP-EQUIV=\"Refresh\" Content=3;URL=\"index.php\">";
}else{
echo"<center>Deleted<br>Taking U back </center><META HTTP-EQUIV=\"Refresh\" Content=5;URL=\"index.php\">";
}

}
?>