foreign keys elements don't store data

Z

zorzis

Member
let's say we have 5 tables:

1) Observation
2) Location
3) Species
4) Quadrat
5) QuadratSpecies

Observation is our main table/list with joins to the other tables as :
Observation LEFT JOIN Location ON Observation.Loc_ID=Location.Loc_ID

Observation LEFT JOIN QuadratSpecies ON Observation.QuadratSpecies_ID=QuadratSpecies.QuadratSpecies_ID

QuadratSpecies LEFT JOIN Quadrat ON QuadratSpecies.Quadrat_ID=Quadrat.Quadrat_ID

QuadratSpecies LEFT JOIN Species ON QuadratSpecies.Species_ID=Species.Species_ID

all these joins are made from Observation List (is that right?)

Continue:
as you can see i have Observation as main table with many joins.
The Observation has Location_ID foreign key references Location.Location_ID
and QuadratSpecies_ID FOreign key references QuadratSpecies.QuadratSpecies_ID

the QuadratSpecies has also Quadrat_ID as foreign key references Quadrat.Quadrat_ID
and Species_ID as FOREIGN KEY references Species.Species_ID

Now. I have the Observation Form with the following groups made:
Observation (the main)
Observation-[Location]
Observation-[QuadratSpecies]
Observation-[Quadrat]
Observation-[Species]

when i go to Form as you can check: http://archipelagosdatabase.com/index.php/form-demo
( i can provide you with pass and username)

there is The QuadratSpecies_ID wich i want to take value or Values (As the QuadratSpecies_ID is repeatable) after the form QuadratSpecies is submited and has store the new QuadratSpecies_ID's in the database.
Is any way to take automatically the new created values? it's maybe only one but it could be 3,5 ,10 i don't know how...

am i doing anything wrong?

please give me any help cause i'm stuck here.... thanx
 
