Select with LIKE and AND at the same time

Asked

Viewed 41 times

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 that 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 table of categories and another of customers/categories.

I would like the script to search for the categories that are attached to the registered clients, instead of searching all categories.

All the attempts I’ve made don’t refine the research.

php javascript self-cmplete

  • This query looks for subcategories with id 1306 or 1405 and starts with the search term. That’s what you want?

  • These ids were an example. If you follow this dynamic, I will select before and extract the ids from an array.

1 answer

0


You need a Join and the simplest way is to have a FK of sub_categoria as a 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 I need to modify something or other, but that must be it, I hope to have helped!

Browser other questions tagged php javascript self-cmplete

You are not signed in. Login or sign up in order to post.