File in Database Exists - MySQL + PHP

[jasoninfminor]

I have a mysql database that contains the name of flv files

 

Is there a way of checking if the file actually exists on the server before selecting the row?

 

my code is basically:

 

"SELECT * from video where type='public'"

 

but i want to add a condition that makes it if the flv file in the row is a file on the server

[gluck]

Might your php for the file checking

[jasoninfminor]

the only problem is, that will throw off the row count of the selected rows. i dont want to use php to filter the rows after the sql already selects them.

[fenway]

I think gluck meant check for the file from php before the query.

[jasoninfminor]

so basically i would do 2 queries:

1. one that grabs all the rows, check to see if the file exists that's in that query. then an array of that row to "active"

2. then the second one would compare to see if each row is marked active and grab the row if it is

 

how would i translate those instructions into code? is there a condition in mysql that can do this?

[Hughesy1986]

As long as your saving the file name in the mysql, you could use a simple file check to see if its there, if it is show it otherwise dont.

 

<?php

$query = "SELECT * FROM video WHERE type = 'public'";
$result = mysql_query($query) or die (mysql_error());
$total = @mysql_num_rows($result);

// error check

if ($total == 0)
{
echo "None to show";
}else{
$file = "link to the folder they are in" . $row['file'] . ".fla";
if (file_exists($file))
{
// show the flash file
}
}
?>

[jasoninfminor]

thanks a lot helping. that's definitely how i would do it, however i have page numbers and stuff going off of how big the record set is. so i basically need the filtering to happen in the query...

[fenway]

thanks a lot helping. that's definitely how i would do it, however i have page numbers and stuff going off of how big the record set is. so i basically need the filtering to happen in the query...

Well, MySQL doesn't "know" about the filesystem, so that's not really an option.  The "best" way to do this is to run a CRON job periodically and update the table with "stale/missing" videos, so they're not marked as public.