Solved

# Printing unique/distinct values PHP, MSSQL, Codeigniter

Posted on 2016-10-28
155 Views
I have a very messy sql statement joining 4 different tables. I need distinct/unique values from one column. My statement is pulling all of the information I need but now I need to print out the unique value from one column with all values from the other columns.

Using Codeigniter controller, model and view.

What it accomplishes:
Research Area would be unique to each faculty member while (several) Research Area Topics are displayed under the Research area. (the $Faculty_Research_Match is determined from drop down menu with faculty members information) --------------------------------------------------------------------------------------------------------------------------------------------------------------- Model: public function post_it_now($Faculty_Research_Match) {
$this->load->database();$query = $this->db->query("SELECT * FROM suapp.FACULTY JOIN suapp.RESEARCH_MATCH on suapp.RESEARCH_MATCH.Faculty_UUID = suapp.FACULTY.Faculty_UUID JOIN suapp.AREA_TOPIC on suapp.AREA_TOPIC.Area_Topic_Code = RESEARCH_MATCH.Area_Topic_Code JOIN suapp.RESEARCH_AREA on suapp.RESEARCH_AREA.Research_Area_Prefix = LEFT (suapp.AREA_TOPIC.Area_Topic_Code, 2) WHERE suapp.FACULTY.Faculty_UUID = '".$Faculty_Research_Match."'");
{
$rows =$query->result_array();
return $rows; } } View: foreach($rows as $row){ print "<table>"; print "<tr><td>"; if ($i % 2 == 0) {
$bgColor = ' style="background-color:#ffffff;" '; } else {$bgColor = ' style="background-color:#e1edfe;" ';
}

print "<tr><td>" .$row['Research_Area']. "</td><td style='padding-left:5pt;padding-right:5pt'>" .$row['Area_Topic'].  "</td></tr>";

$i++; } ---------------------------------------------------------------------------------------------------------------------------------- Does anyone have any suggestions on how to pull distinct / unique Research Area from this beautiful mess OR how I can print/echo only the unique value for Research Area? Here is a link to working version. http://casweb.memphis.edu/suapp-research/index.php/post/show I appreciate any ideas and suggestions. Debra 0 Question by:Debra Turner • 26 • 24 • 5 58 Comments LVL 109 Expert Comment ID: 41864156 You might want to consider GROUP BY and ORDER BY clauses in the query. Please use var_export($rows) and post the sample data here, thanks.
0

Author Comment

ID: 41864373
Thank you Ray Paseur,

I tried the group by and order by, but they do not give me the result I need

I'm wondering about the array_unique function, but can't figure out how to use it.
debra
0

LVL 43

Expert Comment

ID: 41864403
Without really understanding your data structure, this is only a general suggestion relating to CodeIgniter, but I would suggest you make use of the Models in a more structured way. It should make everything easier to understand and code against, as you'd be using something closer to resembling real-world entities, rather than rows of data.

For example, if you have a several Topics for each ResearchArea, then you could create a Model to access that info:

class Research extends CI_Model {
public function GetResearchAreas()
{
$this->db ->select('ResearchId, ResearchArea') ->from('Research') ->order_by('ResearchArea'); return$this->db->get()->result('Research');
}

public function GetTopics()
{
$this->db ->distinct() ->select('TopicName') ->from('Topics') ->where('ResearchId',$this->ResearchId)
->order_by('TopicName);

return $this->db->get()->result(); } }  Now you can access the data in a more structured way: <?php foreach ($this->Research->GetResearchAreas() as $area): ?> <h2><?=$area->ResearchArea ?></h2>
<h3>Topics</h3>
<?php foreach ($area->GetTopics() as$topic): ?>
<p><?= $topic->TopicName ?></p> <?php endforeach; ?> <?php endforeach; ?>  Obviously you understand your data better than anyone, but the principles would still apply. You could do a DB query to get the distinct ResearchAreas. Loop that returned list, echoing out the relevant data, and then call a method to get all the child info, filtered by your$Faculty_Research_Match variable. You can even build your dataset at the Controller level, and then pass the whole thing into your view to keep them cleaner (best practice)

One other tip - as you're using codeigniter, it can often make your code easier to understand and maintain if you use the built in DB methods, so instead of passing in one beautiful mess of SQL, build it up in a more structured way:

$this->db ->select('*') ->from('suapp.FACULTY') ->join('suapp.RESEARCH_MATCH', 'suapp.RESEARCH_MATCH.Faculty_UUID = suapp.FACULTY.Faculty_UUID') ->join('suapp.AREA_TOPIC', 'suapp.AREA_TOPIC.Area_Topic_Code = RESEARCH_MATCH.Area_Topic_Code') ->join('suapp.RESEARCH_AREA', 'suapp.RESEARCH_AREA.Research_Area_Prefix = LEFT(suapp.AREA_TOPIC.Area_Topic_Code, 2)') ->where('suapp.FACULTY.Faculty_UUID',$Faculty_Research_Match);

return $this->db->get()->result();  Any questions, ask away :) 1 LVL 109 Expert Comment ID: 41864431 Please use var_export($rows) and post the sample data here, thanks.
It's a data-dependent problem, and we can't really test any solution without having some sample data.  PHP array_unique() certainly might be useful, but it would be helpful to see the input data.
0

Author Comment

