CodeIgniter Forums
a sql condition like this is not gonna work in CI - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: a sql condition like this is not gonna work in CI (/showthread.php?tid=16516)

Pages: 1 2 3 4

a sql condition like this is not gonna work in CI - El Forum - 03-09-2009

[eluser]runrun[/eluser]
Hi,

I have a query like this

Code:
$data['query']=$this->db->query("SELECT title, price FROM listing WHERE close_time > '".time()."'")
<?php foreach($query->result() as $row):?>
<tr>
&lt;?php echo $row->title; ?&gt;
&lt;?php echo $row->price; ?&gt;
<tr>
<php endforeach; ?&gt;

ouput: 1 table rows

if eleminate the WHERE close_time > '".time()."' in the query

output will be: 12 table rows

note: in the database table there is only 12 records, and all 12 records match the condition WHERE close_time > '".time()."'

a sql condition like this is not gonna work in CI - El Forum - 03-09-2009

[eluser]pistolPete[/eluser]
What data type is the close_time column?
Is the close_time a UNIX timestamp?

a sql condition like this is not gonna work in CI - El Forum - 03-09-2009

[eluser]TheFuzzy0ne[/eluser]
Can you show us your database table data and schema?

a sql condition like this is not gonna work in CI - El Forum - 03-09-2009

[eluser]jdfwarrior[/eluser]
Following pete and fuzzy, I bet it would work, I would learn more toward you dont have the correct format. Is the database using a DATE/TIME field? If so you would have to format your time in the sql query to display the correct format. By default, time() returns the number of seconds since Unix Epoch. Date/Time field in SQL (if thats what your using) stores it as YYYY-MM-DD HH:MM:SS, and those obviously arent the same.

a sql condition like this is not gonna work in CI - El Forum - 03-09-2009

[eluser]runrun[/eluser]
[quote author="pistolPete" date="1236625745"]What data type is the close_time column?
Is the close_time a UNIX timestamp?[/quote]

the close_time field stores data like this: 1236723060

sidenote: I have a regular site with procedural code run the same query very well

here is the table structure
Code:
CREATE TABLE IF NOT EXISTS `listing` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `username` varchar(40) collate utf8_unicode_ci NOT NULL,
  `title` char(100) collate utf8_unicode_ci NOT NULL,
  `price` int(11) unsigned default NULL,
  `minute` varchar(40) collate utf8_unicode_ci NOT NULL,
  `start_time` int(11) NOT NULL,
  `close_time` int(11) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=421 ;


a sql condition like this is not gonna work in CI - El Forum - 03-09-2009

[eluser]jdfwarrior[/eluser]
Have you tried to echo the query it produces and testing that same query in say phpmyadmin or something to verify it produces everything correctly?

a sql condition like this is not gonna work in CI - El Forum - 03-09-2009

[eluser]TheFuzzy0ne[/eluser]
You can do this by adding:
Code:
$this->output->enable_profiler(TRUE);

at the start of the method, or in your constructor.

EDIT: Also, please provide the data that's actually in the table. Just a few rows will suffice.

a sql condition like this is not gonna work in CI - El Forum - 03-09-2009

[eluser]pistolPete[/eluser]
Enable the profiler to see which query is actually generated:

Code:
$this->output->enable_profiler(TRUE);

EDIT: too slow ;-)

a sql condition like this is not gonna work in CI - El Forum - 03-09-2009

[eluser]runrun[/eluser]
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"


a sql condition like this is not gonna work in CI - El Forum - 03-09-2009

[eluser]TheFuzzy0ne[/eluser]
Then remove the quote around the timestamp in your query. Wink

However, I didn't think it mattered whether it was a string or not. Perhaps I am wrong.