SELECT multiple values from one record

Question

I need to show data from database on my webpage by selecting checkboxes. The data are driving license code assigned to users in database, so if users checks one checkbox I want to display every person that have selected driving license but others too (if there are others). Data in the database are written like this (for example):

A1,A,B1,BE,C

HTML FORM:

<form action="filter-results.php" method="post">
    <div class="form-check-inline">   
    <label><input class="form-check-input" type="checkbox" name="vodic[]"  value="A1">A1</label>
</div>
<div class="form-check-inline">   
    <label><input class="form-check-input" type="checkbox" name="vodic[]"  value="A2">A2</label>
</div>
<div class="form-check-inline">   
    <label><input class="form-check-input" type="checkbox" name="vodic[]"  value="A">A</label>
</div>
<div class="form-check-inline">   
    <label><input class="form-check-input" type="checkbox" name="vodic[]"  value="B1">B1</label>
</div>
<div class="form-check-inline">   
    <label><input class="form-check-input" type="checkbox" name="vodic[]"  value="B">B</label>
</div>
<div class="form-check-inline">   
    <label><input class="form-check-input" type="checkbox" name="vodic[]" value="BE">BE</label>
</div>
<div class="form-check-inline">   
    <label><input class="form-check-input" type="checkbox" name="vodic[]" value="C1">C1</label>
</div>
<div class="form-check-inline">   
    <label><input class="form-check-input" type="checkbox" name="vodic[]" value="C1E">C1E</label>
</div>
<div class="form-check-inline">   
    <label><input class="form-check-input" type="checkbox" name="vodic[]" value="C">C</label>
</div>
<div class="form-check-inline">   
    <label><input class="form-check-input" type="checkbox" name="vodic[]" value="CE">CE</label>
</div>
<div class="form-check-inline">   
    <label><input class="form-check-input" type="checkbox" name="vodic[]" value="D1">D1</label>
</div>
<div class="form-check-inline">   
    <label><input class="form-check-input" type="checkbox" name="vodic[]" value="D1E">D1E</label>
</div>
<div class="form-check-inline">   
    <label><input class="form-check-input" type="checkbox" name="vodic[]" value="D">D</label>
</div>
<div class="form-check-inline">   
    <label><input class="form-check-input" type="checkbox" name="vodic[]" value="DE">DE</label>
</div>
<div class="form-check-inline">   
    <label><input class="form-check-input" type="checkbox" name="vodic[]" value="T">T</label>
</div>

PHP:

if(empty($_POST['license'])){
        $license = "";
    } else {
        $license = array();
        foreach ($_POST['license'] as $value){
            $license[] = $value;
        }
    }

PHP MYSQL QUERY:

query=mysqli_query($link,"SELECT * FROM users WHERE vodicaky RLIKE '$license[]');

Answer 1

PHP does not expand arrays when you put them in strings. i.e. $list = "$values[]" doesn't work.

Next, putting a PHP variable directly into your query is extremely bad practice, & horribly insecure giving that this is user data, as mentioned in a comment above.

To solve your problem, you'll need to perform multiple LIKE checks. You shouldn't need regex here, so it's not RLIKE.

We'll assume your submission looks like this:

$license = [
    'A1',
    'A',
    'B1',
];

And this is the query we're trying to build:

SELECT * FROM users 
WHERE vodicaky LIKE '%A1%' 
OR vodicaky LIKE '%A%'
OR vodicaky LIKE '%B1%'

Which will actually have issues because LIKE %A% will match A1 as well. This query would fetch more than you need & you could filter it down in php. Or you could go to the regex route & you'd need something like:

SELECT * FROM users
WHERE vodicaky REGEXP '(^|,)A1(,|$)'
OR vodicaky REGEXP '(^|,)A(,|$)'
OR vodicaky REGEXP '(^|,)B1(,|$)'

REGEXP is a synonym for RLIKE, I think. I've always used REGEXP. I don't think it matters which you use.

(^|,)A1(,|$) says:

• The start of the string ^ OR a comma ,
• Followed by A1
• Followed by a comma , OR the end of the string $

Regexr is a fabulous place to play with regex. rexegg is a great resource for properly learning regex (reading required).

NOW, back to doing your query without security vulnerabilities. I use PDO which allows :named_paramaters for binding & haven't used mysqli in quite some time, and I believe mysqli only allows ? binding. I'm not testing this code. But the actual query string you need to build is:

SELECT * FROM users
WHERE vodicaky REGEXP ?
OR vodicaky REGEXP ?
OR vodicaky REGEXP ?

Since you're binding, I don't think the ?s need to be surrounded in quotes

Then you have to bind it. We're looking at mysqli prepared statements:

$licenses = //from POST
$mysqli = new mysqli(...);
$binds = [];
$bindTypes = '';
$query = [];
foreach ($licenses as $lic){
    $query[] = 'vodicaky LIKE ?'
    $binds[] = "(^|,){$lic}(,|$)";
    $bindTypes .= 's';
}

$qsImplode = implode("\nOR ", $query);
$qs = 
<<<SQL
SELECT * FROM USERS
WHERE {$qsImplode}
SQL;

// echo $qs; exit; // for debugging purposes. 

$statement = $mysqli->prepare($qs);
//here's where I'm less confident since I use PDO, not mysqli
$statement->bind_param($bindTypes, ...$binds);

//then execute your query & get your results.

So, I think that basically does it. Hope this all helps. I think you should be able to get it from here, even if my code has a couple errors.