Help with generating db driven dropdown search function

[eluser]IbnKhnata[/eluser]
Hello

I am a codeigniter newb and is trying to create a search function for my website where people could either search by entering keywords, or by selecting a year or publication type or author name from different drop down boxes( the whole thing should be database driven off course.
I am having trouble with my last dropdown box(authors), I can't get it do display the first and last name of the authors without having an undefined Index First_Name to be diplayed inside the combo box.
Some help would be greatly appreciated
Thanks

here what I have so far

Controller
function search()
{

$keywords = trim($this->input->post('search_string'));
$yr = trim($this->input->post('Year'));
$type = trim($this->input->post('Type'));
$author = trim($this->input->post('Authors'));
$search_items = array('keywords' =>$keywords,'yr'=>$yr);
$this->load->model('publications_model');
$data['Year'] = $this->publications_model->get_Year_dropdown();
$data['Type'] = $this->publications_model->get_Type_dropdown();
$data['Authors'] = $this->publications_model->get_Authors_dropdown();
$data['publications'] = $this->publications_model->getTableResults($search_items);
$this->load->view ('publication_view',$data);
}

Model

function get_Authors_dropdown() {

$this->db->select('StaffID','Last_Name','First_Name');
$this->db->order_by('Last_Name', 'asc');
$query=$this->db->get('tbl_staff');
$author_result = $query->result_array();

return ($author_result);
}

View ( partial code)
<select name ="Authors" />
<option value="all">- By Authors-</option>
&lt;?php foreach ($Authors as $key => $items){ ?&gt;
<option value="&lt;?=$items['First_Name'] . ' '. $items['Last_Name']?&gt;"&lt;?php echo set_select('Authors',$items['First_Name'] . ' '. $items['Last_Name']);?&gt;>&lt;?=$items['First_Name'] . ' ' . $items['Last_Name']?&gt;</option>
&lt;?php } ?&gt;
</select>

[eluser]pickupman[/eluser]
First tip. Use the code button in the menu just above where you make a post to help read your code. I noticed you may have a small typo, maybe it's from pasting into the forum, but take a look below.

Code:

//View ( partial code)
<select name =“Authors” >
<option value=“all”>- By Authors-</option>
&lt;?php foreach ($Authors as $key => $items){ ?&gt;
  <option value=”&lt;?=$items[‘First_Name’] . ’ ‘. $items[‘Last_Name’]?&gt;”&lt;?php echo set_select(‘Authors’,$items[‘First_Name’] . ’ ‘. $items[‘Last_Name’]);?&gt;>&lt;?=$items[‘First_Name’] . ’ ’ . $items[‘Last_Name’]?&gt;</option>
            &lt;?php } ?&gt;
</select>

Try changing to

Code:

<?php foreach($Authors as $items){?>
  <option value="&lt;?=$items['First_Name']. ' ' .$items['Last_Name'];?&gt;" &lt;?php echo set_select('Authors',$items['First_Name']. ' ' .$items['Last_Name'];?&gt; > &lt;?=$items['First_Name']. ' ' .$items['Last_Name'];?&gt;</option>
&lt;?php } ?&gt;

You usually and undefined index would typically mean a field you are trying to reference in the array key does not exists. ie('First_Name / Last_Name') Make sure they are in the table you are pulling them from.

[eluser]IbnKhnata[/eluser]
Hello PickupMAn
thanks for the tip about using the code button to generate more readable code segment.

I actually checked my model and realized that when I am using this->db->select in codeigniter I need to pass the fields names as one string and not comma separated string.

thanks for helping me realizing that....

Also do you have any good tips or link on how to retrieve info from the database when the user do a selection from the three different dropdown boxes.

The way I have it right now it gets everything that have any of the items selected.

Code:

function getTableResults ($search_items){
                
                $search_str = explode(' ', $search_items['keywords']);
                array_push($search_str,$search_items['yr'],$search_items['type'],$search_items['author'] );
                foreach($search_str as $value){
                    echo $value;
                }
                $searchflds = array('Publications_Title','Date','Type','First_Name','Last_Name');    
                $this->db->select('Publication_ID,Main_Author, Other_SEI_Authors,Other_non_SEI_Authors,Publications_Title,Publications_Photo,Type,Date,First_Name,Last_Name');
                $this->db->from('tbl_publications');
                $this->db->join('tbl_staff','StaffID = Main_Author');
                $i=0;
                foreach ($search_str as $ss)
                {  
                    foreach ($searchflds as $fld){
                        if ($i < 1){
                            $this->db->like($fld, $ss);
                            $i = 2;
                        }
                        else {
                            $this->db->orlike($fld, $ss);
                        }
                    }
                }
                $this->db->orderby('Date','desc');
                $query=$this->db->get();        
                $result = $query->result_array();

Thanks in advance for ure help

[eluser]pickupman[/eluser]
You would want to chain ->like() if each of the criteria is required for a result.

Code:

$searchflds = array('Publications_Title','Date','Type','First_Name','Last_Name');    
                $this->db->select('Publication_ID,Main_Author, Other_SEI_Authors,Other_non_SEI_Authors,Publications_Title,Publications_Photo,Type,Date,First_Name,Last_Name');
                $this->db->from('tbl_publications');
                $this->db->join('tbl_staff','StaffID = Main_Author');
                
                foreach ($search_str as $ss)
                {  
                    foreach ($searchflds as $fld){
                        $this->db->like($fld, $ss);                        
                    }
                }
                $this->db->orderby('Date','desc');
                $query=$this->db->get();

If you want any match, use (you can chain or_like(). CI will add the OR when it needs it):

Code:

$searchflds = array('Publications_Title','Date','Type','First_Name','Last_Name');    
                $this->db->select('Publication_ID,Main_Author, Other_SEI_Authors,Other_non_SEI_Authors,Publications_Title,Publications_Photo,Type,Date,First_Name,Last_Name');
                $this->db->from('tbl_publications');
                $this->db->join('tbl_staff','StaffID = Main_Author');
                
                foreach ($search_str as $ss)
                {  
                    foreach ($searchflds as $fld){
                        $this->db->or_like($fld, $ss);
                    }
                }
                $this->db->orderby('Date','desc');
                $query=$this->db->get();

You want to consider creating a sub statement like:

Code:

$where ="WHERE (Publication_Title LIKE '%".$ss."%' OR Publication_Title LIKE '%".$ss."%') AND (Type LIKE '%".$ss."%' OR Type LIKE '%".$ss."%') AND (//next)";
$this->db->where($where);

Depend on your desired results, try each see what you get and check which way is faster.

[eluser]IbnKhnata[/eluser]
hello pick up man I have tried both of your approach and the query either return everything or nothing so i am not sure what is going on with the like or_like statement (My orginal search function works but will get you any publication that have any item selected in the search box or dropdown... any other tip would be greatly appreciated.

[eluser]pickupman[/eluser]
You could one of the methods for retrieving any matching field. Then assign a score to create a weighted score. Example:
Publication_Title = 25
Type = 15
First Name = 5
After fetching all records, iterate through each result and compare each field to the search terms, and if it matches add the score amount. After looping through each field, store the row in a array with the score, and result. Loop through each row until you have scored everything. Then you can sort the array using usort() to sort the array by the score.

[eluser]dubstep[/eluser]
edit: my reply is best fit for another thread. sorry!