mysqli select/search names that have and don't have sapces

Question

I am wanting to echo rows where the column 'tipster' is equal to the form input option the user chooses.

I have tried replace (tipster ' '. '') LIKE '$tipster' and it did not return any results... Some fields have spaces in the name whilst some don't - e.g. 'John Jones Smith', 'John Jones' and 'JohnJones'.... I need to search the column that matches the option chosen in the html form (check the rows with and without spaces)

If I Just do a single word search (e.g. Example)...the result comes back fine as its LIKE query. I would like to comment that the GET function works but is passing the input as example+one in the url....not sure if the + is effecting the mysqli result potentially?

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>";

}
?>

How about: `WHERE REPLACE(tipster, ' ', '') LIKE CONCAT('%', REPLACE($tipster, ' ', ''), '%')` ? — GMB, Mar 17 '19 at 20:00
@GMB - clicked Example One from drop down then got this - _You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'One, ' ', ''),_ — George Richardson, Mar 17 '19 at 20:08
Sorry: `WHERE REPLACE(tipster, ' ', '') LIKE CONCAT('%', REPLACE('$tipster', ' ', ''), '%')`. NB: you should consider using parameterized query to protect your code against SQL injections... — GMB, Mar 17 '19 at 20:11

score 1 · Accepted Answer · answered Mar 17 '19 at 20:15

How about:

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

NB: anyone on SO will strongly suggest to use prepared statements and parameterized queries, to protect your code from SQL injection and make your queries more readable and maintainable. Such typo is far much easier to detect when using parameterized queries.

mysqli select/search names that have and don't have sapces

1 Answers1