Jeff Reach Jeff Reach - 6 months ago 14
MySQL Question

Only want results of those that are active to show in query. PHP MySQL

I am a new MySQL and PHP coder... used to work with it a about 7 years ago but now it has fallen in my lap again. Currently the code pulls students for attendance tracking and works like this:

$count = sqlValue("SELECT count(*) FROM `attendance` LEFT JOIN `enrollments` ON attendance.`student_class` = enrollments.id Where enrollments.class='".$class."' AND attendance.date='".$date."'");
if ($count == 0)
{
$query = "SELECT CONCAT_WS('', `students`.`fname`, ' ', `students`.`lname`) as `student`, `classes`.`class_name` as class, enrollments.id as eid FROM `students` LEFT JOIN `enrollments` ON students.id = enrollments.student LEFT JOIN `classes` ON classes.id = enrollments.class";
$query.= " WHERE enrollments.class='".$class."'";
$query.= " ORDER BY students.id";

$res = sql($query, $eo);
$tbody = '';$ids=array();
if(db_num_rows($res) > 0)


This works great but now they only want to show those that are "Active". I added a column to the database and coded the front end to flag the student as active. All this works, now I want to run a query that will only show the active students, which will then let me use a check box to mark them as present for the class. I have tried the following, but it did not work:

$count = sqlValue("SELECT count(*) FROM `attendance` LEFT JOIN `enrollments` ON attendance.`student_class` = enrollments.id Where enrollments.class='".$class."' AND attendance.date='".$date."'");
if ($count == 0)
{
$query = "SELECT CONCAT_WS('', `students`.`fname`, ' ', `students`.`lname`) as `student`, `classes`.`class_name` as class, enrollments.id as eid FROM `students` LEFT JOIN `enrollments` ON students.id = enrollments.student LEFT JOIN `classes` ON classes.id = enrollments.class";
$query.= " WHERE enrollments.class='".$class."'" and enrollments.status <> 'Active';
$query.= " ORDER BY students.id";

$res = sql($query, $eo);
$tbody = '';$ids=array();
if(db_num_rows($res) > 0)


It throws an error on line 45 which is the
$query.= " WHERE enrollments.class='".$class."'" and enrollments.status <> 'Active';
line.

Structure is

Table structure for table `enrollments`
--

CREATE TABLE IF NOT EXISTS `enrollments` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`student` int(10) unsigned DEFAULT NULL,
`class` int(10) unsigned DEFAULT NULL,
`enrollment_date` date NOT NULL,
`status` varchar(40) NOT NULL,
PRIMARY KEY (`id`),
KEY `student` (`student`),
KEY `class` (`class`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=109 ;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

Answer

You have an additional quotation mark in "'".

The line should read:

     $query.= " WHERE enrollments.class='".$class."' and enrollments.status <> 'Active'";
Comments