Mark Alan Mark Alan - 7 months ago 13
SQL Question

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

Answer

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);
?>