ID: 41864460
Ray Paseur,
I am a struggling 'self-taught' php'er and I am not sure how to use the var_export($rows); (the reason behind me coming to experts such as yourself. After adding it to the 'view' in my application, this is the output... Is this helpful to you? Thank you for your willingness to help. debra ______________________________________________________________________ Faculty Members Reza Banai, Professor City and Regional Planning http://www.memphis.edu/planning/people/reza-banai.php rbanai@memphis.eduarray ( 0 => array ( 'First_Name' => 'Reza', 'Middle_Name' => '', 'Last_Name' => 'Banai', 'Department_Code' => 'PLAN', 'Department_Name' => 'City and Regional Planning', 'Faculty_UUID' => 'rbanai', 'Faculty_Email' => 'rbanai@memphis.edu', 'Faculty_Title' => 'Professor', 'Faculty_Profile_Link' => 'http://www.memphis.edu/planning/people/reza-banai.php', 'Faculty_Image' => NULL, 'Area_Topic_Code' => 'CD-f', 'Area_Topic' => 'Neighborhood Revitalization', 'Research_Area' => 'Community Development', 'Research_Area_Prefix' => 'CD', 'Research_Area_Description' => 'A description of the Research Topic presented (including methods used) by the SUAPP Faculty…', ), 1 => array ( 'First_Name' => 'Reza', 'Middle_Name' => '', 'Last_Name' => 'Banai', 'Department_Code' => 'PLAN', 'Department_Name' => 'City and Regional Planning', 'Faculty_UUID' => 'rbanai', 'Faculty_Email' => 'rbanai@memphis.edu', 'Faculty_Title' => 'Professor', 'Faculty_Profile_Link' => 'http://www.memphis.edu/planning/people/reza-banai.php', 'Faculty_Image' => NULL, 'Area_Topic_Code' => 'ED-d', 'Area_Topic' => 'Higher Education', 'Research_Area' => 'Education', 'Research_Area_Prefix' => 'ED', 'Research_Area_Description' => 'A description of the Research Topic presented (including methods used) by the SUAPP Faculty…', ), 2 => array ( 'First_Name' => 'Reza', 'Middle_Name' => '', 'Last_Name' => 'Banai', 'Department_Code' => 'PLAN', 'Department_Name' => 'City and Regional Planning', 'Faculty_UUID' => 'rbanai', 'Faculty_Email' => 'rbanai@memphis.edu', 'Faculty_Title' => 'Professor', 'Faculty_Profile_Link' => 'http://www.memphis.edu/planning/people/reza-banai.php', 'Faculty_Image' => NULL, 'Area_Topic_Code' => 'HO-e', 'Area_Topic' => 'Public Housing/Section 8', 'Research_Area' => 'Housing', 'Research_Area_Prefix' => 'HO', 'Research_Area_Description' => 'A description of the Research Topic presented (including methods used) by the SUAPP Faculty…', ), 3 => array ( 'First_Name' => 'Reza', 'Middle_Name' => '', 'Last_Name' => 'Banai', 'Department_Code' => 'PLAN', 'Department_Name' => 'City and Regional Planning', 'Faculty_UUID' => 'rbanai', 'Faculty_Email' => 'rbanai@memphis.edu', 'Faculty_Title' => 'Professor', 'Faculty_Profile_Link' => 'http://www.memphis.edu/planning/people/reza-banai.php', 'Faculty_Image' => NULL, 'Area_Topic_Code' => 'UD-a', 'Area_Topic' => 'Urban Design', 'Research_Area' => 'Urban Design', 'Research_Area_Prefix' => 'UD', 'Research_Area_Description' => 'A description of the Research Topic presented (including methods used) by the SUAPP Faculty…', ), 4 => array ( 'First_Name' => 'Reza', 'Middle_Name' => '', 'Last_Name' => 'Banai', 'Department_Code' => 'PLAN', 'Department_Name' => 'City and Regional Planning', 'Faculty_UUID' => 'rbanai', 'Faculty_Email' => 'rbanai@memphis.edu', 'Faculty_Title' => 'Professor', 'Faculty_Profile_Link' => 'http://www.memphis.edu/planning/people/reza-banai.php', 'Faculty_Image' => NULL, 'Area_Topic_Code' => 'UD-b', 'Area_Topic' => 'Growth Management', 'Research_Area' => 'Urban Design', 'Research_Area_Prefix' => 'UD', 'Research_Area_Description' => 'A description of the Research Topic presented (including methods used) by the SUAPP Faculty…', ), 5 => array ( 'First_Name' => 'Reza', 'Middle_Name' => '', 'Last_Name' => 'Banai', 'Department_Code' => 'PLAN', 'Department_Name' => 'City and Regional Planning', 'Faculty_UUID' => 'rbanai', 'Faculty_Email' => 'rbanai@memphis.edu', 'Faculty_Title' => 'Professor', 'Faculty_Profile_Link' => 'http://www.memphis.edu/planning/people/reza-banai.php', 'Faculty_Image' => NULL, 'Area_Topic_Code' => 'UD-c', 'Area_Topic' => 'Land Use/Zoning', 'Research_Area' => 'Urban Design', 'Research_Area_Prefix' => 'UD', 'Research_Area_Description' => 'A description of the Research Topic presented (including methods used) by the SUAPP Faculty…', ), 6 => array ( 'First_Name' => 'Reza', 'Middle_Name' => '', 'Last_Name' => 'Banai', 'Department_Code' => 'PLAN', 'Department_Name' => 'City and Regional Planning', 'Faculty_UUID' => 'rbanai', 'Faculty_Email' => 'rbanai@memphis.edu', 'Faculty_Title' => 'Professor', 'Faculty_Profile_Link' => 'http://www.memphis.edu/planning/people/reza-banai.php', 'Faculty_Image' => NULL, 'Area_Topic_Code' => 'UD-d', 'Area_Topic' => 'Regionalism', 'Research_Area' => 'Urban Design', 'Research_Area_Prefix' => 'UD', 'Research_Area_Description' => 'A description of the Research Topic presented (including methods used) by the SUAPP Faculty…', ), 7 => array ( 'First_Name' => 'Reza', 'Middle_Name' => '', 'Last_Name' => 'Banai', 'Department_Code' => 'PLAN', 'Department_Name' => 'City and Regional Planning', 'Faculty_UUID' => 'rbanai', 'Faculty_Email' => 'rbanai@memphis.edu', 'Faculty_Title' => 'Professor', 'Faculty_Profile_Link' => 'http://www.memphis.edu/planning/people/reza-banai.php', 'Faculty_Image' => NULL, 'Area_Topic_Code' => 'UD-e', 'Area_Topic' => 'Urban Sprawl', 'Research_Area' => 'Urban Design', 'Research_Area_Prefix' => 'UD', 'Research_Area_Description' => 'A description of the Research Topic presented (including methods used) by the SUAPP Faculty…', ), 8 => array ( 'First_Name' => 'Reza', 'Middle_Name' => '', 'Last_Name' => 'Banai', 'Department_Code' => 'PLAN', 'Department_Name' => 'City and Regional Planning', 'Faculty_UUID' => 'rbanai', 'Faculty_Email' => 'rbanai@memphis.edu', 'Faculty_Title' => 'Professor', 'Faculty_Profile_Link' => 'http://www.memphis.edu/planning/people/reza-banai.php', 'Faculty_Image' => NULL, 'Area_Topic_Code' => 'UD-f', 'Area_Topic' => 'Sustainability', 'Research_Area' => 'Urban Design', 'Research_Area_Prefix' => 'UD', 'Research_Area_Description' => 'A description of the Research Topic presented (including methods used) by the SUAPP Faculty…', ), 9 => array ( 'First_Name' => 'Reza', 'Middle_Name' => '', 'Last_Name' => 'Banai', 'Department_Code' => 'PLAN', 'Department_Name' => 'City and Regional Planning', 'Faculty_UUID' => 'rbanai', 'Faculty_Email' => 'rbanai@memphis.edu', 'Faculty_Title' => 'Professor', 'Faculty_Profile_Link' => 'http://www.memphis.edu/planning/people/reza-banai.php', 'Faculty_Image' => NULL, 'Area_Topic_Code' => 'UD-g', 'Area_Topic' => 'Transportation', 'Research_Area' => 'Urban Design', 'Research_Area_Prefix' => 'UD', 'Research_Area_Description' => 'A description of the Research Topic presented (including methods used) by the SUAPP Faculty…', ), 10 => array ( 'First_Name' => 'Reza', 'Middle_Name' => '', 'Last_Name' => 'Banai', 'Department_Code' => 'PLAN', 'Department_Name' => 'City and Regional Planning', 'Faculty_UUID' => 'rbanai', 'Faculty_Email' => 'rbanai@memphis.edu', 'Faculty_Title' => 'Professor', 'Faculty_Profile_Link' => 'http://www.memphis.edu/planning/people/reza-banai.php', 'Faculty_Image' => NULL, 'Area_Topic_Code' => 'UD-h', 'Area_Topic' => 'Food Deserts/Food Systems', 'Research_Area' => 'Urban Design', 'Research_Area_Prefix' => 'UD', 'Research_Area_Description' => 'A description of the Research Topic presented (including methods used) by the SUAPP Faculty…', ), ) 0 LVL 43 Expert Comment ID: 41864484 There's a LOT of duplicate and unnecessary data there Debra considering what you need to output. This is a side-effect of trying to flatten your data using one big query with lots of JOINs, and also by not filtering your output - SELECT * is often a bad idea for this very reason. 1 Author Comment ID: 41864487 Yes, Definitely too many duplicates! I'm going back to the drawing board and try your model for query structure. Be back in a little bit. Thank you. debra 0 LVL 43 Expert Comment ID: 41864496 Try to create your Models to minimise the duplicate data. Looking at the data you've just posted, it seems like the first 10 fields are all duplicates, so that would be a great place to start. If you need that data then pull out those details once for each unique set, and then use that data to get the 'child' records. 0 Author Comment ID: 41864578 Chris Stanyon, Can I have more than one function in the model file? And if so, Can I use the selected faculty info that I retrieve through the first query/function (which would be the faculty id) in the second function of the model to pull the 'child' records (as in a "where faculty_id = ' .$faculty_id. '") ?

Debra
0

LVL 43

Expert Comment

ID: 41864594
Absolutely. That's the beauty of it. Imagine something like this:

class Faculty extends CI_Model {
public function GetFaculty($id) {$this->db
->select('firstname, lastname, faculty_id')
->from('Faculty')
->where('faculty_id', $id); return$this->db->get()->result('Faculty');
}

public function GetChildRecords()
{
$this->db ->select('Column1, Column2, Column3') ->from('ChildTable') ->where('faculty_id',$this->faculty_id)
->order_by('Column1');

return $this->db->get()->result(); } }  Now you can do something like: $member = $this->faculty->GetFaculty('someID'); echo$member->firstname;
echo $member->lastname; foreach ($member->GetChildRecords() as $record): echo$record->Column1;
echo $record->Column2; echo$record->Column3;
endforeach;

1

LVL 109

Expert Comment

ID: 41864955
I think Chris has you on a better track, using the framework instead of PHP.  But that said, here is a PHP solution for this data set.  I lined up the information to make for better visualization of the similarities and differences.  The formatting has no programmatic meaning; it's just for human eyes.
https://iconoun.com/demo/temp_debra.php
<?php // demo/temp_debra.php
/**
* https://www.experts-exchange.com/questions/28979613/Printing-unique-distinct-values-PHP-MSSQL-Codeigniter.html#a41864373
*/
error_reporting(E_ALL);

