PHP / SQL if变量包含的字符串与表列中的x字符相同

Question

I have a variable which contains multiple dates as string. It's a range of dates. When I send this into the database table, I want to check if the column contains one of these dates. This is hard to do because a variable is a string of multiple dates. Every date excists out of 10 characters. So I send a variable as string into a database which contains multiple or a single date and I want to check if there already is one of those dates which excist out of 10 characters, so if 10 characters without a space between are the same.

Example of a column: https://i.stack.imgur.com/iUiIK.png

    $query_overeenkomst = "SELECT * from $stad WHERE beschikbare_data LIKE '%$date%'" or die(mysql_error());
    $result_overeenkomst = mysqli_query($conn, $query_overeenkomst);
    $rows_overeenkomst = mysqli_num_rows($result_overeenkomst);
    if($rows_overeenkomst>=1){
    echo "There is a match!";
    }
    else
    { 
    echo "No date the same.";
    }

If I use this code, the column has to contain the whole string of $data (which contains all the dates), but I want to check if there is only one or more dates that match.

The data variable:

$data .= $date->format("Y-m-d") . PHP_EOL;

Returns for example:

2018-12-19 2018-12-20 2018-12-21 2018-12-22 2018-12-23 2018-12-24 2018-12-25 

Answer 1

You can do something like this:

$dates = str_replace(PHP_EOL, "','", trim($data));
$query_overeenkomst = "SELECT * from $stad WHERE beschikbare_data IN ('$dates')";

The query variable will have the following content:

SELECT * from WHERE beschikbare_data IN ('2018-12-19','2018-12-20','2018-12-21','2018-12-22','2018-12-23','2018-12-24','2018-12-25')

Which will select all rows with the listed dates.

EDIT: After fuguring out that you store your in the database the list of dates in one row the answer will be different.

First, you should know that it's a terrible database structure design, you should read about many-to-many relationship.

In any case, this is how you can solve this without changing any structure:

$dates = explode(PHP_EOL, trim($data));
$where = "";

foreach ($dates as $key => $date) {
    $where .= ($key > 0) ? " OR " : "";
    $where .= "beschikbare_data LIKE ('%$date%')";
}


$query_overeenkomst = "SELECT * from $stad WHERE $where";

This will result this SQL query:

SELECT * from table WHERE beschikbare_data LIKE ('%2018-12-19%') OR beschikbare_data LIKE ('%2018-12-20%') OR beschikbare_data LIKE ('%2018-12-21%') OR beschikbare_data LIKE ('%2018-12-22%') OR beschikbare_data LIKE ('%2018-12-23%') OR beschikbare_data LIKE ('%2018-12-24%') OR beschikbare_data LIKE ('%2018-12-25%')

Notice that this is an extremly slow query, and in order to avoid this you must change your data structure design.