can we use LIKE keyword with Variable and undescore sign?-CodePudding

i have database table with reservoir details. these reservoirs are categorized to 18 types according to their characters. each reservoir has unique code (reservoir code). then i need to find and display these reservoirs based on their type. i use form to select reservoir type and submit to PHP page and display reservoir list ORDER BY reservoir code. I use following code.

<?php
if (isset($_POST['submit1'])) {

$errors = array();

  $discode = $_REQUEST['discode'];
  $restype = $_REQUEST['restype'];

  $resreq= $discode.$restype;

$sql = "SELECT * FROM resourcelist WHERE rescode LIKE '$resreq__' ORDER BY rescode  ";

$result = $con->query($sql);

     ?>
<form action="" method="post" enctype="multipart/form-data" >       
<table >
    <thead>
        <tr>
        <th>ID</th>
        <th>Resource Type</th>
        <th>Reservoir Name</th>
        <th>Reservoir Code</th>
    </tr>
    </thead>
    <tbody> 
        <?php
            if ($result->num_rows > 0) {
                while ($row = $result->fetch_assoc()) {
        ?>
                    <tr>
                    <td><?php echo $row['id']; ?></td>         
                    <td><?php echo $restype; ?></td>
                    <td><?php echo $row['cultsysname']; ?></td>
                    <td><?php echo $row['rescode']; ?></td>
                    
                    </tr>                       

        <?php       }

            }
}
        ?> 
        </tbody>
        </table>
    </form>

` but it give empty results.

i try to use

$sql = "SELECT * FROM resourcelist WHERE rescode LIKE CONCAT($resreq,'_') ORDER BY rescode "; but when use

$sql = "SELECT * FROM resourcelist WHERE rescode LIKE '$resreq%') ORDER BY rescode ";

give result with other categories rescode with same characters with extra letters.

what will be the issue? please help to solve this. Thanks,

CodePudding user response：

You need to delimit the variable so that the underscores will not be treated as part of the variable name, e.g. {$resreq}__.

However, you should not substitute variables directly into SQL queries, as this opens you up to SQL-injection attacks. Use a prepared statement with parameters. Then you can use CONCAT() to concatenate _ or % to the regexp.

$sql = "SELECT * FROM resourcelist WHERE rescode LIKE CONCAT(?, '__') ORDER BY rescode";
$stmt = $con->prepare($sql);
$stmt->bind_param("s", $resreq);
$stmt->execute();
$result = $stmt->get_result();