Search for two columns in the database

Question

I need help on how to do a two-column search, I’m a beginner.

If I type a category it finds, if I type a city it finds, however, if I type category and city ex: agencies são paulo he returns nothing. Someone can help me?

I am using PDO

<?php
include("conexao.php");

$pesquisa = $_POST["pesquisa"];


$cidades = $conn->prepare("SELECT * FROM edicao WHERE categoria LIKE ? OR cidade LIKE ? ");
$cidades->bindValue(1, "%" . $pesquisa . "%");
$cidades->bindValue(2, "%" . $pesquisa . "%");
$cidades->execute();

$resultado = $cidades->fetchAll();

if (!empty($pesquisa)) {


    foreach ($resultado as $uf_s) {
        ?>
        <div class="row" style="text-align: center;">
            <div class="col-md-3"></div>
            <div class="col-md-5" style=" margin-left: 5%; background-color: #E8E8E8; border-radius: 20px;">
                <br>
                <div class="col-md-4" style="text-align: left;">
                    <?php
                    echo "<a href='anuncio.php?id_usuario=" . $uf_s['id_user'] . "'style='text-decoration: none;'><h4 style='color:black;'> " . $uf_s['nomefantazia'] . "</h4></a>";
                    ?>
                    <p style=" text-align: left; "><b
                                style="color: red;">Categoria: </b><?php echo $uf_s['categoria'] . ''; ?></p>
                    <p style=" text-align: left; "><b
                                style="color: red;">SubCategoria: </b><?php echo $uf_s['subcategoria'] . ''; ?></p>
                    <p style=" text-align: left;"><b
                                style="color: red;">Segmentação: </b> <?php echo $uf_s['segmentacao'] . ''; ?></p>
                </div>
                <div class="col-md-6" style="text-align: left;">
                    <h4 style="color: red;">Dados</h4>
                    <b style="color: red;">Telefone: </b><?php echo $uf_s['telefone'] . ''; ?><br>
                    <b style="color: red;">Cidade: </b><?php echo $uf_s['cidade'] . ''; ?><br>
                    <b style="color: red;">Bairro: </b><?php echo $uf_s['bairro'] . ''; ?><br>
                    <b style="color: red;">Endereço: </b><?php echo $uf_s['endereco'] . ''; ?>
                </div>
                <div class="col-md-2" style="margin-top: 1%; color: grey;">
                    <?php
                    if (!isset($uf_s['logo'])) {
                        ?>
                        <img src="assets/img/iconsemfoto.png" style="width: 100%;">
                        <?php
                    } else {
                        ?>
                        <img src="<?php echo substr($uf_s['logo'], 1); ?>" style="width: 100%;">
                        <?php
                    }
                    ?>
                    <br>
                    <?php
                    echo "<a href='anuncio.php?id_usuario=" . $uf_s['id_user'] . "'style='text-decoration: none;'>"
                    ?>
                    <h5 style="color: red; text-decoration: none;">Saiba Mais...</h5>
                    </a>
                </div>
                <br>
            </div>
            <div class="col-md-1"></div>
        </div>
        <br>
        <?php
    }
} else {
    echo '<h4> Informe sua pesquisa no campo acima </h4>';
}
?>

Answer 1

The way you are doing your research is passing the same value to both columns, that is, you will never find the category together with the city. A solution would be to create a new table in your select.

SQL in MYSQL:

SELECT * 

  FROM 
    (SELECT edicao.campo1, edicao.campo2, edicao.campo3, concat(categoria,' ', cidade) as categoria_cidade FROM edicao) nova_edicao 
 WHERE nova_edicao.categoria_cidade LIKE "%agencias são paulo%"

This way the two columns are united and you can search in the two columns at the same time.