Make select with LIKE and AND at the same time

Navigation
0

I have this script that does a search: 

$(function($) {
  $("#loc_menu").autocomplete({
    source: "php/search_cidades.php",
    minLength: 2,

    select: function(event, ui) {
      // Set autocomplete element to display the label
      this.value = ui.item.label;

      // Store value in hidden field
      $('#h_l').val(ui.item.id);

      // Prevent default behaviour
      return false;
    }
  });

  $("#loc_menu").click(function() {
    $('#h_l').val(0);
    $('#loc_menu').val('');
  });
});
<?php
	/******* Conexão com o bando de dados *******/
	include "../Conexao/config.php";
	mysqli_select_db($config, $database_config);
	mysqli_set_charset($config,"utf8");
	/******* Conexão com o bando de dados *******/

	//get search term
	$searchTerm = $_GET['term'];
	//$cidade_uf  = $_GET['cidade_uf'];

	$sql_1 = mysqli_query($config, "SELECT * FROM tb_sub_categorias WHERE sub_categoria LIKE '".$searchTerm."%' ORDER BY sub_categoria ASC") or die(mysqli_error($config)); // LIMIT 5

	if(@mysqli_num_rows($sql_1) <= '0'){
		//echo "$erro";	
		
		$sql_2 = mysqli_query($config, "SELECT * FROM tb_empresas WHERE razao_social LIKE '%".$searchTerm."%' AND cidade_uf = '$cidade_uf' ORDER BY razao_social ASC") or die(mysqli_error($config)); // LIMIT 5

		if(@mysqli_num_rows($sql_2) <= '0'){
			$output_array[] = array( 
				'id' => '0'
				, 'label' => 'Nenhum resultado encontrado'
				, 'value' => 'Nenhum resultado encontrado'
			);	
		}else{
			while($r_sql_2 = mysqli_fetch_array($sql_2)){
				//$selecao = $r_sql_2['razao_social'];
				//$data[]  = $selecao;
				
				$output_array[] = array( 
					'id' => "co_" . $r_sql_2['id']
					, 'label' => $r_sql_2['razao_social']
					, 'value' => $r_sql_2['razao_social']
				);					
			}
		}		
	}else{
		while($r_sql_1 = mysqli_fetch_array($sql_1)){
			//$selecao = $r_sql_1['sub_categoria'];
			//$data[]  = $selecao;
			
			$output_array[] = array( 
				'id' => "ca_" . $r_sql_1['id']
				, 'label' => $r_sql_1['sub_categoria']
				, 'value' => $r_sql_1['sub_categoria']
			);				
		}
	}

	//return json data
	echo json_encode($output_array);

	mysqli_close($config);	
?>

You'd need to refine this search. I'm trying to do it this way: 

$sql_1 = mysqli_query($config, "SELECT * FROM tb_sub_categorias WHERE sub_categoria LIKE '".$searchTerm."%' AND id_sub_cat IN (1306, 1405) ORDER BY sub_categoria ASC") or die(mysqli_error($config));

I have a category table and another customer / category table.

I would like the script to search the categories that are tied to registered customers, rather than searching for all categories.

All the attempts I've made do not make the search refine.

    
asked by anonymous 22.06.2017 / 14:09

1 answer

0

You need a Join and the simplest way is to have a FK of sub_categoria on the client. 

SELECT * FROM TB_SUB_CATEGORIA SC, CLIENTES CL
WHERE SC.CD_SUB_CATEGORIA = CL.CD_SUB_CATEGORIA
AND SC.SUB_CATEGORIA LIKE '".$SEARCHTERM."%'
AND SC.ID_SUB_CAT IN (1306, 1405)

Maybe you need to change one thing or another, but that should be it, I hope I have helped!

    
22.06.2017 / 14:33
Automatic form search using data from a string sessionStorage with expiration time