#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2014
    Posts
    6
    Rep Power
    0

    MySQL returning zeros


    We are developing a System for educational institutions. In this system, MySQL has been returning zeros whenever no marks are entered for a particular exam. How do we go round this problem because it shouldn't always be returning zeros when no mark has been entered. It should only do so in cases where it should but not in all cases.
    How can we get round that problem?
  2. #2
  3. Lord of the Dance
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Oct 2003
    Posts
    4,131
    Rep Power
    2011
    What SQL statement are you using?

    How does it know when it should or should not return zeros?

    Comments on this post

    • dastel agrees
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2014
    Posts
    6
    Rep Power
    0
    An example of a Select Statement we are using 'SELECT gl.min_score, gl.max_score, gl.name, gl.points FROM exam_groups e LEFT JOIN grading_levels gl ON gl.grading_groups_id = e.grading_group_id WHERE e.id='.$id;

    The grading system is such that in some cases there are no marks entered as certain subjects are not taken by some students hence it should just be a dash not a zero. The reason why we want to do away with the zeros in such cases is the grading system assigns a FAIL for a zero, which would be wrong when an exam isnt taken by that particular student.

    Is there a way we can prevent MySQL from returning zero when no data is entered in some cases and returning zero in other cases?

    Thank You.
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,732
    Rep Power
    4288
    Originally Posted by dastel
    Is there a way we can prevent MySQL from returning zero when no data is entered in some cases and returning zero in other cases?
    yes, there is

    could you please do a SHOW CREATE TABLE for the table in question

    Comments on this post

    • dastel agrees
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2014
    Posts
    6
    Rep Power
    0
    Originally Posted by r937
    yes, there is

    could you please do a SHOW CREATE TABLE for the table in question
    v_scores | CREATE TABLE `v_scores` (
    `id` int(11) DEFAULT NULL,
    `name` varchar(255) DEFAULT NULL,
    `subject` varchar(255) DEFAULT NULL,
    `subject_type` enum('Principle','Subsidiary') DE
    `subject_id` int(11) DEFAULT NULL,
    `code` varchar(255) DEFAULT NULL,
    `subject_group_id` varbinary(11) DEFAULT NULL,
    `subject_group` varchar(255) DEFAULT NULL,
    `batch_unit_id` int(10) unsigned DEFAULT NULL,
    `number` tinyint(3) DEFAULT NULL,
    `class` varchar(255) DEFAULT NULL,
    `calendar_year_begin` int(11) DEFAULT NULL,
    `year` int(11) DEFAULT NULL,
    `stream` varchar(5) DEFAULT NULL,
    `student_id` int(11) DEFAULT NULL,
    `student` varchar(511) DEFAULT NULL,
    `bot_weight` tinyint(4) DEFAULT NULL,
    `mot_weight` tinyint(4) DEFAULT NULL,
    `eot_weight` tinyint(4) DEFAULT NULL,
    `bot_marks` varbinary(9) DEFAULT NULL,
    `mot_marks` varbinary(9) DEFAULT NULL,
    `eot_marks` varbinary(9) DEFAULT NULL,
    `weighted_mark` decimal(16,6) DEFAULT NULL,
    `aggregate` varchar(255) DEFAULT NULL,
    `points` int(4) unsigned DEFAULT NULL,
    `letter_grade` varchar(255) DEFAULT NULL,
    `remarks` text,
    `general_comments` text,
    `class_teacher` varchar(511) DEFAULT NULL,
    `grading_group_id` int(11) DEFAULT NULL,
    `exam_id` int(11) DEFAULT NULL,
    `grading_level_id` int(11) DEFAULT NULL,
    `weightage` int(11) DEFAULT NULL,
    `grading_name` varchar(255) DEFAULT NULL
    ENGINE=InnoDB DEFAULT CHARSET=latin1
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,732
    Rep Power
    4288
    grading levels? min score? max score? are you sure you've got the right table?

    all the columns in the v_score table look like they accept NULL so i don't understand where your zeroes are coming from
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2014
    Posts
    6
    Rep Power
    0
    Originally Posted by r937
    grading levels? min score? max score? are you sure you've got the right table?

    all the columns in the v_score table look like they accept NULL so i don't understand where your zeroes are coming from
    v_score is a view not a table, the table where we were storing marks is the exam_score table which is:

    exam_scores | CREATE TABLE `exam_scores` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `student_id` int(11) DEFAULT NULL,
    `exam_id` int(11) DEFAULT NULL,
    `bot_marks` decimal(7,2) DEFAULT NULL,
    `mot_marks` decimal(7,2) DEFAULT NULL,
    `eot_marks` decimal(7,2) DEFAULT NULL,
    `remarks` text COLLATE utf8_unicode_ci,
    `created_at` datetime DEFAULT NULL,
    `updated_at` datetime DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `index_exam_scores_on_student_id_and_exam_id` (`student_id`,`exam_i
    `)
    ENGINE=InnoDB AUTO_INCREMENT=219 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

    Any way i can stop the zeros when no data is entered?
  14. #8
  15. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,732
    Rep Power
    4288
    Originally Posted by dastel
    Any way i can stop the zeros when no data is entered?
    make sure you enter NULLs instead of "no data"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  16. #9
  17. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2014
    Posts
    6
    Rep Power
    0
    Originally Posted by r937
    make sure you enter NULLs instead of "no data"
    The system doesn't allow NULL as an entry, it only allows numbers, so when we leave it blank, it assumes a zero and grades accordingly, which is what we want to get a way around for certain cases.
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2006
    Posts
    2,632
    Rep Power
    1811
    Your table looks like it will accept NULL as a score (I presume to reflect the 'not taken' status). Why, as a matter of interest does it accept NULL for student id? But back to scores ... as your table accepts NULL, it would look to be your application which will populate the table that is not liking nothing/NULL and converting that to a score (presumably valid for a really bad test where the student could not even spell their own name (which should be good for 1% at least!)).

    Comments on this post

    • dastel agrees
    The moon on the one hand, the dawn on the other:
    The moon is my sister, the dawn is my brother.
    The moon on my left and the dawn on my right.
    My brother, good morning: my sister, good night.
    -- Hilaire Belloc
  20. #11
  21. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,732
    Rep Power
    4288
    Originally Posted by dastel
    The system doesn't allow NULL as an entry, it only allows numbers, so when we leave it blank, it assumes a zero and grades accordingly, which is what we want to get a way around for certain cases.
    so... it's not a database problem, is it

    you need to change the application

    Comments on this post

    • dastel agrees
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  22. #12
  23. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2014
    Posts
    6
    Rep Power
    0
    We are using cross tab queries and I think this is where the problem is coming from (Returning zeros when nothing is entered). Is there a way round this?
  24. #13
  25. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,732
    Rep Power
    4288
    microsoft access?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo