SQL query not working but connection is done

Question

I'm Setting the page to execute the query but Not happening

<?php


        $username = "root";
        $password = "";
        $dbname = "grading";
        $mysqli = new mysqli("localhost", $username, $password, $dbname);
        if ($mysqli->connect_errno) {
            echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
        }
        $addregno = filter_input(INPUT_POST ,'add_s_regno');
        $addname =filter_input( INPUT_POST ,'add_s_name');
        $q1 = filter_input( INPUT_POST ,'add_q1');
        $q2 = filter_input( INPUT_POST ,'add_q2');
        $q3 = filter_input( INPUT_POST ,'add_q3');
        $q4 = filter_input( INPUT_POST ,'add_q4');
        $q5 = filter_input( INPUT_POST ,'add_q5');
        $q6 = filter_input( INPUT_POST ,'add_q6');
        $q7 = filter_input( INPUT_POST ,'add_q7');
        $q8 = filter_input( INPUT_POST ,'add_q8');
        $q9 = filter_input( INPUT_POST ,'add_q9');
        $q10 = filter_input( INPUT_POST ,'add_q10');

        if($addregno=="" OR $addname=="" OR $q1=="" OR $q2=="" OR $q3=="" OR $q4=="" OR $q5=="" OR $q6=="" OR $q7=="" OR $q8=="" OR $q9=="" OR $q10=="")
        {
            echo "<script type='text/javascript'>alert('Enter all The Details');</script>";
        }
        else
        {   

            $total=$q1+$q2+$q3+$q4+$q5+$q6+$q7+$q8+$q9+$q10;
            $sql=" INSERT INTO studentmarks (`Reg_No`, `student_name`, `q1`, `q2`, `q3`, `q4`, `q5`, `q6`, `q7`, `q8`, `q9`, `q10`, `Total`) VALUES ($addregno,$addname,$q1,$q2,$q3,$q4,$q5,$q6,$q7,$q8,$q9,$q10,$total)";
            $result=$mysqli->query($sql);
            if($result==true)
            {
                echo "<script>window.location = 'markentry.php'; alert('success entry');</script>" ;
            }
            else{
                echo "<script>window.location = 'markentry.php'; alert('failed');</script>";
            }
        }

?>

Please format your code and add a little bit more description to what error you are encountering, what you expected and anything else that can be helpful for someone who doesn't know what you are trying to do. — Dennis Alund, Jan 07 '19 at 07:42
Try to debug by echoing the statement that will be executed for each request, copy/paste it and see if manually it will insert the data in the database without problems. — Ebrahim Talaq, Jan 07 '19 at 07:48
You should think about normalising your database design, having a set number of questions means changing the system becomes a much more complex change. Also you shouldn't have a total as this is just a product of data already held - what happens if someone changes a value but doesn't update the total? — Nigel Ren, Jan 07 '19 at 07:59

score 0 · Accepted Answer · answered Jan 07 '19 at 07:49

When inserting, the correct way would be:

$sql=" INSERT INTO studentmarks (`Reg_No`, `student_name`, `q1`, `q2`, `q3`, `q4`, `q5`, `q6`, `q7`, `q8`, `q9`, `q10`, `Total`) VALUES ('$addregno','$addname','$q1','$q2','$q3','$q4','$q5','$q6','$q7','$q8','$q9','$q10','$total')";

That is,'$q7' instead of $q7(the quotes).This however is no no because of sql injection

score 0 · Answer 2 · answered Jan 07 '19 at 07:54

You are using string so you should use quote arounbd var but the use of php var in sql implies a risk for sql injection so you should use prepared statement and binding param

assuming that reg_no, is an integer q* and total are an double and student_name is a string you should use

stmt = $mysqli->prepare(" INSERT INTO studentmarks 
    (`Reg_No`, `student_name`, `q1`, `q2`, `q3`, `q4`, `q5`, `q6`, `q7`, `q8`, `q9`, `q10`, `Total`) 
     VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?) ");
$stmt->bind_param('isddddddddddd', $addregno,$addname,$q1,$q2,$q3,$q4,$q5,$q6,$q7,$q8,$q9,$q10,$total);

    $addregno = filter_input(INPUT_POST ,'add_s_regno');
    $addname =filter_input( INPUT_POST ,'add_s_name');
    $q1 = filter_input( INPUT_POST ,'add_q1');
    $q2 = filter_input( INPUT_POST ,'add_q2');
    $q3 = filter_input( INPUT_POST ,'add_q3');
    $q4 = filter_input( INPUT_POST ,'add_q4');
    $q5 = filter_input( INPUT_POST ,'add_q5');
    $q6 = filter_input( INPUT_POST ,'add_q6');
    $q7 = filter_input( INPUT_POST ,'add_q7');
    $q8 = filter_input( INPUT_POST ,'add_q8');
    $q9 = filter_input( INPUT_POST ,'add_q9');
    $q10 = filter_input( INPUT_POST ,'add_q10');
    $total=$q1+$q2+$q3+$q4+$q5+$q6+$q7+$q8+$q9+$q10;
  /* execute prepared statement */
$stmt->execute();

SQL query not working but connection is done

2 Answers2