Advanced search/filter?

General PHP Help
#1

Hello Boys and Girls :slight_smile:

I got a problem. I want to write a sql query where the search can find more than one string in a var.
I try to explain it.

$test = $_POST[‘var’]; //here I got my input from the form to the var ‘test’

$query = "SELECT * FROM table WHERE var LIKE ‘%$test%’;

If there is a entry in the table thats says “Hello World” and I write “Worl” in the form I get the entry because of the Wildcards “%”.

But I want to write for example “llo; orld” in the form and get the entry. Just two contiguous strings who are seperated by a “;”. Hope it’s understandable what I mean.

Can you help me please :slight_smile: ?

#2

You would generally use a space as the separator. You would then explode the search input on the separator character, then dynamically build the sql query with each search term OR’ed together.

Also, don’t put external, unknown, unsafe, dynamic values directly into an sql query. Use a prepared query instead.

1 Like
#3

That’s awesome thanks.

And do you have a site for me where I can learn this ?

Also, don’t put external, unknown, unsafe, dynamic values directly into an sql query. Use a prepared query instead.

#4

#Please teach me master

Now I got for example:
$query = "SELECT * FROM table WHERE var LIKE ‘%$testarray[0]%’ OR var LIKE ‘%$testarray[1]%’;

But how can I just go throw the whole array. Can I somehow write a loop in a sql query?

#5
1 Like
#6

Example -

<?php

// the base query - you should list the columns you are selecting
$query = "SELECT * FROM table";

$terms = []; // array to hold the search terms
$params = []; // array to hold the prepared query input values

// condition the input - a search, that determines what will be displayed on a page should use a $_GET input
$search = isset($_GET['search']) ? $_GET['search'] : '';

// if there's no search input, decide what to do
// is this an error for the user or should the query match all data?
if(!$search)
{
	// if this is an error, setup and display a user message, then don't execute the code for the query at all
	// if this should match all data, skip the part of the code for building the WHERE part of the query and just execute the base query
}
else
{
	// explode the search term and trim each part
	$parts = array_map('trim',explode(' ',$search));

	foreach($parts as $part)
	{
		$terms[] = "var LIKE ?";
		$params[] = "%$part%";
	}

	if($terms)
	{
		$query .= " WHERE " . implode(' OR ',$terms);
	}
}

// examine the sql and any input values
echo $query; echo '<pre>'; print_r($params); echo '</pre>';

// the following uses the PDO extension
// prepare and execute the query
$stmt = $pdo->prepare($query);
// note: the following works as expected even if $params is an empty array
$stmt->execute($params);
// fetch all the data, to be tested/looped over at the appropriate point in the html document
$serach_result = $stmt->fetchAll();
#7

Thats awesome. Thank you very very much!

Sponsor our Newsletter | Privacy Policy | Terms of Service