Random Broken Search Feature in Codeigniter, Gives Database Error?

Question

Here is the server version info I know you might need:

CodeIgniter Version: 3.1.9
PHP Version: 7.3.6
MySQL Server Version: 5.7.27

I am new to php and web design in general.

This is particularly true when it comes to CodeIgniter's PHP shorthand tools. A friend of mine who has become increasingly hard to get in touch with due to her "day job" is the one who wrote the code for this site a couple years ago. Recently, I have been on my own with it for awhile now and I am learning a lot. But, I recently encountered a small issue that I wonder if anyone could help me with:

Not too long ago my recipe search feature worked flawlessly. Until now. I rarely edit anything that builds or drives the search feature of my site. However, recently I have been getting the following error when I type anything into the search field:

Error screenshot:

https://gyazo.com/d949731310229541b33ead40a806c333

Recipe.php (Recipe Controller)

<?php
class Recipe extends MY_Controller{

 function __construct() {
  parent::__construct();
   $uri = $this->uri->uri_string();
   $this->session->set_flashdata('uri', $uri);
 }
    
 function index(){
  $recipe_linkname = $this->uri->rsegment(3);
  if(!$recipe_linkname){
   $recipe_linkname = $this->recipe_model->first_linkname();
  }
  $recipe = $this->recipe_model->read_record($recipe_linkname);
  if(count($recipe) == 0){
   show_404('');
   die();
  }
  $data = array(
     'title' => "Shirley's Recipes:".$recipe['name'],
   'columns' => array('toc', 'recipe'),
    'recipe' => $recipe
  );
  $this->load->view('includes/template', $data);
 }
    
 function flipbook(){
  $this->require_login();
  $this->load->library('pagination');
    
        $config['base_url'] = site_url('recipe/flipbook');
      $config['total_rows'] = $this->recipe_model->num_rows();
     $config['uri_segment'] = '3';
        $config['per_page'] = 1;
       $config['num_links'] = 1;
   $config['display_pages'] = TRUE;
   $config['full_tag_open'] = '<div id="pagination">';
  $config['full_tag_close'] = '</div>';
  
  $this->pagination->initialize($config);
  $recipe = $this->recipe_model->read_records($config['per_page'], $this->uri->segment(3))->row_array();
    $data = array(
     'title' => "Shirley's Recipes:".$recipe['name'],
   'columns' => array('toc', 'recipe_flipbook'),
    'recipe' => $recipe
  );
  $this->load->view('includes/template', $data);
 }
  
 function search(){
   if($this->input->method() === 'post'){
    $search = $this->input->post('query');
   $results = $this->recipe_model->search($search);
      $data = array(
     'title' => "Shirley's Recipes:Search Results",
   'columns' => array('toc', 'recipe_search'),
   'recipes' => $results,
   );
   $this->load->view('includes/template', $data);
   }
   else{
    redirect('recipe');
   }
 } 
    
 function add(){
  $this->require_login();
  if($this->input->post('submit')){
   $data = array(
          'name' => $this->input->post('name'),
    'buttontext' => $this->input->post('buttontext'),
      'linkname' => $this->input->post('linkname'),
           'img' => $this->input->post('img'),
          'time' => $this->input->post('time'),
      'category' => $this->input->post('category'),
   'subcategory' => $this->input->post('subcategory'),
  ' ingredients' => $this->input->post('ingredients'),
     'equipment' => $this->input->post('equipment'),
   'preparation' => $this->input->post('preparation'),
    'directions' => $this->input->post('directions')
   );
   $recipeid = $this->recipe_model->create_record($data);
   redirect('recipe/'.$this->input->post('linkname'));
  }
  else{
   $data = array(
         'title' => "Shirley's Recipes:Add New Recipe",
       'columns' => array('toc', 'admin/recipe_edit'),
        'recipe' => array('name' => '',
    'buttontext' => '',
      'category' => '',
   'subcategory' => '',
      'linkname' => '',
           'img' => '',
          'time' => '',
   'ingredients' => '',
     'equipment' => '',
   'preparation' => '',
    'directions' => '')
   );
   $this->load->view('includes/template', $data);
  }
 }

 function edit(){
  $this->require_login();
  $id = $this->uri->segment(3);
  if($this->input->post('submit')){
   $data = array(
          'name' => $this->input->post('name'),
    'buttontext' => $this->input->post('buttontext'),
      'linkname' => $this->input->post('linkname'),
           'img' => $this->input->post('img'),
          'time' => $this->input->post('time'),
      'category' => $this->input->post('category'),
   'subcategory' => $this->input->post('subcategory'),
   'ingredients' => $this->input->post('ingredients'),
     'equipment' => $this->input->post('equipment'),
   'preparation' => $this->input->post('preparation'),
    'directions' => $this->input->post('directions')
    );
    $this->recipe_model->update_record($id, $data);
    redirect('recipe/'.$this->input->post('linkname'));
  }
  else{
   $recipe = $this->recipe_model->read_record($id);
   $data = array(
     'title' => "Shirley's Recipes:Edit Recipe : ".$recipe['name'],
       'columns' => array('toc', 'admin/recipe_edit'),
            'id' => $id,
    'recipe' => $recipe
   );
   $this->load->view('includes/template', $data);
  }
 }
    
 function delete(){
  $this->require_login();
  $this->recipe_model->delete_record($this->uri->segment(3));
  redirect('/');
 } 
}
// End - recipe.php
?>

Recipe_model.php (Recipe Model)

<?php
class Recipe_model extends CI_Model {

 function create_record($data){
  if(isset($data['buttontext']) and $data['buttontext'] == ''){
   $data['buttontext'] = NULL;
  }
  $this->db->insert('recipe', $data);
  return $this->db->insert_id();
 }
  
