Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL MySQL database query
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
Edited 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
0 OriginalGriff 310
1 PhilLenoir 164
2 Richard MacCutchan 160
3 Sharmanuj 146
4 Magic Wonder 129
0 Sergey Alexandrovich Kryukov 6,081
1 OriginalGriff 5,115
2 CPallini 2,473
3 Richard MacCutchan 1,597
4 Abhinav S 1,505


Advertise | Privacy | Mobile
Web01 | 2.8.140814.1 | Last Updated 8 Jan 2013
Copyright © CodeProject, 1999-2014
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