$response = array ( 0 => array ( 'First_Name' => 'Reza', 'Middle_Name' => '', 'Last_Name' => 'Banai', 'Department_Code' => 'PLAN', 'Department_Name' => 'City and Regional Planning', 'Faculty_UUID' => 'rbanai', 'Faculty_Email' => 'rbanai@memphis.edu', 'Faculty_Title' => 'Professor', 'Faculty_Profile_Link' => 'http://www.memphis.edu/planning/people/reza-banai.php', 'Faculty_Image' => NULL, 'Area_Topic_Code' => 'CD-f', 'Area_Topic' => 'Neighborhood Revitalization', 'Research_Area' => 'Community Development', 'Research_Area_Prefix' => 'CD', 'Research_Area_Description' => 'A description of the Research Topic presented (including methods used) by the SUAPP Faculty…', ) , 1 => array ( 'First_Name' => 'Reza', 'Middle_Name' => '', 'Last_Name' => 'Banai', 'Department_Code' => 'PLAN', 'Department_Name' => 'City and Regional Planning', 'Faculty_UUID' => 'rbanai', 'Faculty_Email' => 'rbanai@memphis.edu', 'Faculty_Title' => 'Professor', 'Faculty_Profile_Link' => 'http://www.memphis.edu/planning/people/reza-banai.php', 'Faculty_Image' => NULL, 'Area_Topic_Code' => 'ED-d', 'Area_Topic' => 'Higher Education', 'Research_Area' => 'Education', 'Research_Area_Prefix' => 'ED', 'Research_Area_Description' => 'A description of the Research Topic presented (including methods used) by the SUAPP Faculty…', ) , 2 => array ( 'First_Name' => 'Reza', 'Middle_Name' => '', 'Last_Name' => 'Banai', 'Department_Code' => 'PLAN', 'Department_Name' => 'City and Regional Planning', 'Faculty_UUID' => 'rbanai', 'Faculty_Email' => 'rbanai@memphis.edu', 'Faculty_Title' => 'Professor', 'Faculty_Profile_Link' => 'http://www.memphis.edu/planning/people/reza-banai.php', 'Faculty_Image' => NULL, 'Area_Topic_Code' => 'HO-e', 'Area_Topic' => 'Public Housing/Section 8', 'Research_Area' => 'Housing', 'Research_Area_Prefix' => 'HO', 'Research_Area_Description' => 'A description of the Research Topic presented (including methods used) by the SUAPP Faculty…', ) , 3 => array ( 'First_Name' => 'Reza', 'Middle_Name' => '', 'Last_Name' => 'Banai', 'Department_Code' => 'PLAN', 'Department_Name' => 'City and Regional Planning', 'Faculty_UUID' => 'rbanai', 'Faculty_Email' => 'rbanai@memphis.edu', 'Faculty_Title' => 'Professor', 'Faculty_Profile_Link' => 'http://www.memphis.edu/planning/people/reza-banai.php', 'Faculty_Image' => NULL, 'Area_Topic_Code' => 'UD-a', 'Area_Topic' => 'Urban Design', 'Research_Area' => 'Urban Design', 'Research_Area_Prefix' => 'UD', 'Research_Area_Description' => 'A description of the Research Topic presented (including methods used) by the SUAPP Faculty…', ) , 4 => array ( 'First_Name' => 'Reza', 'Middle_Name' => '', 'Last_Name' => 'Banai', 'Department_Code' => 'PLAN', 'Department_Name' => 'City and Regional Planning', 'Faculty_UUID' => 'rbanai', 'Faculty_Email' => 'rbanai@memphis.edu', 'Faculty_Title' => 'Professor', 'Faculty_Profile_Link' => 'http://www.memphis.edu/planning/people/reza-banai.php', 'Faculty_Image' => NULL, 'Area_Topic_Code' => 'UD-b', 'Area_Topic' => 'Growth Management', 'Research_Area' => 'Urban Design', 'Research_Area_Prefix' => 'UD', 'Research_Area_Description' => 'A description of the Research Topic presented (including methods used) by the SUAPP Faculty…', ) , 5 => array ( 'First_Name' => 'Reza', 'Middle_Name' => '', 'Last_Name' => 'Banai', 'Department_Code' => 'PLAN', 'Department_Name' => 'City and Regional Planning', 'Faculty_UUID' => 'rbanai', 'Faculty_Email' => 'rbanai@memphis.edu', 'Faculty_Title' => 'Professor', 'Faculty_Profile_Link' => 'http://www.memphis.edu/planning/people/reza-banai.php', 'Faculty_Image' => NULL, 'Area_Topic_Code' => 'UD-c', 'Area_Topic' => 'Land Use/Zoning', 'Research_Area' => 'Urban Design', 'Research_Area_Prefix' => 'UD', 'Research_Area_Description' => 'A description of the Research Topic presented (including methods used) by the SUAPP Faculty…', ) , 6 => array ( 'First_Name' => 'Reza', 'Middle_Name' => '', 'Last_Name' => 'Banai', 'Department_Code' => 'PLAN', 'Department_Name' => 'City and Regional Planning', 'Faculty_UUID' => 'rbanai', 'Faculty_Email' => 'rbanai@memphis.edu', 'Faculty_Title' => 'Professor', 'Faculty_Profile_Link' => 'http://www.memphis.edu/planning/people/reza-banai.php', 'Faculty_Image' => NULL, 'Area_Topic_Code' => 'UD-d', 'Area_Topic' => 'Regionalism', 'Research_Area' => 'Urban Design', 'Research_Area_Prefix' => 'UD', 'Research_Area_Description' => 'A description of the Research Topic presented (including methods used) by the SUAPP Faculty…', ) , 7 => array ( 'First_Name' => 'Reza', 'Middle_Name' => '', 'Last_Name' => 'Banai', 'Department_Code' => 'PLAN', 'Department_Name' => 'City and Regional Planning', 'Faculty_UUID' => 'rbanai', 'Faculty_Email' => 'rbanai@memphis.edu', 'Faculty_Title' => 'Professor', 'Faculty_Profile_Link' => 'http://www.memphis.edu/planning/people/reza-banai.php', 'Faculty_Image' => NULL, 'Area_Topic_Code' => 'UD-e', 'Area_Topic' => 'Urban Sprawl', 'Research_Area' => 'Urban Design', 'Research_Area_Prefix' => 'UD', 'Research_Area_Description' => 'A description of the Research Topic presented (including methods used) by the SUAPP Faculty…', ) , 8 => array ( 'First_Name' => 'Reza', 'Middle_Name' => '', 'Last_Name' => 'Banai', 'Department_Code' => 'PLAN', 'Department_Name' => 'City and Regional Planning', 'Faculty_UUID' => 'rbanai', 'Faculty_Email' => 'rbanai@memphis.edu', 'Faculty_Title' => 'Professor', 'Faculty_Profile_Link' => 'http://www.memphis.edu/planning/people/reza-banai.php', 'Faculty_Image' => NULL, 'Area_Topic_Code' => 'UD-f', 'Area_Topic' => 'Sustainability', 'Research_Area' => 'Urban Design', 'Research_Area_Prefix' => 'UD', 'Research_Area_Description' => 'A description of the Research Topic presented (including methods used) by the SUAPP Faculty…', ) , 9 => array ( 'First_Name' => 'Reza', 'Middle_Name' => '', 'Last_Name' => 'Banai', 'Department_Code' => 'PLAN', 'Department_Name' => 'City and Regional Planning', 'Faculty_UUID' => 'rbanai', 'Faculty_Email' => 'rbanai@memphis.edu', 'Faculty_Title' => 'Professor', 'Faculty_Profile_Link' => 'http://www.memphis.edu/planning/people/reza-banai.php', 'Faculty_Image' => NULL, 'Area_Topic_Code' => 'UD-g', 'Area_Topic' => 'Transportation', 'Research_Area' => 'Urban Design', 'Research_Area_Prefix' => 'UD', 'Research_Area_Description' => 'A description of the Research Topic presented (including methods used) by the SUAPP Faculty…', ) , 10 => array ( 'First_Name' => 'Reza', 'Middle_Name' => '', 'Last_Name' => 'Banai', 'Department_Code' => 'PLAN', 'Department_Name' => 'City and Regional Planning', 'Faculty_UUID' => 'rbanai', 'Faculty_Email' => 'rbanai@memphis.edu', 'Faculty_Title' => 'Professor', 'Faculty_Profile_Link' => 'http://www.memphis.edu/planning/people/reza-banai.php', 'Faculty_Image' => NULL, 'Area_Topic_Code' => 'UD-h', 'Area_Topic' => 'Food Deserts/Food Systems', 'Research_Area' => 'Urban Design', 'Research_Area_Prefix' => 'UD', 'Research_Area_Description' => 'A description of the Research Topic presented (including methods used) by the SUAPP Faculty…', ), ) ; echo '<pre>'; // REPLICATED INFORMATION$basis = $response[0];$other = $response[1]; print_r(array_intersect($other, $basis)); echo PHP_EOL; // UNIQUE INFORMATION$basis = $response[0]; unset($response[0]);
foreach ($response as$key => $array) { print_r(array_diff_assoc($array, $basis)); } echo PHP_EOL;  Outputs: Array ( [First_Name] => Reza [Middle_Name] => [Last_Name] => Banai [Department_Code] => PLAN [Department_Name] => City and Regional Planning [Faculty_UUID] => rbanai [Faculty_Email] => rbanai@memphis.edu [Faculty_Title] => Professor [Faculty_Profile_Link] => http://www.memphis.edu/planning/people/reza-banai.php [Faculty_Image] => [Research_Area_Description] => A description of the Research Topic presented (including methods used) by the SUAPP Faculty… ) Array ( [Area_Topic_Code] => ED-d [Area_Topic] => Higher Education [Research_Area] => Education [Research_Area_Prefix] => ED ) Array ( [Area_Topic_Code] => HO-e [Area_Topic] => Public Housing/Section 8 [Research_Area] => Housing [Research_Area_Prefix] => HO ) Array ( [Area_Topic_Code] => UD-a [Area_Topic] => Urban Design [Research_Area] => Urban Design [Research_Area_Prefix] => UD ) Array ( [Area_Topic_Code] => UD-b [Area_Topic] => Growth Management [Research_Area] => Urban Design [Research_Area_Prefix] => UD ) Array ( [Area_Topic_Code] => UD-c [Area_Topic] => Land Use/Zoning [Research_Area] => Urban Design [Research_Area_Prefix] => UD ) Array ( [Area_Topic_Code] => UD-d [Area_Topic] => Regionalism [Research_Area] => Urban Design [Research_Area_Prefix] => UD ) Array ( [Area_Topic_Code] => UD-e [Area_Topic] => Urban Sprawl [Research_Area] => Urban Design [Research_Area_Prefix] => UD ) Array ( [Area_Topic_Code] => UD-f [Area_Topic] => Sustainability [Research_Area] => Urban Design [Research_Area_Prefix] => UD ) Array ( [Area_Topic_Code] => UD-g [Area_Topic] => Transportation [Research_Area] => Urban Design [Research_Area_Prefix] => UD ) Array ( [Area_Topic_Code] => UD-h [Area_Topic] => Food Deserts/Food Systems [Research_Area] => Urban Design [Research_Area_Prefix] => UD )  1 LVL 109 Expert Comment ID: 41865091 Afterthought... If you're interested in learning more about PHP, this article can help you find well-vetted learning examples, and perhaps more importantly, can help you avoid the many obsolete, misleading, and dangerous examples that still litter the internet. Just skip over the parts you already know. :-) 0 Author Comment ID: 41866984 Thank you both Ray and Chris for your expertise. I'm working on these right now to see if I can make it work correctly! I will comment back in a few minutes. Happy Monday! Debra 0 Author Comment ID: 41869016 Hello Ray and Chris, I have been reading some of the articles and trying to get the functions set up in the Codeigniter model and I have to admit that I am so confused. You have both been extremely helpful and I know that I am not on your level of programming! I have 4 tables (I've attached a screenshot of their set up that will give you more of an idea of what I am trying to accomplish) The following is what I would like to present when the user selects the faculty member from dropdown menu. __________________________________________________________________________ Faculty Information and Research David Cox, Professor Public and Nonprofit Administration http://www.memphis.edu/padm/people/david-cox.php davidcox@memphis.edu Community Development: • Community-Based Development • Community Organizing • Entrepreneurship/Social Entrepreneurship • Neighborhood Revitalization Education: • Higher Education Government: • Leadership and Management • Public Finance _____________________________________________________________________ As I'm working with the examples you have given me, I'm not sure about where some of the variables go in the public functions in order for my output to be like the above example. Since there are several topics under each research area, I need to only pull unique values. Ray, can you please explain this a little more to me? The following example is from your previous post and I'm not sure how to use this in my application. // REPLICATED INFORMATION$basis = $response[0];$other = $response[1]; print_r(array_intersect($other, $basis)); echo PHP_EOL; // UNIQUE INFORMATION$basis = $response[0]; unset($response[0]);
foreach ($response as$key => $array) { print_r(array_diff_assoc($array, $basis)); } echo PHP_EOL;  __________________________________________________________________________________________________ Chris This is what I have pulled together from the example you posted. I may very well be way off base, but I am trying... I've added 3 public functions in the CI model but I'm not sure how to determine the result (like you have 'Faculty' in the GetFaculty function) class Faculty extends CI_Model { //* get all faculty information for selected faculty member public function GetFaculty($id)

{
$this->db ->select('First_Name, Last_Name, Department_Name, Faculty_Email, Faculty_Title, Faculty_Profile_Link, Faculty_UUID') ->from('suapp.FACULTY') ->where('Faculty_UUID',$id);

return $this->db->get()->result('Faculty'); } //* get research area topics for selected faculty member public function GetChildRecords() {$this->db
->select('Faculty_UUID','Area_Topic_Code')
->from('suapp.RESEARCH_MATCH')
->where('Faculty_UUID', $this->Faculty_UUID) ->order_by('Faculty_UUID','Area_Topic_Code'); return$this->db->get()->result(Area_Topic_Code);
}

//* get research area that matches research area topic by matching Area_Topic_Code with Research_Area_Prefix

public function GetMatchingRecords()
{
$this->db ->select('*') ->from('suapp.RESEARCH_AREA','suapp.AREA_TOPIC') ->where('suapp.RESEARCH_AREA.Research_Area_Prefix',$this->(suapp.AREA_TOPIC.Area_Topic_Code, 2))
->order_by('Area_Topic_Code');

return $this->db->get()->result(); } }  You will both probably hate me after this long post and I apologize ahead of time. As you can see, I am struggling with this. Thank you, Debrasuapp-research-dashboard-example-deb.pdf 0 LVL 109 Expert Comment ID: 41869033 Hi, Debra. Like I said, I think Chris has a better path forward, using the Model in the framework. From a purely PHP perspective, you can find all of the PHP functions documented in the online man pages. Examples: http://php.net/manual/en/function.array-intersect.php http://php.net/manual/en/function.array-diff-assoc.php 1 Author Comment ID: 41869186 Chris, I'm looking at some of the CI query builders to use in my functions. I think they will be helpful in finding the distinct values I need. One helper is "$this->db->distinct()"
and it might do what the array-diff-assoc and array-intersect that Ray suggested in php.

