how to check a value in multiple rows in mysql table associated with a unique data in each row

Question

I'm developing a record management application using php. I've designed a table named records with fields id, recordname, recordvalue, recordid. For example if a record consists of 3 data firstname, lastname, gender. 3 data (John Doe Male, Jane Doe Female and Sherlcok Holmes Male) is inserted in the table as below-

----+-------------+--------------+-----------
id  | recordname  | recordvalue  | recordid
----+-------------+--------------+-----------
1   | firstname   | John         | 001
2   | lastname    | Doe          | 001
3   | gender      | Male         | 001
4   | firstname   | Jane         | 002  
5   | lastname    | Doe          | 002
6   | gender      | Female       | 002
7   | firstname   | Sherlock     | 003
8   | lastname    | Holmes       | 003
9   | gender      | Male         | 003

----+-------------+--------------+-----------

my question is when i'm going to insert a new record in the table i need to check if there is any duplication of the record. That means any field for different records can be duplicated anytime but all the fields having same values for a record can't be duplicated. From previous example records having value John Doe Female, Jane Doe Male, Sherlock Doe Male etc are OK but again inserting John Doe Male is not expected and it'll give an error.

How can i achieve this easily with PHP?

Thanks in advance.

Answer 1

I think you should fix your table, It has a really bad design. It should be something like that

table
--------------------------------------------
record_id | firstname | lastname | gender

and then you could use a UNIQUE INDEX (firstname, lastname, gender) to control that, if you wanted to keep it on only in your database.

[EDIT]

Due to the fact you can not change the database design and it is using EAV structure, you can use the query below to achieve what you are looking for. This query is gonna return if there is or not records based in your criteria.

SELECT a.recordvalue, b.recordvalue, c.recordvalue
FROM rec_eav a
INNER JOIN rec_eav b ON (b.recordid = a.recordid AND b.recordname = 'lastname' )
INNER JOIN rec_eav c ON (c.recordid = a.recordid AND c.recordname = 'gender' )
WHERE a.recordname = 'firstname' 
    AND a.recordvalue = 'John'
    AND b.recordvalue = 'Doe'
    AND c.recordvalue = 'Male' 

Answer 2

Thanks everyone for your affords. Anyway I've found a solution of my problem. Its tricky and I don't know how efficient it is. So that I'm posting it here for expert's comments. Thanks.

My problem was- I have a EAV table where single entity has multiple attributes and corresponding values. I needed to check if anyone submits a form with some values of a record, all those are already in the database as another record. E.g. if John Doe Male is already in the table under recordid 001 then user can not save John Doe Male as another record, rather it'll give the user a message "Record already exists!".

I performed some queries -

foreach($submitted_values as $name => $value){
    $r = mysql_query("SELECT DISTINCT `recordid` FROM `records` WHERE `recordname` = '$name' AND `recordvalue` = '$value'");                
    while($row  = mysql_fetch_assoc($r)){
        $assoc[$name][] = $row['recordid']; 
    }
}

so in $assoc i got an 2 dimensional array indexed with field names and associated with an array of matched recordid each.

Then I checked if there are any matched value in the $assoc array and if i get a non empty array that i'm sure that there one or many duplication of the submitted values under a single record.

This process helped me also tell the user that there are some partial matches of values. And it worked for me perfectly.

Again thanks everyone for wasting your valuable times.

Answer 3

You may try the following query, if an answer is returned, then you already have similar data. If not, it will return 0 rows:

select recordid from records 
where recordvalue in ('John', 'Doe', 'Male')
group by recordid
having count(recordid) = 3

EDIT: Above query might give more than 1 result, as explained by @Xavjer, try this:

select * from 
(
select 
max(case when recordname="firstname" then recordvalue end) as firstname,
max(case when recordname="lastname" then recordvalue end) as lastname,
max(case when recordname="gender" then recordvalue end) as gender,
recordid
from records
group by recordid
) as record
where firstname='doe'
and lastname='john'
and gender='male'

Answer 4

Run a query similar to the following:

select count(*) countmatch
from datatable
where concat(recordname,':',recordvalue) in
      ('firstname:John', 'lastname:Doe', 'gender:Male')
group by recordid
order by countmatch desc
limit 1

- if the countmatch value returned is 3, reject the new John Doe Male record, otherwise add it to the table.

EDIT: If firstname, lastname and gender together uniquely identify a record, but the other attributes may be sparsely and/or arbitrarily populated, then you may want to consider a hybrid schema - possibly something like this:

KeyTable
--------
firstname
lastname
gender
recordid

- with a unique index on the combination of firstname, lastname and gender, and a separate unique index on recordid.

DataTable
---------

- as now, with a foreign key on recordid. Note that the firstname, lastname and gender attributes should not be stored on this table.

When trying to add "new" combinations of firstname, lastname and gender, try adding them to the KeyTable - if the insert is rejected by the DBMS, it means that the record already exists.

In this scenario, recordname/recordvalue combinations stored on DataTable should always include a recordid that links to a valid recordid on KeyTable.

Answer 5

If you're going to use an EAV model to store data then consider a structure like this:

eav_hell(entity*,attribute*,value) 

* = (component of) PRIMRY KEY

And spare a thought for the poor discarded data types.

Answer 6

You will need something like this:

SELECT a.recordid
FROM test a
JOIN test b ON ( b.recordname = 'firstname' AND b.recordvalue = 'John' AND b.recordid = a.recordid)
JOIN test c ON ( c.recordname = 'lastname' AND c.recordvalue = 'Doe' AND c.recordid = a.recordid)
WHERE (a.recordname = 'gender' AND a.recordvalue = 'female')

If the result is empty, then you can proceed (like in my example, if you change gender value to male it will return a recordid therefore this combination already exists)

Answer 7

my question is when i'm going to insert a new record in the table i need to check if there is any duplication of the record.

Your “records” are something different than a normal database record (= one row) here.

That means any field for different records can be duplicated anytime but all the fields having same values for a record can't be duplicated. From previous example records having value John Doe Female, Jane Doe Male, Sherlock Doe Male etc are OK but again inserting John Doe Male is not expected and it'll give an error.

So the combination of several records has to be unique in your case.

How can i achieve this easily with PHP?

You could SELECT first and see if you get a result for the data to be inserted already, as Yadav suggested in his answer. But that will result in a possible TOCTTOU problem – to avoid that, you will have to encapsulate SELECT and INSERT into a transaction.

The other way would be to use an INSERT … SELECT statement. The SELECT statement must be formulated in a way that it only returns values to insert if it does not find them already – counting the matching records and using HAVING to filter for only those that give the right number of records (three in your example) would do that.