Value in Column

Question

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:

Does defining the UNIQUE constraint maintain the apparent case-insensitivity addressed above?
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.

1. http://stackoverflow.com/questions/463764/are-unique-indices-case-sensitive-in-mysql — Waleed Khan, Aug 20 '12 at 14:26
Please, don't use `mysql_*` functions to write new code. They are no longer maintained and the community has begun [deprecation process](http://goo.gl/KJveJ). See the *[red box](http://goo.gl/GPmFd)*? Instead you should learn about [prepared statements](http://goo.gl/vn8zQ) and use either [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli). If you can't decide which, [this article](http://goo.gl/3gqF9) will help you. If you pick PDO, [here is good tutorial](http://goo.gl/vFWnC). — Madara's Ghost, Aug 20 '12 at 14:26
Aha! Thank you @arxanas. I had searched for Q2. I had thought of Q1 while in the process of writing this question and hadn't looked it up (though I should have) — inspectorG4dget, Aug 20 '12 at 14:27
@Truth: I wanted to use prepared statements, but couldn't get them to work immediately (PDO was one of the first things I saw, but couldn't find a good tutorial). Thank you for the tutorial - I will be sure to migrate to PDO soon. — inspectorG4dget, Aug 20 '12 at 14:29

score 2 · Answer 1 · answered Aug 20 '12 at 14:27

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

score 1 · Answer 2 · answered Aug 20 '12 at 14:28

1

TRY

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

duplicated rows would be ignored

answered Aug 20 '12 at 14:28

Miroslav

1,960
1
13
26

Stu · Accepted Answer · 2012-08-20T14:45:08.313

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...
}

I am aware that the correct way to do insertions is to use PDO and prepared statements. But this is not what I have for my first version. So how would I go about doing this without PDO? — inspectorG4dget, Aug 20 '12 at 14:39

Value in Column

3 Answers3