Duplication is possible [08] Error: during query execution

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?

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!

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?

________________________________________________________
Eugenio |\.-./| DaDaBIK developer and project admin

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?

Addenum:

The field I check for double entry during insert is not connected with other tables!

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 |\.-./| DaDaBIK developer and project admin

Thanks.
My version then is 4.2.

So I don't know if this error occurs in later versions, too.

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

________________________________________________________
Eugenio |\.-./| DaDaBIK developer and project admin