php - How to use 3 conditions in SQL properly which is SUBSTRING, IF ELSE, EXISTS on making a data unique?
Get the solution ↓↓↓So I haven't figured out my problem when it comes to my query, I want to get the first 3 strings of my data and make it unique for example I inputted GTX-2070 if I input another first 3 strings that is GTX-3070 it should not be accepted because the first 3 initial is already used. I tried to do this first in mysQL query.
Here is my code in mySQL
SELECT (CASE WHEN (IF EXISTS(SELECT SUBSTRING(control_no, 1, 3) FROM tools_masterlist BEGIN INSERT IGNORE INTO tools_masterlist (tools_id, tools_name, control_no)
VALUES ('value a', 'value b', 'value c') END) THEN 'Error input' ELSE = 'Okay' END)) FROM tools_masterlist WHERE control_no = 'GTX-2070'
I am still figuring it out because I got errors in mySQL query and can't find the solution. Thank you for the help
Answer
Solution:
One sensisble option here would be to add aprefix
column and make it unique:
ALTER TABLE tools_masterlist ADD prefix VARCHAR(3);
ALTER TABLE tools_masterlist ADD CONSTRAINT cnstr_prefix UNIQUE (prefix);
When you insert, now also specify the prefix. Attempting to insert acontrol_no
whose prefix already exists in the table will fail with an error.
Share solution ↓
Additional Information:
Link To Answer People are also looking for solutions of the problem: xml parsing error: no root element found
Didn't find the answer?
Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.
Similar questions
Find the answer in similar questions on our website.
Write quick answer
Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.