DATE_SUB问题

Question

my code:

$song = mysql_query("SELECT COUNT(*) FROM `".$this->prefix."playlist`  
          WHERE `time` > DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 20 MICROSECOND) 
          AND `long` < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 20 MICROSECOND)");
if(mysql_result($song, 0, 0) == 0)
{
  return "כלום";
} else {
  $query = mysql_query("SELECT * FROM `".$this->prefix."playlist`  
          WHERE `time` > DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 20 MICROSECOND) 
          AND `long` < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 20 MICROSECOND)");
  $song = mysql_fetch_assoc($query);

  return $song["song"];
}

It should return what song is playing now.

myDB:

1   till the worlds ends    2011-06-04 11:20:10 2011-06-04 11:21:10 zRafael

the time :

11:20

it returns:"כלום" Instead the of song name

this my table:

CREATE TABLE IF NOT EXISTS `playlist` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `song` varchar(150) NOT NULL,
  `time` varchar(150) NOT NULL,
  `long` varchar(150) NOT NULL,
  `by` varchar(150) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;

--
-- Dumping data for table `playlist`
--

INSERT INTO `playlist` (`id`, `song`, `time`, `long`, `by`) VALUES
(1, 'till the worlds ends', '2011-06-04 11:40:10', '10', 'zRafael');

Answer 1

I would re-write this to avoid the 2nd query, this should work

Updated again - your date fields are VARCHAR, not DATETIME, so my previous query would not have worked.

$rs = mysql_query("SELECT song FROM `".$this->prefix."playlist`  
   WHERE CAST(`time` AS DATETIME) >= NOW() 
   AND CAST(`long` AS DATETIME) < NOW()");
if (mysql_num_rows($rs) == 0)
  return "כלום";
else
  return mysql_result($rs, 0);

In reality you should fix your table definition.

ALTER TABLE `playlist` 
  MODIFY COLUMN `time` DATETIME NOT NULL
  , MODIFY COLUMN `long` DATETIME NOT NULL;

Then you will not need those casts and your database lookups will be faster.