CODEWITHSUNDEEP
phpmysqlsql-server
Mark Alan
Mark Alan

Reputation: 455

sql search with 2 or more fields

Created an sql search query with having multiple fields I created using if else condition it is working fine but if 1 and 2nd field is emty and 3rd field is not then it dies not work just because of OR keyword please advise how I would be able to correct this 

<form method="POST" action="search.php?action=go">
            <li>
                <h3>Player</h3>
                <input type="text" class="form-control" placeholder="Dylan Scout" name="playername" value="<?php if(isset($_POST["playername"])) {echo $_POST["playername"];} ?>">
            </li>
            <li>
                <h3>Age</h3>
                <input type="text" class="form-control" placeholder="25" name="age" value="<?php if(isset($_POST["age"])) {echo $_POST["age"];} ?>">
            </li>
            <li>
                <h3>Country</h3>
                <input type="text" class="form-control" placeholder="Wallabies" name="country" value="<?php if(isset($_POST["country"])) {echo $_POST["country"];} ?>">
            </li>
            <li>
                <h3>Club</h3>
                <input type="text" class="form-control" placeholder="Eagle" name="club" value="<?php if(isset($_POST["club"])) {echo $_POST["club"];} ?>">
            </li>
            <li>
                <button type="submit" name="search">Search</button>
            </li>
        </form>

And here is my sql php query 

<?php 
        if(isset($_GET["action"]) == 'go') {
            $stmt = "SELECT * FROM users WHERE";
            if($_POST["playername"]) {
                $stmt .= " OR fname LIKE '%".$_POST["playername"]."%' OR lname LIKE '%".$_POST["playername"]."%'";
            } 
            if($_POST["age"]) {
                $stmt .= " OR age LIKE '%".$_POST["age"]."%' ";
            }
            if($_POST["country"]) {
                $stmt .= " OR country LIKE '%".$_POST["country"]."%' ";
            }
            if($_POST["club"]) {
                $stmt .= " OR club LIKE '%".$_POST["club"]."%' ";
            }
        } else {
            $stmt = "SELECT * FROM users ";
        }   
        echo $stmt . "<br />";
        $sql = mysqli_query($connection, $stmt);
  ?>

Please let me know how would I be able to make it work properly as if i write on 3rd fields and leave other fields empty then it will become asWHERE OR which will become obviously wrong query and won't work

Thank You

Upvotes: 0

Views: 83

Answers (3)

ssnake
ssnake

Reputation: 375

add where condition to an array, and next use implode function, for example:

<?php 
if(isset($_GET["action"]) == 'go') {
    $stmt = "SELECT * FROM users";
    if($_POST["playername"]) {
        $where[] = "fname LIKE '%".$_POST["playername"]."%' OR lname LIKE '%".$_POST["playername"]."%'";
    } 
    if($_POST["age"]) {
        $where[] = "age LIKE '%".$_POST["age"]."%' ";
    }
    if($_POST["country"]) {
        $where[] = "country LIKE '%".$_POST["country"]."%' ";
    }
    if($_POST["club"]) {
        $where[] = "club LIKE '%".$_POST["club"]."%' ";
    }

    if(count($where))
        $stmt .= " WHERE " . implode(" OR ", $where);

    echo $stmt . "<br />";
    $sql = mysqli_query($connection, $stmt);
?>

Upvotes: 0

androbin
androbin

Reputation: 1759

The function implode will help you.

Add them into an array and connect them after.

<?php 
$array = array();
if (isset($_POST["playername"]))
    $array[] = "fname LIKE '%".$_POST["playername"]."%' OR lname LIKE '%".$_POST["playername"]."%";
if (isset($_POST["age"]))

   ...

$stmt = "SELECT * FROM users";
if (count($array) > 0)
    $stmt .= " WHERE " . implode(" OR ",$array);
$sql = mysqli_query($connection, $stmt);
?>

Upvotes: 4

RJParikh
RJParikh

Reputation: 4166

Try this. Using implode() you can achieve this.

<?php 

    if(isset($_GET["action"]) == 'go') {

        $where = array();
        if($_POST["playername"]) {
            $where[] = " OR fname LIKE '%".$_POST["playername"]."%' OR lname LIKE '%".$_POST["playername"]."%'";
        } 
        if($_POST["age"]) {
            $where[] = " OR age LIKE '%".$_POST["age"]."%' ";
        }
        if($_POST["country"]) {
            $where[] = " OR country LIKE '%".$_POST["country"]."%' ";
        }
        if($_POST["club"]) {
            $where[] = " OR club LIKE '%".$_POST["club"]."%' ";
        }

        if(!empty($where))
        {
            $stmt = "SELECT * FROM users WHERE " . implode(" AND ", $where) ." ";
        }
        else
        {
            $stmt = "SELECT * FROM users ";
        }

    } else {
        $stmt = "SELECT * FROM users ";
    }   

    echo $stmt . "<br />";
    $sql = mysqli_query($connection, $stmt);
  ?>

Upvotes: 0

Related Questions