CODEWITHSUNDEEP
phpmysqlphpmyadmin
sans0909
sans0909

Reputation: 435

How can I specify the range of values for the user to enter from in phpmyadmin?

My question is I have a database named Autodb, this has a table named Bluetooth in phpmyadmin in which I have to create a column named LAP. In this column user should enter any value between 0X9E8B00-0X9E8B3F, is there a way to do it in phpmyadmin and is there a query in SQL to set the range of values to be entered from?

Upvotes: 0

Views: 2756

Answers (3)

Isaac Bennetch
Isaac Bennetch

Reputation: 12432

phpMyAdmin is a tool for administrators to manage a MySQL database, not an end-user front end, so this is a bit beyond the scope of what phpMyAdmin can handle.

Basically you have three choices when it comes to verifying data on insert:

Application level

In your front end, which appears to be PHP based on the tag you've used, check the value before passing it to MySQL at all. You should be able to do standard hex comparisons on the values and print an error message if it's outside the bounds you set.

MySQL trigger

MySQL allows you to set up a trigger which can check the data before the INSERT occurs. A trigger is a command or test that runs before (or after) an event such as INSERT, UPDATE, or DELETE — you'd probably want the same trigger action to occur after an INSERT or DELETE. You can return an error message to the client if the value is outside your bounds. MySQL should be able to compare hex values.

MySQL stored procedure

Instead of calling directly the INSERT command, you could call a stored procedure. This has the advantage of being written once for all the INSERTs you'd perform regardless of what front end you use; they'd all use the stored procedure. If you change the bounds, you'll do so in the stored procedure instead of the application code, which is good if you have multiple deployments or applications accessing the data. Again, MySQL should be able to handle the hex values.

Which one you select depends on your deployment and experience level with each, but in all these cases you're able to achieve the checks you're trying for.

Upvotes: 0

Tomasz Ferfecki
Tomasz Ferfecki

Reputation: 1263

If you want only insert data between range:

INSERT INTO Bluetooth (ID, BLAHBLAH, LAP)
SELECT (1, 'BlahBlah', 0x9e8f21) WHERE 
IF (0x9e8f21 >= 0x9e8b00 and 0x9e8f21 <= 0x9e8b3f, 1, 0) = 1

Upvotes: 1

Mohammed Che
Mohammed Che

Reputation: 11

Im not 100% sure what you're asking but this might help:

If you want to set each value the user can enter you can use an ENUM data type.

Have a look at ENUM here

Here is an example of how to use it.

CREATE TABLE Bluetooth
(
  ID SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  BlahBlah VARCHAR(50) NOT NULL,
  LAP ENUM('0X9E8B00', '0X9E8B3F', '0X9E8B3F') NOT NULL
)

If the range is between 2 numbers you can use a constraint and CHECK

ALTER TABLE myTableName
ADD CONSTRAINT myTableName_myColumnName_valZeroToOneHundred
CHECK (myColumnName BETWEEN 0 AND 100)

ALTER TABLE Table
ADD CONSTRAINT CK_Table_Column_Range CHECK (
    Column >= 0 AND Column <= 100 --Inclusive
)

Check this question out.

Upvotes: 1

Related Questions