OR operator in SQL statment not giving proper results

Question

I have an HTML search box which have an SQL query which should display data if the word matches any of 4 columns in table.

<?php
$link = mysqli_connect("localhost", "root", "", "sample");

if($link === false){
    die("ERROR: Could not connect. " . mysqli_connect_error());
}

if(isset($_REQUEST["search"])){
    $sql = "SELECT * FROM image_gallery WHERE key1 OR key2 OR key3 OR key4 LIKE ?";

    if($stmt = mysqli_prepare($link, $sql)){
        mysqli_stmt_bind_param($stmt, "s", $param_term);

        $param_term = $_REQUEST["search"] . '%';


        if(mysqli_stmt_execute($stmt)){
            $result = mysqli_stmt_get_result($stmt);

            if(mysqli_num_rows($result) > 0){
                while($row = mysqli_fetch_array($result, MYSQLI_ASSOC)){?>

SQL column

This is the complete SQL code, its displaying data sometimes only when word matches last column, doesn't display for first 3 columns when I type corresponding word.

Does this answer your question? [How to test multiple variables against a value?](https://stackoverflow.com/questions/15112125/how-to-test-multiple-variables-against-a-value) — Anonymous, Dec 26 '19 at 12:30
Please read: [Should we ever check for mysqli_connect() errors manually?](https://stackoverflow.com/q/58808332/1839439) — Dharman, Dec 26 '19 at 12:56

juergen d · Answer 1 · 2019-12-26T13:03:18.223

2

You need to declare the conditions for each column and not just list columns.
Change it like this:

SELECT * FROM image_gallery 
WHERE key1 LIKE ?
   OR key2 LIKE ? 
   OR key3 LIKE ?
   OR key4 LIKE ?

Currently you fill your parameter value after you use it. Reverse that. Replace

mysqli_stmt_bind_param($stmt, "s", $param_term);
$param_term = $_REQUEST["search"] . '%';

with

$param_term = $_REQUEST["search"] . '%';
mysqli_stmt_bind_param($stmt, "ssss", $param_term, $param_term, $param_term, $param_term);

And your table structure is not normalized and should be changed. If you have numbers in your column names then you almost always do something wrong.

edited Dec 26 '19 at 13:03

answered Dec 26 '19 at 12:27

juergen d

201,996
37
293
362

hey, i tried its but its showing me the following eroor "mysqli_stmt_bind_param(): Number of variables doesn't match number of parameters in prepared statement in C:\xampp\htdocs\sample\view.php on line 143 ERROR" – Seep Sooo Dec 26 '19 at 12:31
1

Yes, you need to bind 4 parameters now and not just one. – juergen d Dec 26 '19 at 12:31
i didnt understand, can you please tell me in plain english or show in ur code – Seep Sooo Dec 26 '19 at 12:34
1

Currently you bind one parameter here: `mysqli_stmt_bind_param($stmt, "s", $param_term);` But using my query you need to bind 4 parameters because you have now 4 `?` in your query which need to be filled. – juergen d Dec 26 '19 at 12:35
@juergend It would be useful to show in your code how to bind more than one parameter. With mysqli it is not so obvious. – Dharman Dec 26 '19 at 12:58

score 0 · Answer 2 · answered Dec 26 '19 at 12:29

0

It should be as

Also if you match exact same then you can use WHERE without LIKE

  $sql = "SELECT * FROM image_gallery WHERE key1 LIKE ? OR key2 LIKE ? OR key3 LIKE ? OR key4 LIKE ?";

answered Dec 26 '19 at 12:29

Devsi Odedra

5,244
1
23
37

OR operator in SQL statment not giving proper results

2 Answers2