Duplication is possible [08] Error: during query execution

meanster99

Well-known member
Hi All,

Dadabik v4.3
Firefox
Windows Vista
MySQL 5.1.56
Apache 2x

I have enabled the duplicate checking function and all works well on the email field I have set to check for duplicates, in one of my tables. When I try and enter a duplicate email address it warns me it could be a duplicate but gives me the option to add the record anyway. Perfect, all as should be.

However, in another table of mine I have set the telephone field (although its set to alphanumeric as I encourage a space being put between the area code and number) to check for duplicates and if I try and enter a duplicate I get the following message only, (with no option to add the record anyway):


Duplication is possible

[08] Error: during query execution.

Here is the full SQL statement and error:

Your SQL query (for debugging purpose): SELECT `artiste_contacts`.`ID`, `artiste_contacts`.`Stage_Name`, `artiste_contacts`.`Real_Name`, `artiste_contacts`.`Phone_Number`, `artiste_contacts`.`Email`, `artiste_contacts`.`Home_Town`, `artiste_contacts`.`Areas`, `artiste_contacts`.`Grade`, `artiste_contacts`.`Notes`, `artiste_contacts`.`Price`, `artiste_contacts`.`Gender`, `ethnicity_race__1`.`Ethnicity` AS `ethnicity_race__Ethnicity__1`, `performer_types__1`.`Performer_Type` AS `performer_types__Performer_Type__1` FROM `artiste_contacts` LEFT JOIN `ethnicity_race` AS `ethnicity_race__1` ON `artiste_contacts`.`Race` = `ethnicity_race__1`.`ID` LEFT JOIN `performer_types` AS `performer_types__1` ON `artiste_contacts`.`Performer_Type` = `performer_types__1`.`ID` where `ID` = '146'

[08] Error: during query execution.
SELECT `artiste_contacts`.`ID`, `artiste_contacts`.`Stage_Name`, `artiste_contacts`.`Real_Name`, `artiste_contacts`.`Phone_Number`, `artiste_contacts`.`Email`, `artiste_contacts`.`Home_Town`, `artiste_contacts`.`Areas`, `artiste_contacts`.`Grade`, `artiste_contacts`.`Notes`, `artiste_contacts`.`Price`, `artiste_contacts`.`Gender`, `ethnicity_race__1`.`Ethnicity` AS `ethnicity_race__Ethnicity__1`, `performer_types__1`.`Performer_Type` AS `performer_types__Performer_Type__1` FROM `artiste_contacts` LEFT JOIN `ethnicity_race` AS `ethnicity_race__1` ON `artiste_contacts`.`Race` = `ethnicity_race__1`.`ID` LEFT JOIN `performer_types` AS `performer_types__1` ON `artiste_contacts`.`Performer_Type` = `performer_types__1`.`ID` where `ID` = '146'
DBMS server said: Column 'ID' in where clause is ambiguous


Any ideas - obviously related to my auto-incremented ID field - but this has the same settings as in the table that the duplication check works, so I can't see what I've done wrong! Any ideas?
 

meanster99

Well-known member
OK have worked it out - 3 of my tables have identical field names (ID) and although this should not normally be an issue with SQL (as far as I understood) because the field name gets the table name prefix, it was apparently causing problems with this SQL statement.

I have renamed 2 of the field names to something else and all is working as it should!
 

eugenio

Administrator
Staff member
Hi,
I've tried to reproduce your problem without success, even if I have same field names in different tables I don't get the error messages.
Could you send me your database schema to verify?
 

geronimo

New member
Hello!

Interestingly I have encountered exactly the same error (dadabik 4.3):

Duplizierung möglich

[08] Error: during query execution.
SELECT `jf_kk_kleidungsstuecke`.`id`, `jf_kk_kleidungsstuecke`.`inventarnr`, `jf_kk_typen__1`.`kleidungstyp` AS `jf_kk_typen__kleidungstyp__1`, `jf_kk_kleidungsstuecke`.`Groesse`, `jf_kk_kleidungsstuecke`.`Beschaffung`, `jf_kk_mitglieder__1`.`name` AS `jf_kk_mitglieder__name__1`, `jf_kk_mitglieder__1`.`aktiv` AS `jf_kk_mitglieder__aktiv__1`, `jf_kk_kleidungsstuecke`.`ausgemustert`, `jf_kk_kleidungsstuecke`.`inaktiv`, `jf_kk_kleidungsstuecke`.`Bemerkung` FROM `jf_kk_kleidungsstuecke` LEFT JOIN `jf_kk_typen` AS `jf_kk_typen__1` ON `jf_kk_kleidungsstuecke`.`typ` = `jf_kk_typen__1`.`id` LEFT JOIN `jf_kk_mitglieder` AS `jf_kk_mitglieder__1` ON `jf_kk_kleidungsstuecke`.`zu_mitglied` = `jf_kk_mitglieder__1`.`id` where `id` = '1'
DBMS server said: Column 'id' in where clause is ambiguous


I also have two other tables with the (primary key) field "id".
As soon as I changed the names into "id1" and "id2" in the other tables everything worked fine!

Also interesting to know: Both other tables were connected with the one where I wanted to insert a new record.
In that table I have two 'select-single'-fields where I pick up records from the other tables (and of course chose the id-field of every table as primary key field which worked perfectly as far as I don't try to insert a new record where a double entry is possible).
Then I tried something and changed the two 'select-single'-fields into normal text-fields, so they are not connected with the other two tables anymore. In the other two tables I re-named id1 and id2 into id.
Now the double entry check worked without error message.

So, to summarize my somewhat strange english, the error in my dadabik occurs only when I connect other tables with the one where I want to make the double entry check during insert and all tables have a primary key field with the same name (in this case "id").


BTW:
I hope my dadabik version is 4.3.
Newbie question: where can I see my version number? B)
 

eugenio

Administrator
Staff member
If you still have the documentation or changelog file, there you can find the version. Otherwise, open the include/header.php file and you'll find the version in the meta generator tag (however, I version was released with the wrong meta tag, so documentation / changelog is better).
 

eugenio

Administrator
Staff member
Hi Geronimo,
thanks for your detailed feedback; the bug has finally been fixed and the bugfix will be released with DaDaBIK 5.1
 
Top