if you want more information about the plan (i think it's clear for the data part)
but if you want to thing another way of submission we could talk about that...
just ask me whatever you want to explain, and maybe we could find a solution man...
 
the general purpose is that (as needed in my webpage) a grouped that contains the foreign key of a more general table wich use that foreign key value to link the data between that tables,
to take automatically the newly created foreign key values (1 or more new created id's) in the same submission form...
let's say you repeat 3 times the group, so 3 new id's are created that time, and that 3 id's must be linked to the other table foreign key column that contains the foreing key, automatically without the user to do something...
is that more clear? sorry for my bad english i do my best.
maybe if drink a beer or something to expain better ;) but its not dark yet...
 
yes i've seen that database join, but i think you have to visit the webpage with the form to understand better what im doing here...
the users must just select the quadrat number as you will se from the radiobuttons,
after that choose the Algae Specie from the Databasejoin element i used (QuadratSpecies.Specie_ID fk references Species.Specie_ID)
and the quandity(a simple field) where they have to record how many of each specie have seen in the sea...
These 3 elements (this is the QuadratSpecies group) is repeatable as many Species may have been observed in that location.
It's logic..
So now the Collector has to record only these things... the part that Observation (it's "dokimi" in the webpage form as you can see) table works is that collector choose from a databasejoin element "Loc_ID" the location he went to do the observation...
But the Observation table has also a FK called QuadratSpecies_ID to LINK to the QuadratSpecies table...
Sooo...
the use of that element has nothing to do with the collectors...it has to take the values while the form (with the 2 groups to be shown Observation/"dokimi" and the QuadratSpecies) as many as they have observed, and put them in the Observation.QuadratSpecies_ID....
is more clear now?
please, make a visit to the webpage, you maybe understand it better mate...
is it a Javascript issue? or php things?
or something i don;t think right?
 
Sorry, maybe it is just me but I am really struggling to understand your structure and why it is that way. Seems that you are duplicating information that would be present in your list view.

So you want to store the value(s) from an element in your repeat group into an element in your main group? Here are some things that you could check out that might help you.

The calc element might work too depending on how you want it displayed.

http://fabrikar.com/wiki/index.php/Element_Calculation

Or you could use some custom JS or the Form PHP plugin.

http://fabrikar.com/wiki/index.php/Form_plugin_php
http://fabrikar.com/forums/index.php?wiki/form-javascript/

I guess it comes down to whether the user needs to see it when editing. I am not sure on the code for getting the values from repeated group, that has tripped me up in the past.
 
As per the PM you sent me, (I have only just matched the name to this post),

My tables in mysql are in engine INNODB so they can handle Foreign keys.
Click to expand...

They don't need to be INNODB because Fabrik handles foreign keys on a software level.

So ideally your tables should be MYISAM and the joins created within Fabrik.
 
no i don't duplicate any informations....
it's clear that the db structure in INNODB is for safety reasons.
i can't change that...
the point im stuck is that the QuadratSpecies_ID as databasejoin element can choose from QuadratSpecies.QuadratSpecies_ID values that have already created in the Database...
My point is that untill a user create the new QuadratSpecies id by submiting the new values (species_id, quadrat_id and quantity) there are not these values in the database(that's clear).
So how can it be work, so that QuadratSpecies_ID in Observation Table to take the new (maybe more than one) id's from QuadratSpecies_ID while the form submission?
a way to take the new values that have been created by the form submission?
any idea how to make that js or php to work?
maybe rob could help?
please let me give you more informations if needed...
 
Okay there is a lot of information here.... too much for me to understand until we have the very very basics out of the way.

Your PM said you are using INNODB, this is not something strictly supported within Fabrik purely because of the way Fabrik works.

So before we even start diagnosing any issues.....

Your database cannot have any tables which have foreign key retraints that you want to use within Fabrik.

So can you confirm that all your references in regards to joins and FK's are all being handles via Fabrik?
 
We don't support Innodb. We only support MyISAM. Using Innodb will almost certainly generate errors, because we are handling things like foreign key constraints, related row deletion, etc, which tends to conflict with Innodb doing the same thing underneath us, as there is no way for an application to have "hooks" in innodb, to tell us when Innodb has done something to the tables we need to know about.

-- hugh
 
cheesegrits said:
We don't support Innodb. We only support MyISAM. Using Innodb will almost certainly generate errors, because we are handling things like foreign key constraints, related row deletion, etc, which tends to conflict with Innodb doing the same thing underneath us, as there is no way for an application to have "hooks" in innodb, to tell us when Innodb has done something to the tables we need to know about.

-- hugh
Click to expand...

ok lets say i make my tables in MyISAM...
how can i manage the same goal without foreign keys?
im new to sql and i have worries about that.
can you explain me with my example of quadrats species and observation?
i suppose i stop handling a quadratspecies table as before...how can i manage to work with my example?
be more specific mate... thanx in advance


but exept the innodb issue, wich hasn't create any problem to me untill now with fabrik, how we can solve my issue?
it will solved automatically by turning into myisam?
do i have to change the way i have structure my database?
cause if this must hapen we have to disqus the alternatives..
so for any informations send me..anything..
 
mates any help here...it's exactly the same probelm as if it was an INNODB database...
no diferrence..same tables...same problem...any suggestion?
 
I suggest you start with a simple test, rather that launching into 5 joins etc, just to get your head around the way fabrik will work.

So lets take just the first 2 tables:

Observation
Location

Can an observation have more than one location? I would guess not?
In this case a list join is not what you want.
You should use a database join element instead.

3) Species
4) Quadrat
5) QuadratSpecies
Click to expand...
could you describe what the relation ship between these sections are? I'm guessing a location is split into many quadrants and each quadrant can contain many quadrant species, but I don't understand how all that relates to the species table?
 
ok let's take it from the beggining to give you the exact plan....
this is all for algae observation in aegean sea...

so..let's say there are 3 observators (3 people who go to the sea and make their observation)
the observation is: im going to ONE location per time in a specific day...
then i put 3 Transects (3 measures papers 25metres each one)
and in each Transect we put 3 Quadrats(squares with cm for measure the algae)

SO we have 3 Transects per Location and 3 Quadrats per Transect...
These are standard.we cannot have 4 Transect or 5 Quadrats.IT's 3 Transect and 3 Quadrats (Call them as you want if you don't prefer 1,2 ,3)
The 3 Transects may complete in diferent days but in one day we complete ONE TRANSECT.THAT means we observe at least 3 Quadrats per day.

So at each quadrat we may observe more than one Algae_Species
And for each Algae_Specie we observe we have to record how much per specie have seen in the Quadrat..So let's say that in Quadrat Number 1 we observed 23 Algae Rigida , 4 Algae Red Rock ,. 56 Alge Bullshit and goes on...
IN Quadrat number 2 we observed 42 Algae .... , 2 Algae .... , 52 Algae .... , 14 Algae ..... and goes on...

