如何避免使用php在SQLite表中添加冗余数据? [英] How to avoid adding redundant data in SQLite table using php?

问题描述

我正在编写如下所示的 php 代码，其中列出了 $src_dir 中存在的所有 mp4 文件.

I am working on a php code as shown below which lists all the mp4 files present in a $src_dir.

$src_dir = ('\\\ABCD-ST-001\Audio_Test\podcast\incoming_folder'); 

$mp4_files = preg_grep('~\.(mp4)$~', scandir($src_dir));

print_r(array_values($mp4_files));  // LineA

这是从Line#A获得的O/P:

Array ( [0] => 36031P.mp4 [1] => hello.mp4 )

现在，我在我的 php 代码中使用了以下脚本，以便在 Podcast_Export 表中插入这些 mp4 文件.

Now, I have used the following script in my php code in order to insert those mp4 files in Podcast_Export table.

    foreach ($mp4_files as $value) {
        $db->exec(SELECT COUNT(*) FROM Podcast_Export WHERE House# = '".$value."' AND status = 'GO');
        $db->exec("INSERT INTO Podcast_Export ('House_number', 'Status') VALUES ('".$value."', 'Go')");    // Line B
    }

上述脚本在Podcast_Export表中添加以下数据:

The above script add the following data inside Podcast_Export table:

36031.mp4 Go
hello.mp4 Go

问题陈述:

我现在面临的问题是当我刷新页面时，再次运行 LineB 处的脚本，并在 Podcast_Export 中再次添加 $src_dir 中的 mp4 文件代码>表如下图:

The issue which I am facing right now is when I refresh the page, the script at LineB is run again and mp4 files present in a $src_dir is added again inside Podcast_Export table as shown below:

36031.mp4 Go
hello.mp4 Go
36031.mp4 Go
hello.mp4 Go

它应该以一种方式工作，一旦新文件出现在 $src_dir 中，那么它应该添加到 Podcast_Export 表中.让我们假设新文件是 hxz210.mp4 那么Podcast_Export 表中的内容应该是:

It should work in a way that once new file comes up inside $src_dir then it should add inside Podcast_Export table. Let us suppose the new file is hxz210.mp4 then the content inside Podcast_Export table should be:

36031.mp4  Go
hello.mp4  Go
hxz210.mp4 Go

推荐答案

您的选择没问题，但您需要将结果存储到一个变量中，例如:

Your selection is fine, but you need to store result into an variable like:

$count = $db->querySingle("SELECT COUNT(*) as count FROM Podcast_Export WHERE House_number = '".$value."'"); 
if($count <= 0){ 
    $db->exec("INSERT INTO Podcast_Export (House_number,Status) 
               VALUES ('".$value."', 'Go')");
}

使用 querySingle 获取 SQLite 中的行数.

Using querySingle to get the no of rows in SQLite.

请注意，我在此处将 House# 更改为 House_number 并删除了 AND status = 'GO' 子句，因为它们都具有GO 状态.

note that, i have chanaged the House# to House_number here and removed AND status = 'GO' clause as all of them having GO status.

这篇关于如何避免使用php在SQLite表中添加冗余数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！