I can't Seem To Prevent Duplicate Name inputs

Question

Okay, so I've been going at it for a while now. What I'm trying to achieve is to prevent people from being able to create the same name that is entered in the name='name' field. Here's the html code.

<div class="fieldclass"><form action='/newlist.php' method='POST'  id="formID">
Name Your Card <input class='ha' type='text' name='name'><p>
<input type='submit' value='create'/>
        </form>

and this is my mysql page.

<?php
$servername = "localhost";
$username = "root";
$password = "root";
$dbname = "christmas";

// Create connection
$dbhandle = mysqli_connect ($servername, $username, $password, $dbname) or die ("could not connect to database");


$selected = mysql_connect('christmas', $dbhandle);



$query = mysql_query("SELECT * FROM list WHERE name='$name'");
if(mysql_num_rows($query) > 0){
    echo 'that name already exists';
}else{
    mysql_query("INSERT INTO list(name, one , two, three, four, five, six, seven, eight) VALUES ('$name' , '$one' , '$two' , '$three' , '$four' , '$five' , '$six', '$seven' , '$eight')");

}


mysql_close();
?>

what the heck am I doing wrong?

read this http://stackoverflow.com/questions/17498216/can-i-mix-mysql-apis-in-php — Funk Forty Niner, Nov 05 '15 at 04:20
plus, I even gave you a link in your other question, which is a tried/tested method. why didn't you use it? — Funk Forty Niner, Nov 05 '15 at 04:21
you're also using a MySQL keyword `list` as a function, and that alone will cause you problems. we also don't know where `$name` is coming from. — Funk Forty Niner, Nov 05 '15 at 04:22
ok let me explain, cause you are mixing mysql and mysqli, that's mean mysql num rows is always zero, hence it always going to run the else statements, hope i am clear this time. — arif_suhail_123, Nov 05 '15 at 04:23

score 8 · Accepted Answer · edited May 23 '17 at 12:23

There is quite a few things wrong here.

You're using a MySQL keyword list as a function, and that alone will cause you problems.

Sidenote: Yes, I know it's not a "reserved" word, it's a "keyword" and MySQL treats keywords specially if used as a function, which is what you're doing now in your second query, and is the way that MySQL is interpreting it as; a function rather than a table declaration name.

https://dev.mysql.com/doc/refman/5.5/en/keywords.html

I suggest you rename that table to lists, or wrap it in ticks.

You're also mixing MySQL APIs/functions that do not intermix.

Ref: Can I mix MySQL APIs in PHP?

So your new code would read as,
while dropping $selected = mysql_connect('christmas', $dbhandle);

$query = mysqli_query($dbhandle, "SELECT * FROM `list` WHERE name='$name'") 
        or die (mysqli_error($dbhandle));

if(mysqli_num_rows($query) > 0){
    echo 'that name already exists';
}else{
    mysqli_query($dbhandle, "INSERT INTO `list` (name, one, two, three, four, five, six, seven, eight) 
                VALUES ('$name' , '$one' , '$two' , '$three' , '$four' , '$five' , '$six', '$seven' , '$eight')") 
                 or die (mysqli_error($dbhandle));

}


mysqli_close($dbhandle);

Your code is also open to an SQL injection. Use a prepared statement.

https://en.wikipedia.org/wiki/Prepared_statement

Now, we have no idea as to where all your variables are coming from.

Use error reporting.

http://php.net/manual/en/function.error-reporting.php

I also suggest you use a conditional empty() for your variables/inputs.

Otherwise, you may also get other errors that MySQL may complain about.

I.e.:

if(!empty($var)){...}

Ref: http://php.net/manual/en/function.empty.php

Ultimately and to make sure there are no duplicate entries in your database, you can set a UNIQUE constraint.

Ref: http://dev.mysql.com/doc/refman/5.7/en/constraint-primary-key.html

oh yeah. layin' it out there as a checklist. Check 'em off OP — Drew, Nov 05 '15 at 04:33
@Sean I edited it, yet if it isn't used as the 2nd query (as a function), it's not needed, but I added it anyway. ;-) — Funk Forty Niner, Nov 05 '15 at 04:34

Suyog · Answer 2 · 2015-11-05T04:33:58.147

1

You are mixing mysql and mysqli.

Try following code.

<?php
$servername = "localhost";
$username = "root";
$password = "root";
$dbname = "christmas";

// Create connection
$dbhandle = mysqli_connect ($servername, $username, $password, $dbname) or die ("could not connect to database");

$query = mysqli_query($dbhandle, "SELECT * FROM list WHERE LOWER(name) = LOWER('$name') ");
if(mysqli_num_rows($query) > 0){
    echo 'that name already exists';
}else{
    mysqli_query($dbhandle, "INSERT INTO list(name, one , two, three, four, five, six, seven, eight) VALUES ('$name' , '$one' , '$two' , '$three' , '$four' , '$five' , '$six', '$seven' , '$eight')");

}

mysqli_close($dbhandle);
?>

edited Nov 05 '15 at 04:33

answered Nov 05 '15 at 04:25

Suyog

2,472
1
14
27

whats wrong with original query `"SELECT * FROM list WHERE name='$name'"` – arif_suhail_123 Nov 05 '15 at 04:27
@arif_suhail_123 there is nothing wrong in `"SELECT * FROM list WHERE name='$name'"` but using `LIKE` and `LOWER` will give more precise result. – Suyog Nov 05 '15 at 04:28
1

It can be considered as a suggestion rather than a fix.. :-) – Suyog Nov 05 '15 at 04:29
Hey, thank you so much for the help. I tried the code but it said that every name I entered already exists. – kenny Nov 05 '15 at 04:31
3

Using `LIKE` will not be beneficial in this situation. If **John** already exists in the DB, but **Johnny** does not... using `LIKE` will tell me that *Johnny* already exists; when, in fact, it does not. The OP is looking for an **exact** match! – Kuya Nov 05 '15 at 04:33

I can't Seem To Prevent Duplicate Name inputs

2 Answers2

Linked