Error 1054 When Editing Registry - After copying and using juser

[tiagovareta]

Hi,

I am using the following code to copy records from one table to 4 other related tables:

$app = JFactory::getApplication();
$ids = $app->input->get('ids', array(), 'array');

foreach ($ids AS $myid)
{
$row = $model->getRow($myid);
$id = $row->tb_contactos___id_contacto;
$nome = $row->tb_contactos___nome;
$apelido = $row->tb_contactos___apelido;
$email = $row->tb_contactos___email;
$contribuinte = $row->tb_contactos___contribuinte;
$morada = $row->tb_contactos___morada;
$numero = $row->tb_contactos___numero;
$codigopostal = $row->tb_contactos___codigo_postal;
$localidade = $row->tb_contactos___localidade;
$contacto = $row->tb_contactos___contacto;
$datanascimento = $row->tb_contactos___data_nascimento;
$sponsor = $row->tb_contactos___sponsor;
$data = $row->tb_contactos___data;
$origem = $row->tb_contactos___origem;

$db = FabrikWorker::getDbo(); //Liga??o ? BD APP OriGest
$db2 = FabrikWorker::getDbo(false, 2); //Liga??o ? BD que recebe os contactos da LP


$query = "INSERT INTO tb_contactos_geral (id, nome_ctg, apelido_ctg, contribuinte_ctg, data_nascimento_ctg, sponsor_ctg, data_registo_ctg, origem_ctg) VALUES ('$id', '$nome', '$apelido', '$contribuinte', '$datanascimento', '$sponsor', '$data', '$origem')";
$db->setQuery($query);
$db->execute();

$query1 = "INSERT INTO tb_email (email_eml, id_contacto_geral_eml) VALUES ('$email', '$id')";
$db->setQuery($query1);
$db->execute();

$query2 = "INSERT INTO tb_morada (morada_mrd, numero_porta_mrd, codigo_postal_mrd, localidade_mrd, id_contacto_geral_mrd) VALUES ('$morada', '$numero', '$codigopostal', '$localidade', '$id')";
$db->setQuery($query2);
$db->execute();

$query3 = "INSERT INTO tb_contacto (contacto_ctt, id_contacto_geral_ctt) VALUES ('$contacto', '$id')";
$db->setQuery($query3);
$db->execute();

$query4 = "UPDATE tb_contactos SET registado='1' WHERE id_contacto=$id";
$db2->setQuery($query4);
$db2->execute();
}

Basically I copy a record from the table "tb_contactos", where the fields are distributed to the tables "tb_contactos_geral, tb_email, tb_morada, tb_contacto".
After the copy the main table is: tb_contactos_geral, the relationship with the other tables (tb_email, tb_morada, tb_contacto) is LEFT JOIN.

Until now I think everything is right! Because it copies the record well for all tables.
In the table form "tb_contactos_geral" i'm using the Juser Plugin.

My problem is when I try to edit the record I copied (already in the table tb_contactos_geral).
When I click edit, the information at the top appears:
Alert
JUser: :_load: Could not load user with ID: 9448

When I "save" it gives the error:
1054 Unknown column 'Array' in 'where clause'

Can anyone help me? What could be the cause of this error?

 

[troester]

INSERT INTO tb_contactos_geral (id,

What is id, the primary key which is usually autoincrement in Fabrik?

 

[tiagovareta]

INSERT INTO tb_contactos_geral (id,

What is id, the primary key which is usually autoincrement in Fabrik?

Hi Troester,

Yes exactly, this id I'm copying I want to be the id of the tb_contactos_geral table.
Or do I have to have a field with another id?

 

[troester]

If it's set to autoincrement (List settings/Data) it will ignore any value of your INSERT.
So if you want a PK = your-contact-id (similar as the userid in the #__users table) you must change this element from internalID to field, formatting Integer and set Autoincrement=NO in list settings.
You must make sure that the inserted values are unique.

Because you want to do LEFT JOINS you can't use an other element for this (contact)id: Fabrik needs one "leg" of the join to be a primary key.

 

[cheesegrits]

If it's set to autoincrement (List settings/Data) it will ignore any value of your INSERT.

Just to be clear, that's not a Fabrik or Joomla thing, that's MySQL. A field set to auto-inc will ignore any value given in an INSERT, and just obey the auto-inc. I think in earlier versions of MySQL it used to actually throw an error if you tried to specify a value for an auto-inc on INSERT, but for whatever reason no longer does, it just ignores it.

-- hugh

 

[tiagovareta]

If it's set to autoincrement (List settings/Data) it will ignore any value of your INSERT.
So if you want a PK = your-contact-id (similar as the userid in the #__users table) you must change this element from internalID to field, formatting Integer and set Autoincrement=NO in list settings.
You must make sure that the inserted values are unique.

Because you want to do LEFT JOINS you can't use an other element for this (contact)id: Fabrik needs one "leg" of the join to be a primary key.

Hi Troester and Cheesegrits,
Thanks for the answers!

I changed the "id" field:
- Remove the auto-increment;
- Set as field (integer 11)

It still gives the same error: 1054 Unknown column 'Array' in 'where clause'

I send the field settings:

 

[tiagovareta]

Hi,

I think I have already discovered the source of the error! In the Email + Contact lists, I had the fields email and contact with a unique validation, defined in the fabrik.
I removed these validations and it seems to me that everything is ok!

As the idea will always be to copy the records to this new table, I go through the validations in the original table.