Mysql search function

Question

I am trying to setup a search function where i can either type in what i want or i can select it from a drop down menu, however when the fields are blank i want it to show everything.

Right now when i search for something it will work however when the fields are blank it does not show anything at all. I am using prepare and bind_params to setup the mysql query which is why it is making it difficult to setup because if the variable is empty im not sure how to easily remove that section of the query and also change the amount of variables that are being binded to the query. Here is the query

$stmt = $conn->prepare("SELECT * FROM re_tblcombinationlist WHERE active != ? AND ModifierKey = ? AND (LootItem1Key = ? OR LootItem2Key = ? ) ORDER BY ReportedOn DESC LIMIT ? , ?");
$stmt->bind_param("isssii", $i = 0, $modifier, $lootsearch, $lootsearch, $limits, $max);
$recent1 = infoo($stmt);

I tried to solve it by adding this if then statement but it didnt change anything

if(!isset($_POST["modifier"])){
$modifier = '';
}else{
$modifier = clean($_POST["modifier"]);
}

if(!isset($_POST["lootsearch"])){
$lootsearch = '';
}else{
$lootsearch = clean($_POST["lootsearch"]);
}

Basically if modifier or lootsearch are empty i dont want that section to be in the mysql query, which is easy to do however it then makes it very difficult to deal with the amount of variables to bind so i was trying to find a way to make it search everything if the variable is empty.

Thanks

You need to actually code some logic that will change the statement. Have you done that? — Félix Adriyel Gagnon-Grenier
– Félix Adriyel Gagnon-Grenier, Commented Oct 3, 2015 at 21:41
Well you f.e. could use LIKE as the comparison operator, and then simply use % as the “search value” for any empty field … — C3roe
– C3roe, Commented Oct 3, 2015 at 21:42
I had an if then statement that would set a variable equal to "AND ModifierKey = ?" if the search was set and i put that variable into the query however the issue is then how to change the bind param because either 1 of the search fields could be set or both. I will try using LIKE — Adonai
– Adonai, Commented Oct 3, 2015 at 21:48
Ok i tried the LIKE statement and i am getting better results however i cannot get the entire result i need. If i do the query like so: '$stmt = $conn->prepare("SELECT * FROM re_tblcombinationlist WHERE active != ? AND (LootItem1Key LIKE ? OR LootItem2Key LIKE ?) ORDER BY ReportedOn DESC LIMIT ? , ?");' it will work and if i do it like this: '$stmt = $conn->prepare("SELECT * FROM re_tblcombinationlist WHERE active != ? AND ModifierKey LIKE ? ORDER BY ReportedOn DESC LIMIT ? , ?");' It will work but if i combine them it does not — Adonai
– Adonai, Commented Oct 3, 2015 at 22:00

Kostas Mitsarakis · Accepted Answer · 2015-10-04 15:12:58Z

1

I think that i found a solution to your problem based on this: http://php.net/manual/en/mysqli-stmt.bind-param.php#109256

First create this class:

class BindParam { 
    private $values = array();
    private $types = ''; 

    public function add( $type, $value ){ 
        $this->values[] = $value; 
        $this->types .= $type; 
    } 

    public function get() { 
        return array_merge(array($this->types), $this->values); 
    } 
}

After this, make sure you import that class in your code and then use insert the following lines in your search file:

//User inputs, just for testing
$active = 0;
$modifier = 5;
$lootsearch = 'item';
$limits = 3;
$max = 5;

//Conditional binding
$bindParam = new BindParam(); 
$binds = array(); 
$binds[] = 'active = ?';
$bindParam->add('i', $active);

$query = "SELECT * FROM re_tblcombinationlist WHERE ";
if (!empty($modifier)) {
    $binds[] = 'ModifierKey = ?';
    $bindParam->add('s', $modifier);
} 
if (!empty($lootsearch)) {
    $binds[] = ' (LootItem1Key LIKE ? OR LootItem2Key LIKE ?) ';
    $bindParam->add('s', $lootsearch);
    $bindParam->add('s', $lootsearch);
}

$query .= implode(" AND ", $binds);
$query .= " ORDER BY ReportedOn DESC LIMIT ? , ?";

$bindParam->add('i', $limits);
$bindParam->add('i', $max);

//Uncomment this for debugging

//echo $query . '<br/>';

//Using the above user inputs query should be:
//SELECT * FROM re_tblcombinationlist WHERE active = ? AND ModifierKey = ? AND (LootItem1Key LIKE ? OR LootItem2Key LIKE ?) ORDER BY ReportedOn DESC LIMIT ? , ?

//var_dump($bindParam->get());

