+- 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)
a sql condition like this is not gonna work in CI - El Forum - 03-09-2009
[eluser]runrun[/eluser]
I am pretty sure that's valid, as I used the same query I gave you on this site www.badede.com , it works as expected and that site isn't using CI.
The listing_photo table
Code:
CREATE TABLE IF NOT EXISTS `listing_photo` (
`id` int(11) unsigned NOT NULL auto_increment,
`listing_id` int(11) unsigned NOT NULL,
`photo_id` varchar(100) collate utf8_unicode_ci NOT NULL,
`thumb_id` varchar(100) collate utf8_unicode_ci NOT NULL,
`key` int(11) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=349 ;
--
-- Dumping data for table `listing_photo`
--
INSERT INTO `listing_photo` (`id`, `listing_id`, `photo_id`, `thumb_id`, `key`) VALUES
(329, 411, '4664411_thongtin.jpg', '4664411_t_thongtin.jpg', 1),
(330, 412, '2068412_007watch.jpg', '2068412_t_007watch.jpg', 1),
(331, 412, '7275412_007watch.jpg', '7275412_t_007watch.jpg', NULL),
(332, 413, '3015413_bdps1qd1.jpg', '3015413_t_bdps1qd1.jpg', 1),
(333, 391, '3923391_navigate.png', '', NULL),
(334, 391, '9151391_5.jpg', '9151391_t_5.jpg', NULL),
(335, 391, '6546391_6.jpg', '6546391_t_6.jpg', NULL),
(336, 391, '6836391_7.jpg', '6836391_t_7.jpg', NULL),
(337, 391, '4379391_8.jpg', '4379391_t_8.jpg', NULL),
(338, 391, '4072391_9.jpg', '4072391_t_9.jpg', NULL),
(339, 391, '8354391_10.jpg', '8354391_t_10.jpg', NULL),
(340, 391, '3469391_11.jpg', '3469391_t_11.jpg', NULL),
(341, 391, '2519391_12.jpg', '2519391_t_12.jpg', NULL),
(342, 415, '3282415_macbook-Amit.jpg', '3282415_t_macbook-Amit.jpg', 1),
(343, 416, '8022416_Dell_Laptop.jpg', '8022416_t_Dell_Laptop.jpg', 1),
(344, 417, '5791417_hp_desktop_design.jpg', '5791417_t_hp_desktop_design.jpg', 1),
(345, 418, '3418418_mb_icon.gif', '', 1);a sql condition like this is not gonna work in CI - El Forum - 03-09-2009
[eluser]TheFuzzy0ne[/eluser]
The trouble is though, CI doesn't touch queries that go through $this-db->query(), so I can't see how it could possible be CI.
a sql condition like this is not gonna work in CI - El Forum - 03-09-2009
[eluser]runrun[/eluser]
Well you had my listing table, just try something simple like this you can see the problem.
controller:
Code:
$data['query'] = $this->db->query("SELECT * FROM listing WHERE close_time > '".time()."'");
$this->load->view('the_view_page',$data);view:
Code:
<?php foreach($query->result() as $row):?>
<?=$row->title?>
<?php endforeach;?>a sql condition like this is not gonna work in CI - El Forum - 03-09-2009
[eluser]drewbee[/eluser]
Fuzzy, its valid syntax, just as is comparing against other tables, or comparing against constats.
runrun, you are not getting any results back because the database is returning what you are selecting.
Your query is currently trying to find any dates whose closing times are greater then right now (time());
Taken from your previous data dump on the last page, 1234476360 was a timestamp listed for closing time.
Last I checked, 1234476360 is currently less then time() (1236619738 at time of writing this), not greater then.
You've got your symbol backwards.
My guess is that you have some seriously missing logic here though, and the query is more complex then what you initially are thinking. Simply put, you have no rows whos closing time is beyond right now.
What are you trying to accomplish?
a sql condition like this is not gonna work in CI - El Forum - 03-09-2009
[eluser]TheFuzzy0ne[/eluser]
I'm baffled!
Code:
<?php
class Test extends Controller {
function Test()
{
parent::Controller();
}
function index()
{
$this->query();
}
function query()
{
$query = '
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';
$result = $this->db->query($query);
echo $result->num_rows() . ' row(s) found';
}
}
// End of file: test.php
// Location: ./system/application/controllers/test.phpResult:
[quote author="" date=""]
1 row(s) found
[/quote]
a sql condition like this is not gonna work in CI - El Forum - 03-09-2009
[eluser]TheFuzzy0ne[/eluser]
Drew, the problem is that all of the rows are being returned. However, my test of the same database query, both via PHPMyadmin and within CI, return a single row.
a sql condition like this is not gonna work in CI - El Forum - 03-09-2009
[eluser]jdfwarrior[/eluser]
The he said it was the other way around? That he only got one result, and it was supposed to return all?
a sql condition like this is not gonna work in CI - El Forum - 03-09-2009
[eluser]drewbee[/eluser]
I think their is some confusion here. With the given data and his supplied query, I would only expect 1 row to be returned with the use of the time() function.
He is saying all the rows are being returned? I don't believe that.
a sql condition like this is not gonna work in CI - El Forum - 03-09-2009
[eluser]TheFuzzy0ne[/eluser]
That's what I'm trying to get to the bottom of. It doesn't make sense, and I can confirm the integrity of the data that's been posted. One thing I am sure of though, is that it's nothing to do with CodeIgniter.
a sql condition like this is not gonna work in CI - El Forum - 03-09-2009
[eluser]drewbee[/eluser]
I personally think he is confused.
Quote:note: in the database table there is only 12 records, and all 12 records match the condition WHERE close_time > ‘“.time().”’
is a false statement.