PHP/SQL if variable contains string which is the same as x characters in table column

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

Use an array, not a string. Then each date will be its own data point. You also should parameterize your query. — user3783243, Jun 10 '18 at 14:49
You should note store delimited data in the DB. 1 data point per column. Also, and unrelated currently but `mysql_error` will not help you with `mysqli` and might make your code stop working if your upgrade to PHP 7. https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad — user3783243, Jun 10 '18 at 14:57

HTMHell · Accepted Answer · 2018-06-10T15:21:17.337

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.

edited Jun 10 '18 at 15:21

answered Jun 10 '18 at 14:44

HTMHell

5,761
5
37
79

It looks like the DB is new line separated as well so the `like` will be required (`in` would require an exact match). – user3783243 Jun 10 '18 at 14:56
@user3783243 Thanks, I haven't noticed that, I thought those were different rows. I will update my answer in a bit. – HTMHell Jun 10 '18 at 14:57
Im getting this error: `Column count doesn't match value count at row 1` – beau van der meer Jun 10 '18 at 15:00
@beauvandermeer That doesn't sound like an error you'd get from a `select` query. – user3783243 Jun 10 '18 at 15:08
@HtmHell I don't know why, but it returns this error `Error: INSERT INTO den_haag (`gnummer`, `beschikbare_data`) VALUES ('44', '2018-12-19','2018-12-20','2018-12-21','2018-12-22','2018-12-23') Column count doesn't match value count at row 1` Using `$sql = "INSERT INTO $stad (`gnummer`, `beschikbare_data`) VALUES ('$klantnummer', '$dates')" or die (mysqli_error($conn)); ` – beau van der meer Jun 10 '18 at 15:30
You probably edited your code in the wrong place, we are talking about the `SELECT` query, not the `INSERT` – HTMHell Jun 10 '18 at 15:31
I know, but I changed $data from the insert query to $dates, or should it still be $data? – beau van der meer Jun 10 '18 at 15:35
@beauvandermeer Just copy & paste my code. It should look like this: https://pastebin.com/mgb2XhaC – HTMHell Jun 10 '18 at 16:01
@HtmHell sorry, forgot to say, but eventually it worked perfectely. Thanks. – beau van der meer Jun 16 '18 at 13:57

PHP/SQL if variable contains string which is the same as x characters in table column

1 Answers1