Combine mysql queries into one-CodePudding

I'm making a filter for the shop and need to combine several query into one. My problem is that I can't use UNION because I don't know the exact number of filter queries, so I use foreach. I have a sample code below.

$searchArray = array("%XS%", "%S%");

foreach ($searchArray as $value) {
    $load_sql = $db->prepare("SELECT * FROM `lm_items` WHERE LOWER(`size`) LIKE LOWER(?)");
    $load_sql->bind_param('s', $value);
}

$load_sql->execute();
$load_result = $load_sql->get_result();

mysql image

CodePudding user response：

Based on comments if you do insist on using Json This query will search for all items with "S" size


select JSON_EXTRACT(size, '$') from lm_items where JSON_CONTAINS(size, '"S"', '$')

You may concat all filter variables in one query

// filter parameters
$searchArray = array("%XS%", "%S%");
// init query, 
$query = "SELECT * FROM `lm_items` WHERE TRUE ";

foreach ($searchArray as $value) {
    // add a new condition to query for each filter parameter
    $query .= " AND JSON_CONTAINS(size, '"?"', '$') "
}
$load_sql = $db->prepare($query);
// bind params
$load_sql->execute($searchArray);

$load_result = $load_sql->get_result();

CodePudding user response：

I think you not need UNION here. You should to run query like

SELECT * FROM table WHERE field_value IN ('val1', 'val2');

below is PHP implementation:

<?php   
$searchArray = ["XS", "S"]; // array values
// Place holders string '?,?' string built according values count
$place_holders = implode(",", array_fill(0, count($searchArray), "?")); 

//types string 'ss'
$sql_types  = str_repeat("s", count($searchArray)); 
$query      = "SELECT * FROM `lm_items` WHERE `size` IN (" . $place_holders . ")";

$stmt = $db->prepare($query); // prepare SQL statement
$stmt->bind_param($sql_types, ...$searchArray); // bind array at once
$stmt->execute();

$result = $stmt->get_result();
$load_result = $result->fetch_all();

print_r($load_result);

PHP mysql sandbox