Why doesn't this SELECT query return the results I expect?

I need help with a select query, but before asking the question, I will give a short description of how my system works:

My database has 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 have already made a suggest jQuery (in the index.php), where every word I type calls suggest.php, which makes a select and returns the result into the suggestion box in 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>';


}
}


}



?>

Here are my questions:

1. Currently when the user types 't', the select brings nothing, only when the user type 'tv' is bringing the result:

   tv led tv plasm tv samsumg

   I would like that when the user type 't' the select bring me 'tv'.

2. When you type 'tv plasm' it's bringing the same name_characteristic twice:

   ex: tv plasm plasm

3. Currently my select selects the prd_name and the descriptions of table description_characteristc:

   tv led

   I would like my select could make a inverse select too, ex: led tv.

4. I would like that when the results of the sel开发者_如何学运维ect were shown, there could be a cache feature that shows the order of the most sought for the less sought; remembering that prd_name stores only 'tv'.

The help I'm looking for can be in the form of select, as in the form of procedure. Also, I can edit the php file.

---

You should split and join your search query on PHP side like this:

<?php

  $words = preg_split("/[^\\w]+/", $q);
  $first = $words[0] + "%";
  $all = implode(" ", $words) + "%";

?>

then use the variables $first and $all in this query:

SELECT  p.prd_name, d.description
FROM    product p
JOIN    description d
ON      d.prd_cod = p.prd_cod
WHERE   p.prd_name LIKE '$first'
        AND CONCAT(p.prd_name, ' ', d.description) LIKE '$all'

Create an index on product (prd_name) for this to work fast.

If you want the words matched in any order, you will have to create a FULLTEXT index on your tables (this is only possible in MyISAM):

CREATE FULLTEXT INDEX fx_product_name ON product (prd_name)
CREATE FULLTEXT INDEX fx_description_name ON description (description)

and write a query like this:

SELECT  p.prd_name, d.description
FROM    (
        SELECT  prd_cod
        FROM    product pi
        WHERE   MATCH(prd_name) AGAINST ('lcd tv' IN BOOLEAN MODE)
        UNION
        SELECT  prd_cod
        FROM    description di
        WHERE   MATCH(description) AGAINST ('lcd tv' IN BOOLEAN MODE)
        ) q
JOIN    product p
ON      p.prd_cod = q.prd_cod
JOIN    description d
ON      d.prd_cod= p.prd_cod
WHERE   MATCH(p.prd_name, d.description) AGAINST ('+lcd +tv' IN BOOLEAN MODE)

Note the search term syntax change: 'lcd tv' in the inner query and '+lcd +tv' in the outer one.

You may also want to set @@ft_min_word_len to 1 for the shorter words like tv or gps to match.

Since MySQL cannot build a fulltext index from two or more tables at once, it would be more simple if you denormalized you tables and put the prd_name into the description table. This way, you could get rid of the joins and just write:

SELECT  prd_name, description
FROM    description d
WHERE   MATCH(prd_name, description) AGAINST ('+lcd +tv' IN BOOLEAN MODE)

---

You're using the LIKE clause badly and you don't seem to know what "AND" means. It's important to separate "and" as used in casual speech from "AND" as used in programming. AND in programming means "BOTH MUST BE TRUE". "and" in casual speech can mean "one of these conditions, you know what I mean?"

Also, you shouldn't be building SQL like this, it's an accident waiting to happen. You really should find a way to bind variables into SQL statements. I don't know PHP, so I can't help with that.

First, you should be using this in your WHERE clause p.prd_name LIKE '$q%'. Try this outside PHP -- outside the web -- just as a simple SQL query: SELECT * FROM PRODUCT P WHERE P.PRD_NAME LIKE 'T%'.

Second, you should fix "AND" to be "OR", since you want one condition OR the other condition to be true. If you want for BOTH conditions to be true, hardly anything will match.