0
I am creating a search box that allows users of my system to search for products entered in the database according to their name and year of insertion (for now), the code developed so far already returns all values entered in the database if no search is performed and returns the searched value, but this only happens with an echo $data['name'] independent, and when I tried to implement this in the tables does not show any output, my idea would be that it would be possible for the user to see all the records that are entered in the database when he does not want to perform the search but that when he does a search those same data would be filtered in order to show only what was searched.
These days this is what comes to me:
HTML code (Search box):
<form method="post" action="index.php">
<input type="text" name="q" placeholder="Pesquise um produto...">
<select name="column">
<option value="">Selecionar</option>
<option value="nome">Produtos</option>
<option value="ano_atual">Produtos P/Ano</option>
</select>
<input type="submit" name="submit" value="Pesquisar">
</form>
PHP code (Search result /Table):
<?php
require_once "config.php";
if (isset($_POST['submit'])) {
$connection = new mysqli("localhost", "root", "", "pap_database");
$q = $connection->real_escape_string($_POST['q']);
$column = $connection->real_escape_string($_POST['column']);
if ($column == "" || ($column != "nome" && $column != "ano_atual"))
$column = "nome";
$sql = $connection->query("SELECT nome,preco_base,iva,stock,preco_com_iva FROM produtos WHERE $column LIKE '%$q%'");
while ($data = $sql->fetch_array())
echo "<table class='table table-bordered table-striped'>";
echo "<thead>";
echo "<tr>";
echo "<th>Produto</th>";
echo "<th>Preço Base (€)</th>";
echo "<th>Stock (Unidades)</th>";
echo "<th>IVA</th>";
echo "<th>Preço c/IVA (€)</th>";
echo "<th>Opções</th>";
echo "</tr>";
echo "</thead>";
echo "<tbody>";
echo "<tr>";
echo "<td>" . $data['nome'] . "</td>";
echo "<td>" . $data['preco_base'] . "€</td>";
echo "<td>" . $data['stock'] . " Unidades</td>";
echo "<td>" . $data['iva'] . "%</td>";
echo "<td>" . $data['preco_com_iva'] . "€</td>";
echo "<td>";
echo "<a href='read.php?id=". $data['idprodutos'] ."' title='View Record' data-toggle='tooltip'><span class='fa fa-eye-open'></span></a>";
echo "<a href='update.php?id=". $data['idprodutos'] ."' title='Update Record' data-toggle='tooltip'><span class='fa fa-pencil'></span></a>";
echo "<a href='delete.php?id=". $data['idprodutos'] ."' title='Delete Record' data-toggle='tooltip'><span class='fa fa-trash'></span></a>";
echo "</td>";
echo "</tr>";
}
else{
echo "A pesquisa efetuada não devolve nenhum registo!";
}
echo "</tbody>";
echo "</table>";
?>
Unix, why don’t you take out the Where clause if the variable
qis empty?– Lucas
What do you mean? @Lucas?
– UniX
With the code as it is now, if you do not set anything in the search which information is returned? Does not return all records?
– João Martins
@João Martins, with the code I have now if I do not research nothing shows, if searching also shows nothing.
– UniX
@Joãomartins but I already checked with an independent echo (without the table etc) and it shows the data that is in db according to the query
– UniX
If you withdraw the clause
WHEREof your query, which returns?– João Martins
You can debug what you are receiving to better understand your own code, first add print_r() the $q and $column variables, create a $query variable and put it getting your sql mounted! ex: $query = "SELECT name,preco_base,Iva,stock,preco_com_iva FROM products WHERE $column LIKE '%$q%'"; and a print_r in it tbm. then run $sql = $Connection->query($query);, so you will see each step to better identify the error.
– Marcus Italo
@Marcusitalo I will try
– UniX
@Joãomartins se tirar (Fatal error: Uncaught Error: Call to a Member Function fetch_array() on Boolean in C: xampp htdocs administracao produtos index.php:180 Stack trace: #0 {main} in C: xampp htdocs administracao produtos index.php on line 180)
– UniX
What I think @Joãomartins meant is this: $Where = (($column != "" ))? WHERE $column LIKE '%$q%':''; $sql = $Connection->query("SELECT name,preco_base,Iva,stock,preco_com_iva FROM products $Where"); replacing line: if ($column == "|| ($column != " name" && $column != " ano_current")) $column = "name"; $sql = $Connection->query("SELECT name,preco_base,Iva,stock,preco_com_iva FROM products WHERE $column LIKE '%$q%'");
– Marcus Italo
What I meant was to put the following query:
SELECT nome,preco_base,iva,stock,preco_com_iva FROM produtos.– João Martins
@Joãomartins just tried SELECT and returns nothing
– UniX