MYSQL - Don't add to query if string is empty

Question

I have a query that selects based on multiple things. For example, game_name is a string that can be empty if the user so chooses. Is there any way to not do the searching part for game_name if the input is "" but still look for the others?

 $stmt = $connection->prepare("SELECT * FROM $config->tablename WHERE id = ? OR game_name = ? OR gamer_tag = ? LIMIT ?");
 $stmt->bind_param("ssss", $request->id, $request->game_name, $request->gamer_tag, $request->limit);

Biggum · Accepted Answer · 2019-11-11 10:32:20Z

1

You should do a little workaround to control it and when the user wants the field to be empty, you should control that and add to query something like this:

convert this

game_name = ?

into this

game_name = ? OR game_name = game_name

So this will make always to be true for that field.

EDIT: Combining @Amado's solution and my proposal resolves in less IF statements (only my POV).

$MyWhere = "id = ? OR gamer_tag = ? OR game_name = ?"
if(game_name !="") $MyWhere = $MyWhere." OR game_name = game_name";
$stmt = $connection->prepare("SELECT * FROM $config->tablename WHERE ".$MyWhere." LIMIT ?");
$stmt->bind_param("ssss", $request->id, $request->gamer_tag, $request->game_name, $request->limit);

edited Nov 11, 2019 at 10:32

answered Nov 6, 2019 at 7:23

Biggum

3722 silver badges8 bronze badges

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

Comments

Amado · Accepted Answer · 2019-11-12 07:51:52Z

1

You can add a little checking before performing the query:

    $MyWhere = "id = ? OR gamer_tag = ?"
    if(game_name !="") $MyWhere = $MyWhere." Or game_name = ?";
    $stmt = $connection->prepare("SELECT * FROM $config->tablename WHERE ".$MyWhere." LIMIT ?");
if(game_name !=""){
       $stmt->bind_param("ssss", $request->id, $request->game_name, $request->gamer_tag, $request->limit);
}
else{
    $stmt->bind_param("sss",$request->id,$request->gamer_tag, $request->limit);
}

edited Nov 12, 2019 at 7:51

answered Nov 6, 2019 at 7:28

Amado

3832 silver badges16 bronze badges

8 Comments

Biggum Over a year ago

How affect parameter binding when you have 3 params and binding 4 params?

Biggum Over a year ago

I can not edit because is a very little issue: In else there is "ssss" and should be "sss" to fit with the number of params

Amado Over a year ago

check answer again

Biggum Over a year ago

I understand what you want to do. At first sight "else $MyGameName = ?;" will raise an error, but I'm ont sure. I cannot check if it works, but being "prepared statements", what do you expect passing "?" to statement? an always true for that field?.

Biggum Over a year ago

You have just returned to your intial point. With this code you need again an IF-ELSE to use three or four bindind paramenters like you did in your first attempt. "sss" if for when you have three "?" and "ssss" is for when you have four "?" It's like a mapping. The number of "s" must match with the number of "?". Initial $MyWhere has two "?", if(game_name !="") Will become into three "?". All this goes into prepare statement, which has another "?". So you will need to bind three or four "?"

|

Collectives™ on Stack Overflow

MYSQL - Don't add to query if string is empty

2 Answers 2

Comments

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related