php mysql query from a loop - determine which table rows are Not in the loop

Question

probably a confusing title - I did find one result in the S.O. archives that I thought would do the trick but still not getting it.

I'm using a function to display all files (to me as an admin) in a directory on the server (this works fine) I also have filenames stored in a database table...

what I want to do is; while im generating the server file list, also query the table for a matching filename - I'm trying to identify orphans on the server that are not in my db table.

It's seems like I've done this before or it's "easy" task with a query but I'm drawing a total blank and hitting a wall. (if there's not a result for a row, your basic query just looks for the next match and I want to flag the non-matches).

the one solution I found in the S.O. archives is using "NOT IN" the query something like:

$files_on_server = "file1,file3,file4,file7";
// Your SQL WHERE clause...
...WHERE `filename` NOT IN ($files_in_server)

but that's not doing it for me. thanks in advance for any suggestions.

From what you have said NOT IN is probably easiest approach. What is not working? — ficuscr
– ficuscr, Commented Jan 11, 2013 at 20:13
Just a follow up - I got the query to work after all however it is the reverse result of what I wanted - NOT IN shows me titles in the DB that are not in my server file-list that I create. I was looking for the opposite... something like (psuedo) "look for file1 in the DB table... no match but flag that there was no match for "file1" in the db table... look for file2... and so on — user371051
– user371051, Commented Jan 11, 2013 at 21:58
resolved - I just had to move the echo result outside of the query loop } echo $filename there, and it displayed a blank result also reset $filename == "" and continued back through the outer-loop of my server-filenames. — user371051
– user371051, Commented Jan 11, 2013 at 22:25

full.stack.ex · Accepted Answer · 2013-01-11 20:13:53Z

2

Looks like it's all about punctuation. How about single-quoting the values?

$files_on_server = "'file1','file3','file4','file7'";

But it's usually more preferable to use prepared statements - google for mysqli and PDO.

answered Jan 11, 2013 at 20:13

full.stack.ex

1,7472 gold badges11 silver badges15 bronze badges

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

3 Comments

user371051 Over a year ago

it appears that NOT IN is the approach then, so I'll keep at it - I'm not getting any result from my query I'm using mysql_fetch_array(): so I must have a typo or problem that I'm missing...

full.stack.ex Over a year ago

Like some other answers suggest, it may be $files_on_server vs. $files_in_server. Anyway, quotes are needed for 'File Names Like This.txt'

user371051 Over a year ago

I will mark this as answered for confirming NOT IN and trouble-shoot my code further. thanks!

ROY Finley · Accepted Answer · 2013-01-11 20:29:52Z

1

I think this $files_on_server should match this: $files_in_server

 $files_on_server = "'file1','file3','file4','file7'";
// Your SQL WHERE clause...
...WHERE `filename` NOT IN ($files_on_server)

From reading your question. If you are collecting the file names, you could use a recursive function to put them in an array. then you could implode the array in the NOT IN statement

NOT IN('" . implode("' , '", $files_on_server) . "')

edited Jan 11, 2013 at 20:29

answered Jan 11, 2013 at 20:13

ROY Finley

1,4161 gold badge9 silver badges18 bronze badges

2 Comments

ROY Finley Over a year ago

edited the code to incorperate @full.stack.ex answer also. His is correct.

user371051 Over a year ago

ok I Missed your comment earlier before i checked that off. I'll try that, thanks!

Scutterman · Accepted Answer · 2013-01-11 20:14:51Z

0

I think your problem is with this line:

$files_on_server = "file1,file3,file4,file7";

It should be (notice individual quotes):

$files_on_server = "'file1','file3','file4','file7'";

But also consider why there are orphaned files. If it is because files got deleted via FTP, consider writing a php interface to delete them instead. When there's large numbers of files to keep track of, the current solution may start slowing down.

answered Jan 11, 2013 at 20:14

Scutterman

4274 silver badges14 bronze badges

2 Comments

ficuscr Over a year ago

I just gotta throw this one out there... php.net/manual/en/book.fam.php :) perpetual beta?

Scutterman Over a year ago

That looks interesting. If I ever have need for this myself I'll be sure to remember it

diggersworld · Accepted Answer · 2013-01-11 20:19:20Z

0

Your string:

$files_on_server = "file1,file3,file4,file7";

Make it into this:

$files_on_server = "'file1','file3','file4','file7'";

SELECT *
FROM files
WHERE filename NOT IN ($files_on_server);

edited Jan 11, 2013 at 20:19

answered Jan 11, 2013 at 20:14

diggersworld

13.1k25 gold badges87 silver badges120 bronze badges

Collectives™ on Stack Overflow

php mysql query from a loop - determine which table rows are Not in the loop

4 Answers 4

3 Comments

2 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

2 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related