There are a lot of other query builders and helpers I am trying. (http://www.codeigniter.com/user_guide/database/query_builder.html#looking-for-specific-data )

Still struggling, though.
debra
0

LVL 43

Expert Comment

ID: 41869494
Hi Debra,

Had a quick look through your DB Schema but I'll need to have a proper look tomorrow when I have more time.
0

LVL 43

Expert Comment

ID: 41869999
Ok Debra,

I think I've got my head around your DB Schema and it's relationships. Since the Research Areas are linked on a substring of the Area Code, we'll need to write that part of the class in proper SQL, as CodeIgniters Query Builder doesn't seem to like that (never tried it before!).

If I've understood correctly, then your Faculty Model should look something like this:

class Faculty extends CI_Model {

function __construct()
{
parent::__construct();
}

public function GetFaculty($id) {$this->db
->select('Faculty_UUID, First_Name, Last_Name, Faculty_Title, Faculty_Profile_Link, Faculty_Email, Department_Name')
->from('suapp.FACULTY')
->where('Faculty_UUID', $id); return$this->db->get()->row(0, 'Faculty');
}

public function GetResearchAreas()
{
$str = " SELECT DISTINCT Research_Area, Research_Area_Prefix FROM (suapp.RESEARCH_MATCH) JOIN suapp.AREA_TOPIC ON suapp.RESEARCH_MATCH.Area_Topic_Code = suapp.AREA_TOPIC.Area_Topic_Code JOIN suapp.RESEARCH_AREA ON LEFT(suapp.AREA_TOPIC.Area_Topic_Code, 2) = suapp.RESEARCH_AREA.Research_Area_Prefix WHERE suapp.RESEARCH_MATCH.Faculty_UUID = '%s'";$qryStr = sprintf($str,$this->Faculty_UUID);

$query =$this->db->query($qryStr); return$query->result('Faculty');
}

public function GetResearchTopics()
{
$this->db ->select('Area_Topic') ->from('suapp.AREA_TOPIC') ->where('LEFT(Area_Topic_Code, 2) =',$this->Research_Area_Prefix);

return $this->db->get()->result('Faculty'); } }  Your controller code would then look something like this: $data = new stdClass();

if ($this->input->post('Faculty_Research_Match')) {$data->faculty = $this->faculty->GetFaculty($this->input->post('Faculty_Research_Match'));
}

$this->load->view('viewName',$data);

And finally, your view could look something like this:

<!DOCTYPE html>
<html lang="en">
<meta charset="utf-8">
<title>Chris Stanyon</title>
<body>
<?php if (isset($faculty)): ?> <h1>Faculty Information and Research</h1> <p><?php echo$faculty->First_Name ?> <?php echo $faculty->Last_Name ?>, <?php echo$faculty->Faculty_Title ?></p>
<p><?php echo $faculty->Department_Name ?></p> <p><?php echo anchor($faculty->Faculty_Profile_Link) ?></p>
<p><?php echo $faculty->Faculty_Email ?></p> <div> <?php foreach ($faculty->GetAreas() as $area): ?> <h2><?php echo$area->Research_Area ?></h2>
<ul>
<?php foreach ($area->GetResearchTopics() as$researchTopic): ?>
<li><?php echo $researchTopic->Area_Topic ?></li> <?php endforeach; ?> </ul> <?php endforeach; ?> </div> <?php endif; ?> </body> </html>  Have a read through that and let me know if you'd like anything explaining in more detail. The concept of doing things like this is generally known as FAT MODEL / SKINNY CONTROLLER. Basically, any of your Data CRUD and Logic goes in a model (making it a Fat Model), which keeps it out of the controller (Skinny Controller) You can of course add even more methods to the Model, such as getting all your Faculty members along with their Departments from the DB. Your form dropdown, where you select the Faculty Member could then simply be created in the view with: echo form_dropdown('Faculty_Research_Match',$members);

Hope it all makes sense :)
2