So we have a Quadrat Table ( Quadrat_ID and Quadrat_Number- only 3 records in that table no matter what as i told you before)

Species Table (Species_ID, Species_Name, Species_Family - we have by default 106 or 107 species as the people from the biologic team told me)

we Have Transect Table (Transect_ID and Transect_Number - only 3 records.that's it.like in Quadrat..only 3)

aah ... we have a Location Table where we record the places we have gone to make the observation. This must be editable from the collectors so they can add more locations...that's easy as i make it work before with the ADD button from fabrik with the nice popup window.

so these are our default tables...

the other ones i created is for the relationships between them...can u think a better way?

the plan is: Rob is an observator and goes with Zorzis to make an observation in HAWAI in an exact Location lets say YaBanaki...
so we go to the location YABANAKI with an exact latitude and longitude...
so..
we put the Transect Paper NUMBER ONE. so Transect_NUMBER:1
and we put in that transect paper the first Quadrat.so QUADRAT_NUMBER:1
and we observe all that Algae Species as i told you before...
One person per Quadrat each time...ONLY ONE PERSON that means one User goes to the PC and make the record in the database...so
He/She has to record in the webpage after he/she first login(so the fabrik takes automaticaly the user id my->id as i remember...
after: where he/she gone to observe..means the LOCATION...
after:DATE (i put the date in the TransectLocation Table because it it may differs per Transect)
And now we have The Transect Number we did the observation..ok
some other characteristics like Temperature Transect_Rugosity Light_Intensity Sea State Wind Force and Cloud_Cover....all these are in my TransectLocation Table...
and finaly we have QuadratSpecies with Quadrat_Number to be chosen and repeat form for Species and Quantity...
so i made QuadratSpecies to store data like Quadrat_Number Species_ID(name label in the dropdown) and Quantity of each Specie Chosen...

so we have a table QuadratSpecie to take the Quantity wich can't be neither in Quadrat table nor in the Species Table... is that clear?

that's my problem...

so i start doing some simple things with fabrik but i think i get that..
i have made a lot of forms and i think i understand that..
but i always stuck to the same problem...
Let's suppose we have the TransectLocation...
it's very simple as it has Transect number to be chosen from a databasejoin element to the Transect table, and a second databasejoin element (location_id) to take data from Location table (location_id).
and an add button to location so user can add more locations..
that's simple untill now and all work fine.


but when we have to go to the Observation you understand that we cannot have databasejoin element to the TransectLocation as we cannot choose from that table because new records has to be done..

ok it's a big post but i gave you the way that things work or may work ;)
think about it and if you want a skype or email to not disturb people here all the time with bumps tell me...
zorzis
 
or maybe if there is a way,
to make it work as multipage forms,
and the last form to be the general table OBSERVATION and with some js or php thing,
to take the previous submitted TransectLocation and QuadratSpecies ID's and automatically submit them in the Observation.TransectLocation_ID and Observation.QuadratSpecies_ID.
is there any way? it is a good way this one to make things work Rob?
i can't think something else...
if this could work will be a user friendly solution and for me and for the collectors...
 
There is a lot of information here to absorb.... every time you add to it you are making it harder and harder. :)

Please give people time to respond and if you reference people directly....

it is a good way this one to make things work Rob?
Click to expand...

... then you are putting off other people from answering as they may feel you only want Rob to answer. (You are not the first to do this though, a few posts I have seen today have done this).
 
sorry felixkat and other people here...
really.. accept my apologise mate...

because of my bad english i cannot set the problem the way i must do...
maybe is any way to talk through skype or something to explain to someone the purpose of the db must work so someone else (you rob anyone) can post it as it is and not the way i post it with my bad english? to do that once so all the information needed to be in one post in the right way?
maybe this could be more helpfull for other to understand?
thanx again for the responces anyway
 
You must log in or register to reply here.

Useful links

  • Create a Fabrik Account (Downloads)
  • Create a Forum Account
  • Fabrik Wiki
  • Fabrik 4 Changelog /Install /Upgrade
    • We are in need of some funding.
    More details.

    Thank you.

    Members online

    Total: 64 (members: 2, guests: 62)
    Back
    Top