jobberBase custom development and support


Code School

Experimenting on Full-Text Search

today i had time to look closely in to the Search() function on ‘class.Job.php’, and i wanted to see how Full-Text Search can improve the search performance of jobberBase when searching for a couple thousand jobs. i don’t have that much data to test yet so i am hoping the good people reading my posts can help me with testing and measuring any improvement.

first things first. back up your ‘jobs’ and ‘cities’ table, you’ll have a fall back if anything breaks.

so let’s start with running these sql lines on your ‘jobs’ and ‘cities’ tables in your phpmyadmin:

`title` ,
`description` ,
`company` ,

then open up /_includes/class.Job.php and comment out the whole ‘public function Search($keywords) {}’ block.

then add this below it:

// Search for jobs
public function Search($keywords)
     global $db;
     $jobs = array();
     $spchars = array(",",";",":");
     $keywords = str_replace($spchars," ", trim($keywords));
     $sql = 'SELECT AS id
                    FROM jobs a, cities b
                    WHERE a.city_id =
                    AND a.is_temp = 0 AND a.is_active = 1
                    AND MATCH (a.title, a.description,, a.outside_location,
                    AGAINST ("'. $keywords .'" IN BOOLEAN MODE )';
        $result = $db->query($sql);
        while ($row = $result->fetch_assoc())
            $current_job = new Job($row['id']);
            $jobs[] = $current_job->GetBasicInfo();
        return $jobs;

i would like to hear your feedback! post a comment whether or not this actually improved the search performance in your jobberBase website. do note that this is merely an experiment. you’ve been warned! 😉


Code School

  1. I just tried this now. Filled my jobs table with a few thousand records of test data and
    sorry to say but this does not work.

    When I type a 3 letter keyword no results are returned. When I type the full keyword, results are returned but the page freezes up and a warning is returned:

    “A script on this page may be busy, or it may have stopped responding. You can stop the script now, or you can continue to see if the script will complete.


    I think the query is trying to return too much data. The results need to be split up into pages.

    Check out:

  2. hi, actually jobberBase has its own paging class. thanks for your feedback though! like i said this is an experiment, and not a solution (at least not yet) hopefully it gets explored more on the next version of jobberBase.

  3. about this 3-letter word, this is a mysql full-text searching feature and not because of how it was coded 😉

  4. Any luck using Sphinx as you suggested before?

  5. hi there, no luck yet. starting out with experimenting on full-text search first and maybe go up. we’ll see!

  6. is there anyway to make that in a new category like a new tab with a label advanced search (searching by city job type and etc..) Thanks Khaled

Leave a Response