检查数据库是否有项目，如果没有插入

时间：2022-05-09 03:19:55

I have an form, where is field called team.

我有一个表单,其中的字段称为团队。

<input type="text" name="team" id="team" />

I would like to inser data from that field IF that same team isn't in database yet.

如果相同的团队还没有在数据库中,我想从该字段输入数据。

Basically if user writes 'Chelsea' and that is already in database table then nothing basically happends but if it's not there yet, then it's inserted in to database table tt_clubs.

基本上如果用户写“切尔西”并且已经在数据库表中,那么基本上什么也没发生,但如果它还没有,那么它就被插入到数据库表tt_clubs中。

Can I check that if it's already there somehow? I'm rookie with SQL still :/

如果它已经存在,我可以检查一下吗?我仍然是SQL的菜鸟:/

EDIT also it shouldn't matter if users writes 'chelsea' or 'Chelsea' or 'chElsea'.. all those should be same.

如果用户写“chelsea”或“Chelsea”或“chElsea”,编辑也无所谓。所有这些应该是相同的。

EDIT table structure is just, 'id' <- automatic and 'Team name'

编辑表结构只是'id'< - 自动和'团队名称'

3 个解决方案

#1

2  

The easy way

I believe that INSERT IGNORE would solve this problem:

我相信INSERT IGNORE会解决这个问题:

INSERT IGNORE INTO tt_clubs (team) VALUES ('Chelsea');

If the name already exists in the table then the insert will simply be ignored.

如果表中已存在该名称,则只会忽略该插入。

Also if you have not already done so set the team field to be a unique key:

此外,如果您还没有这样做,请将团队字段设置为唯一键:

ALTER TABLE tt_clubs ADD UNIQUE(team);

Another way

Attempt to select the value first:

尝试先选择值:

SELECT id
FROM tt_clubs
WHERE team LIKE 'Chelsea'

Then in PHP you can check how many rows have been returned. If there is one then don't run the insert statement otherwise insert the team name.

然后在PHP中,您可以检查已返回的行数。如果有,则不要运行insert语句,否则插入团队名称。

#2

1  

You can use solution like this:

你可以使用这样的解决方案:

  1. Use ci collation for you table. CI means case-insensitive

    2. 为你的表使用ci collat​​ion。 CI表示不区分大小写

  2. Add Unique KEY on this field which is store CHELSEA.

    3. 在此字段上添加唯一KEY,即存储CHELSEA。

  3. Use Insert IGNORE INTO teams(field1) VALUES('Chelsea')

    4. 使用Insert IGNORE INTO团队(field1)VALUES('Chelsea')

Another way:

  1. Select row from db by this field - select id from teams where field1 = 'Chelsea'.

    2. 通过此字段从db中选择行 - 从field1 ='Chelsea'的团队中选择id。

  2. If row empty Insert a new row.

    3. 如果行为空插入一个新行。

#3

0  

It totally depends on the DBMS. Oracle, doesn't have the INSERT IGNORE clause. I'd do a MERGE:

这完全取决于DBMS。 Oracle,没有INSERT IGNORE子句。我做了一个合并:

MERGE INTO my_table
USING ( SELECT 1 from dual )
ON UPPER( team_name ) = UPPER( user_input )
WHEN NOT MATCHED THEN INSERT
VALUES ( user_input )

#1

2  

The easy way

I believe that INSERT IGNORE would solve this problem:

我相信INSERT IGNORE会解决这个问题:

INSERT IGNORE INTO tt_clubs (team) VALUES ('Chelsea');

If the name already exists in the table then the insert will simply be ignored.

如果表中已存在该名称,则只会忽略该插入。

Also if you have not already done so set the team field to be a unique key:

此外,如果您还没有这样做,请将团队字段设置为唯一键:

ALTER TABLE tt_clubs ADD UNIQUE(team);

Another way

Attempt to select the value first:

尝试先选择值:

SELECT id
FROM tt_clubs
WHERE team LIKE 'Chelsea'

Then in PHP you can check how many rows have been returned. If there is one then don't run the insert statement otherwise insert the team name.

然后在PHP中,您可以检查已返回的行数。如果有,则不要运行insert语句,否则插入团队名称。

#2

1  

You can use solution like this:

你可以使用这样的解决方案:

  1. Use ci collation for you table. CI means case-insensitive

    2. 为你的表使用ci collat​​ion。 CI表示不区分大小写

  2. Add Unique KEY on this field which is store CHELSEA.

    3. 在此字段上添加唯一KEY,即存储CHELSEA。

  3. Use Insert IGNORE INTO teams(field1) VALUES('Chelsea')

    4. 使用Insert IGNORE INTO团队(field1)VALUES('Chelsea')

Another way:

  1. Select row from db by this field - select id from teams where field1 = 'Chelsea'.

    2. 通过此字段从db中选择行 - 从field1 ='Chelsea'的团队中选择id。

  2. If row empty Insert a new row.

    3. 如果行为空插入一个新行。

#3

0  

It totally depends on the DBMS. Oracle, doesn't have the INSERT IGNORE clause. I'd do a MERGE:

这完全取决于DBMS。 Oracle,没有INSERT IGNORE子句。我做了一个合并:

MERGE INTO my_table
USING ( SELECT 1 from dual )
ON UPPER( team_name ) = UPPER( user_input )
WHEN NOT MATCHED THEN INSERT
VALUES ( user_input )
标签：

相关文章