Query Problem - Printable Version

Query Problem - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived General Discussion (https://forum.codeigniter.com/forumdisplay.php?fid=21)
+--- Thread: Query Problem (/showthread.php?tid=4925)

Query Problem - El Forum - 12-24-2007

[eluser]Sawariya[/eluser]
I have some problem while write this query in code igniter format.. Anybody can help me..

"SELECT created_on,user_id,profilepic FROM xyz WHERE date_format(created_on,'%Y-%m') < date_format(now(), '%Y-%m') GROUP BY date_format(created_on,'%Y-%m') desc ";

here created_on is table field name

Thanks in advance.............

Query Problem - El Forum - 12-24-2007

[eluser]Muhammad Faisal Shabbir[/eluser]
can u plz paste the structure of the table.

Query Problem - El Forum - 12-24-2007

[eluser]Muhammad Faisal Shabbir[/eluser]
$this->db->select(' created_on,user_id,profilepic');
$this->db->from('xyz');
$this->db->where(date_format(created_on,’%Y-%m’) < date_format(now(), ‘%Y-%m’));
$this->db->groupby(date_format(created_on,’%Y-%m’));
$this->db->orderby("created_on","desc");
i think it will work. if there are some issue then u can debug it on your local server but structure of the query will like this

Query Problem - El Forum - 12-24-2007

[eluser]Sarfaraz Momin[/eluser]
@Mohd Faisal Shabbir

Quote:$this->db->select(’ created_on,user_id,profilepic’);
$this->db->from(’xyz’);
$this->db->where(date_format(created_on,’%Y-%m’) < date_format(now(), ‘%Y-%m’));
$this->db->groupby(date_format(created_on,’%Y-%m’));
$this->db->orderby("created_on”,"desc");

There seems to be an issue in you implementation.

Code:
$this->db->select(’created_on,user_id,profilepic’); 

$this->db->where('date_format(created_on,’%Y-%m’) <', 'date_format(now(), ‘%Y-%m’)'); 

$this->db->groupby('date_format(created_on,’%Y-%m’)');

$this->db->orderby("created_on”,"desc");

$this->db->get('xyz');

If you have any doubts please lemme know.

Good Day !!!

Query Problem - El Forum - 12-25-2007

[eluser]Muhammad Faisal Shabbir[/eluser]
Thanx for correction

Query Problem - El Forum - 12-26-2007

[eluser]Sawariya[/eluser]
syntax error, unexpected '%' in E:\xampp\htdocs\......\system\application\models\archivesmodel.php on line 60

Table syntax
CREATE TABLE `users` (
`user_id` int(11) NOT NULL auto_increment,
`group_id` int(1) NOT NULL,
`category` enum('agencies','models','photographers') collate latin1_general_ci NOT NULL,
`email_id` varchar(100) collate latin1_general_ci NOT NULL,
`password` varchar(32) collate latin1_general_ci NOT NULL,
`salt` varchar(5) collate latin1_general_ci NOT NULL,
`display_name` varchar(100) collate latin1_general_ci NOT NULL,
`profilepic` varchar(100) collate latin1_general_ci NOT NULL,
`dob` date NOT NULL,
`model_type` enum('male','female','children','agencies') collate latin1_general_ci NOT NULL,
`address` varchar(100) collate latin1_general_ci NOT NULL,
`city` varchar(50) collate latin1_general_ci NOT NULL,
`state` varchar(50) collate latin1_general_ci NOT NULL,
`country` varchar(50) collate latin1_general_ci NOT NULL,
`zipcode` varchar(20) collate latin1_general_ci NOT NULL,
`oftheweek` enum('published','unpublished') collate latin1_general_ci NOT NULL default 'unpublished',
`created_on` datetime NOT NULL,
`activation_date` datetime NOT NULL,
`last_login` datetime NOT NULL,
`verification` varchar(20) collate latin1_general_ci NOT NULL,
`status` enum('active','inactive','deleted') collate latin1_general_ci NOT NULL default 'inactive',
`top_models` enum('active','inactive','notyet') collate latin1_general_ci NOT NULL default 'notyet',
PRIMARY KEY (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=28 ;

Query Problem - El Forum - 12-26-2007

[eluser]eggshape[/eluser]
Hi Sawariya,

In your implementation, you have a simple syntax error because you are trying to use single quotes within a single-quoted string; use double quotes:

Code:
$this->db->select(’created_on,user_id,profilepic’);

$this->db->where("date_format(created_on,’%Y-%m’) <", "date_format(now(), ‘%Y-%m’)");

$this->db->groupby("date_format(created_on,’%Y-%m’)");

$this->db->orderby("created_on”,"desc");

$this->db->get('xyz');