a sql condition like this is not gonna work in CI

[eluser]TheFuzzy0ne[/eluser]

Code:

CREATE TABLE `test` (
  `ts` int(11) NOT NULL,
  KEY `ts` (`ts`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `test` (`ts`) VALUES
(1236612404),
(1236612504);

SELECT * FROM `test` WHERE ts > '1236612404';

-- returns 1236612504

Works for me, which means that it has to be to do with your data. Please post the contents of your table.

[eluser]runrun[/eluser]
What's the alternative syntax for ' ".time()." ' ?

And Its really matter. First because I tried this:

close_time > 756

I have all the legitimate matched records (as close_time data is always bigger 1kkk, and its compare with integer )

Secondly, I have created a simple table with a query that has that condition, it won't work. I will email CI team to fix this.

[eluser]runrun[/eluser]
Some rows of the table

Code:

INSERT INTO `listing` (`id`, `username`, `category1`, `category2`, `category3`, `region`, `title`, `start_price`, `buynow`, `end`, `hour`, `minute`, `start_time`, `close_time`, `visit`, `detail`, `condition`, `currency`, `shipping`, `shipping_cost`, `shipping_detail`, `cash`, `credit_card`, `bank_tranfer`, `badede`, `activation`, `bidder`, `buyer`, `visibility`) VALUES
(398, 'runrun', 'com', '', '', 'Hồ Chí Minh', 'pitbill dog', 200000, 300000, '10', '5', '6', 1233661715, 1234476360, 0, '5 năm tuổi\r\n\r\nThích uống sửa', 'new', 'VND', 2, '', '', 'Tiền mặt', 'Thẻ tín dụng', 'Chuyển khoản', '', 1, '', '', 1),
(402, 'runrun', 'mob', '', '', 'Hồ Chí Minh', 'ok', 34, 35, '2', '2', '3', 1234345108, 1234465380, 0, 'sa\r\n\r\nas\r\n\r\nas\r\n\r\n', 'new', 'VND', 0, '', '', 'Tiền mặt', 'Thẻ tín dụng', '', '', 1, '', '', 1),
(404, 'no hot', 'toy', 'toy5', '', 'hochiminh', 'ibm laptop', 100000, 20000000, '8', '10', '7', 1234001875, 1234667220, 0, 'core 2 duo 2,0 GHZ\r\n\r\n14 inches screen\r\n\r\n1 Gb of RAM\r\n\r\n2 year warranty left.', '', 'VND', 1, '', '', 'Tiền mặt', '', 'Chuyển khoản', '', 1, '', '', NULL),
(405, 'no hot', 'boo', 'ele2', 'jew2.4', 'hochiminh', 'f', 34, 233, '4', '5', '6', 1234004550, 1234303560, 0, 'f', 'new', 'VND', 1, '', '', 'Tiền mặt', '', '', '', NULL, '', '', NULL),
(406, 'no hot', 'jew', 'jew2', 'jewoth2', 'hochiminh', '', NULL, NULL, '', '', '', 0, 0, 0, '', '', '', NULL, '', '', '', '', '', '', NULL, '', '', NULL),
(407, 'runrun', 'ele', 'ele1', '', 'Hồ Chí Minh', '', NULL, NULL, '', '', '', 0, 0, 0, '', '', '', NULL, '', '', '', '', '', '', NULL, '', '', NULL),
(408, 'runrun', 'mob', 'mob12', 'mob12.4', 'Hồ Chí Minh', 'nokia 666', 3000000, 5000000, '3', '5', '11', 1235900491, 1236118260, 0, 'dien thoai chinh hang\r\n\r\ncon bao hanh 5 nam', 'new', 'USD', 1, '', '', 'Tiền mặt', 'Thẻ tín dụng', 'Chuyển khoản', '', 1, '', '', NULL),
(409, 'runrun', 'mob', 'mob12', 'mob12.9', 'Hồ Chí Minh', 'camera sonycyber shot wws', 1200000, 10000000, '10', '18', '15', 1235900490, 1236770100, 0, 'camera sony moi 100%\r\n\r\nhang nhap tu singapore\r\n\r\nbao hanh 1 thang\r\n\r\ngia goc', '', 'VND', 2, '', '', 'Tiền mặt', 'Thẻ tín dụng', 'Chuyển khoản', '', 1, '', '', NULL),
(410, 'runrun', 'toy', 'toy2', '', 'Hồ Chí Minh', 'barbie 2009', 100000, 200000, '4', '18', '36', 1235550187, 1235907360, 0, 'hang moi dep\r\n\r\nbupbe di kem 5 bo do\r\n\r\n5 doi giay va 2 cap kinh\r\n\r\ngia khuyen mai', '', 'USD', 3, '12000', 'khu vuc ho chi minh', 'Tiền mặt', 'Thẻ tín dụng', 'Chuyển khoản', '', 1, '', '', NULL),
(411, 'runrun', 'gam', 'gam1', 'gam1.1', 'Hồ Chí Minh', 'tai khoan thieu lam lvl 199', 1000000000, 2500000000, '4', '17', '16', 1235550187, 1235902560, 0, 'tai khoan VIP\r\n\r\nhang khung\r\n\r\nuoc tinh gia thi khoan 5 ti vnd', '', 'USD', 1, '', '', 'Tiền mặt', 'Thẻ tín dụng', 'Chuyển khoản', '', 1, '', '', NULL),
(412, 'runrun', 'jew', 'jew2', 'jew2.1', 'Hồ Chí Minh', 'Omage seamaster for lady', 3000000, 5000000, '7', '17', '35', 1235550187, 1236162900, 0, 'moi 99,9%\r\n\r\nkhong tray\r\n\r\ncon nguyen, giay chung nhan san pham \r\n\r\nbao hanh tran 159 quoc gia', '', 'USD', 1, '', '', 'Tiền mặt', 'Thẻ tín dụng', 'Chuyển khoản', 'Badede', 1, '', '', NULL),
(414, 'runrun', 'mob', 'mob12', 'mob12.7', 'Hồ Chí Minh', '', NULL, NULL, '', '', '', 0, 0, 0, '', '', '', NULL, '', '', '', '', '', '', NULL, '', '', NULL);

[eluser]runrun[/eluser]
the full tables structure

CREATE TABLE IF NOT EXISTS `listing` (
`id` int(11) unsigned NOT NULL auto_increment,
`username` varchar(40) collate utf8_unicode_ci NOT NULL,
`category1` varchar(40) collate utf8_unicode_ci NOT NULL,
`category2` varchar(40) collate utf8_unicode_ci NOT NULL,
`category3` varchar(50) collate utf8_unicode_ci NOT NULL,
`region` varchar(40) collate utf8_unicode_ci NOT NULL,
`title` char(100) collate utf8_unicode_ci NOT NULL,
`start_price` int(11) unsigned default NULL,
`buynow` int(11) unsigned default NULL,
`end` varchar(40) collate utf8_unicode_ci NOT NULL,
`hour` varchar(40) collate utf8_unicode_ci NOT NULL,
`minute` varchar(40) collate utf8_unicode_ci NOT NULL,
`start_time` int(11) NOT NULL,
`close_time` int(11) NOT NULL,
`visit` int(11) NOT NULL,
`detail` varchar(2000) collate utf8_unicode_ci NOT NULL,
`condition` varchar(50) collate utf8_unicode_ci NOT NULL,
`currency` varchar(50) collate utf8_unicode_ci NOT NULL,
`shipping` int(11) default NULL,
`shipping_cost` varchar(12) collate utf8_unicode_ci NOT NULL,
`shipping_detail` varchar(50) collate utf8_unicode_ci NOT NULL,
`cash` varchar(40) collate utf8_unicode_ci NOT NULL,
`credit_card` varchar(40) collate utf8_unicode_ci NOT NULL,
`bank_tranfer` varchar(40) collate utf8_unicode_ci NOT NULL,
`badede` varchar(50) collate utf8_unicode_ci NOT NULL,
`activation` int(11) default NULL,
`bidder` varchar(60) collate utf8_unicode_ci NOT NULL,
`buyer` varchar(60) collate utf8_unicode_ci NOT NULL,
`visibility` int(11) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=419 ;

[eluser]runrun[/eluser]
[quote author="TheFuzzy0ne" date="1236630174"]

Code:

SELECT * FROM `test` WHERE ts > '1236612404';

-- returns 1236612504

[/quote]
Yea, you are right about the string will not matter but

Can you help me tried the time() function instead of string '1236612404' ?

[eluser]jdfwarrior[/eluser]
Wait wait wait... you get this produced SQL from

$this->db->query("SELECT title, price FROM listing WHERE close_time > '".time()."'" ???

Thats a completely different query. The original code selects two fields from one table, based on a single where statement. Unless your not giving us the actual code?

[quote author="runrun" date="1236629260"]Here we are

The query produced:

Code:

SELECT a.*
       , b.thumb_id
       , ( SELECT COUNT(*)
  FROM listing_bid
WHERE listing_id = a.id ) AS c_rows
  FROM listing a
  LEFT OUTER
  JOIN listing_photo b
    ON b.listing_id = a.id
   AND b.key = 1    
WHERE (a.activation = 1 OR a.activation = 2)
   AND a.close_time > '1236610854'
ORDER BY a.close_time ASC

The time() function is recognized by CI as a string, not integer. I think this is the problem

This is actual the query that is in my controller :

Code:

"SELECT a.*
             , b.thumb_id
             , ( SELECT COUNT(*)
          FROM listing_bid
         WHERE listing_id = a.id ) AS c_rows
          FROM listing a
          LEFT OUTER
          JOIN listing_photo b
            ON b.listing_id = a.id
           AND b.key = 1    
         WHERE (a.activation = 1 OR a.activation = 2)
           AND a.close_time > '".time()."'
         ORDER BY a.close_time ASC"

[/quote]

[eluser]runrun[/eluser]
The later code is the actual code in my controller, the code in the first post is the code I created to test on the fly.

[eluser]TheFuzzy0ne[/eluser]
Hmmm... Can you give me the schema for listing bid, and the appropriate rows? I'm trying to do a mock-up.

[eluser]runrun[/eluser]
Yes.

There is no data in this table.

CREATE TABLE IF NOT EXISTS `listing_bid` (
`id` int(10) unsigned NOT NULL auto_increment,
`listing_id` int(11) NOT NULL,
`seller` varchar(60) collate utf8_unicode_ci NOT NULL,
`buyer` varchar(60) collate utf8_unicode_ci NOT NULL,
`amount` int(11) NOT NULL,
`currency` varchar(3) collate utf8_unicode_ci NOT NULL,
`time` bigint(20) NOT NULL,
`buynow` int(10) unsigned default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

[eluser]TheFuzzy0ne[/eluser]
Is this valid syntax?

Code:

JOIN listing_photo b
ON b.listing_id = a.id
AND b.key = 1

I was unaware you could join on a constant.

EDIT: I also need some data for listing_photo