-3

So, for sake of simplicity, let's say I've got a table like this (f1,f2,f3 are fields):

f1   f2   f3
a    b    c
d    e    f

And say I've got a PHP array like this I want to add in to the table:

a    g    h

Now, I want to be able to add this one in, but I don't want to be able to add something like this:

a    e    f

Field f1 can have duplicates. Fields f2 & f3 cannot. I've tried adding a unique index to f2, and used REPLACE INTO, but it's not what I want. I need there to be a way to only insert if the f2 & f3 field entries are not already found on the same row in the database table.

$query = "REPLACE INTO table1 (title, description, start, end, location, affiliation)
          VALUES ('{$my_title_php_array[$count]}',
                  '{$my_description_php_array[$count]}',
                  '{$my_start_php_array[$count]}',
                  '{$my_end_php_array[$count]}',
                  '{$my_location_php_array[$count]}',
                  '{$my_affiliation_php_array[$count]}')”;

description is of data type 'TEXT', which can't be used as an INDEX (too big). Ideally, I'd like to have all be necessarily checked in comparison with the incoming PHP array entries.

Let's say I have these things in my new example's PHP array:

Title1   Desc1   1:00pm   2:00pm   Loc1   Aff1
Title1   Desc1   3:00pm   4:00pm   Loc1   Aff1
Title2   Desc2   1:00pm   2:00pm   Loc2   Aff2
Title2   Desc1   1:00pm   2:00pm   Loc3   Aff3

These should all be considered unique and entered in. I'd like to be able to enter them only when they don't exactly match a row in my database already. Desc1 is long. The descriptions are usually the most unique. Is there a way of shortening?

Einion Reishus
  • 1
  • 6
  • 1
    You might be better off posting your *actual* data and table structure. a, b, c, and f1, f2 etc are meaningless. Also, do you have any PHP code yet? – James Apr 07 '15 at 22:25
  • Do f2 and f3 have to be unique individually or as a pair? That is, in your example table can I insert a e z for f1 f2 f3? – Zippers Apr 07 '15 at 22:26
  • 2
    To extend @Zippers' answer: if f2 and f3 both have to be unique, you'll want to create a composite index on both columns, e.g. `UNIQUE INDEX (f2, f3)` – xathien Apr 07 '15 at 22:27
  • ^ Unique index will work. You could also make the fields the primary key, then any insert where they already exist will fail with "violation of primary key" error. – developerwjk Apr 07 '15 at 22:28
  • @Zippers, yes you can add `a e z`. As a pair (or tuple) is what I seek. – Einion Reishus Apr 07 '15 at 22:29
  • 2
    In that case I would say to create the unique index as @xathien suggested, and use INSERT INTO... (and catch a possible duplicate key error) or INSERT IGNORE... (and check whether you actually inserted, if you care about that). – Zippers Apr 07 '15 at 22:31
  • I think I do need to be more specific with what data I'm using. I'm going to edit now. – Einion Reishus Apr 07 '15 at 22:32
  • I tried `ALTER TABLE table ADD UNIQUE KEY combine ( title, description, start, end, location, affiliation );` but when I run `SHOW COLUMNS FROM table;` I don't see a change... – Einion Reishus Apr 07 '15 at 22:47

4 Answers4

-1

I had to look up this information myself recently and here is the answer I found: Use multiple columns as unique identifier for mysql

I believe it's what you're needing.

Community
  • 1
  • 1
BSimpson
  • 74
  • 4
  • This worked. By using `ALTER TABLE table ADD UNIQUE KEY combine ( title, description, start, end, location, affiliation );` in combination with `REPLACE INTO`. Thank you. – Einion Reishus Apr 07 '15 at 23:09
-2

Globally, you want a constraint on a pair of entry? In MySQL, I think you can't. Just, do a select (something like SELECT COUNT(*) FROM _table WHERE f1 = ? AND f2 = ? replace with your values) check the result.

Thiryn
  • 199
  • 1
  • 2
  • 12
  • Sure, if you want your data to end up as a mess. Always better to handle this type of thing with unique constraints, keys, on the table itself. – developerwjk Apr 07 '15 at 22:29
  • *"Globally, you want a constraint on a pair of entry? In MySQL, I think you can't."* - Wrong. You can: http://stackoverflow.com/questions/635937/how-do-i-specify-unique-constraint-for-multiple-columns-in-mysql – Mark Amery Apr 07 '15 at 23:17
-2

I personally would use PHP and mysql to check the database table for existing rows for "f2" and "f3".

For sake of simplicity, I will use standard mysql and will assume the table name is "alphabet". Example:

$array = array('a', 'e', 'f');
$checkTable = mysql_query("SELECT * FROM `alphabet` WHERE `f2` = '".$array[1]."' OR `f3` = '".$array[2]."'");

if(!mysql_num_rows($checkTable)){
    mysql_query("INSERT INTO `alphabet` VALUES('".$array[0]."', '".$array[1]."', '".$array[2]."')");
}

Something like the above. Lets go through it together.

We first set the array containing the letters "a", "e", and "f".

$array[0] = "a", $array[1] = "e", and $array[2] = "f".

We check in the database for any rows where the field "f2" or "f3" is equal to the second and third parameter of our array.

Using mysql_num_rows() if there is no duplicates it will go ahead and add the array into the database table "alphabet".

Correct me if I am wrong or if anybody has a simpler solution. Hope I helped.

Kyle Aldridge
  • 107
  • 5
  • I see what you're doing though, and what you meant. It's just that I think I need a way to detect if the incoming query is an exact match for a row in my database table. – Einion Reishus Apr 07 '15 at 22:59
-2
<?php
    $data[] = 'f1 value';
    $data[] = 'f2 value';
    $data[] = 'f3 value';

    for ($i = 0; $i < count($data); $i++) {
        for ($j = $i+1; $j < count($data); $j++) {
            $value_a = $data[$i];
            $value_b = $data[$j];

            $query = "SELECT *FROM mytable WHERE ";
            $query.= "(f1=".$value_a." AND f2=".$value_b.") OR";
            $query.= "(f1=".$value_a." AND f3=".$value_b.") OR";
            $query.= "(f2=".$value_a." AND f3=".$value_b.")";

            $result = mysql_query($query);
            if (mysql_num_rows($result)) {
                //duplicated
            }

        }
    }
?>
please delete me
  • 97
  • 5
  • I see what you're doing, but I wonder if this is a process-intensive method. – Einion Reishus Apr 07 '15 at 23:13
  • Use query loop is not good, but that's just for you to understand what you have to do. You only need a query that will check value "a" and value "b" are part of any column in the table, but as you have three fields, you must make all checks. But can still improve, just use a query to list and another to verify that the values are in the field with the same id. A select embedded in another may resolve. – please delete me Apr 07 '15 at 23:32