I want assist with a choose query, before wondering, I'll provide a short description of methods my system works:

My database includes a many-to-many relationship:

table product:
prd_cod(pk) //stores the product code ex: 0,1,2
cat_cod(fk) 
prd_name    //stores the product name, ex: tv, gps, notebook

table description_characteristc:
prd_cod(fk) 
id_characteristic(fk)
description //stores the description of the characteristic, ex: sony, 1kg, hj10

table characteristic:
id_characteristic (pk)
name_characteristic //store the name of characteristic, ex: brand, weight, model

I've already designed a suggest jQuery (within the index.php), where every word I type calls suggest.php, making a choose and returns the end result in to the suggestion box within the index:

<?php



header('Content-type: text/html; charset=UTF-8');


$hostname = 'localhost';

$username = 'root';

$password = '';

$dbname = 'cpd';



mysql_connect($hostname, $username, $password)or die('Erro ao tentar conecta o banco 
de dados.');


mysql_select_db( $dbname );



if( isset( $_REQUEST['query'] ) && $_REQUEST['query'] != "" )

{
$q = mysql_real_escape_string( $_REQUEST['query'] );



if( isset( $_REQUEST['identifier'] ) && $_REQUEST['identifier'] == "sugestao")

{
$sql = "SELECT p.prd_name, d.description

FROM product p

INNER JOIN description_characteristc d using (prd_cod)

WHERE '".$q."' like concat(p.prd_name, '%') AND

concat(p.prd_name, ' ', d.description) like concat('".$q."', '%')LIMIT 10";



$r = mysql_query( $sql );


if ( $r )
{

echo '<ul>'."\n";

$cont = 0;

while( $l = mysql_fetch_array( $r ) ){

    $p = $l['nome'];

    $p = preg_replace('/(' . $q . ')/i', '<span style="font-
                     weight:bold;">$1</span>',  

$l['prd_nome'].'&nbsp;'.$l['descricao'].'&nbsp;'.$l['descricao']);

echo "\t".'<li id="autocomplete_'.$cont.'"              
  rel="'.$l['prd_nome'].'.'.$l['descricao'].'">'. utf8_encode( $p ) .'</li>'."\n";
$cont++;
}

echo '</ul>';


}
}


}



?>

Listed here are my questions:

  1. Presently once the user types 't', the choose brings nothing, only if the consumer type 'tv' is getting the end result:

    tv led tv plasm tv samsumg

    I'd like that after the consumer type 't' the choose bring me 'tv'.

  2. Whenever you type 'tv plasm' it's getting exactly the same title_characteristic two times:

    ex: tv plasm plasm

  3. Presently my choose chooses the prd_title and also the explanations of table description_characteristc:

    tv led

    I'd like my choose might make a inverse choose too, ex: led tv.

  4. I'd like that after the outcomes from the choose were proven, there might be a cache feature that shows an order of the very searched for for that less searched for recalling that prd_title stores only 'tv'.

The assistance I am searching for could be as choose, as with the type of procedure. Also, I'm able to edit the php file.

You are while using LIKE clause badly and also you don't appear to be aware what "AND" means. You need to separate "and" as utilized in casual speech from "AND" as utilized in programming. As well as in programming means "BOTH Should Be TRUE". "and" in casual speech often means "one of these simple conditions, guess what happens I am talking about?Inch

Also, you should not build SQL such as this, this is an accident waiting to occur. You should try to bind variables into SQL claims. I'm not sure PHP, and so i won't assist with this.

First, you ought to be by using this inside your WHERE clause p.prd_name LIKE '$q%'. Do this outdoors PHP -- outdoors the net -- just like an easy SQL query: SELECT * FROM PRODUCT P WHERE P.PRD_NAME LIKE 'T%'.

Second, you need to fix "AND" to become "OR", because you have to have one condition Or another condition to be real. If you would like for conditions to be real, almost nothing will match.