PHP - Query with empty values

Question

I want to create a form like in this post, but I want to make it so that if one of the inputs is empty, then the php will still process the queries. Do I use INNERJOIN or LEFTJOIN?

EDIT: This is the html form from that post:

<form action="results.php" method="GET">
  <input type="text" name="input">
  <input type="text" name="topic">
  <input type="text" name="location">
</form>

And the php code for it:

$db = new mysqli(*your database connection information here*);
$input = $_GET['input']; //this is for the text input - ignore
$topic = $_GET['topic']; // the first select box value which works well
$location = $_GET['location']; //the second select box value which isn't being inserted into the query
$combined = $input . $topic . $location;
$terms = explode(" ", $combined);
$stmt = $db->prepare("SELECT * FROM search WHERE input = ? AND topic = ? AND location = ?");
$stmt->bind_param("sss", $input, $topic, $location);
$stmt->execute();
$stmt->close();

If the "topic" input is empty for example, I want to make it so that the SELECT query would still return a row instead of nothing

PHP processing has nothing to do with a left or inner join. Please add code so we can see what you are actually doing. — user3783243
– user3783243, Commented May 20, 2018 at 15:12
So you want the where to be built dynamically? That's not what joins are for. You should use conditionals in the PHP to build the where clause dynamically. — user3783243
– user3783243, Commented May 20, 2018 at 15:22
Use pdo instead of mysqli. Its placeholder (:placeholder) format is more flexible for this task. — Karlo Kokkak
– Karlo Kokkak, Commented May 21, 2018 at 1:40

Oluwafemi Sule · Accepted Answer · 2018-05-20 16:32:38Z

You want to build your query clause for non-empty request params.

Here is a Where class that abstracts the building of the where clause.

<?php

class Where {
    private $values;
    private $types;

    static $VALUE_TYPES = [
        'string' => 's',
        'integer' => 'i',
        'double' => 'd',
        'blob' => 'b',
    ];

    function __construct()
    {
        $this->values = [];
        $this->types = '';
    }

    function addCondition($column, $operator, $value) 
    {
        if(!empty($value)) {
            $this->values["$column $operator ?"] = $value;
            $this->types .= static::$VALUE_TYPES[gettype($value)];
        }
        return $this;
    }

    function clause() 
    {
       $condition = join(' AND ', array_keys($this->values));
       if ($condition) {
           return "WHERE $condition";
       }
       return "";
    }

    function params()
    {
        return array_merge([$this->types], array_values($this->values));
    }
}

To use this class, you need to initialize a Where then add your conditions.

$where = new Where();
$where->addCondition('input', '=', $input);
$where->addCondition('topic', '=', $topic);
$where->addCondition('location', '=', $location);

Append the clause to the query this way.

echo "SELECT * FROM search {$where->clause()}\n";

Then bind params to the query statement.

call_user_func_array($stmt->bind_param, $where->params());

Karlo Kokkak · Accepted Answer · 2018-05-21 03:09:11Z

Using PDO for better flexibility for this task. The code below also uses if else statements to build the query needed.

Updated Code:

$db = new PDO('mysql:host=localhost;dbname=dbnme', 'root','password');

$input = $_GET['input'];
$topic = $_GET['topic'];
$location = $_GET['location'];

$sql_string = "SELECT * FROM search";
$where_clause = "";

if($input != ""){
    $where_clause .= "input = :input";
}

if($topic != ""){
    if($where_clause !="") $where_clause .= " AND ";
    $where_clause .= "topic = :topic";
}

if($location != ""){
    if($where_clause !="") $where_clause .= " AND ";
    $where_clause .= "location = :location";
}

$sql_string = $sql_string.($where_clause!=""?" WHERE ":"").$where_clause;

$stmt = $db->prepare($sql_string);
$stmt->bindParam(':input', $input);
$stmt->bindParam(':topic', $topic);
$stmt->bindParam(':location', $location);
$stmt->execute();

while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    print_r( $row );
}

Collectives™ on Stack Overflow

PHP - Query with empty values

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related