Author Comment

ID: 41874183
Chris,
I'm playing with this today. I will let you know how it goes. You are a huge help and I am learning much from all of the articles and examples given!

I will definitely get back with you.

Thank you,
Debra
1

Author Comment

ID: 41877945
Hi Chris,
I am getting an error for Undefined property.
This is the link  - this page is good, but when you choose from the dropdown, then there is an error.

http://casweb.memphis.edu/suapp-research/index.php/post/show

Do you have any suggestions?
I appreciate any help or advice.
Debra

This is the Controller:

<?php

defined('BASEPATH') OR exit('No direct script access allowed');

class Match_faculty_research_controller extends CI_Controller {

public function match_it_now() {
$data = new stdClass(); if ($this->input->post('Faculty_Research_Match'))
{
$data->faculty =$this->faculty->GetFaculty($this->input->post('Faculty_Research_Match')); }$this->load->view('final_match_faculty_view', $data); } }  ______________________________________________________________________________________ This is the model. <?php defined('BASEPATH') OR exit('No direct script access allowed'); class Faculty extends CI_Model { function __construct() { parent::__construct(); } public function GetFaculty($Faculty_Research_Match)
{
$this->db ->select('Faculty_UUID, First_Name, Last_Name, Faculty_Title, Faculty_Profile_Link, Faculty_Email, Department_Name') ->from('suapp.FACULTY') ->where('Faculty_UUID',$Faculty_Research_Match);

return $this->db->get()->row(0, 'Faculty'); } public function GetResearchAreas() {$str = "
SELECT DISTINCT Research_Area, Research_Area_Prefix
FROM (suapp.RESEARCH_MATCH)
JOIN suapp.AREA_TOPIC ON suapp.RESEARCH_MATCH.Area_Topic_Code = suapp.AREA_TOPIC.Area_Topic_Code
JOIN suapp.RESEARCH_AREA ON LEFT(suapp.AREA_TOPIC.Area_Topic_Code, 2) = suapp.RESEARCH_AREA.Research_Area_Prefix
WHERE suapp.RESEARCH_MATCH.Faculty_UUID = '%s'";

$qryStr = sprintf($str, $this->Faculty_UUID);$query = $this->db->query($qryStr);

return $query->result('Faculty'); } public function GetResearchTopics() {$this->db
->select('Area_Topic')
->from('suapp.AREA_TOPIC')
->where('LEFT(Area_Topic_Code, 2) =', $this->Research_Area_Prefix); return$this->db->get()->result('Faculty');
}

}


_____________________________________________________________________________________

This is the View:

<!DOCTYPE html>
<html lang="en">
<meta charset="utf-8">
<title>Chris Stanyon</title>
<body>
<?php if (isset($faculty)): ?> <h1>Faculty Information and Research</h1> <p><?php echo$faculty->First_Name ?> <?php echo $faculty->Last_Name ?>, <?php echo$faculty->Faculty_Title ?></p>
<p><?php echo $faculty->Department_Name ?></p> <p><?php echo anchor($faculty->Faculty_Profile_Link) ?></p>
<p><?php echo $faculty->Faculty_Email ?></p> <div> <?php foreach ($faculty->GetAreas() as $area): ?> <h2><?php echo$area->Research_Area ?></h2>
<ul>
<?php foreach ($area->GetResearchTopics() as$researchTopic): ?>
<li><?php echo $researchTopic->Area_Topic ?></li> <?php endforeach; ?> </ul> <?php endforeach; ?> </div> <?php endif; ?> </body> </html>  0 LVL 43 Expert Comment ID: 41877995 Hi Debra, I think it's because your model isn't loaded, so your Controller is not aware of$this->faculty. There are 2 or 3 ways of loading your model, but the easiest is to just add a call into your controller method:

