2. PHPRunner General questions
  3. Not normalised db
This topic is locked

Not normalised db

 		7/8/2006 1:28:47 PM
PHPRunner General questions
A
 ACV author

I have a record with two relations to one and the same adress table. I made a join of the first to the table. All perfect. Now I made another join for the second field to the same table. PHPRunner accepted it but no new fields are shown.
I copied the orginal adress table to another one and made joins to both tables. Nothing was shown!
Somebody with familiar problems???? <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=question&id=2967&image=1&table=forumtopics' class='bbc_emoticon' alt=';)' />
J
 		Jane 7/10/2006

Hi,
try to test your query directly on your database.

You can post here structure of your tables and your query and I'll help you to fix it.
A
 		ACV author 7/10/2006

Hi Jan,
This is the query I'm trying. The issue is the double reference to the ACVNAW table. There are references in table ACVVLADM, one for the instructor and one for the pilot. ACVVLADM keeps records of every flight. Not every flight includes an instructor. In that case the join references to ACVNAW record with lnummer=0 which returns a blank anaam (name of instructor)
SQL-query:SELECT `VLUCHTSTAAT_ID` , acv2.anaam, acv3.datum, acv4.reg, acv5.anaam, timediff( TIJD_LND, TIJD_TO ) AS Tijd_vliegen, timediff( TIJD_UIT, TIJD_AAN ) AS Tijd_blok

FROM `ACVVLADM`

JOIN ACVNAW acv2 ON ACVVLADM.`Instr/tech` = acv2.lnummer

JOIN VLUCHTSTAAT acv3 ON VLUCHTSTAAT_ID = acv3.VLUCHTSTAAT

JOIN ACVKIST acv4 ON acv3.KNUMMER = acv4.KNUMMER

JOIN ACVNAW acv5 ON ACVVLADM.lnummer = acv5.lnummer

LIMIT 0 , 30
I have tried this with PHPADMIN with the result I expect. However entering it in PHPRUNNER doesn't show any errors in step 3 -> step 4, but the requested field acv5.anaam is not shown in step 6.
J
 		Jane 7/11/2006

Hi,
try to remove LIMIT 0 , 30 in your query.
If it doesn't work send to [email=support@xlinesoft.com]support@xlinesoft.com[/email] database creation script.

I'll find what's wrong with your project running it on my test box.
A
 		ACV author 8/24/2006

Hi,

try to remove LIMIT 0 , 30 in your query.
If it doesn't work send to [email=support@xlinesoft.com]support@xlinesoft.com[/email] database creation script.

I'll find what's wrong with your project running it on my test box.


Hi jane,
Vacations interupted my development but it also solved the most important problems. The solution was to alias the names to be displayed.
SQL-query:SELECT `VLUCHTSTAAT_ID` , acv2.anaam AS 'NAME A', acv3.datum, acv4.reg, acv5.anaam AS 'NAME A', timediff( TIJD_LND, TIJD_TO ) AS Tijd_vliegen, timediff( TIJD_UIT, TIJD_AAN ) AS Tijd_blok

FROM `ACVVLADM`

JOIN ACVNAW acv2 ON ACVVLADM.`Instr/tech` = acv2.lnummer

JOIN VLUCHTSTAAT acv3 ON VLUCHTSTAAT_ID = acv3.VLUCHTSTAAT

JOIN ACVKIST acv4 ON acv3.KNUMMER = acv4.KNUMMER

JOIN ACVNAW acv5 ON ACVVLADM.lnummer = acv5.lnummer

LIMIT 0 , 30
Now the appear in the rest of phprunner and you can use them in the list sort overview etc.
Another problem with a reserved word CHECK was solved by some investigation on MySQL and use quotes around the table name.
Thanks for your support and your offer to test it on your own server!