php - mysqli 选择/搜索有空格和没有空格的名称

Question

我想要回显“tipster”列等于用户选择的表单输入选项的行。

我已尝试replace (tipster ' '. '') LIKE '$tipster'，但它没有返回任何结果...有些字段名称中包含空格，而有些字段则没有 -例如'John Jones Smith'、'John Jones' 和 'JohnJones'...我需要搜索与所选选项匹配的列在 html 表单中(检查带空格和不带空格的行)

如果我只进行单个单词搜索(例如示例)...结果会像其 LIKE 查询一样正常返回。我想评论一下，GET 函数可以工作，但在 url 中将输入作为 example+one 传递......不确定 + 是否可能影响 mysqli 结果？

HTML

 <form id="tipster_search" method="get" action="example.php">               
      <label for="tipster">Select Tipster</label>
      <select class="form-control" name="tipster" id="tipster">
          <option>Example One</option>
          <option>ExampleTwo</option>
          <option>Example Option Three</option>
      </select>
      <br>
      <button class="btn btn-info" name="submit">Search</button>
</form>

PHP

function processForm() {
    $tipster = $_GET['tipster'];
    $url = "example.php?tipster=".$tipster."";
    header("Location: $url");
    exit;
}

$tipster = $_GET['tipster'];
$q = "SELECT * FROM bets WHERE `tipster` LIKE '%$tipster%' ORDER BY betDate DESC LIMIT 25";
$query = mysqli_query($connection,$q);
$x = 1;
echo "<table class='table'><tr>";
echo "<th>ID</th>";
echo "<th>Bet Date</th>";
echo "<th>Tipster</th>";
echo "<th>Sport</th>";
echo "<th>Meeting</th>";
echo "<th>Time</th>";
echo "<th>Stake Name</th>";
echo "<th>Odds</th>";
echo "<th>Stake Type</th>";
echo "<th>Stake Placed</th>";
echo "<th>Result</th>";
echo "<th>Return</th>";
echo "<th>Profit</th>";

if($query === FALSE) { 
    die(mysqli_error($connection)); // better error handling
}

while($res = mysqli_fetch_array($query)){
    $id = $res['id'];
    $betDate = $res['betDate'];
    $tipster = $res['tipster'];
    $sport = $res['sport'];
    $meeting = $res['meeting'];
    $time = $res['time'];
    $stakeName = $res['stakeName'];
    $odds = $res['odds'];
    $stakeType = $res['stakeType'];
    $stakePlaced = $res['stakePlaced'];
    $result = $res['result'];
    $return = $res['return'];
    $profit = $res['profit'];

    echo "<tr><td><p>$id</p></td>"; 
    echo "<td><p>$betDate</p></td>";
    echo "<td><p>$tipster</p></td>"; 
    echo "<td><p>$sport</p></td>"; 
    echo "<td><p>$meeting</p></td>";
    echo "<td><p>$time</p></td>"; 
    echo "<td><p>$stakeName</p></td>"; 
    echo "<td><p>$odds</p></td>";
    echo "<td><p>$stakeType</p></td>";
    echo "<td><p>&pound;$stakePlaced</p></td>"; 
    echo "<td><p>$result</p></td>"; 
    echo "<td><p>&pound;$return</p></td>";
    echo "<td><p>&pound;$profit</p></td></tr>";

}
?>

Answer 1

怎么样:

WHERE REPLACE(tipster, ' ', '') LIKE CONCAT('%', REPLACE('$tipster', ' ', ''), '%')

注意:SO 上的任何人都会强烈建议 use prepared statements and parameterized queries ，保护您的代码免受 SQL 注入(inject)，并使您的查询更具可读性和可维护性。使用参数化查询时，这种拼写错误更容易检测到。