public function match_it_now() {
$this->load->model('faculty');$data = new stdClass();

if ($this->input->post('Faculty_Research_Match')) ...  If you find yourself loading the same mode in several of your Controller methods, then you can load in the Controller constructor instead. If you want your Model to be available to your entire application (all the Controllers), then you can add it to the autoload config file. Give that a go and let me know how it goes 1 Author Comment ID: 41878896 So now that I've loaded the model. I had an error that it could not find the model page. I reviewed the CI manual for naming pages and realized that the page should be the same name as the model class which is 'Faculty'. So I renamed the page. I now have an error for the$db - I've attached a screenshot.

Should all of the '$faculty' names be '$Faculty'?
An example in the controller is: $data->faculty =$this->faculty->GetFaculty($this->input->post('Faculty_Research_Match')); Can I talk to you on the phone? I'm so confused and really need to get this working. I appreciate all of your help and know there is probably a fee. Not sure how to go about it though. debra Doc1.docx 0 LVL 43 Expert Comment ID: 41879178 Hi Debra, Unfortunately, I don't take phone calls for EE Questions. There area couple of moving parts that I've failed to comment on - I've made the assumption that you already had them in place, so apologies for that. Yes you are right. CodeIgniter works in part on Naming Conventions, so you need to name your model file the same as your Model class. Models should be named with a leading Capital letter (no spaces). so in your case Faculty. This means that the model file needs be called Faculty.php (in the Models folder), and in order to use that model, you need to load it:$this->load->model('faculty');

You can then access the members of that model like so $this->faculty->someMethod(); The error you're getting regarding the$db is because you're not loading the database class, so CodeIgniter is not aware of $this->db. CodeIgniter works on a modular basis, meaning that you have to load the parts you need. As I said in my previous comment, you can load these parts (Models, Classes, Helpers etc) in different ways, depending on how you use them (method / class ctor / autoload). If I'm working on a project that needs database access I tend to include the database class in the autoload config (https://www.codeigniter.com/user_guide/general/autoloader.html). That means that I'll have access to$this->db through my entire application. If you only need database access for your specific model, then you can load it in the constructor of the Faculty model:

class Faculty extends CI_Model {
function __construct()
{
parent::__construct();
$this->load->database(); } ...  Doing this means that all the members of your Faculty model will now be aware of$this->db.

It looks like you're very nearly there - it's just a couple of small CodeIgniter 'gotchas', but once you get your head around them, it all does start to make more sense - honest :)
1

Author Comment

ID: 41879347
Yes, I had the database loaded in the first draft of this application and neglected to add in the current version you have been helping with.

It's pulling the correct information now.

I read the loader page you linked to in the last message and was able to add the anchor and mailto helpers. Thank you for that!!!

there was an error about undefined stdClass in the controller.  I don't really understand the stdClass issue, and I don't find anything in the Codeigniter manual for it.
So I added the GetResearchAreas and GetResearchTopics methods in the Controller file (like you had the GetFaculty method) and the error went away, BUT now a new one...
New Error;

A PHP Error was encountered

Severity: Notice

Message: Undefined property: Match_faculty_research_controller::$Faculty_UUID Filename: core/Model.php Line Number: 77 Backtrace: File: C:\inetpub\wwwroot\suapp-research\models\Faculty.php Line: 32 Function: __get File: C:\inetpub\wwwroot\suapp-research\controllers\Match_faculty_research_controller.php Line: 16 Function: GetResearchAreas File: C:\inetpub\wwwroot\suapp-research\index.php Line: 315 Function: require_once A Database Error Occurred Error Number: 42000/102 [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Incorrect syntax near ')'. SELECT DISTINCT Research_Area, Research_Area_Prefix FROM (suapp.RESEARCH_MATCH) JOIN suapp.AREA_TOPIC ON suapp.RESEARCH_MATCH.Area_Topic_Code = suapp.AREA_TOPIC.Area_Topic_Code JOIN suapp.RESEARCH_AREA ON LEFT(suapp.AREA_TOPIC.Area_Topic_Code, 2) = suapp.RESEARCH_AREA.Research_Area_Prefix WHERE suapp.RESEARCH_MATCH.Faculty_UUID = '' Filename: C:/inetpub/codeigniter/system/database/DB_driver.php Line Number: 691 Controller File <?php defined('BASEPATH') OR exit('No direct script access allowed'); class Match_faculty_research_controller extends CI_Controller { public function match_it_now() {$this->load->model('faculty');

$data = new stdClass(); if ($this->input->post('Faculty_Research_Match'))
{
$data->faculty =$this->faculty->GetFaculty($this->input->post('Faculty_Research_Match'));$data->faculty = $this->faculty->GetResearchAreas($this->input->post('area'));
$data->faculty =$this->faculty->GetResearchTopics($this->input->post('researchTopic')); }$this->load->view('final_match_faculty_view', $data); } }  Model File <?php defined('BASEPATH') OR exit('No direct script access allowed'); class Faculty extends CI_Model { function __construct() { parent::__construct();$this->load->database();
$this->load->helper('url'); } public function GetFaculty($Faculty_Research_Match)
{
$this->db ->select('Faculty_UUID, First_Name, Last_Name, Faculty_Title, Faculty_Profile_Link, Faculty_Email, Department_Name') ->from('suapp.FACULTY') ->where('Faculty_UUID',$Faculty_Research_Match);

return $this->db->get()->row(0, 'Faculty'); } public function GetResearchAreas() {$str = "
SELECT DISTINCT Research_Area, Research_Area_Prefix
FROM (suapp.RESEARCH_MATCH)
JOIN suapp.AREA_TOPIC ON suapp.RESEARCH_MATCH.Area_Topic_Code = suapp.AREA_TOPIC.Area_Topic_Code
JOIN suapp.RESEARCH_AREA ON LEFT(suapp.AREA_TOPIC.Area_Topic_Code, 2) = suapp.RESEARCH_AREA.Research_Area_Prefix
WHERE suapp.RESEARCH_MATCH.Faculty_UUID = '%s'";

$qryStr = sprintf($str, $this->Faculty_UUID);$query = $this->db->query($qryStr);

return $query->result('Faculty'); } public function GetResearchTopics() {$this->db
->select('Area_Topic')
->from('suapp.AREA_TOPIC')
->where('LEFT(Area_Topic_Code, 2) =', $this->Research_Area_Prefix); return$this->db->get()->result('Faculty');
}

}


View File

<html lang="en">
<meta charset="utf-8">
<title>Chris Stanyon</title>
<body>
<?php if (isset($faculty)): ?> <h1>Faculty Information and Research</h1> <p><?php echo$faculty->First_Name ?> <?php echo $faculty->Last_Name ?>, <?php echo$faculty->Faculty_Title ?><br />
<?php echo $faculty->Department_Name ?><br /> <?php echo anchor($faculty->Faculty_Profile_Link) ?><br />
<?php echo mailto($faculty->Faculty_Email) ?></p> <div> <?php foreach ($faculty->GetResearchAreas() as $area): ?> <h2><?php echo$area->Research_Area ?></h2>
<ul>
<?php foreach ($area->GetResearchTopics() as$researchTopic): ?>
<li><?php echo $researchTopic->Area_Topic ?></li> <?php endforeach; ?> </ul> <?php endforeach; ?> </div> <?php endif; ?> </body> </html>  Any ideas? Debra 0 LVL 43 Expert Comment ID: 41879490 Hi Debra, stdClass is Predefined PHP class and has nothing to do specifically with CodeIgniter. It's basically a generic, empty class (don't worry about it for now). It's unlikely you would have got an undefined stdClass error. You would have possibly got an undefined property stdClass::$someProperty error, but without you showing the exact error, I can't really advise on what's gone wrong.

Adding the GetResearchAreas() and GetResearchTopics() method calls directly to the Controller won't work (as you've already discovered). In the Controller, you only need to call GetFaculty() and pass in the form field value (Faculty_Research_Match). Once you've got that, the result get's passed into your view as part of the $data stdClass object and it's from there that you need to call the other 2 methods. If you post the original stdClass error, we can ge to the bottom of it - we need exact details, not just an overview :) I'm sure it's something small that's stopping this from working. 1 Author Comment ID: 41879502 This is the error. Thanks! An uncaught Exception was encountered Type: Error Message: Call to undefined method stdClass::GetResearchAreas() Filename: C:\inetpub\wwwroot\suapp-research\views\final_match_faculty_view.php Line Number: 17 Backtrace: File: C:\inetpub\wwwroot\suapp-research\controllers\Match_faculty_research_controller.php Line: 19 Function: view File: C:\inetpub\wwwroot\suapp-research\index.php Line: 315 Function: require_once 0 LVL 43 Expert Comment ID: 41879524 Hmmm. That looks like your call to GetFaculty() is returning a stdClass object instead of a Faculty object, but I can't see why. Can you just change your controller method to the following and post the results: public function match_it_now() {$this->load->model('faculty');
$data = new stdClass(); if ($this->input->post('Faculty_Research_Match')) {
$data->faculty =$this->faculty->GetFaculty($this->input->post('Faculty_Research_Match')); } var_dump($data);
}

0

Author Comment

ID: 41879536
Here it is...

object(stdClass)#16 (1) { ["faculty"]=> object(stdClass)#18 (7) { ["Faculty_UUID"]=> string(6) "rbanai" ["First_Name"]=> string(4) "Reza" ["Last_Name"]=> string(5) "Banai" ["Faculty_Title"]=> string(9) "Professor" ["Faculty_Profile_Link"]=> string(53) "http://www.memphis.edu/planning/people/reza-banai.php" ["Faculty_Email"]=> string(18) "rbanai@memphis.edu" ["Department_Name"]=> string(26) "City and Regional Planning" } }

0

LVL 43

Expert Comment

ID: 41879576
OK. Something's not working. This line in your GetFaculty() model:

return $this->db->get()->row(0, 'Faculty'); Should return the database query result as an instance of the Faculty model, but your datadump indicates that it's returning an instance of the stdClass, which is the problem. Couple of things - can you confirm what version of CodeIgniter you're running. Secondly, just as a temporary measure, can you change the above line to the following: return$this->db->get()->result('Faculty');

and then call your controller method again and re-post the results. We're into debug mode now so we'll have to dig a little deeper to find out why it's not doing what it's supposed to.
1

Author Comment

ID: 41879590
here it is...
object(stdClass)#16 (1) { ["faculty"]=> array(1) { [0]=> object(stdClass)#18 (7) { ["Faculty_UUID"]=> string(6) "rbanai" ["First_Name"]=> string(4) "Reza" ["Last_Name"]=> string(5) "Banai" ["Faculty_Title"]=> string(9) "Professor" ["Faculty_Profile_Link"]=> string(53) "http://www.memphis.edu/planning/people/reza-banai.php" ["Faculty_Email"]=> string(18) "rbanai@memphis.edu" ["Department_Name"]=> string(26) "City and Regional Planning" } } }

0

LVL 43

Expert Comment

ID: 41879704
Hmm. At a bit of a loss as to why it's not working. I'll need to look into it get back to you. All your code looks correct, so I'm guessing that something else is going on.

Can you confirm the version you're running and I'll see what I can find out.
0

Author Comment

ID: 41880550
Thank you Chris,
It is CodeIgniter 3.1.0

PHP 7.07

IIS 8.5

Debra
0

LVL 43

Expert Comment

ID: 41880600
Thanks Debra,

I'm still looking into it. I've set up my system to closely replicate your setup as best I can, but I can't get it to fail. It does exactly what it's supposed to. Couple more things I want to try, but if that fails, then I'll be suggesting that we take a step back and start debugging it with a very simple sample. We can then build on top of that and keep an eye on each step of the process.

I'll be in touch shortly
1

LVL 43

Expert Comment

ID: 41881051
Hi Debra,

Unfortunately I can't identify why you code is misbehaving, so can I suggest we start with a few, very basic parts and go from there. What I would advise is to bring things back to the very basic moving parts and see if that works. If it does, then we can build on it until it breaks (or succeeds). It should only take a couple of minutes to setup. Basically, start with a simple controller, and a simple model:

Controller (/application/controllers/Test.php):
<?php
defined('BASEPATH') OR exit('No direct script access allowed');

class Test extends CI_Controller {
public function index()
{
$this->load->database();$this->load->model('member');

$member =$this->member->GetMember('rbanai');
var_dump($member); } }  Model (/application/models/Member.php): <?php defined('BASEPATH') OR exit('No direct script access allowed'); class Member extends CI_Model { public function __construct() { parent::__construct(); } public function GetMember($id)
{
$this->db ->select('First_Name, Last_Name') ->from('suapp.FACULTY') ->where('Faculty_UUID',$id);

return $this->db->get()->result('Member'); } }  Once you've got the model and controller created, then call your controller in your browser (yourdomain/index.php/test/) Post the results so we can take a look. 0 Author Comment ID: 41881055 Okay, I'm setting it up now. I thank you for your patience and willingness to help me. debra 0 Author Comment ID: 41881107 Here is the output from http://casweb.memphis.edu/suapp-research/index.php/test array(1) { [0]=> object(stdClass)#17 (2) { ["First_Name"]=> string(4) "Reza" ["Last_Name"]=> string(5) "Banai" } } 0 LVL 43 Expert Comment ID: 41881130 ARGHHHH! This is so frustrating. Those 2 simple codeblocks that I gave you should work perfectly (they do - I've tested them!). One final thing to quickly try and then I'm all out of ideas. Can you change the last line of your model to the following: return$this->db->get()->result(get_called_class());

