Add specific range of numbers in column / MYSQL-CodePudding

I have a slider that counts the bpm of a song. The range is from 15 to 300 bpm. It is connected to the MySql Database with php language and when I press an edit button I can change its value. For example from 50 to 55bpm. Table = users | Column = song_one.

<input value="<?php echo $this->data->song_one;?>" name="song_one" type="range" min="15" max="300" value="100" readonly>\

What I want is, if I change the value below 15 and above 300 to not accept it. How can I do this? I heard something about CONSTRAINT..

I tried this but it doesn't work..

ALTER TABLE users
ADD CONSTRAINT song_one CHECK (num >= 15 AND num <= 300);

Any information would be very helpful, thanks

CodePudding user response：

This will work for you.


    ALTER TABLE users
    MODIFY COLUMN song_one INT CHECK (song_one >= 15 AND song_one <= 300);

CodePudding user response：

@PC12345 - I realized you don't want to get error in SQL query. Your SQL query is correct, but it seems you have misunderstanding about the meaning of Constraint. It returns the error when we are gonna add the value of the out of the range. It's not solution that you are gonna solve this issue via SQL. This is jQuery problem. I Added my code. I am not sure this is an exact answer for you. :)

<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.6.1/jquery.min.js"></script>
<input type="range" id="slider" value="51" min="0" max="1000" step="1" />

<br />

<span id="slider_value">Nothing yet.</span>

jQuery code part

$(document).on('input', '#slider', function() {
    if( $(this).val() < 15) {
      $(this).val(15)
    } else if( $(this).val() > 300) {
      $(this).val(300)
    }
   $("#slider_value").text($(this).val())
});

CodePudding user response：

You could do it as follows :

ALTER TABLE users
ADD CONSTRAINT song_one_check CHECK(song_one BETWEEN 15 AND 300);