Using multiple search criteria

[dudleylearning]

Hi,

 

I am in the process of creating a search on my website. It has two search criteria (joke_text as a text field and author as a list box). When I try to search using just the joke_text field it presents no data. But when I add data to both joke_text and author the search presents the correct data. It also works when I select just an author. Here is a snippet of the code that I have used: 

 

<div>
			<form action="search.php" method="POST">
            <div class="control-group">
				<label class="control-label">Joke</label>
				<div class="controls">
					<input type="text" name="text" id="text">
				</div>
			</div>
                
				<div class="control-group">
				<label class="control-label">Author</label>
				<div class="controls">
				  <label for="author">author</label>
				  <select name="author" id="author">
				    <option>select</option>
				    <option value="1">1</option>
				    <option value="2">2</option>
				    <option value="3">3</option>
			      </select>
				</div>
			</div>
                
                
                <button type="submit" class="btn btn-orange">Go</button>
			</form>
		</div>
		
		<?php
			# retrieve text from search field
			if (!empty($_GET['text']) || !empty($_POST)) {
				$joke_text_search = $_REQUEST['text'];
				$joke_text_search_filter = str_replace(array('%', '_'),'\\',$joke_text_search);
				$joke_text = '%' . $joke_text_search_filter . '%';
				
				$author_select = $_REQUEST['author'];
				$author = '%' . $author_select . '%';
				
				$search  = $dbConnection->prepare("SELECT * FROM joke WHERE author_id LIKE ? AND joke_text LIKE ?");
				$search->execute([$author, $joke_text]);
				$result = $search->fetchAll(); 

Could someone take a look and give me some guidance to what I should be looking at 

 

[ginerjm]

How about you take a look at what the actual query statement looks like in each situation and decide for yourself what needs to be changed? Do an echo of the query after it is built and make sure you are building the 'correct' statement.

[Barand]

If you aren't searching for the author, don't include the author in the WHERE clause. Similarly, exclude joke_text if there is no value for that.

 

You have a colourful mix of REQUEST, GET and POST variables in your code. Your form uses 'post', so you should be using $_POST. (Actually, as you only want to get data for display, you should be using form method "get"). Don't use REQUEST.

Edited January 16, 2017 by Barand

[Jacques1]

The LIKE fragments are also badly broken. For some reason, you replace all percent characters and underscores in the text parameter with literal backslashes. That's not what I told you in your last thread. And the author parameter isn't escaped at all. Why do you even use LIKE for a numeric ID? Shouldn't that be an exact match?

 

A lot of this doesn't make sense, and it seems you're randomly typing code on your keyboard with no real concept. That's not a good approach. Make sure you understand what you want and then implement it systematically. For example, start with a proper search form just for the joke text. Also write a function which correctly(!) escapes a string for a LIKE context.

function escape_mysql_like($input, $escape_char = '\\')
{
    return str_replace(['%', '_'], [$escape_char.'%', $escape_char.'_'], $input);
}