If that doesn't work, I really am out of ideas, and can only put it down to some strage configuration you have setup that I'm not seeing, or it's simply a bug in CodeIgniter that only shows on your particular setup (IIS etc). I can't find any references to your problem on Google, so this seems to be unique to you :(
1

Author Comment

ID: 41881144
Looks the same to me.

Isnt' it pulling the information that it should be getting?

array(1) { [0]=> object(stdClass)#17 (2) { ["First_Name"]=> string(4) "Reza" ["Last_Name"]=> string(5) "Banai" } }

I'm so confused with codeigniter. I really thought it was going to be much simpler to set up.
debra
0

LVL 43

Expert Comment

ID: 41881166
Hey Debra,

CodeIgniter is fairly simple once you get your head around a few key principles. The reason this is turning out to be a nightmare is because for some reason your installation of CodeIgniter isn't working the way it should, and I can't see why.

You're correct in that it's pulling the data it should, but what we're looking to do is push that data into a new Member object, and not a stdClass object. By pushing it into our own custom class, we can add new methods to the class which makes everything a lot easier to work with. For example, on the Member model we could add a method called FullName():

function FullName()
{
return sprintf("%s %s", $this->First_Name,$this->Last_Name);
}

Then each time we pulled data out of the database and pushed it into a Member class, we could simply call $member->FullName(). Because your data is being pushed into a stdClass, it won't have those extra methods, so you can't call FullName() - because it's not a Member object. I know that's a trivial example, but if it's not working, then you're going to lose a lot of the power of the Models from CodeIgniter. You're only going to be able to use them for simple database access, and not like a proper Object. You're driving a car with 3 wheels!! Let me have another look at your model and see if I can refactor it to work around your problem. Back in a minute 1 LVL 43 Expert Comment ID: 41881225 Right. I think we can get around this by making use of the stdClass instead of our own Faculty class. You'll need to change the Model to the following: <?php defined('BASEPATH') OR exit('No direct script access allowed'); class Faculty extends CI_Model { function __construct() { parent::__construct();$this->load->database();
}

public function GetFaculty($Faculty_Research_Match) {$this->db
->select('Faculty_UUID, First_Name, Last_Name, Faculty_Title, Faculty_Profile_Link, Faculty_Email, Department_Name')
->from('suapp.FACULTY')
->where('Faculty_UUID', $Faculty_Research_Match); return$this->db->get()->row();
}

public function GetResearchAreas($facultyUuid) {$str = "
SELECT DISTINCT Research_Area, Research_Area_Prefix
FROM (suapp.RESEARCH_MATCH)
JOIN suapp.AREA_TOPIC ON suapp.RESEARCH_MATCH.Area_Topic_Code = suapp.AREA_TOPIC.Area_Topic_Code
JOIN suapp.RESEARCH_AREA ON LEFT(suapp.AREA_TOPIC.Area_Topic_Code, 2) = suapp.RESEARCH_AREA.Research_Area_Prefix
WHERE suapp.RESEARCH_MATCH.Faculty_UUID = '%s'";

$qryStr = sprintf($str, $facultyUuid);$query = $this->db->query($qryStr);

return $query->result(); } public function GetResearchTopics($areaPrefix)
{
$this->db ->select('Area_Topic') ->from('suapp.AREA_TOPIC') ->where('LEFT(Area_Topic_Code, 2) =',$areaPrefix);

return $this->db->get()->result(); } }  And your view will need to change too: <html lang="en"> <head> <meta charset="utf-8"> <title>Chris Stanyon</title> </head> <body> <?php if (isset($faculty)): ?>
<h1>Faculty Information and Research</h1>

<p><?php echo $faculty->First_Name ?> <?php echo$faculty->Last_Name ?>, <?php echo $faculty->Faculty_Title ?><br /> <?php echo$faculty->Department_Name ?><br />
<?php echo anchor($faculty->Faculty_Profile_Link) ?><br /> <?php echo mailto($faculty->Faculty_Email) ?></p>

<div>
<?php foreach ($this->faculty->GetResearchAreas($faculty->Faculty_UUID) as $area): ?> <h2><?php echo$area->Research_Area ?></h2>
<ul>
<?php foreach ($this->faculty->GetResearchTopics($area->Research_Area_Prefix) as $researchTopic): ?> <li><?php echo$researchTopic->Area_Topic ?></li>
<?php endforeach; ?>
</ul>
<?php endforeach; ?>
</div>
<?php endif; ?>
</body>
</html>

I've also removed the load>helper('url') from the Faculty model - it doesn't belong in there - it belongs in your controller

public function match_it_now() {
$this->load->helper('url');$this->load->model('faculty');
$data = new stdClass(); if ($this->input->post('Faculty_Research_Match')) {
$data->faculty =$this->faculty->GetFaculty($this->input->post('Faculty_Research_Match')); }$this->load->view('final_match_faculty_view', $data); }  Pretty sure that should get us back on track as it won't need to create our custom object. 0 Author Comment ID: 41881241 This is coming up with DB error. Have never seen this one. It's pulling from the Database, so why the error? Faculty Information and Research David Cox, Professor Public and Nonprofit Administration http://www.memphis.edu/padm/people/david-cox.php davidcox@memphis.edu A Database Error Occurred Error Number: 42000/102 [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Incorrect syntax near ')'. SELECT DISTINCT Research_Area, Research_Area_Prefix FROM (suapp.RESEARCH_MATCH) JOIN suapp.AREA_TOPIC ON suapp.RESEARCH_MATCH.Area_Topic_Code = suapp.AREA_TOPIC.Area_Topic_Code JOIN suapp.RESEARCH_AREA ON LEFT(suapp.AREA_TOPIC.Area_Topic_Code, 2) = suapp.RESEARCH_AREA.Research_Area_Prefix WHERE suapp.RESEARCH_MATCH.Faculty_UUID = 'davidcox' Filename: C:/inetpub/codeigniter/system/database/DB_driver.php Line Number: 691 0 LVL 43 Expert Comment ID: 41881271 OK. This is implying that the JOIN Sql Statement has an error in it. I don't currently have access to an MSSQL Server to test on but you should be able to post that query straight into your SQL Server Management console to get some extra info if needed. It implies it's near a closing parenthesis, so that would be a good place to start. Remove the first set and change the order of the second. You'll need to change your model:$str = "
SELECT DISTINCT Research_Area, Research_Area_Prefix
FROM suapp.RESEARCH_MATCH
JOIN suapp.AREA_TOPIC ON suapp.RESEARCH_MATCH.Area_Topic_Code = suapp.AREA_TOPIC.Area_Topic_Code
JOIN suapp.RESEARCH_AREA ON  suapp.RESEARCH_AREA.Research_Area_Prefix = LEFT(suapp.AREA_TOPIC.Area_Topic_Code, 2)
WHERE suapp.RESEARCH_MATCH.Faculty_UUID = '%s'";

I've only tested this query on mySQL so there may be slight differences between the syntax.
0

Author Comment

ID: 41881296
With this,
SELECT DISTINCT Research_Area, Research_Area_Prefix
FROM (suapp.RESEARCH_AREA)
JOIN suapp.AREA_TOPIC ON suapp.RESEARCH_MATCH.Area_Topic_Code = suapp.AREA_TOPIC.Area_Topic_Code)
JOIN suapp.RESEARCH_AREA ON  suapp.RESEARCH_AREA.Research_Area_Prefix = LEFT(suapp.AREA_TOPIC.Area_Topic_Code, 2)
WHERE suapp.RESEARCH_MATCH.Faculty_UUID = 'davidcox';

I get...

Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "suapp.RESEARCH_MATCH.Area_Topic_Code" could not be bound.
Msg 1013, Level 16, State 1, Line 1
The objects "suapp.RESEARCH_AREA" and "suapp.RESEARCH_AREA" in the FROM clause have the same exposed names. Use correlation names to distinguish them.

I'm googling it and changing the paranthesis. Has to do with explicit and implicit

This is what I got from Stackoverflow.

"The thing is, explicit joins (the ones that are implemented using the JOIN keyword) take precedence over implicit ones (the 'comma' joins, where the join condition is specified in the WHERE clause)." http://stackoverflow.com/questions/7314134/the-multi-part-identifier-could-not-be-bound
0

LVL 43

Expert Comment

