Question 297660 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql query based on empty and filled values

Two queries;

SELECT * 
FROM `products` 
WHERE product_type = "" AND product_description LIKE "%a%";

and

SELECT * 
FROM `products` 
WHERE product_type = 10 AND product_description LIKE "%a%";

The product_type value is full and works when it is correct (2md query).
But I also want it to work when the value is empty.

I also want a query to work like this, because there are a lot of columns;

SELECT * 
FROM `products` 
WHERE product_type = "" AND product_description LIKE "%a%" AND product_price = ""

Can you help me, please?

Tags:

2

Answers


  1. 0

    May be you get empty output because is not an empty string but null?
    If it can be null check for white space.

    Try

    SELECT * FROM `products` WHERE (product_type is null OR TRIM(product_type) = '') AND product_description LIKE "%a%";
    Login or Signup to reply.
  2. 0

    This is just builds up the WHERE clause dynamically, using the approach demonstrated in the example I suggested in the comments:

    <?php

$host = 'localhost';
$db   = '';
$user = '';
$pass = '';
$port = '3306';
$charset = 'utf8mb4';

$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_OBJ,
    PDO::ATTR_EMULATE_PREPARES   => false,
];
$dsn = "mysql:host=$host;dbname=$db;charset=$charset;port=$port";
$pdo = new PDO($dsn, $user, $pass, $options);

// arrays to store the criteria
$conditions = [];
$parameters = [];

// conditional statements
if (!empty($_GET['product_type'])) {
    $conditions[] = 'product_type = ?';
    $parameters[] = $_GET['product_type'];
}

if (!empty($_GET['product_description'])) {
    $conditions[] = 'product_description LIKE ?';
    $parameters[] = '%' . $_GET['product_description'] . '%';
}

// the main query
$sql = 'SELECT * FROM products';

// if there are conditions, generate the WHERE clause
if ($conditions) {
    $sql .= ' WHERE '. implode(' AND ', $conditions);
}

// the usual prepare/execute/fetch routine
$stmt = $pdo->prepare($sql);
$stmt->execute($parameters);

var_dump($stmt->fetchAll());
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search