Question 216169 in PHP Question posted in
Official manual can be found here.

Multiple column research query using PHP

I have a problem. i need to research through query all text containing a substring given by a post method on php. for example, if i input an "a" or a number like "1" (char not int) i need to find all the things containing that char or substring, checking every column in the table
is it possible? i tried like this but it’s kinda messy

<?php
if ($_SERVER["REQUEST_METHOD"] == "POST") {
    $usersearch = $_POST["usersearch"];

    try {
        require_once "includes/dbh.inc.php";

        $query = "SELECT * FROM tlattine WHERE 
        tipologia LIKE :usersearch OR 
        nome LIKE :usersearch OR 
        caratteristiche LIKE :usersearch OR
        tabstyle LIKE :usersearch OR
        tabcolor LIKE :usersearch OR
        topstyle LIKE :usersearch OR
        topcolor LIKE :usersearch OR
        provenienza LIKE :usersearch OR
        produttore LIKE :usersearch OR
        sku LIKE :usersearch
        ORDER BY tipologia, provenienza, year, dimensione;";

        $stmt = $pdo->prepare($query);

        $stmt->bindParam(":usersearch", $usersearch);

        $stmt->execute();

        $results = $stmt->fetchAll(PDO::FETCH_ASSOC);

        $pdo = null;
        $stmt = null;
    } catch (PDOException $e) {
        die("Query failed: " . $e->getMessage());
    }
}
else{
    header("Location: ../index.php");
}
?>

already tried using ‘%:usersearch%’ or combining the % in any form, it seems i’m missing something which i’m totally sure but i’m dumb to find it out 🙁

Tags:

2

Answers


  1. 0

    Looking at your query I notice that you used multiple times the parameter :usersearch.

    The PHP documentation says:

    You must include a unique parameter marker for each value you wish to pass in to the statement when you call PDOStatement::execute(). You cannot use a named parameter marker of the same name more than once in a prepared statement, unless emulation mode is on.

    I would prefer avoiding enabling the emulation mode and I’d rather change the named placeholder with a positional one (using ‘?’ instead of ‘:usersearch’) and then using

    $stmt->bindParam(1, $usersearch, PDO::PARAM_STR);
$stmt->bindParam(2, $usersearch, PDO::PARAM_STR);
$stmt->bindParam(3, $usersearch, PDO::PARAM_STR);
// and so on

    One last thing, if you’re querying to hhave the column include the $usersearch variable, you should probably add % at the start and at the end of the string

    $usersearch = "%" . $_POST["usersearch"] . "%"
    Login or Signup to reply.
  2. 0

    I think changing $stmt->bindParam(":usersearch", $usersearch); to $stmt->bindParam(":usersearch", "%$usersearch%"); fix the problem.

    You can also create a function like:

    function columnsToSeach($table, $cols, $select = '*'){
    $q = "SELECT {$select} FROM {$table} WHERE ";
    foreach ($cols as $col) {
        $q .= "{$col} LIKE :usersearch OR ";
    }
    return substr($q,0,-3);
}

    and your final code would be something like this:

    <?php
if ($_SERVER["REQUEST_METHOD"] == "POST") {
    $usersearch = $_POST["usersearch"];

    try {
        require_once "includes/dbh.inc.php";

        $query = columnsToSeach('tlattine', ['tipologia', 'nome', 'caratteristiche', 'tabstyle', 'tabcolor', 'topstyle', 'topcolor' ,'provenienza', 'produttore', 'sku']);
        $query .= "ORDER BY tipologia, provenienza, year, dimensione;";

        $stmt = $pdo->prepare($query);

        $stmt->bindParam(":usersearch", "%$usersearch%");

        $stmt->execute();

        $results = $stmt->fetchAll(PDO::FETCH_ASSOC);

        $pdo = null;
        $stmt = null;
    } catch (PDOException $e) {
        die("Query failed: " . $e->getMessage());
    }
}
else{
    header("Location: ../index.php");
}
?>

    To do a similar search on another tables you just need to change columnsToSeach function parameters.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search