advanced lists
14 posts by 2 authors in: Forums > CMS Builder
Last Post: March 27, 2015 (RSS)
By rez - March 25, 2015 - edited: March 25, 2015
It was painful, but I got it. How you can select before you join, or how it's known to look ahead for a join while not understanding yet what I'm selecting, I don't know. I would think the join should have been first.
SELECT <?php echo $TABLE_PREFIX ?>locations.num, CONCAT (<?php echo $TABLE_PREFIX ?>state.name, ' - ', city, ' - ',address)
FROM `<?php echo $TABLE_PREFIX ?>locations`
JOIN <?php echo $TABLE_PREFIX ?>state ON <?php echo $TABLE_PREFIX ?>locations.state = <?php echo $TABLE_PREFIX ?>state.num
By rconring - March 26, 2015
Glad to see you got the join working. I have always used the state abbreviation in the state validation table as a key field. Then, instead of storing the state record number in the locations file, you could store the 2 character state abbreviation. That would make the state available without the join to the state table.
SELECT num, CONCAT(city. ', ',abbreviation, ' - ', address)
FROM `<?php echo $TABLE_PREFIX ?>locations`
ORDER BY 'abbreviation, city'
Just a thought.
Conring Automation Services
----------------------------------------
Software for Business and Industry Since 1987
By rez - March 26, 2015
Oh, that is convenient. However, in CMSB, the keys are always automatically num, right? How are you getting the abbreviation into the locations table as a key somewhere before this (if i am understanding correctly)?
By rconring - March 27, 2015
You do not have to always use the record ID as the value for options. Just make sure that the abbreviation field is not editable in the states master table.
In the locations table list, Get options from database (advanced)
Section Tablename -states
Use this field for option values - abbreviation
Use this field for option labels - name
I tried to upload a screenshot but something is wrong. It refuses to accept the file.
I am late for an appointment and can continue this later if you need more assistance. I could send you the states table schema and data but the file upload is not working. LOL
Conring Automation Services
----------------------------------------
Software for Business and Industry Since 1987