SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Wizard gold trophysilver trophy
    Join Date
    Nov 2000
    Location
    Switzerland
    Posts
    2,479
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Having been through the excellent PHP / MySQL tutorial at webmasterbase.com, I'm now dangerous.

    Have a table "Categories" in my MySQL database, which has two columns; ID (INT NOT NULL AUTO_INCREMENT PRIMARY KEY), Category (TEXT).

    From a PHP form, want to prevent duplicate entries in the Category column (i.e. the same text string) being inserted into the table.

    From the PHP form, the MySQL command I'm using is;

    "INSERT INTO Categories SET Category=$cat"

    where $cat is a PHP variable.

    Is there a way to enhance this command to get it to ignore the INSERT if there is already an entry with the same string as $cat?

    Have tried;

    "INSERT IGNORE INTO Categories SET Category=$cat"

    but this doesnt work (my guess because it checks for a duplicate ID value as well ? ).

    Many thanks.

  2. #2
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    you'll probably need to run two queries the first being:
    $sql = "SELECT * from Categories WHERE Category = '$cat'";
    $result = mysql_query($sql);
    if(mysql_num_rows($result) > 0) {
    print "Sorry that category already exists";
    }
    else {
    $sql2 = "INSERT INTO Categories SET Category='$cat'";
    $result2 = mysql_query($sql2);
    }
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  3. #3
    SitePoint Wizard silver trophy Karl's Avatar
    Join Date
    Jul 1999
    Location
    Derbyshire, UK
    Posts
    4,411
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Just make sure the Category field is marked as unique, you can determine if it is or not by looking at the mysql error message.

    To make the key unique: ALTER TABLE Categories ADD UNIQUE(Category), then do a query like this:

    $res = @mysql_query( "INSERT INTO Categories SET Category='$cat'");

    if( $res ){

    } else {
    // An error occured

    if( mysql_errno() == 'code used for not unique' )

    echo "please enter a unique value";

    }

    }


    I'm not sure off the top of my head what the error number is for a duplicate entry but you can easily find out by trying it and echoing the result of the mysql_errno()
    Karl Austin :: Profile :: KDA Web Services Ltd.
    Business Web Hosting :: Managed Dedicated Hosting
    Call 0800 542 9764 today and ask how we can help your business grow.

  4. #4
    SitePoint Wizard gold trophysilver trophy
    Join Date
    Nov 2000
    Location
    Switzerland
    Posts
    2,479
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Freddy, Karl,

    Many many thanks - gobsmacked by how fast you've answered. Have used Karls suggestion and has worked. Like Freddies thinking - giving me ideas to become truly dangerous.

    Thanks.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •