[eluser]jcavard[/eluser]
[quote author="vivar" date="1249513348"]Hi,
I usually find my answers just by lurking here on the forums but today I actually have a question to ask.
I have a database table with a column that holds a list of names. I actually even added a column called sections which holds the first letter of each name because I was having difficulty with this. I am trying to place the data in an array that resembles the output below:
<dict>
<key>A</key>
<array>
<string>Aaliyah</string>
<string>Aaron</string>
<string>Abagail</string>
</array>
<key>B</key>
<array>
<string>Bailee</string>
<string>Bailey</string>
<string>Bailey</string>
</array>
.......................................
<key>Y</key>
<array>
<string>Yadira</string>
<string>Yael</string>
<string>Yahir</string>
</array>
<key>Z</key>
<array>
<string>Zachariah</string>
<string>Zachary</string>
<string>Zachery</string>
</array>
</dict>
The reason I need the data setup in this fashion is because it is being fed to another application via JSON. I am having no success returning my data in a key / value list. I may be looking at this the wrong way. I wrote a quick loop to visualize the results:
Code:
<?php
// see if our query returned any data before processing (query is taken care of in the model)
if (count($adminUnit)){
// $myLetter is an array returned from my Model (connecting to MySQL)
// it holds the following data: A,B,C,D etc.
// loop through each letter that has a section in the database
foreach ($myLetter as $id => $letter){
echo $letter['section'];
echo '<br/>';
// $adminUnit is an array returned from my Model (connecting to MySQL)
// it holds a list of names and a section column (A, B, C, D etc.)
// loop through $adminUnit
foreach ($adminUnit as $key => $list){
// now print out the data IF the section matches the outer loops section (ie. 'A' =='A')
if (strtoupper( $list['section']) == $letter['section']){
echo $list['name'];
echo '<br/>';
}
}
}
}
?>
That is as far as I have gotten. I'm not sure how to 'build' the results into a key/value array like the dictionary I displayed at the top of the post.
Basically I would need a dictionary of 26 arrays (the alphabet). The key in each dictionary entry is 'A' to 'Z', and the objectForKey is an array (which contains all the names that begin with that letter).
Any help / guidance / suggestions would be greatly appreciated.[/quote]
I am not sure I get it, but here it goes...
let's say you have a query that returns 2 fields:
- category (A, B...)
- name (Aaron, Baylee...)
You could loop through the result and build an array that way
Code:
$this->db->select('category, name');
$handle = $this->db->get('myTable');
$json = array();
foreach($handle->result as $row)
{
$json[$row->category][] = $row->name;
}
This would output an array with that sturcture, something in the likes of
Code:
$json['A']['Aaliyah'];
$json['A']['Aaron'];
$json['A']['Abagail'];
$json['B']['Bailee'];
$json['B']['Bailey'];
$json['B']['Bailey'];
$json['Y']['Yadira'];
$json['Y']['Yael'];
$json['Y']['Yahir'];
$json['Z']['Zachariah'];
$json['Z']['Zachary'];
$json['Z']['Zachery'];
Also, you don't need that 'category' column in you table. It would be redundant information. You can create it at 'runtime' with a select like this instead:
Code:
$this->db->select('name');
$this->db->select('UPPER(SUBSTRING(name, 0, 1)) as category'); //this will select the first letter of the name and store it in a row named category - note: drop the current category column or rename it otherwise mysql will throw some err on the column name
$this->db->order_by('name', 'ASC');
$handle = $this->db->get('myTable');
I am at work right now, so I haven't had time to test the code, but it should do the trick. let me know, hope it helped.