Select in suggest php, relation many-to-many

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

Other Topics

Select in suggest php, relation many-to-many

Author

Topic

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources