CODEWITHSUNDEEP
phpmysqlunique-constraint
inspectorG4dget
inspectorG4dget

Reputation: 113915

Value in Column

I am developing a small hobby application. Though I've worked with MySQL and PostgreSQL before, I'm more of a n00b here and would appreciate any help.

I have a table in my MySQL database called "TECH". This table has two columns: "ID" (primary key) and "name" (name of the tech - not a key of any sort). Here are a couple of example rows:

+----+--------+
| ID |  name  |
+----+--------+
| 1  |  Python|
| 2  |  ASP   |
| 3  |  java  |
+----+--------+

Here is the code that creates TECH:

CREATE TABLE TECH (
        id      INT(5) ,
        name    VARCHAR(20),
        PRIMARY KEY (id)
);

I have developed an html form for the user to input a new technology into TECH. However, I would like to ensure that duplicate entries do not exist in TECH. For example, the user should not be allowed to enter "Python" to be assigned ID 4. Further, the user should also not be allowed to enter "pYthon" (or any variant of capitalization) at another ID.

Currently, I have the following code that does this (on the PHP side, not the MySQL side):

// I discovered that MySQL is not case sensitive with TECH.name
$rows = 0;
$result = $mysql_query("SELECT * FROM tech AS T WHERE T.name='python'");
while ($row = mysql_fetch_array($result)) {
    $rows += 1;
}

if ($rows != 0) {
    echo "'python' cannot be inserted as it already exists";
} else {
    // insertion code
}

Now, I know that the correct way to do this would be to constrain TECH.name to be UNIQUE by doing UNIQUE (name) and catching an "insert error" on the PHP side. However, I have the following two questions regarding this process:

  1. Does defining the UNIQUE constraint maintain the apparent case-insensitivity addressed above?
  2. How do I go about catching exactly such an insert error on the PHP side?

I'd appreciate any help with this or any better ideas that anyone has.

Upvotes: 2

Views: 93

Answers (3)

Stu
Stu

Reputation: 4150

Changing the collation of the field to _ci or _cs would determine whether a unique key was caseinsensitive or casesensitive.

As for catching the error, you should try using mysqli or PDO to run db queries: http://www.php.net/manual/en/pdo.exec.php

You can catch a duplicate error entry with PDO like so:

try
{
    $dbh->exec($mySqlQuery);
    // insert was successful...
} catch (PDOException $e) {
    if ($e->errorInfo[1]==1062) {
        // a 'duplicate' error occurred...
    } else {
        // a non 'duplicate error' occurred...
    }
}

Edit:

If you're not using PDO, this should work after your mysql_query:

if (mysql_errno() == 1062)
{
   // you have a duplicate error...
}

Upvotes: 0

Miroslav
Miroslav

Reputation: 1960

TRY

INSERT IGNORE INTO mytable
    (primaryKey, field1, field2)
VALUES
    ('abc', 1, 2),
    ('def', 3, 4),
    ('ghi', 5, 6);

duplicated rows would be ignored

Upvotes: 1

ddoor
ddoor

Reputation: 5963

When you manipulate mysql form php (i.e. by doing an INSERT or UPDATE), you can call mysql_get_rows_affected which will return the rows affected. If the query has failed due to the UNIQUE constraint then the affected rows will be 0

http://php.net/manual/en/function.mysql-affected-rows.php

I usually check the number of rows returned from that function, The same check can be applyed if you take the INSERT OR IGNORE approach

Upvotes: 2

Related Questions