can mysql query interact with file system?

[omen]

server version: 5.0.67-community

MySQL client version: 4.1.22

 

Table structure:

CREATE TABLE IF NOT EXISTS `jp_adsmanager_ads` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `category` int(10) unsigned default '0',
  `userid` int(10) unsigned default NULL,
  `name` text,
  `ad_zip` text,
  `ad_city` text,
  `ad_phone` text,
  `email` text,
  `ad_headline` text,
  `ad_text` text,
  `date_created` date default NULL,
  `date_recall` date default NULL,
  `recall_mail_sent` tinyint(1) default '0',
  `views` int(10) unsigned default '0',
  `published` tinyint(1) default '1',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1354 ;

 

This table holds data for ads, users have also option to add picture to any listing, however DB does not store this information. All pictures are stored in a directory using ads' ID as a file name (1a.jpg, 1b.jpg 1c.jpg 1a_t.jpg).

 

The following query will display X number of ads within specific directory (cars, bikes, fishing, etc...) regardless if it has a picture or not.

$database->setQuery("SELECT a.id, a.ad_headline, a.category, a.date_created,p.id as parentid,p.name as parent,c.id as catid, c.name as cat ".
  "FROM #__adsmanager_ads as a ".
  "LEFT JOIN #__adsmanager_categories as c ON a.category = c.id ".
  "LEFT JOIN #__adsmanager_categories as p ON c.parent = p.id ".
  "WHERE $cat_query c.published = 1 and a.published = 1 $order_sql LIMIT 0, $nb_ads");

Another function in PHP will process all ads from the query above, if a file with matching ID is found it will be displayed in the ad.

 

For example 3 IDs are returned: 1,7,100 and only ID=100 had picture attached to it (picture name is 100a_t.jpg). First two ads will be displayed as links only, the last one will have picture attached to it

 

PHP code to do it:

 if ($i < $nb_images + 1)
{
   $ext_name = chr(ord('a')+$i-1);
   $pic = $mosConfig_absolute_path."/images/com_adsmanager/ads/".$row->id.$ext_name."_t.jpg";
    if (file_exists( $pic))
   {
       echo "<div align='left'><a href='".$linkTarget."'>
       <img src='".$mosConfig_live_site."/images/com_adsmanager/ads/"
      .$row->id.$ext_name."_t.jpg' alt='".htmlentities(stripslashes($row->ad_headline),ENT_QUOTES)."
       ' border='0' /></a>";
       $ok = 1;
    }
}

 

How do I modify the query to show only listings that have pictures?

 

Thank you

[fenway]

I belive there's a UDF for filesystem stuff... but you should be able to "mark" your records appropriately regardless.