Click here to Skip to main content
12,954,856 members (75,827 online)
Rate this:
 
Please Sign up or sign in to vote.
See more:
I need help with a select, but before asking the question, I will give a short description of how my system works:

my database has a many-to-many relationship, look below:

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 ready made in the index.php, a suggest jquery, that every word I type it's calls the suggest.php look below, that makes a select and returns the result into the suggestion box in the index.

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-<br mode=" hold=" />                         weight:bold;">$1</span>',  
 
$l['prd_nome'].' '.$l['descricao'].' '.$l['descricao']);
 
echo "\t".'<li id="autocomplete_'.$cont.'">
      rel="'.$l['prd_nome'].'.'.$l['descricao'].'">'. utf8_encode( $p ) .':</li:>'."\n";
    $cont++;
    }
 
    echo ':</ul:>';
 

}
}
 

}
 

 
?>


questions:

1° currently when the user type '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 two times the same name_characteristic:

ex: tv plasm plasm

3° currently my select selects the prd_name and the descriptions of table description_characteristc, ex: 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 select were shown, could have 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, php file can also be edited, i thank you all.
Posted 29-Jul-11 21:45pm
Updated 30-Jul-11 22:51pm
v3

1 solution

Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 12

Hi,

You need to just change the where clause

SELECT p.prd_name, d.description
 
    FROM product p
 
    INNER JOIN description_characteristc d using (prd_cod)
 
    WHERE
 
    concat(p.prd_name, ' ', d.description) like concat('".$q."', '%')LIMIT 10";
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

    Print Answers RSS
Top Experts
Last 24hrsThis month
OriginalGriff 6,539
CHill60 3,490
Maciej Los 3,153
ppolymorphe 2,030
Jochen Arndt 1,975


Advertise | Privacy | Mobile
Web02 | 2.8.170525.1 | Last Updated 8 Jan 2013
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100