Concatenate two fields
5 posts by 2 authors in: Forums > CMS Builder
Last Post: September 14, 2011 (RSS)
I have a table where I want to concatenate two fields to display in a drop down list. The fields are "num" and "zone_name".
I assume I will probably have to do a "left-join", but need some help with the code.
Any help appreciated!
Ragi
I assume I will probably have to do a "left-join", but need some help with the code.
Any help appreciated!
Ragi
--
northernpenguin
Northern Penguin Technologies
"Any sufficiently advanced technology
is indistinguishable from magic."
........Arthur C. Clarke
northernpenguin
Northern Penguin Technologies
"Any sufficiently advanced technology
is indistinguishable from magic."
........Arthur C. Clarke
Re: [northernpenguin] Concatenate two fields
By Dave - September 13, 2011
Hi Ragi,
You mean in the CMS right? I think I know what you mean. Here's an example:
- Create a list field
- Set "List Options" to "Get options from MySQL query (advanced)"
- Use a query like this:
The first column value returned by the query is the value used in the database, the second value is the "label" displayed to the user. This loads users from the 'accounts' table and displays them like this "1 - dave@interactivetools.com".
Just switch the fieldnames and tablenames and it should work for you.
Hope that helps! Let me know how it goes.
You mean in the CMS right? I think I know what you mean. Here's an example:
- Create a list field
- Set "List Options" to "Get options from MySQL query (advanced)"
- Use a query like this:
SELECT num, CONCAT(num, ' - ', email)
FROM `<?php echo $TABLE_PREFIX ?>accounts`The first column value returned by the query is the value used in the database, the second value is the "label" displayed to the user. This loads users from the 'accounts' table and displays them like this "1 - dave@interactivetools.com".
Just switch the fieldnames and tablenames and it should work for you.
Hope that helps! Let me know how it goes.
Dave Edis - Senior Developer
interactivetools.com
interactivetools.com
Re: [Dave] Concatenate two fields
By northernpenguin - September 14, 2011 - edited: September 14, 2011
Dave: Actually, a lot easier than I originally thought!
I am using the following code:
I added the code in red to allow me to see both the squadron number and its name on the same dropdown. Unfortunately, when I try to sort them in numerical order by squadron number, the result is something like this:
12 - squadron 1
125 - squadron 2
14 - squadron 3
etc...
It is treating the squadron number as text, therefore the sort order doesn't really work. Any ideas on how to fix this?
Ragi
I am using the following code:
<form action="leagueDbDetails.php" method="post">
Choose a squadron:
<select name="sqn_num_match" size="1" style="width: 160px;">
<?php foreach ($sqnRecords as $record): ?>
<option value="<?php echo $record['sqn_num'] ?>"><?php echo $record['sqn_num'] ?> - <?php echo $record['sqn_name'] ?></option>
<?php endforeach; ?>
</select>
<input type="submit" value="Submit">
</form> I added the code in red to allow me to see both the squadron number and its name on the same dropdown. Unfortunately, when I try to sort them in numerical order by squadron number, the result is something like this:
12 - squadron 1
125 - squadron 2
14 - squadron 3
etc...
It is treating the squadron number as text, therefore the sort order doesn't really work. Any ideas on how to fix this?
Ragi
--
northernpenguin
Northern Penguin Technologies
"Any sufficiently advanced technology
is indistinguishable from magic."
........Arthur C. Clarke
northernpenguin
Northern Penguin Technologies
"Any sufficiently advanced technology
is indistinguishable from magic."
........Arthur C. Clarke
Re: [northernpenguin] Concatenate two fields
By Dave - September 14, 2011
Hi Ragi,
The simple fix is to add +0 so mysql coverts it to a number:
Or another solution is to edit the field under: Admin > Section Editors > Field Editor and set the MySQL Column Type at the very bottom.
Let me know if either of those work for you.
The simple fix is to add +0 so mysql coverts it to a number:
'orderBy' => ' sqn_num+0 ',Or another solution is to edit the field under: Admin > Section Editors > Field Editor and set the MySQL Column Type at the very bottom.
Let me know if either of those work for you.
Dave Edis - Senior Developer
interactivetools.com
interactivetools.com
Re: [Dave] Concatenate two fields
Thanx Dave! Your first solution worked.
Ragi
Ragi
--
northernpenguin
Northern Penguin Technologies
"Any sufficiently advanced technology
is indistinguishable from magic."
........Arthur C. Clarke
northernpenguin
Northern Penguin Technologies
"Any sufficiently advanced technology
is indistinguishable from magic."
........Arthur C. Clarke