CODEWITHSUNDEEP
phpmysql
Graham
Graham

Reputation: 35

comparing number of table entries with a maximum number

I am trying to retrieve an integer table element (max) which is 3 or 4 depending on the course, and comparing that with the number of elements already entered into the table.

Here is the code I am using to get the course maximum enrolment and the total number of courses registered: 

//Store the value of the course enrolment limit
$course1 = mysql_real_escape_string($_POST['coursename']);
$qw2 = "SELECT max FROM course WHERE name = '$course1'";
$courselimit = mysql_query($qw2) or die (mysql_error());


//check how many students are enrolled in the course
$qw3 = "SELECT COUNT(*) FROM enrolled WHERE course = '$course1' LIMIT 4";
$totalenrolled = mysql_query($qw3) or die (mysql_error());

Unfortunately my if statement does not seem to be working as it registers as true even when the number of table entries has exceeded the course limit.

    if ($totalenrolled >= $courselimit ){
        die('That course has reached the enrolment limit, please select another');
    }

EDIT1: Here is the dump for the course table, I have tried Alexey's solution, but unfortunately the statement does not appear to be functioning yet. I have isolated the problem to the $courselimit variable

--
-- Table structure for table `course`
--

CREATE TABLE `course` (
`name` varchar(50) DEFAULT NULL,
`code` varchar(10) DEFAULT NULL,
`max` int(2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `course`
--

INSERT INTO `course` (`name`, `code`, `max`) VALUES
('HTML', 'PROG-1288', 4),
('Javascript', 'PROG-2283', 3),
('Dreamweaver', 'MEDA-1380', 4),
('Photoshop', 'PHOT-1382', 3);

and here is the dump for the table I am inserting the values into and attempting to read from:

--
-- Table structure for table `enrolled`
--

CREATE TABLE `enrolled` (
`student` varchar(50) DEFAULT NULL,
`studentnum` varchar(50) DEFAULT NULL,
`course` varchar(50) DEFAULT NULL,
FULLTEXT KEY `student` (`student`,`studentnum`,`course`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `enrolled`
--

INSERT INTO `enrolled` (`student`, `studentnum`, `course`) VALUES
('graham', '987654', 'HTML'),
('bruce', '123456', 'HTML'),
('jane', '111222', 'HTML');

Upvotes: 0

Views: 47

Answers (4)

Alexey Palamar
Alexey Palamar

Reputation: 1430

You need to use mysql_num_rows().

//Store the value of the course enrolment limit
$course1 = mysql_real_escape_string($_POST['coursename']);
$qw2 = "SELECT max as `maxitem` FROM course WHERE name = '$course1'";
$res = mysql_query($qw2) or die (mysql_error());
$result = mysql_fetch_assoc($res);
$courselimit = $result['maxitem'];


//check how many students are enrolled in the course
$qw3 = "SELECT COUNT(*) FROM enrolled WHERE course = '$course1'";
$res = mysql_query($qw3) or die (mysql_error());
$totalenrolled = mysql_num_rows($res);

Upvotes: 1

NoobEditor
NoobEditor

Reputation: 15871

First, forget using mysql, instead, prefer mysqli_ or PDO for all operations, its more robust and secure!

You can get basic understanding of mysqli_ from here

Now, Problem :

Under if condition, you are comparting on objects when you have to do that on value :

Solution

fetch the values, before comparing them :

$row = mysqli_fetch_array($courselimit,MYSQLI_ASSOC); /* associative array */
$row2 = mysqli_fetch_array($totalenrolled,MYSQLI_ASSOC); /* associative array */

then compare them

if ($row['max'] >= $row2['count'] ) //assuming count is column name for 2nd resukt

Upvotes: 0

Mahendra Jella
Mahendra Jella

Reputation: 5596

You are not fetching the Mysql Object. Try this one will works I hope.

//Store the value of the course enrolment limit
$course1 = mysql_real_escape_string($_POST['coursename']);
$qw2 = "SELECT  COUNT(*) as count1 FROM course WHERE name = '$course1'";
$courselimit = mysql_fetch_assoc((mysql_query($qw2)) or die (mysql_error());


//check how many students are enrolled in the course
$qw3 = "SELECT COUNT(*) as count2 FROM enrolled WHERE course = '$course1' LIMIT 4";
$totalenrolled = mysql_fetch_assoc(mysql_query($qw3)) or die (mysql_error());


 if ($totalenrolled['count2'] >= $courselimit['count1 '] ){
    die('That course has reached the enrolment limit, please select another');
}

Upvotes: 0

JayEdgar
JayEdgar

Reputation: 196

Have you echoed the values $totalenrolled and $courselimit and checked it against the data in your data table to ensure you're getting what you want?

Also, the LIMIT 4 in the $qw3 won't be doing anything--there would only be one result since you're using COUNT(*).

Upvotes: 0

Related Questions