-3

I'm creating an educational platform in which courses (course codes) are created. I want to be able to prevent a new course code from being inserted ignoring the case being used or use of whitespace. e.g if "PHY 101" already exists and one types in "phy101". It should reject the new entry. 

$new_course = $_POST['new_course'];
$sql = mysqli_query($conn, "SELECT * FROM courses WHERE course = '$new_course'") or die(mysqli_error($conn));
        $num_row=mysqli_num_rows($sql);
        if($num_row === 1){
          echo "<script>
          alert('Course code already exists.');
          window.location = 'create_newcourse.php';
          </script>";                                
        }
Godlovesme
  • 21
  • 6
  • Sorry abt that. Kindly answer if you know it. – Godlovesme Jan 21 '20 at 14:17
  • 2
    Does this answer your question? [How do I strip all spaces out of a string in PHP?](https://stackoverflow.com/questions/2109325/how-do-i-strip-all-spaces-out-of-a-string-in-php) – mitkosoft Jan 21 '20 at 14:18
  • Not really @mitkosoft. Some entries in the database already have spacing: such as "PHY 101". I want to be able to prevent something like "phy101" from being inserted. – Godlovesme Jan 21 '20 at 14:26
  • 1
    then you need to apply this logic (lowercase, no whitespaces) both over `course` column and `$_POST['new_course']` – mitkosoft Jan 21 '20 at 14:30
  • 1
    When you insert data doesn't matter if you insert Upper or Lower alwys will reject if exist – Simone Rossaini Jan 21 '20 at 14:34
  • Good comment, only that we can't predict the casing users will use. Can there simply be a code to examing the characters alone and ignore the casing or spaces used? – Godlovesme Jan 21 '20 at 14:34
  • Thanks @SimoneRossaini – Godlovesme Jan 21 '20 at 14:40
  • @Godlovesme Work? – Simone Rossaini Jan 21 '20 at 14:40
  • I'd go a bit further and store a second field in the database (lower, no whitespace) strictly for searching this condition on. Doing all the conversions in the WHERE clause can be painful on large tables of data. – IncredibleHat Jan 21 '20 at 14:43
  • Please do not use `or die(mysqli_error($conn))`. See https://stackoverflow.com/q/15318368/1839439 – Dharman Jan 21 '20 at 15:24

1 Answers1

0

If you already have records on your courses table, then you need to equalize both strings (course column and $_POST['new_course']) upfront:

$new_course = strtolower(str_replace(' ', '',$_POST['new_course']));
$sql = mysqli_query($conn, "SELECT * FROM courses WHERE LOWER(REPLACE(`cource`, ' ', '')) = '$new_course'") or die(mysqli_error($conn));

If you don't need formatted $new_course value further, you can perform both modifications only on SQL level, like:

$sql = mysqli_query($conn, "SELECT * FROM courses WHERE LOWER(REPLACE(`cource`, ' ', '')) = LOWER(REPLACE('$new_course', ' ', ''))") or die(mysqli_error($conn));
mitkosoft
  • 5,262
  • 1
  • 13
  • 31