Search script for autocomplete suggestions The following search script creates a JSON response for an autocomplete field. The tables and fields search from are flexible to allow using the same script for different type of searches - the idea is to search from each database field separately and see how often a term appears and sort them in descending order.
The part about ordering the found hits for a word by number of occurrences seems like it could be improved, but I don't know how to better approach the problem.
if (isset($_REQUEST['term']) &amp;&amp; !empty($_REQUEST['term'])) {
    
    // define what table -&gt; array(fields) to search from based on a possibly sent
    // parameter 'type', which is 'customer' or 'admin' or empty or some specific
    if (!isset($_REQUEST['type']) || $_REQUEST['type'] === 'customer') {
        $searchFrom = array( 
            'tuotesarjat' =&gt; array('sarjanimi', 'laattatyyppi'),
            'tuotteet'    =&gt; array('varikategoria1', 'varikategoria2', 'pinta', 'hakupinta')
        );
    } else if ($_REQUEST['type'] === 'admin') {
        $searchFrom = array( 
            'tuotesarjat' =&gt; array('sarjanimi', 'laattatyyppi'),
            'tuotteet'    =&gt; array('tuotenumero', 'kuvaus1', 'varikategoria1', 'varikategoria2', 'pinta', 'hakupinta')
        );
    } else if ($_REQUEST['type'] === 'tuotesarja') {
        $searchFrom = array(
            'tuotesarjat' =&gt; array('sarjanimi')
        );
    } else if ($_REQUEST['type'] === 'tuotenumero') {
        $searchFrom = array(
            'tuotteet'    =&gt; array('tuotenumero')
        );
    }
    
    $found = array();
    $terms = explode(' ', $_REQUEST['term']);

    // loop through all split up terms
    foreach ($terms as $key =&gt; $term) {
        if (!empty($term)) {
            $term = mysql_real_escape_string($term);
            
            // loop through tables
            foreach ($searchFrom as $table =&gt; $cols) {
                // for searches from table 'tuotesarjat' limit hits to published items
                $andWhere = $table == 'tuotesarjat' ? ' AND arkistoitu != 1 ' : '';
                
                // loop through fields
                foreach ($cols as $column) {
                    $sql = &quot;SELECT $column FROM $table WHERE $column &quot;;
                        
                    // to limit searches, for 1-2 letter terms search string start, 
                    // else from whole term
                    if (strlen($term) &lt; 2) {
                        $sql .= &quot;LIKE '$term%' $andWhere GROUP BY $column&quot;;
                    } else {
                        $sql .= &quot;LIKE '%$term%' $andWhere GROUP BY $column&quot;;
                    }
                    // in case a field does not exist, fail silently 
                    // (a json query will still be generated returning the 
                    // _other_ fields' searches' results)
                    $res = @mysql_query($sql); 
                    if (mysql_num_rows($res) &gt; 0) {
                        while($row = mysql_fetch_assoc($res)) {
                            // insert new found word to array with count 1
                            // or increment already found words' counts
                            if (!key_exists($row[$column], $found)) {
                                $found[$row[$column]] = 1;
                            } else {
                                $found[$row[$column]] += 1;
                            }
                        }
                    }
                }  
            }          
        }
    }
    
    // sort by number of hits for each word desc
    arsort($found);
    
    // filter empty strings
    $found = array_filter(array_keys($found));
    
    // reduce array to global max hits
    while (count($found) &gt; MAX_LIVESEARCH_SUGGESTIONS) {
        array_pop($found);
    }
    
    // generate and echo json
    $json = '[';
    foreach ($found as $sana) {
        $json .= '&quot;' . $sana . '&quot;, ';
    }
    $json = substr($json, 0, -2) . ']';
    echo $json;    
}
Upgrading PHP

Upgrade your PHP version. After reading this code I determined that you are running at the most 4.0.6. key_exists() was replaced with array_key_exists() in 4.1.1, and this was some time ago. An old version of PHP runs the risk of having security issues, and you won't be able to get much help because everyone is using the newer version. Some of these suggestions might still work, but others may not be supported in your version, such as this first one.

REQUEST vs. POST/GET

You should stop using $_REQUEST. It is insecure, being that you are running an older version of PHP this is understandable, but if you upgrade, you should definitely update this as well. If you know where the information is coming from, and you should, then you should just use that array. For example, if it is coming from POST use the $_POST array, if it is coming from GET use the $_GET array, etc...

switch vs. if/else

Think of using a switch. It is faster than if/else statements and, in my opinion, cleaner looking and more legible.

$searchFrom = array(
    'tuotesarjat' =&gt; array('sarjanimi', 'laattatyyppi'),
    'tuotteet'    =&gt; array('varikategoria1', 'varikategoria2', 'pinta', 'hakupinta')
);

if( isset( $_REQUEST[ 'type' ] ) {
    $type = $_REQUEST[ 'type' ];
    switch( $type ) {
        case 'admin':
            $searchFrom = array( 
                'tuotesarjat' =&gt; array('sarjanimi', 'laattatyyppi'),
                'tuotteet'    =&gt; array('tuotenumero', 'kuvaus1', 'varikategoria1', 'varikategoria2', 'pinta', 'hakupinta')
            );
        break;
        //Use a case for each "type", exclude customer as it was defined above as a default
    }
}


Foreach Keys

No need to return $key in your foreach loop, especially since you're not using it. Just remove $key =&gt; from the declaration. You can also just use array_filter() after exploding it into an array to remove any empty array members. This will relieve some overhead and is cleaner. Also, since you probably don't want to search for the same term twice you can use array_unique() to remove duplicates.

Repetition

Try to make it so that you repeat yourself as little as possible. This is so that if you ever need to change something, you'll only have to do so once. So...

$term .= '%';
if (strlen($term) &gt; 2) { $term = "%$term"; }

$sql .= "LIKE '$term' $andWhere GROUP BY $column";


You'll also find, if you upgrade your PHP version, that mysql() is being deprecated. mysqli() and pdo() are now the preferred methods.

Error Suppressors

DON'T USE ERROR SUPPRESSORS. This is a sign of bad code. If there is an error fix it. If you are doing it just so you don't have to type a couple extra lines, don't. Always ensure your code works 100%. The error suppressor should only be used during debugging.

Incrementing

There's got to be some better way to increment the "found" array... I thought of using a ternary operator, but that got a little messy, and at the moment, I can't think of anything else... I'll update this if I think of anything...

Question

How are there empty strings in $found?

MAX_LIVESEARCH_SUGGESTIONS

Not sure what the MAX_LIVESEARCH_SUGGESTIONS is, but would it not make more sense to perform this before creating the $found array?

$length = count( $terms );
while ( $length &gt; MAX_LIVESEARCH_SUGGESTIONS) { array_pop( $terms ); }


Notice I moved count() out of the loop. With it in the loop, it will run it every time, which means more overhead. Remove it from the loop to remove the overhead. I'm having difficulties following the program, so unless $found ends up with more elements than $terms the above should work. If there are more, then you should limit how many are recorded by breaking out of the loops once that limit is reached.

$length = count( $found );
if( $length == MAX_LIVESEARCH_SUGGESTIONS ) { break 4; } //I think its 4... might be 3...
//perform actions to set $found here


JSON

Updating your PHP will also mean that you can use the JSON library that is now standard instead of needing to recreate it.

Hope this helps!