Headhunter
|
| Posted: 01/02/2003, 5:51 AM |
|
I have a problem when using multiple joins in one form to the same record.
Some explanation: CCS2 beta1, PHP4 with templates, MySQL3
In my project I have a page called "manufacturers" which contains a grid with the manufacturers main data.
In this data is included:
Hardware manufacturer (can be 0 or 1)
Software manufacturer (can be 0 or 1)
PC manufacturer (can be 0 or 1)
Inactive (can be 0 or 1)
Now, the 0 and ore 1 value(s) must be looked up out of a value table in MySQL db.
Now, this works for just 1 value. (creating a join in visual query builder)
When I do this for 2 or more fields, the grid displays no results anymore.
In CC2 this worked. I re-created the whole project in CCS (now working with ccs2 beta1), and here this isn't working it anymore.
I solve this problem now by replasing those values in php itself, but this is not how it should be.
Thanx in advance
|
|
|
 |
D. Johnson
|
| Posted: 01/02/2003, 8:57 AM |
|
You might check the type of join you are using. If it is an inner join you will get only records matching the join. This type of join is set by CCS automatically. To check/change it, open the query window and right click on the join in question. Select "Left Join" and you should get all records from the main table.
|
|
|
|
Headhunter
|
| Posted: 01/02/2003, 9:27 AM |
|
Nope, it doesn't work. (or maybe I do something wrong).
Table Manufacturer Table values
------------------ -------------
id
inactive------------|
pc_manufacturer-----|-------------> id
soft_manufacturer---| value
hard_manufacturer---|
label: display "value" as "text"
When there are 2 dufferent values, it doesn't work.
|
|
|
|
RonB
|
| Posted: 01/02/2003, 4:00 PM |
|
could you explain what the 0 and 1 stand for, I'm a visual kind a guy so this might help me visualize what it is you are trying to do.
Ron
|
|
|
|
Headhunter
|
| Posted: 01/02/2003, 11:41 PM |
|
RonB,
It's very simple.
the 0 value in my lookup table is "No"
the 1 value in my lookup table is "Yes"
So, when the "0" value is assignet to hard_manufacturer, this has to be replaced with "No" in the grid form, and so on
The lookup table contains other values too, like time values and status messages.
I know this can be simply done with replacing the values in php, but I have lots of pages where I have to do this.
Having Multi-langual support to implement (in the future) this is a must.
It's even possible to change values 1 time, so I don't have to change them everywhere.
Hope this is clear enough.
I have created a demo account on my project so you can have a look for your self. just go to the "Main->Manufacturers" page and you will see what I mean.
You may play around with it since it is a test db.
It is located here:
http://headhunter.dnsalias.org:14080/trackitstudio/login.php
Login: demo
Password: demo
(Temporary account)
|
|
|
|
RonB
|
| Posted: 01/03/2003, 5:39 AM |
|
I thought that might be it.. Here's what I did. I created a table yes_no and entered the values 1->Yes 2->No.
In every table that uses a yes/no field I simply use query builder to join that field to the yes_no table. Works like a charm for me.
Ron
|
|
|
|
NewBee
|
| Posted: 01/03/2003, 7:29 AM |
|
RonB,
Could you please write a small walkthrugh for CCS.
How do i join fields in the query builder as you say.
Thanx in advance. :)
|
|
|
|
Headhunter
|
| Posted: 01/03/2003, 11:11 AM |
|
Ok RonB,
but ever tried with more than one value? I know it works for one value, but if you have more than one on a single grid, it doesn't work. Try it with a value 0 and a 1 value in the same grid. You'll see what I mean.
|
|
|
|
RonB
|
| Posted: 01/03/2003, 3:57 PM |
|
"but ever tried with more than one value?"
Yes, the trick is to add the yes_no table for every occurence in the grid. If you have 3 fields in the grid with 0 or 1 you have to add the yes_no table three times in query builder and make a separate join for every field in the master table. CCS will create the needed aliasses automatically.
|
|
|
|
Headhunter
|
| Posted: 01/04/2003, 9:27 AM |
|
RonB,
it works now (thanks for your help till now)
but displaying the grid goes very slow.
I have created the necessary indexes, but no performance gain.
Any idea's?
|
|
|
|
RonB
|
| Posted: 01/04/2003, 4:03 PM |
|
Hmm, how many records are in the master table? a simple join shouldn't eat that much performance. Take a look at the joins and see if a different kind of join works better.
I made a test table with 4 fields containing 1 or 2, being yes or no and joined them seperately to 4 times yes no. The test table holds 69 records the grid displays almost instantly. No index or other tweaks used. Only used straight joins as the come with query builder.
|
|
|
|
Headhunter
|
| Posted: 01/05/2003, 3:35 AM |
|
RonB,
problem solved.
Seemed like a problem in my database structure. The row containing the data was specified as "CHAR" 50 long. I changed it to "tinytext" 30 long (this row contains other data then "yes" and "no" to).
I do not know much of databases itself, but I find it a strange thing.
Thanks for all your help.
|
|
|
|