//Maybe you have to experiment a bit with those lines
$stmt = $conn->prepare($query);
$stmt->bind_param($bindParam->get());
$recent1 = infoo($stmt);

answered Oct 4, 2015 at 15:12

Kostas Mitsarakis

4,7473 gold badges26 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Adonai Over a year ago

This seems like it would work pretty nicely. I had come up with a temporary solution but it was more of a bandaid than anything. I will give this a go and see what happens

Adonai Over a year ago

Ok i tried this and it does work to an extent. I i leave the code as is it does not work because $bindParam->get() returns an array. I tried to implode it but that confused the bind+_param function because its technically only 1 string at that point. However if i set it equal to a variable such as $array, i can then use $array[0], $array[1], ... etc. however that doesnt really work since it is then set by a static number of variables instead of a dynamic number like we are trying to achieve.

Kostas Mitsarakis Over a year ago

Also if you use PDO maybe you can create the sql query as a string and populate the $binds in a similar conditionally way and execute the statement like Example 3 here: php.net/manual/en/pdostatement.execute.php

Adonai Over a year ago

I thought about using PDO however the site is just for personal use and wont really benefit since i always use mysql anyway... I tried to use a while statement in the bind_param to get it to show each variable in the array as a separate variable however that failed as well.

Adonai · Accepted Answer · 2015-10-04 23:47:41Z

The final solution that i chose to go with on this was a combination of my last post and Kostas post.

First we add the class:

class BindParam { 
private $values = array();
private $types = ''; 

public function add( $type, &$value ){ 
    $this->values[] = &$value; 
    $this->types .= $type; 
} 

public function get() { 
    $array = array_merge(array($this->types), $this->values);
    foreach($array as $key => $value)
    $refs[$key] = &$array[$key];
    return $refs; 
} 
}

Then we add this code where ever we want to use the query dynamically:

//User inputs, just for testing
$active = 0;
$modifier = 5;
$lootsearch = 'item';
$limits = 3;
$max = 5;

//Conditional binding
$bindParam = new BindParam(); 
$binds = array(); 
$binds[] = 'active = ?';
$bindParam->add('i', $active);

$query = "SELECT * FROM re_tblcombinationlist WHERE ";
if (!empty($modifier)) {
$binds[] = ' AND ModifierKey = ?';
$bindParam->add('s', $modifier);
} 
if (!empty($lootsearch)) {
$binds[] = ' AND (LootItem1Key LIKE ? OR LootItem2Key LIKE ?) ';
$bindParam->add('s', $lootsearch);
$bindParam->add('s', $lootsearch);
}

$query .= implode(" ", $binds);//removed the AND to allow for AND and OR
$query .= " ORDER BY ReportedOn DESC LIMIT ? , ?";

$bindParam->add('i', $limits);
$bindParam->add('i', $max);

//Uncomment this for debugging

//echo $query . '<br/>';

//Using the above user inputs query should be:
//SELECT * FROM re_tblcombinationlist WHERE active = ? AND ModifierKey = ? AND     (LootItem1Key LIKE ? OR LootItem2Key LIKE ?) ORDER BY ReportedOn DESC LIMIT ? ,     ?

//var_dump($bindParam->get());

//Maybe you have to experiment a bit with those lines
$stmt = $conn->prepare($query);
call_user_func_array(array($stmt, 'bind_param'), $bindParam->get());//Required in order to use the array given by bindParam->get()
$recent1 = infoo($stmt);

Adonai · Accepted Answer · 2015-10-04 19:49:54Z

0

Alright I figured out how to get this to work. First off, most of what Kostas had stated is the solution and I'm not trying to take credit for his work however it needed to be changed slightly to work properly.

First i added a function

function reference($arr){
$refs = array();
foreach($arr as $key => $value)
    $refs[$key] = &$arr[$key];
return $refs;

}

and then i changed this(from Kostas' code)

$stmt->bind_param($bindParam->get());

to

call_user_func_array(array($stmt, 'bind_param'), reference($bindParam->get()));

answered Oct 4, 2015 at 19:49

Adonai

733 silver badges13 bronze badges

4 Comments

Kostas Mitsarakis Over a year ago

Great combination! I knew i was very close. Could you check if the following change on my code works? Change this : public function add( $type, $value ) **To this : public function add( $type, &$value )

Adonai Over a year ago

That did not seem to work. I did however combine the function i added into the get() function and eliminated the need for a second function

Kostas Mitsarakis Over a year ago

Nice. If you want combine all the code and set a new answer as the accepted. I ll voteup for it.

Adonai Over a year ago

Ok i posted the final combination. Thanks for the help

Collectives™ on Stack Overflow

Mysql search function

3 Answers 3

4 Comments

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related