Add specific range of numbers in column / MYSQL

Question

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

This could help you: stackoverflow.com/questions/1736630/… But probably it would be better to check the value in php (or even in javascript on browser side) before trying to write it to db — user1915746
– user1915746, Commented Dec 21, 2022 at 7:08
You can check it in the PHP code. This is a requirement for your application (most probably a business rule) so do it in your application code. — The Alpha
– The Alpha, Commented Dec 21, 2022 at 7:12
I did it but still doesnt work . if (document.getElementById("slider").value == 15) return. So i have to do this in MySql Column — PC12345
– PC12345, Commented Dec 21, 2022 at 7:18
I cannot reproduce the problem. Can you elaborate on how it isn't working for you and what your exact code is? It's also worth noting that check constraints have been supported for a few years now, but some hosting providers are very reluctant to upgrade MySQL. — Álvaro González
– Álvaro González, Commented Dec 21, 2022 at 7:22
@PC12345 This is javascript code, not PHP and your HTML element has no id. Add id="slider" if you want to get the value by js — user1915746
– user1915746, Commented Dec 21, 2022 at 7:23

Ninja Work · Accepted Answer · 2022-12-21 07:45:55Z

0

This will work for you.


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

edited Dec 21, 2022 at 7:45

answered Dec 21, 2022 at 7:45

Ninja Work

7095 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

PC12345 Over a year ago

I get an error during parsing: A new statement was found, but with no separator between it and the previous one.

Ninja Work Over a year ago

i.imgur.com/F5AQKaX.png I am getting the exact resutl.

Ninja Work Over a year ago

i.imgur.com/2NA4NK7.png this is the result

Ninja Work Over a year ago

i.imgur.com/kQbKEpG.png This is getting error when I am going to add 5000 in song_one column

Ninja Work Over a year ago

i.imgur.com/nNgoQVW.png I can add 123 successfully in song_one column

|

Ninja Work · Accepted Answer · 2022-12-21 09:59:33Z

0

@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())
});

answered Dec 21, 2022 at 9:59

Ninja Work

7095 silver badges11 bronze badges

1 Comment

PC12345 Over a year ago

First of all thanks for your time. I see what you mean but surely somehow it will be done through MySql. Also in the jQuery code you sent me, it is forbidden to type a value between 15 and 300 for example 149 etc because when you write 14(9) it understands that it is smaller than 15

SelVazi · Accepted Answer · 2022-12-21 07:31:52Z

-1

You could do it as follows :

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

answered Dec 21, 2022 at 7:31

SelVazi

16.2k2 gold badges20 silver badges35 bronze badges

3 Comments

PC12345 Over a year ago

I get an error during parsing: A new statement was found, but with no separator between it and the previous one.

Álvaro González Over a year ago

song_one BETWEEN 15 AND 300 is not different from num >= 15 AND num <= 300. You can argue it improves readability, but it's literally equivalent.

SelVazi Over a year ago

I agree @ÁlvaroGonzález Its the same.

Collectives™ on Stack Overflow

Add specific range of numbers in column / MYSQL

3 Answers 3

10 Comments

1 Comment

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

10 Comments

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related