SitePoint Sponsor

User Tag List

Results 1 to 16 of 16

Hybrid View

  1. #1
    midnight coder
    Join Date
    Dec 2000
    Location
    The flat edge of the world
    Posts
    838
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Using PHP/MySQL, got a form that submits things to MySQL, how can I check to make sure that every entry is unique in that column? like, give an error when the user submits something that's already there in the MySQl db.

  2. #2
    SitePoint Enthusiast
    Join Date
    Jul 2000
    Posts
    69
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You can always make a field unique when you first create the table.

  3. #3
    midnight coder
    Join Date
    Dec 2000
    Location
    The flat edge of the world
    Posts
    838
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I went to PHPMyAdmin, and clicked on Unique on that field, and got this error:

    Duplicate entry '' for key 2

    Does it have anything to got to do with that I have a Primary Key already?

  4. #4
    SitePoint Addict
    Join Date
    Dec 2000
    Location
    BOSTON MA
    Posts
    335
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    how's about this. i use somthing similar to the following to start a user reg script where it asks for a username,password,pass confirmation, and email address.

    let me know if it helps

    ::::::::::::::::::::::::::::::::::::::::
    <?php

    $DBhost = "localhost";
    $DBuser = "YOUR-USERNAME";
    $DBpass = "YOUR-PASS";
    $DBName = "YOUR-DB-NAME";
    $table = "YOUR-DB-TABLENAME";

    $db = mysql_connect($DBhost,$DBuser,$DBpass) or die("Unable to connect to database");

    //select the DB

    @mysql_select_db("$DBName") or die("Unable to select database $DBName");

    //check to see if the 2 entered passwords match

    if ($upass == $upass2) {
    $sqlquery = mysql_query("SELECT * FROM $table WHERE (uname='$uname')");

    if (mysql_num_rows($sqlquery) > 0) {

    printf("We are sorry to inform you that the User Name <B><?php echo $Name ?></B> Is already Taken.");

    }

    else {

    //insert the values into the Db

    $sqlquery = mysql_query("INSERT INTO $table VALUES('$uname','$upass','$Email')");

    $sqlquery = mysql_query("SELECT * FROM $table WHERE (uname='$uname')");


    printf("You have registered.<BR>

    User Name: $uname

    <BR>

    Password: $upass");
    }
    }
    else {
    printf("Your Two Passwords Did Not Match");
    }
    ?>
    . . . chris

  5. #5
    AdSpeed.com Son Nguyen's Avatar
    Join Date
    Aug 2000
    Location
    Silicon Valley
    Posts
    2,241
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by Robo
    I went to PHPMyAdmin, and clicked on Unique on that field, and got this error:

    Duplicate entry '' for key 2

    Does it have anything to got to do with that I have a Primary Key already?
    I'm not so sure, but tried another field (that could make unique!) or remove the primary key (normally for auto_increment field) and put unique key for that field.
    - Son Nguyen
    AdSpeed.com - Ad Serving and Ad Management Made Easy

  6. #6
    midnight coder
    Join Date
    Dec 2000
    Location
    The flat edge of the world
    Posts
    838
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks atomicmunky, that script should work for me.

  7. #7
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    A couple of serious concerns with atomicmunky's code
    1) printf() being used incorrectly
    printf("We are sorry to inform you that the User Name <B><?php echo $Name ?></B> Is already Taken.");

    This should never be done you can't embed <? ?> tags inside your print statement and it will surely cause a parse error furthermore printf() takes an argument and this should have been written like
    printf("We are sorry to inform you that the User Name <B>%s</B> Is already Taken.", $Name);

    and again this would be the proper syntax

    printf("You have registered.<BR> User Name: %s<BR> Password: %s", $uname, $upass);

    2) in the else statement he inserts the data and then runs a query to get it out again what a waste of resources since if he inserted the data then the data is already available to the script and doesn't need to be retireved again.


    3) the last printf() statement should be a regular print "" because no vars are being used, this can really cut down on processing time.
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  8. #8
    midnight coder
    Join Date
    Dec 2000
    Location
    The flat edge of the world
    Posts
    838
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I only needed the bit about if (mysql_num_rows($sqlquery) > 0) anyway...but yeah, I could see how those things could cause problems.

    What is with those print, printf, and echos anyway? I normally uses echos, and seems easy enough to use as well, somehow it's easier to type the word echo, then print... for the above, I would have gone:


    echo "You have registered.<BR />User Name: " . $uname . "<BR />Password: " . $upass;


    Would that be acceptable?


  9. #9
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You are correct there isn't much difference the only thing I like printf() for is you can print a whole string with html and not worry about escaping double quotes or concatenating you vars into the statement take this for example

    echo "<tr><td width=\"100\">". $somevar ."</td></tr>";

    could be written

    printf('<tr><td width="100">%s</td></tr>', $somevar);

    The key is the %s that means substitue the arg given at the end with the %s in this case $somevar some other useful reasons to use it is to format numbers let's say you have a number like 34 and you want to display it as 000034 you could use printf('%06d', $num);

    <Edited by freddydoesphp on 12-20-2000 at 11:09 PM>
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  10. #10
    SitePoint Addict
    Join Date
    Dec 2000
    Location
    BOSTON MA
    Posts
    335
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok you turkeys, i never claimed to be a php expert.
    i'm over here trying to help and for some reason you can't help but practically tell me that my code is evil and should be exorsized.
    i appreciate the creative critisism though. i guess it gives me a higher "standard" to live up to

    i'll give your "suggestion" a try freddy, but if it craps out on me, i'll know who to blame
    . . . chris

  11. #11
    midnight coder
    Join Date
    Dec 2000
    Location
    The flat edge of the world
    Posts
    838
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Alright, sorry, I shouldn't have been such a jerk, lets ki...I mean shake hands and make up.

    So will you forgive me now?

    I'll take that as a yes.

    OK, now help me with my script! :P

    Here's the script:

    Code:
    // check to see if Anchor Name is unique
    $sql = mysql_query("SELECT * FROM items WHERE anchor_name='$anchor_name'"); 
    if (mysql_num_rows($sql) > 0)
    {
    echo "<p>Hey, <b>" . $anchor_name . "</b>had already been use, be more creative</p>";
    }
    
    // more error checkings
    if ()
    {
    blah;
    }
    
    // even more error checkings
    if ()
    {
    blah;
    }
    
    else
    {
    //process form
    $sql = "INSERT INTO table....";
    // run SQL against the DB to confirm
    $result = mysql_query($sql);
    echo "<p>Record updated/edited!</p>";
    Here's the problem:
    When I submit an anchor name there's already in the db, it'll echo the msg about being more creative, but also the one about being Record updated/edited! And a new record is added, even though it should stop.

    And I get a phrase error if I take the single quotes off the $anchor_name at the SELECT line, but all my other scripts never have quotes like that.
    <Edited by Robo on 12-21-2000 at 01:41 AM>

  12. #12
    midnight coder
    Join Date
    Dec 2000
    Location
    The flat edge of the world
    Posts
    838
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Think I figued out part of the problem, is it because I have to use if, elseif, elseif, else, not if, if, if, else?

    But I still dunno why I need the single quotes around $anchor_name.

  13. #13
    SitePoint Addict
    Join Date
    Dec 2000
    Location
    BOSTON MA
    Posts
    335
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i was just going to suggest the elseif.
    try that, and also try putting parenthesis around :: anchor_name='$anchor_name' :: and see if that works like so.

    ::::::::::::::::::::::
    $sql = mysql_query("SELECT * FROM items WHERE (anchor_name='$anchor_name')");
    ::::::::::::::::::::::

    ....another question is : what are your other 2 if statments checking. maybe those are the culprits??

    i'll keep thinking.

    ps-i forgive you
    . . . chris

  14. #14
    midnight coder
    Join Date
    Dec 2000
    Location
    The flat edge of the world
    Posts
    838
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The code works fine with just


    $sql = mysql_query("SELECT * FROM items WHERE anchor_name='$anchor_name'")


    But I normally go:


    $sql = mysql_query("SELECT * FROM items WHERE anchor_name=$anchor_name")


    Dunno why it needs the quotes this time...

  15. #15
    SitePoint Author Kevin Yank's Avatar
    Join Date
    Apr 2000
    Location
    Melbourne, Australia
    Posts
    2,571
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    The original error message that you obtained when setting the column unique was, I suspect, due to the fact that you already have multiple rows with an empty string value ("") in that column. As a result, MySQL complained because you were trying to set a column unique that was not, in fact unique to begin with.
    Kevin Yank
    CTO, sitepoint.com
    I wrote: Simply JavaScript | BYO PHP/MySQL | Tech Times | Editize
    Baby’s got back—a hard back, that is: The Ultimate CSS Reference

  16. #16
    SitePoint Author Kevin Yank's Avatar
    Join Date
    Apr 2000
    Location
    Melbourne, Australia
    Posts
    2,571
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Robo,

    You should always put quotes around any string value. In your example, if the string contained a space, a comma, or any number of special characters, your SQL statement would become invalid. The quotes ensure that MySQL can tell where the string begins and ends.
    Kevin Yank
    CTO, sitepoint.com
    I wrote: Simply JavaScript | BYO PHP/MySQL | Tech Times | Editize
    Baby’s got back—a hard back, that is: The Ultimate CSS Reference


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
  •