Tukukkk

I have Java program that is connected to a mysql database. What I want to do is take user input and display a column that matches with the user input. The column contains a list of programming languages. I need help with the sql query needed to do this. For example if the column in the first row contained the languages Javascript, C++, C# and the column in the second row contained Java and if I used the query

"SELECT * from Language WHERE Programming Languages LIKE'%"+input.getText()+"%'")

and the user were to search for Java then the table would display the column that has Java and Javascript because it has the word Java. And if there was a third row and that column had C , and if the user were to search C, then it would display the columns with C++ and C#.

I tried to use this regex instead

`"SELECT * from Language WHERE Programming Language REGEXP'[[<:]]"+input.getText()+"[[:>:]]'")`

which solved the Java and Javascript problem, but searching for C would display the columns with C# and C++. Also searching for C# would display nothing and searching for C++ would throw this error repetition operator operand invalid.
My Table

The table's column data:

Javascript HTML CSS PHP

C# Java

C

C++ C#

I tried to put a space before % in the first query which works with differentiating. My question now is how do i search for multiple words in a column, the above query works for two or three inputs in exact order. Searching javascript and html would show but javascript and css would not show because its not in order

Let me just guess, you store all developers to a table, and one of the columns stores the languages that this developer masters.

When someone input a language, you want to search all the developers who master this language?

If so, I would do this, in your language column, in your language column, add an extra white space to the end (use _ as white space below)

Javascript_HTML_CSS_PHP_

C#_Java_

C++_C#_

C_

Python_Java_LUA_

Then in your Java code, you could use the following code to do this.

SELECT * from Language WHERE Programming_Languages LIKE'%"+input.getText()+"_%'")

And please pay attention that you should never concatenate sql string in your Java code. You can do it in this way:

statment = con.prepare("SELECT * from Language WHERE Programming_Languages LIKE ?")

statment.setString(1, "%" + input.getText() + "_%")

Your issue here is that your database model is not correctly normalized. While possible, it is not recommended to store multiple values in one column of a database. Instead, as a first step, use a seperate database to store the proghramming languages, and use unique indexes to map the values to people:

people



id    | name | other stuff

86511 | Bob  | ...         

86513 | TBA  | ...

.

languages



language | user_id

C#       | 86511

Java     | 86511

C++      | 86513

C#       | 86513

Now you can just search the table LANGUAGES for the corresponding language and map it o the users using a JOIN-clause:

SELECT *

FROM languages

  INNER JOIN people ON languages.user_id = people.id

WHERE languages.language = 'C#';

However, there is still room for optimizations: you now have multiple entries for each programming language in the languages table. This takes longer to search and has a potential to result in inconsistent databases if you accidentally have a typo in only one of the entries for the same language, for example.
To avoid all this, we can introduce a third table that only holds one entry for each langauge, and restructure the second table to be only a mapping:

 people



id    | name | other stuff

86511 | Bob  | ...         

86513 | TBA  | ...

.

lang_mapping



lang_id | user_id

1       | 86511

2       | 86511

3       | 86513

1       | 86513   

.

languages



id | language

1  | C#

2  | Java

3  | C++

You can still query the tables and connect languages to usery by introducing another join to the query:

SELECT *

FROM languages

  INNER JOIN lang_mapping ON lang_mapping.lang_id = language.id

  INNER JOIN people ON lang_mapping.user_id = people.id

WHERE languages.language = 'C#'

NOw you have only one entry per language, you can easily search for each language and get all mapped users, and you can add individual new languages or mappings withgout modifying the users table at all.

This is the normalized form of your data model.

Here is a way to do the query. This is derived from this post.

This requires a temporary table with number of rows populated by numbers up to the maximum number of tokens (language strings separated by space, e.g., the string "Javascript HTML CSS PHP" has 4 tokens).

Create a temp table with numbers:

create temporary table temp_numbers as

    select 1 as num

    union all select 2 as num

    union all select 3 as num

    union all select 4 as num

    union all select 5 as num

;

Table with a column of programming language strings:

create table test_langs (

    col1 varchar(10) NOT NULL,

    col2 varchar(50) NOT NULL

);

Insert some test data:

insert into test_langs values ('ID-1', 'Javascript HTML CSS PHP');

insert into test_langs values ('ID-2', 'C# Java');

insert into test_langs values ('ID-3', 'C');

insert into test_langs values ('ID-4', 'C# C++');

insert into test_langs values ('ID-5', 'Python Java LUA');

The query:

This select gets the exact match to the input string "C";

select col1, token from (

    select col1, substring_index(

        substring_index(col2, ' ', num), 

        ' ', 

        -1

    ) as token

from test_langs

join temp_numbers

    on char_length(col2) - char_length(replace(col2, ' ', '')) >= num - 1

) as individual_progamming_languages

where token='C';

The result:

+------+-------+

| col1 | token |

+------+-------+

| ID-3 | C     |

+------+-------+