How to avoid adding redundant data in SQLite table using php?

Question

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

Here is the O/P obtained from Line#A:

Array ( [0] => 36031P.mp4 [1] => hello.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
    }

The above script add the following data inside Podcast_Export table:

36031.mp4 Go
hello.mp4 Go

Problem Statement:

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

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

Modify House# so it is a unique value... or change your script so on a refresh it doesn't run again. — user3783243
– user3783243, Commented Jun 19, 2019 at 17:02
Or check the database forst to see if the House_Number already exist — RiggsFolly
– RiggsFolly, Commented Jun 19, 2019 at 17:06
@user3783243 House# is not a proper column name so I have modified it to 'House_number'. On refresh the script should only run when new mp4 file show up. — flash
– flash, Commented Jun 19, 2019 at 17:18
@flash: again i recommend to add a query which will be used to check either record exist or not link — devpro
– devpro, Commented Jun 19, 2019 at 17:21
@devpro I have added in the question. Let me know if it looks good. — flash
– flash, Commented Jun 19, 2019 at 17:25

devpro · Accepted Answer · 2019-06-20 15:33:57Z

0

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')");
}

Using querySingle to get the no of rows in SQLite.

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

edited Jun 20, 2019 at 15:33

answered Jun 19, 2019 at 17:31

devpro

16.1k3 gold badges29 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

19 Comments

flash Over a year ago

The code is not going after the 1st line. I am wondering what is numRows(); in the code above ?

devpro Over a year ago

@flash using numrows to get no of rows maybe syntax is diff for sqlite isn't it?

flash Over a year ago

As soon, I comment this line the page starts loading but when I uncomment it the page doesn't load.

devpro Over a year ago

@flash u just need to check either query return result or not maybe u need to use diff. Syntax but logic sams

devpro Over a year ago

@flash yes ... ... Right

|

Collectives™ on Stack Overflow

How to avoid adding redundant data in SQLite table using php?

1 Answer 1

19 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

19 Comments

Your Answer

Sign up or log in

Post as a guest