 function read_records($limit, $offset){
  $this->db->order_by('name', 'asc');
  if(isset($limit) && isset($offset)){
   $this->db->limit($limit, $offset);
  }
  $q = $this->db->get('recipe');
  return $q;
 }
  
 function read_record($key){
  if(is_numeric($key)){
   $this->db->where('id', $key);
  }
  else{
   $this->db->where('linkname', $key);
  }
  $q = $this->db->get('recipe');
  return $q->row_array();
 }
  
 function first_linkname(){
  $this->db->select('linkname, name');
  $this->db->order_by('name', 'asc');
  $this->db->limit(1);
  $q = $this->db->get('recipe');    
  return $q->row()->linkname;
 }
        
 function read_names(){
  $this->db->select('linkname, name');
  $this->db->order_by('name', 'asc');
  $q = $this->db->get('recipe');
  return $q->result();
 }
  
 function read_names_categories(){
  $this->db->select('buttontext, linkname, name, category, subcategory, img, time');
  $this->db->order_by('name', 'asc');
  $q = $this->db->get('recipe');
  return $q->result();  
 }
    
 function search($search){
  $terms = explode(" ", $search);
  $match = "";
  foreach($terms as $term){
   $match .=  $term;
  }
  $querystr = "SELECT *, MATCH(name, linkname, time, img, ingredients, equipment, preparation, directions, category, subcategory, keywords) AGAINST('".$match."') as score FROM recipe WHERE MATCH(name, linkname, time, img, ingredients, equipment, preparation, directions, category, subcategory, keywords) AGAINST('".$match."') ORDER BY score DESC;";
  $q = $this->db->query($querystr);
  return $q->result();
 }

 function update_record($id, $data){
  if(isset($data['buttontext']) and $data['buttontext'] == ''){
   $data['buttontext'] = NULL;
  }
  $this->db->where('id', $id);
  $this->db->update('recipe', $data);
 }
  
 function delete_record($id){
  $this->db->where('id', $id);
  $this->db->delete('recipe');
 }
  
 function num_rows(){
  return $this->db->get('recipe')->num_rows();
 }
}
// End - recipe_model.php
?>

recipe_search.php (Search Output)

<div id="recipelist" class="content">
 <h1>Recipe Search</h1>
  <div class="recipesearch">
   <ol>
    <?php if(!empty($recipes)): foreach($recipes as $recipe): ?>
     <li><a href="/recipe/<?=$recipe->id ?>"><?=$recipe->name ?></a><span class="confidence"><?=$recipe->score ?></span></li>
     <?php endforeach; ?>
   </ol>
  </div>
  <?php else: ?>
  <h3>No matching recipes</h3>
  <?php endif; ?>
</div>

Lastly, here is a screenshot of the database structure and field names.

Database screenshot:

https://gyazo.com/b17307885e9ea4e7c619efba707a7e44

I hope that is everything you guys need to help me sort this out. I am also open to rewriting the PHP a different way if It will avoid breakage in the future. Any and all help you could offer is greatly appreciated.

Thank you again!

Please don't post code and error messages as an image but rather as code-formatted text since none of us can copy, paste and run an image. For more on this, please see [**Why may I not upload images of code**](https://meta.stackoverflow.com/questions/285551/why-not-upload-images-of-code-on-so-when-asking-a-question/285557#285557) on SO when asking a question?. Also please check out the [**How to Ask**](https://stackoverflow.com/help/how-to-ask) for more on site best-practices. — Vickel, Jun 22 '20 at 18:50
My apologies if I posted images of code. However, as far as I am aware I only posted images of the error page I received or the database table configuration. Any time code was appropriate or possible, I did use a code block. The only screenshots that I used were of output. My apologies, but I fail to see where I used images in place of code blocks. If you had viewed the screenshots you would see there is no code posted except in the code snippet blocks. — , Jun 22 '20 at 19:11
This was a canned comment (I should have deleted the "code" part), I was referring to the error messages. As I don't know gyazo.com, I don't click on it, many other users feel the same about clicking an unknown link. And what happens, if the links become dead? then we have no more error messages to underline your question? btw. you can use imgur for placing images in your question, that's the platforms supported method. Just click on the image icon and Browse, drag & drop, or an image or link (max 2 MiB) — Vickel, Jun 22 '20 at 19:28

score 0 · Accepted Answer · answered Jun 22 '20 at 18:56

0

At the bottom of the database screenshot, it lists the indexes on the table. The one with the Keyname of name_2 no longer matches all the fields that you are querying with MATCH().

For example, time and img in the error screenshot do not appear in the name_2 index (there might be more missing fields).

You'll need to run a database query to add an index for ALL the fields in your MATCH query. Something like this (but add in ALL fields):

ALTER TABLE `recipe` ADD FULLTEXT(name, linkname, time, img, ingredients, equipment...);

See this question for more detail

answered Jun 22 '20 at 18:56

Spudly

310
1
2
10

Oh, I see what you mean now! Thank you. A quick follow up if I may? Why is it if I type in to the search field say the letter "r" and I know I have numerous results that start with the letter r or have the letter r in them, I get zero results? Thank you again. – Jun 22 '20 at 19:53
No problem. Does the search get called with JavaScript? Check that there isn't something set there to require search terms to be a certain length before the search query can run. Just a side note, MySQL FULLTEXT will only index terms with 4 characters or more. That means it will not match records on words that are 3 characters or shorter. [See this for more detail and how to change it if needed.](https://electrictoolbox.com/mysql-full-text-index-word-length/) – Spudly Jun 22 '20 at 20:16
@J.Davis Did this answer fix the problem? If so, can you upvote it or accept it as the correct answer? – Spudly Jun 24 '20 at 04:01

Random Broken Search Feature in Codeigniter, Gives Database Error?

1 Answers1