bUJAt0nL

1SELECT *, studentid, COUNT(studentid),be_user_profiles.first_name, 
2be_user_profiles.last_name            
3FROM be_user_profiles
4LEFT JOIN hw_homework
5ON be_user_profiles.user_id= hw_homework.studentid
6WHERE be_user_profiles.advisor = $id
7GROUP BY be_user_profiles.user_id
8ORDER BY COUNT(studentid) DESC 
9     
10SELECT *, studentid, COUNT(studentid),be_user_profiles.first_name,
11be_user_profiles.last_name
12FROM be_user_profiles
13LEFT JOIN hw_homework
14ON be_user_profiles.user_id= hw_homework.studentid
15WHERE be_user_profiles.advisor = $id
16AND WHERE hw_homework.date  >= $from AND  <= $to // neither AND WHERE hw_homework.date BETWEEN $from AND $to
17GROUP BY be_user_profiles.user_id
18ORDER BY COUNT(studentid) DESC"; 
19     
20CREATE TABLE IF NOT EXISTS `hw_homework` (
21  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
22  `studentid` int(10) NOT NULL,
23  `subjectid` int(10) NOT NULL,
24  `assignment_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
25  `teacherid` int(10) NOT NULL,
26  `date` datetime NOT NULL,
27  `date_completed` datetime DEFAULT NULL,
28  `approved` int(1) unsigned NOT NULL DEFAULT '1',
29  `entered_by` int(10) unsigned NOT NULL,
30  PRIMARY KEY (`id`)
31) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=977 ;
32
33--
34-- Dumping data for table `hw_homework`
35--
36
37INSERT INTO `hw_homework` (`id`, `studentid`, `subjectid`, `assignment_name`, `teacherid`, `date`, `date_completed`, `approved`, `entered_by`) VALUES
38(2, 128, 4, 'Asdf', 59, '2012-11-08 00:00:00', NULL, 1, 0),
39(3, 96, 12, 'In Class Essay Practise', 67, '2012-11-09 00:00:00', NULL, 1, 0),
40(4, 96, 11, 'Creating Want ', 64, '2012-11-09 00:00:00', NULL, 1, 0),
41(5, 119, 13, '6A, Q1, 6B, Q1~Q3', 20, '2012-11-12 00:00:00', NULL, 1, 0),
42...
43... 
44     
45You have an error in your SQL syntax; check the manual that corresponds to your 
46MySQL server version for the right syntax to use near '00:00:00 AND 2013-06-14 
4700:00:00 GROUP BY be_user_profiles.user_id ' at line 6
48
49SELECT *, studentid, COUNT(studentid),be_user_profiles.first_name, 
50be_user_profiles.last_name FROM be_user_profiles LEFT JOIN hw_homework ON 
51be_user_profiles.user_id= hw_homework.studentid WHERE be_user_profiles.advisor = 20 
52AND hw_homework.date BETWEEN 2012-08-23 00:00:00 AND 2013-06-14 00:00:00 GROUP BY
53be_user_profiles.user_id ORDER BY COUNT(studentid) DESC 
54     
55WHERE be_user_profiles.advisor = $id
56AND WHERE hw_homework.date  >= $from AND  <= $to // neither AND WHERE hw_homework.date BETWEEN $from AND $to 
57     
58WHERE be_user_profiles.advisor = $id
59 AND hw_homework.date  >= $from AND  <= $to // neither AND WHERE hw_homework.date BETWEEN $from AND $to 
60     
61WHERE be_user_profiles.advisor = $id
62AND hw_homework.date  >= $from AND  <= $to 
63     
64WHERE be_user_profiles.advisor = $id
65AND hw_homework.date  >= '$from' AND  <= '$to' 
66     
67WHERE be_user_profiles.advisor = $id
68AND hw_homework.date  >= '$from' AND  hw_homework.date <= '$to'