ID: 41881336
OK. Well that query doesn't look like the one I posted. In mine, I'd removed the parenthesis from the FROM clause altogether. You seem to have left them in and added an extra closing one after Area_Topic_Code.

Fix that and see how you get on. I'll try and get my SQL Server up and running and setup your DB Schema as best I can.

Be back shortly
1

Author Comment

ID: 41881383
I apologize. I must have pasted the wrong query for you. I have been revising it inside SQL Server Management Studio.

SELECT DISTINCT Research_Area, Research_Area_Prefix
FROM suapp.RESEARCH_AREA RA
JOIN suapp.RESEARCH_MATCH RM
JOIN suapp.AREA_TOPIC ON RM.Area_Topic_Code = suapp.AREA_TOPIC.Area_Topic_Code
JOIN RA ON  suapp.RESEARCH_AREA.Research_Area_Prefix = LEFT(suapp.AREA_TOPIC.Area_Topic_Code, 2)
WHERE RM.Faculty_UUID = 'davidcox';

I did clear up the correlation error and the can't be bound error. Now I'm down to the Where error.

Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'WHERE'.

Debra
0

LVL 43

Accepted Solution

Chris Stanyon earned 500 total points
ID: 41881453
Right Debra. May be getting somewhere. I've just setup your DB schema on my SQLSERVER and played around with the query, and whilst I don't actually have any data in my database, this query now exceutes fine (in the Management Console). It looks like you need to wrap your table names in square brackets - I'm guessing it's because you have the DOT in there (but I could be wrong):

SELECT DISTINCT Research_Area, Research_Area_Prefix
FROM [suapp.RESEARCH_MATCH]
JOIN [suapp.AREA_TOPIC] ON [suapp.RESEARCH_MATCH].Area_Topic_Code = [suapp.AREA_TOPIC].Area_Topic_Code
JOIN [suapp.RESEARCH_AREA] ON LEFT([suapp.AREA_TOPIC].Area_Topic_Code, 2) = [suapp.RESEARCH_AREA].Research_Area_Prefix
WHERE [suapp.RESEARCH_MATCH].Faculty_UUID = 'davidcox'

Give that a go and see how you get on.
2

Author Closing Comment

ID: 41882626
Amazing help, advice, patience and knowledge. I could not have finished my project without this help!
Thanks Chris.
Debra
0

LVL 43

Expert Comment

ID: 41882679
Excellent news Debra. Pleased we got there in the end. Good job.

Good luck with the rest of your project, and feel free to ask more questions here on EE. CodeIgniter is great, but it can be a bit of a learning curve to get going properly :)

Chris
1

Author Comment

ID: 41882942
Hi Chris,

I thought I had sent you the working link, but I don't see it in this thread.

http://casweb.memphis.edu/suapp-research/index.php/post/show

One more question...

Now the faculty information is pulling headers for Research Area and the Topics below each header like I need. :)

In my earlier version of this application, I had my 'if statement' for faculty with no research information. Now I'm having trouble setting it up.

With this code, I still get the faculty member information, but no research info (he doesn't have any to show).

<?php

if (isset($faculty)): ?> <h1>Faculty Information and Research</h1> <p><?php echo$faculty->First_Name ?> <?php echo $faculty->Last_Name ?>, <?php echo$faculty->Faculty_Title ?><br />
Department of <?php echo $faculty->Department_Name ?><br /> <?php echo anchor($faculty->Faculty_Profile_Link) ?><br />
<?php echo mailto($faculty->Faculty_Email) ?></p> <div> <?php foreach ($this->faculty->GetResearchAreas($faculty->Faculty_UUID) as$area): ?>
<h2><?php echo $area->Research_Area ?></h2> <ul> <?php foreach ($this->faculty->GetResearchTopics($area->Research_Area_Prefix) as$researchTopic): ?>
<li><?php echo $researchTopic->Area_Topic ?></li> <?php endforeach; ?> </ul> <?php endforeach; ?> </div> <?php elseif (isset($faculty) && !isset($area) && !isset($researchTopic)):

echo "Faculty has no current research listed."
?>
<?php endif; ?>


Any ideas?
Debra

Debra
0

LVL 43

Expert Comment

ID: 41883057
Hi Debra. It's looking good.

I think I understand what you mean - you'd need to change your logic slightly so that you could count the number of items returned from your Model calls. You can then choose to execute one part of the code if there is a count (you have items) and a different part of your code if there isn't.

Have a look at the following code and see if it make sense. I've indented it and spaced it to hopefully make it a little easier to understand, but ask if you need clarification on anything:

<div>
<?php
$areas =$this->faculty->GetResearchAreas($faculty->Faculty_UUID); if (count($areas)):

foreach ($areas as$area): ?>

<h2><?php echo $area->Research_Area ?></h2> <?php$topics = $this->faculty->GetResearchTopics($area->Research_Area_Prefix);
if (count($topics)): ?> <ul> <?php foreach ($topics as $researchTopic): ?> <li><?php echo$researchTopic->Area_Topic ?></li>
<?php endforeach; ?>
</ul>

<?php else: ?>

<h3>No Research Topics</h3>

<?php endif; ?>

<?php endforeach;

else: ?>

<h3>No Areas</h3>

<?php endif; ?>
</div>

1

Author Comment

ID: 41884164
Hi Chris,
Once again, you have made my life much easier! Thank you so much. Sorry didn't get back sooner, had a luncheon.
Debra
0

LVL 43

Expert Comment

ID: 41884212
You're welcome.
1

Author Comment

ID: 41890255
Hi Chris,
It's me again...
I was hoping I could work this out on my own, but I'm having some problems.

This part of the application is matching the research area chosen from the dropdown and pulling the faculty members involved in that area. (whereas the other part of the app was pulling from a drop down populated with faculty - this one pulls from drop down populated with research areas.)

Could you please take a look and see if you know why I'm getting the error of:
Message: Trying to get property of non-object
Filename: views/final_match_area_view.php

Controller: (Match_research_area_controller.php)
<?php

defined('BASEPATH') OR exit('No direct script access allowed');

class Match_research_area_controller extends CI_Controller {

public function match_area_now() {
$this->load->helper('url');$this->load->model('research_area');
$data = new stdClass(); if ($this->input->post('Research_Area_Match')) {
$data->research_area =$this->research_area->GetArea($this->input->post('Research_Area_Match')); }$this->load->view('final_match_area_view', $data); //*var_dump($data);

}
}


Model: (Research_area.php)
<?php
defined('BASEPATH') OR exit('No direct script access allowed');
class Research_area extends CI_Model {

function __construct()
{
parent::__construct();
$this->load->database(); } public function GetArea($Research_Area_Match)
{
$str = " SELECT First_Name, Last_Name, Department_Name, suapp.FACULTY.Faculty_UUID, Faculty_Email, Faculty_Title, Faculty_Profile_Link FROM suapp.FACULTY JOIN suapp.RESEARCH_MATCH ON suapp.FACULTY.Faculty_UUID = suapp.RESEARCH_MATCH.Faculty_UUID JOIN suapp.RESEARCH_AREA ON LEFT(suapp.RESEARCH_MATCH.Area_Topic_Code, 2) = suapp.RESEARCH_AREA.Research_Area_Prefix WHERE suapp.RESEARCH_AREA.Research_Area_Prefix = '%s'";$qryStr = sprintf($str,$Research_Area_Match);

$query =$this->db->query($qryStr); return$query->result();

}

}


View: (final_match_area_view.php)
<html lang="en">
<meta charset="utf-8">
<title>School of Urban Affairs and Public Policy</title>
<body>

<?php if (isset($research_area)): ?> <h1>Faculty Information and Research</h1> <p>The following list of faculty are involved in the <p><strong><?php echo$research_area->First_Name ?> <?php echo $research_area->Last_Name ?></strong>, <?php echo$research_area->Faculty_Title ?><br />
Department of <?php echo $research_area->Department_Name ?><br /> <?php echo anchor($research_area->Faculty_Profile_Link) ?><br />
<?php echo mailto($research_area->Faculty_Email) ?></p> <?php endif; ?>  My query is correct - it pulls the right information from the db. here is the var_dump($data);

0

LVL 43

Expert Comment

ID: 41890285
Hi Debra,

As Kyle has pointed out, it is easier to ask a related question rather than carrying on a closed one.

Having said that, your issue is because your Model method (GetArea) returns an array of objects, so to access them, you'd need to foreach the array. Something like this in your view:

<?php foreach ($person in$research_area): ?>
<p><strong><?php echo $person->First_Name ?> <?php echo$person->Last_Name ?></strong>, <?php echo \$person->Faculty_Title ?></p>
<?php endforeach; ?>

1

Author Comment

ID: 41890456
Ahhh, thank you both. I did not even notice the Ask A Related Question.

This works.
Debra
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

These days socially coordinated efforts have turned into a critical requirement for enterprises.
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.