0

I searched internet and stack-overflow for better logic I could work with but could not find. The error of the code is syntax error which I could not figure out. I am newbie and not well familiar with pdo or mysqli way. So I used deprecated mysql. As of now the print_r() command does not output anything as there is syntax error. Where could I go wrong? Please help me. 

$sub = "SELECT id FROM subjects WHERE class_id = 5 GROUP BY name";
$subj = mysql_query($sub,$dbconnect);
$rows = array();
 while ($row = mysql_fetch_assoc($subj)) {
     $rows[] = $row;
 }

$i=1;
    $query_res = "SELECT regd,";
    foreach($rows as $sub):
    $query_res .= " SUM(CASE WHEN `subject` IN ('".$sub['id']."')";
    $query_res .= " AND entry IN ('1')"; 
    $query_res .= " THEN (mark_score) END)/";
    $query_res .= " SUM(CASE WHEN `subject` IN ('".$sub['id']."')";
    $query_res .= " AND entry IN ('1')";
    $query_res .= " THEN (full_mark) END)*100 AS subj$i";
    endforeach;
    $query_res .= " FROM $dbexam WHERE regd='1' AND session='6'";   

    $res = mysql_query($query_res, $dbconnect) or die(mysql_error());
    $row_res = mysql_fetch_assoc($res);
    print_r($row_res);

Here is the actual error:

You have an error in your SQL syntax; check the manual that corresponds
to your MySQL server version for the right syntax to use near 'SUM(CASE
WHEN `subject` IN ('16') AND entry IN ('1') THEN (mark_score) 
END)/ SU' at line 1
Improve this question
9
  • 5
    'there is an error' is much less useful than including the actual error Commented Apr 30, 2015 at 2:48
  • @pala_, I will include the actual error. Commented Apr 30, 2015 at 2:49
  • 2
    It would also be very helpful to have the final $query_res string after concatenation. Commented Apr 30, 2015 at 2:50
  • mysql_ is depricated now. try mysqli_ instead Commented Apr 30, 2015 at 2:51
  • 1
    That forward slash in the query doesn't look healthy. Commented Apr 30, 2015 at 2:53

2 Answers 2

Reset to default
1

You need a comma separating each expression in the SELECT list.

Move the comma from the first line of the SQL, to be the first character added in the loop.

Looks like you're generating a statement like this: 

SELECT expr1, expr2 expr3 expr4

You need to generate a statement like this

SELECT expr1, expr2, expr3, expr4
                   ^      ^

Just add the comma before you append another expression.

For debugging, do an echo or var_dump of your generated SQL, before you execute it.

The generated SQL appears to be vulnerable to SQL Injection, unless you have elsewhere properly escaped the potentially unsafe values being included in the SQL text.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

thanks for pointing out. but I have no idea how I would skip the last comma.
@Hanner: first part of SQL doesn't have a comma. SELECT expr1. Inside the loop, append the comma first and then the expression , exprN. (That comma basically reads as "and select"). Just prepend the comma to each subsequent expression. No need to handle a "last comma". You only add a comma where you need it, at the beginning of the next expression..
Thanks. You gave me almost correct answer. The only problem is that value of $sub['id'] must be the same for case because it is used as subject, so a subject have markscore and fullmark.
0

The formatting is atrocious. I don't even know if this is valid SQL, but here it is formatted and more likely to be debugged:

$query_res = 'SELECT regd';
foreach($rows as $sub):
    $query_res .= <<<SQL
        , SUM (
          CASE WHEN `subject` IN ('{$sub['id']}')
          AND entry IN ('1')
          THEN (mark_score)
          END
        ) /
        SUM (
          CASE WHEN `subject` IN ('{$sub['id']}')
          AND entry IN ('1')
          THEN (full_mark)
          END
        ) * 100 AS subj$i
SQL;
    $i++;
endforeach;
Improve this answer

5 Comments

Oops, realized the forward slash was for division. I put it back in. Well, that's what I mean about formatting making a big difference in code readability.
I am working on your answer. Value of '{$sub['id']} must be the same. that's the only problem.
@Hanner Maybe try to remove the $i++; that I added?
@Hanner ...and change subj$i to subj{$sub['id']}
now I got the subj0 two times, it must be subj0, subj1 as there can't be the same column name. Thanks.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.