PHP search / best result considering mulptiple inputs / Ingore empty inputs

Question

qurantine time make me try some codes for practise , but i have really stack in this one.

Take a look of my files. My database connection is fine btw

Database

CREATE TABLE `excavated` (
  `owner_name` varchar(255) NOT NULL,
  `area` varchar(255) NOT NULL,
  `street` varchar(255) NOT NULL,
  `building_block` varchar(255) NOT NULL,
  `year` varchar(255) NOT NULL,
  `number` varchar(255) NOT NULL,
  `archeologist` varchar(255) NOT NULL,
  `key_words` varchar(255) NOT NULL,
  `filename` varchar(255) NOT NULL,
  `size` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

download_index.php (my form)

<form id="form" action="filesLogicDownload.php" method="post">
<?php include 'download_info.php'; ?>
<div id="preview"><img src="" width="150px"/></div><br>
<input class="btn btn-success" type="submit" value="Search">
</form>

download_info.php (All the values that user can give. He dont have to give all of them)

<!--Όνομα Ιδιοκτήτη -->
<div class="form-group">
<label>Όνομα Ιδιοκτήτη</label>
<input type="text" class="form-control" id="owner_name" name="owner_name" placeholder="Όνομα Ιδιοκτήτη" >
</div>


<!--Περιοχή -->
<div class="form-group">
<label>Περιοχή</label>
<input type="text" class="form-control" id="area" name="area" placeholder="Περιοχή" >
</div>

<!--Οδός / Θέση -->
<div class="form-group">
<label>Οδός / Θέση</label>
<input type="text" class="form-control" id="street" name="street" placeholder="Οδός / Θέση" >
</div>

<!--Οικοδομικό Τετράγωνο -->
<div class="form-group">
<label>Οικοδομικό Τετράγωνο (Ο.Τ)</label>
<input type="text" class="form-control" id="building_block" name="building_block" placeholder="Οικοδομικό Τετράγωνο (Ο.Τ)" >
</div>

<!--Έτος Ανασκαφής -->
<div class="form-group">
<label>Έτος Ανασκαφής</label>
<input type="text" class="form-control" id="year" name="year" placeholder="Έτος Ανασκαφής" >
</div>

<!--Αριθμός Σχεδιαστικόυ Αρχείου -->
<div class="form-group">
<label>Αριθμός Σχεδιαστικού Αρχείου</label>
<input type="text" class="form-control" id="number" name="number" placeholder="Αριθμός Σχεδιαστικού Αρχείου" >

<!--Λέξεις Κλειδία -->
</div>
<div class="form-group">
<label>Λέξεις Κλειδία</label>
<input type="text" class="form-control" id="key_words" name="key_words" placeholder="Λέξεις Κλειδία" >
</div>

<!--Αρχαιολόγος -->
<div class="form-group">
<label>Αρχαιολόγος</label>
<input type="text" class="form-control" id="archeologist" name="archeologist" placeholder="Αρχαιολόγος" >
</div>

filesLogicDownload.php (Here i want to display the values when he press the button)

<?php
include 'database_connection.php';

if(!empty($_POST['owner_name'])){
    $owner_name = $_POST['owner_name'];
}else {
    $owner_name = "0";
}
if(!empty($_POST['area'])){
    $area = $_POST['area'];
}else {
    $area = "0";
}
if(!empty($_POST['street'])){
    $street = $_POST['street'];
} else {
    $street = "0";
}
if(!empty($_POST['building_block'])){
    $building_block = $_POST['building_block'];
}else {
    $building_block = "0";
}
if(!empty($_POST['year'])){
    $year = $_POST['year'];
}else {
    $year = "0";
}
if(!empty($_POST['number'])){
    $number = $_POST['number'];
}else {
    $number = "0";
}
if(!empty($_POST['key_words'])){
    $key_words = $_POST['key_words'];
}else {
    $key_words = "0";
}
if(!empty($_POST['archeologist'])){
    $archeologist = $_POST['archeologist'];
}else {
    $archeologist = "0";
}


$sql ="SELECT * FROM employees
    WHERE 
    'owner_name' LIKE '%{$owner_name}%' 
    OR 'area' LIKE '%{$area}%' 
    OR 'street' LIKE '%{$street}%' 
    OR building_block LIKE '%{$building_block}%' 
    OR 'year' LIKE '%{$year}%' 
    OR 'number' LIKE '%{$number}%' 
    OR archeologist LIKE '%{$archeologist}%'; ";
$result = mysqli_query($conn, $sql);      
$resultCheck = mysqli_num_rows($result);
?>
<table>
<th>Όνομα Ιδιοκτήτη</th>
<th>Περιοχή</th>
<th>Οδός / Θέση</th>
<th>Οικοδομικό Τετράγωνο (Ο.Τ)</th>
<th>Έτος Ανασκαφής</th>
<th>Αριθμός Σχεδιαστικού  Αρχείου</th>
<th>Λέξεις Κλειδία</th>
<th>Αρχαιολόγος</th>
<th>Μέγεθος Αρχείου</th>
<?php
if ($resultCheck > 0) {
    while  ($row = mysqli_fetch_assoc($result)){
?>
        <tr>
            <td><?php echo $row['owner_name']; ?></td>
            <td><?php echo $row['area']; ?></td>
            <td><?php echo $row['street']; ?></td>
            <td><?php echo $row['building_block']; ?></td>
            <td><?php echo $row['year']; ?></td>
            <td><?php echo $row['number']; ?></td>
            <td><?php echo $row['archeologist']; ?></td>
            <td><?php echo $row['key_words']; ?></td>
            <td><?php echo $row['filename']; ?></td>
            <td><?php echo $row['size']; ?></td>

        </tr>
<?php
}
}
mysqli_close($conn);
?>
</table>
</html>

Well first of all , i want to figure out , how i can ignore in my search when he dont type value in some of my inputs.

For example if he give only 3 values , then my code will display all the database results considering them. The same if he type only 1 or more.

Everytime i dont know what values and how many he will choose.

And at last i want to display only the results that are correct.

When he type year and number , then the results should be like the correct year AND number.

Not displaying all the results that have year OR number.

For example if year=2005 and number=1 :

DONT DISPLAY:

year = 2005 number=1

year = 2005 number=2

year = 2006 number=1

*year = 2007 number=1

BUT DISPLAY ONLY:

year = 2005 number = 1

I hope, you will understand my explanation and what have i in my mind. If it's able to achieve that i would be glad if anyone have ideas.

Thanks , and take care!

What specific inputs do you want to not run the database query if they don't have a value? — The Codesee, Mar 29 '20 at 13:27
Does this answer your question? [Correct way to use LIKE '%{$var}%' with prepared statements? \[mysqli\]](https://stackoverflow.com/questions/28385145/correct-way-to-use-like-var-with-prepared-statements-mysqli) — Dharman, Mar 29 '20 at 16:37

score -1 · Answer 1 · answered Mar 29 '20 at 14:04

-1

If you need to get data with strong AND condition, it could look like this

<?php

include 'database_connection.php';
$fields = ['owner_name','area','street','building_block','year','number','archeologist','key_words','filename','size'];
$sql ="SELECT * FROM employees WHERE 1=1";
$condition = " AND ";
foreach($fields as $f) {
    if ( !empty($_POST[ $f ] ) ) {
    $sql .= ($condition . $f . " LIKE '%" . $_POST[ $f ] . "%'");
    }
}
$sql .= ';';

$result = mysqli_query($conn, $sql);
...

answered Mar 29 '20 at 14:04

Banzay

9,310
2
27
46

@Dharman , thank you a lot, sir, for such a great note even when the question was tagged by sql and database tags and has nothing to do with security issues, which, I believe, question's author is concerning about. – Banzay Mar 29 '20 at 18:20
That is not the point. What you are suggesting is a wrong way of doing things. SQL injection is the main reason why it's wrong, but if you wanted to do it properly you must use prepared statements. Even if the question is not asking for security advice it doesn't mean you should provide a wrong way of doing it. You should put effort into your solutions. – Dharman Mar 29 '20 at 19:20

PHP search / best result considering mulptiple inputs / Ingore empty inputs

1 Answers1