Test for duplicate record

[LoneTraveler]

Why won't this code work? Well, actually, everything works, except I get no error message when I submit a duplicate URL. I tried moving the check record snippit around, commenting out the exit, but whatever I try still does not work. Where should I put it if I've got it in the wrong place? I have been fussing with this for almost a week, and it has me crazy! I also want to add in something to check the "site name" field for nulls, if you happen to know that snippit off the top of your brain... Thanks in advance!

 <?php

if ($REQUEST_METHOD=="POST")

  {

# Test for duplicate record 
$url="SELECT count(*) FROM links where siteurl = '$siteurl'";
    $result = mysql_db_query($db,"$url",$cid);
if   (!$url>0) 

  {          

       echo ("<P><B>$siteurl is already in the database, <a href=\"http://www.atimealone.com/php/AddLinkTest.php\">try another</a>!</B></P>\n");

   # exit;
   } 

  # setup SQL statement 
    $SQL = " INSERT INTO links "; 
    $SQL = $SQL . " (category, sitename, siteurl, description) VALUES "; 
    $SQL = $SQL . " ('$category', '$sitename','$siteurl','$description') ";

   #execute SQL statement 
    $result = mysql_db_query($db,"$SQL",$cid); 

      # check for error 
    if (!$result) { echo("ERROR: " . mysql_error() . "\n$SQL\n");    } 

    echo ("<P><B>New Link Added</B></P>\n"); 

}  

?>  

[bretticus]

Because COUNT(*) will return 0 and you will get a recordset returned. Try just SELECT siteurl ...

[LoneTraveler]

Didn't seem to help, I took out the count(*) but I still don't get the error message I expect - I made the URL field a unique record so it does give an error, and doesn't add it to the DB - but that isn't what I'm going for, I want to tell the person in english, (not error codes that will confuse them) that the address is already in there... thanks for the help, though!

[bretticus]

Oh, whoops, noticed something else that's pretty critical:

$url="SELECT count(*) FROM links where siteurl = '$siteurl'";
        $result = mysql_db_query($db,"$url",$cid);
         if   (!$url>0)...

Will always be true because you are testing if there's anything in the string...I suppose, what's (!$url>0)? Couldn't it be (!$url) ???

Anyway, that won't work ;-) Try if(mysql_fetch_row($result )) ...

[bretticus]

Whoops, try the opposite of my test

[LoneTraveler]

OK, tried this:

$url="SELECT FROM links where siteurl = '$siteurl'";
        $result = mysql_db_query($db,"$url",$cid);
    if(mysql_fetch_row($result )) 

got this:

Warning: Supplied argument is not a valid MySQL result resource in /home/httpd/vhosts/atimealone.com/httpdocs/php/AddLinkTest.php on line 30

Line 30 is the new line

if(mysql_fetch_row($result ))

[bretticus]

You need to SELECT something in your query:

$url="SELECT siteurl FROM links where siteurl = '$siteurl'";
        $result = mysql_db_query($db,"$url",$cid);
    if(mysql_fetch_row($result))

and probably remove my typo space in mysql_fetch_row($result ))

[bretticus]

oh and it's ...

$url="SELECT siteurl FROM links where siteurl = '$siteurl'";
        $result = mysql_db_query($db,"$url",$cid);
    if(!mysql_fetch_row($result))

BTW I don't think if(!$result) will work because I think you get a result resource with $result, and thus a true, even if there are no records (of course I may be wrong, but forget this last paragraph to avoid me confusing you further ;-)

[LoneTraveler]

That didn't work either, so I tried this:

    $url="SELECT FROM links where siteurl = '$siteurl'";
        $Rows = mysql_num_rows($url); 
    if   (!$Rows>0) 

and it sorta works, I have uncommented the exit so now it comes back with an error on every entry! I give up (at least for tonight😉

[bretticus]

Okay, well sure then, but you will NEVER have any success unless you put something between 'SELECT' and 'FROM links'. That's going to throw you a MySQL error EVERY TIME!

I dunno why this didn't work for you:

$url="SELECT siteurl FROM links where siteurl = '$siteurl'";
        $result = mysql_db_query($db,"$url",$cid);
    if(!mysql_fetch_row($result))

Unless "SELECT siteurl FROM..." was "SELECT FROM...".

BUT..............

Here's a tip on mysql_num_rows()

The argument needs to be a valid MySQL result resource, not a string query command.

Try this:

mysql_select_db($db,$cid);
$url="SELECT FROM links where siteurl = '$siteurl'";
$result = mysql__query($url);
if(!mysql_num_rows($result)){
	// do your stuff
}

[bretticus]

Damn typos!!

mysql_select_db($db,$cid);
$url="SELECT FROM links where siteurl = '$siteurl'";
$result = mysql__query($url);
if(!mysql_num_rows($result)){
    // do your stuff
}

[bretticus]

DAMN!!!!

mysql_select_db($db,$cid);
$url="SELECT FROM links where siteurl = '$siteurl'";
$result = mysql_query($url);
if(!mysql_num_rows($result)){
    // do your stuff
}

[bretticus]

I better go to bed to...I forgot to put in the all crucial stuff between SELECT and FROM :rolleyes: after all that!

[LoneTraveler]

OK, this works pretty good, but the output is still sorta sloppy, with error messages printing out regardless of what happens!

edit I just checked this again, and it isn't actually working, it still adds a row even if the site url is already in the database. Back to the drawing board... thanks for your help though!

<?php  
    # this is processed when the form is submitted 
    # back on to this page (POST METHOD) 
    if ($REQUEST_METHOD=="POST")

  {

# Test for duplicate URL
  $url="SELECT siteurl FROM links where siteurl = '$siteurl'";
$result = mysql_query($url);
if(!mysql_num_rows($result)) 

  {          

       echo ("<P><B>$siteurl is already in the database, <a href=\"http://www.atimealone.com/php/AddLinkTest.php\">try another</a>!</B></P>\n");

   #  exit;
   } 

  # setup SQL statement 
    $SQL = " INSERT INTO links "; 
    $SQL = $SQL . " (category, sitename, siteurl, description) VALUES "; 
    $SQL = $SQL . " ('$category', '$sitename','$siteurl','$description') ";

   #execute SQL statement 
    $result = mysql_db_query($db,"$SQL",$cid); 

      # check for error 
    if (!$result) { echo("ERROR: " . mysql_error() . "\n$SQL\n");    } 

    echo ("<P><B>New Link Added</B></P>\n"); 

}  

?>

[LoneTraveler]

I got it working...Thanks so much for your help!

<?php  
    # this is processed when the form is submitted 
    # back on to this page (POST METHOD) 
    if ($REQUEST_METHOD=="POST")

  {


$url="SELECT siteurl FROM links where siteurl = '$siteurl'";
        $result = mysql_db_query($db,"$url",$cid);
    if(!mysql_fetch_row($result)){

    $insert = ("INSERT INTO links (category, sitename, siteurl, description)  VALUES('$category', '$sitename','$siteurl','$description') "); 
    mysql_query($insert) 
    or die(mysql_error()); 
    echo "New Link Added.<br>";  

    # send email
    $to = "[e-mail]";
    $subject = "New Link Added";
    $message = ("$sitename\r\n $siteurl\r\n category: $category\r\n added to the AtimeAlone.com Add A Link Page!\n description: $description\n");
    mail( $to, $subject, $message);

    } else {          
   echo ("<P><B>$siteurl is already in the database, <a href=\"http://www.atimealone.com/php/AddLinkTest.php\">try another</a>!</B></P>\n");    }  

}  

?>