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: https://stackoverflow.com/questions/1736630/sql-constraint-minvalue-maxvalue 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, Dec 21 '22 at 07: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, Dec 21 '22 at 07: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, Dec 21 '22 at 07:18
[I cannot reproduce the problem](https://dbfiddle.uk/GDSVSVnA). 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, Dec 21 '22 at 07: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, Dec 21 '22 at 07:23
@user1915746 PHP (or any server-side technology) will get form elements by `name`, and so can client-side JavaScript. Adding an ID is redundant for client-side and useless for server-side, and only makes everything a little bit messier. — Álvaro González, Dec 21 '22 at 08:15

David F · Answer 1 · 2022-12-21T07:45:55.217

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 '22 at 07:45

answered Dec 21 '22 at 07:45

David F

605
4
10

I get an error during parsing: A new statement was found, but with no separator between it and the previous one. – PC12345 Dec 21 '22 at 07:55
https://i.imgur.com/F5AQKaX.png I am getting the exact resutl. – David F Dec 21 '22 at 08:17
https://i.imgur.com/2NA4NK7.png this is the result – David F Dec 21 '22 at 08:18
https://i.imgur.com/kQbKEpG.png This is getting error when I am going to add 5000 in song_one column – David F Dec 21 '22 at 08:20
https://i.imgur.com/nNgoQVW.png I can add 123 successfully in song_one column – David F Dec 21 '22 at 08:22
@PC12345, my SQL query is a lot different from the previous one. The CONSTRAINT statement is not working in MySQL. Also I added the data type of song_one column. Also he wanted to add contraint to the existing column. That's why I used MODIFY Column. – David F Dec 21 '22 at 08:29
What do you mean with CONSTRAINT not working in MySQL? https://dbfiddle.uk/GDSVSVnA – Álvaro González Dec 21 '22 at 09:03
@ÁlvaroGonzález, Please check this. https://dbfiddle.uk/IZaKLorY – David F Dec 21 '22 at 09:43
That fiddle proves that `MODIFY COLUMN` works, not that `ADD CONSTRAINT` doesn't. – Álvaro González Dec 21 '22 at 09:49
You are right. It seems I had some typo when I run the SQL query. We need the another solution for @PC12345. Thanks for your advice. – David F Dec 21 '22 at 10:01

score 0 · Answer 2 · answered Dec 21 '22 at 09:59

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

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 — PC12345, Dec 22 '22 at 05:47

score -1 · Answer 3 · answered Dec 21 '22 at 07:31

-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 '22 at 07:31

SelVazi

10,028
2
13
29

I get an error during parsing: A new statement was found, but with no separator between it and the previous one. – PC12345 Dec 21 '22 at 07:55
`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. – Álvaro González Dec 21 '22 at 08:17
I agree @ÁlvaroGonzález Its the same. – SelVazi Dec 21 '22 at 08:55

Add specific range of numbers in column / MYSQL

3 Answers3