|
rangelbacher
Starting Member
1 Post |
 Posted - 2011-07-30 : 01:18:07
|
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.
<?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'].' '.$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.
|
|
Select in suggest php, relation many-to-many