Queries are not work properly .Insert query is work fine but other three queries are not working it was occurred errors

Question

I was create a form with four buttons such as submit,update,delete and search.insert query is work fine but other three queries are not working it was occurred errors.

update

Error: UPDATE FROM course_details,personal_details,contact_details,academic_details,extra_curricular_activities WHERE course_details.Student_ID =personal_details.Student_ID AND course_details.Student_ID =contact_details.Student_ID AND course_details.Student_ID =academic_details.Student_ID AND course_details.Student_ID =extra_curricular_activities.Student_ID AND Student_ID=IM/20xx/002 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'FROM course_details,personal_details,contact_details,academic_details,extra_curr' at line 1

search

error: result error

delete

Error: DELETE FROM course_details,personal_details,contact_details,academic_details,extra_curricular_activities WHERE course_details.Student_ID =personal_details.Student_ID AND course_details.Student_ID =contact_details.Student_ID AND course_details.Student_ID =academic_details.Student_ID AND course_details.Student_ID =extra_curricular_activities.Student_ID AND Student_ID=IM/20xx/002 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'WHERE course_details.Student_ID =personal_details.Student_ID AND course_det' at line 1

Here is my files

Form.php

<?php

    $profile_picture=$_POST['profile_picture'];//define variables 
    $student_id=$_POST['student_id'];
    $admitted_date=$_POST['admitted_date'];
    $academic_year=$_POST['academic_year'];
    $course=$_POST['course'];

    $first_name=$_POST['first_name'];//define variables
    $middle_name=$_POST['middle_name'];
    $last_name=$_POST['last_name'];
    $dob=$_POST['dob'];
    $gender=$_POST['gender'];
    $nic=$_POST['nic'];
    $nationality=$_POST['nationality'];
    $religion=$_POST['religion'];
    $race=$_POST['race'];

    $p_add_line1=$_POST['p_add_line1'];//define variables
    $p_add_line2=$_POST['p_add_line2'];
    $p_add_line3=$_POST['p_add_line3'];
    $city=$_POST['city'];
    $email=$_POST['email'];
    $t_add_line1=$_POST['t_add_line1'];
    $t_add_line2=$_POST['t_add_line2'];
    $t_add_line3=$_POST['t_add_line3'];
    $stu_con_home=$_POST['stu_con_home'];
    $stu_con_mobile1=$_POST['stu_con_mobile1'];
    $stu_con_mobile2=$_POST['stu_con_mobile2'];
    $p_con_home=$_POST['p_con_home'];
    $p_con_mobile1=$_POST['p_con_mobile1'];
    $p_con_mobile2=$_POST['p_con_mobile2'];

    $o_admi_no=$_POST['o_admi_no'];//define variables
    $o_year=$_POST['o_year'];
    $o_subject1=$_POST['o_subject1'];
    $o_grade1=$_POST['o_grade1'];
    $o_subject2=$_POST['o_subject2'];
    $o_grade2=$_POST['o_grade2'];
    $o_subject3=$_POST['o_subject3'];
    $o_grade3=$_POST['o_grade3'];
    $o_subject4=$_POST['o_subject4'];
    $o_grade4=$_POST['o_grade4'];
    $o_subject5=$_POST['o_subject5'];
    $o_grade5=$_POST['o_grade5'];
    $o_subject6=$_POST['o_subject6'];
    $o_grade6=$_POST['o_grade6'];
    $o_subject7=$_POST['o_subject7'];
    $o_grade7=$_POST['o_grade7'];
    $o_subject8=$_POST['o_subject8'];
    $o_grade8=$_POST['o_grade8'];
    $o_subject9=$_POST['o_subject9'];
    $o_grade9=$_POST['o_grade9'];

    $a_admi_no=$_POST['a_admi_no'];//define variables
    $a_year=$_POST['a_year'];
    $a_subject1=$_POST['a_subject1'];
    $a_grade1=$_POST['a_grade1'];
    $a_subject2=$_POST['a_subject2'];
    $a_grade2=$_POST['a_grade2'];
    $a_subject3=$_POST['a_subject3'];
    $a_grade3=$_POST['a_grade3'];
    $a_subject4=$_POST['a_subject4'];
    $a_grade4=$_POST['a_grade4'];
    $z_score=$_POST['z_score'];

    $school_level=$_POST['school_level'];//define variables
    $zonal_level=$_POST['zonal_level'];
    $district_level=$_POST['district_level'];
    $provincial_level=$_POST['provincial_level'];
    $national_level=$_POST['national_level'];
    $international_level=$_POST['international_level'];
    $current_activities=$_POST['current_activities'];

    $connection = new mysqli("localhost","root","","student_information");//create connection

    //Test Connection
    if ($connection->connect_error) {
        die("Connection Failed :".$connection->connect_error);
    }

    //Create insert query
    if (isset($_POST['add'])) {
        $sql = "INSERT INTO course_details (Student_ID,Admitted_Date,Academic_Year,Course,Profile_Picture)
            VALUES ('$student_id','$admitted_date','$academic_year','$course','$profile_picture');";

        $sql .= "INSERT INTO personal_details (Student_ID,First_Name,Middle_Name,Last_Name,DOB,Gender,NIC,Nationality,Religion,Race)
            VALUES ('$student_id','$first_name','$middle_name','$last_name','$dob','$gender','$nic','$nationality','$religion','$race');";

        $sql .= "INSERT INTO contact_details (Student_ID,P_Add_Line1,P_Add_Line2,P_Add_Line3,City,Email,T_Add_Line1,T_Add_Line2,T_Add_Line3,stu_con_home,Stu_Con_Mobile1,Stu_Con_Mobile2,P_Con_Home,P_Con_Mobile1,P_Con_Mobile2)
            VALUES ('$student_id','$p_add_line1','$p_add_line2','$p_add_line3','$city','$email','$t_add_line1','$t_add_line2','$t_add_line3','$stu_con_home','$stu_con_mobile1','$stu_con_mobile2','$p_con_home','$p_con_mobile1','$p_con_mobile2');";
      
        $sql .= "INSERT INTO academic_details (Student_ID,O_Admi_No,O_Year,O_Subject1,O_Grade1,O_Subject2,O_Grade2,O_Subject3,O_Grade3,O_Subject4,O_Grade4,O_Subject5,O_Grade5,O_Subject6,O_Grade6,O_Subject7,O_Grade7,O_Subject8,O_Grade8,O_Subject9,O_Grade9,A_Admi_No,A_Year,A_Subject1,A_Grade1,A_Subject2,A_Grade2,A_Subject3,A_Grade3,A_Subject4,A_Grade4,Z_Score)
            VALUES ('$student_id','$o_admi_no','$o_year','$o_subject1','$o_grade1','$o_subject2','$o_grade2','$o_subject3','$o_grade3','$o_subject4','$o_grade4','$o_subject5','$o_grade5','$o_subject6','$o_grade6','$o_subject7','$o_grade7','$o_subject8','$o_grade8','$o_subject9','$o_grade9','$a_admi_no','$a_year','$a_subject1','$a_grade1','$a_subject2','$a_grade2','$a_subject3','$a_grade3','$a_subject4','$a_grade4','$z_score');";      

        $sql .= "INSERT INTO extra_curricular_activities (Student_ID,School_Level,Zonal_Level,District_Level,Provincial_Level,National_Level,International_Level,Current_Activities)
            VALUES ('$student_id','$school_level','$zonal_level','$district_level','$provincial_level','$national_level','$international_level','$current_activities')";

        //Test insert query
        if ($connection->multi_query($sql) === TRUE) {
            echo "Submitted Successsfully";
        } else {
            echo "Error: " . $sql . "<br>" . $connection->error;
        }
    }

    //create update query
    if (isset($_POST['update'])) {
        $sql ="UPDATE FROM course_details,personal_details,contact_details,academic_details,extra_curricular_activities WHERE course_details.Student_ID =personal_details.Student_ID AND
            course_details.Student_ID =contact_details.Student_ID AND
            course_details.Student_ID =academic_details.Student_ID AND
            course_details.Student_ID =extra_curricular_activities.Student_ID AND Student_ID=$student_id";

        if ($connection->multi_query($sql) === TRUE) {
            echo "Updated Successsfully";
        } else {
            echo "Error: " . $sql . "<br>" . $connection->error;
        }
    }

    //create delete query
    if (isset($_POST['delete'])) {
        $sql ="DELETE FROM course_details,personal_details,contact_details,academic_details,extra_curricular_activities WHERE course_details.Student_ID =personal_details.Student_ID AND
            course_details.Student_ID =contact_details.Student_ID AND
            course_details.Student_ID =academic_details.Student_ID AND
            course_details.Student_ID =extra_curricular_activities.Student_ID AND Student_ID=$student_id";

        if ($connection->multi_query($sql) === TRUE) {
            echo "Deleted Successsfully";
        } else {
            echo "Error: " . $sql . "<br>" . $connection->error;
        }
    }

    //create search query
    if (isset($_POST['search'])) {
        $sql ="SELECT course_details.*,personal_details.*,contact_details.*,academic_details.*,extra_curricular_activities.* FROM course_details,personal_details,contact_details,academic_details,extra_curricular_activities WHERE course_details.Student_ID =personal_details.Student_ID AND
            course_details.Student_ID =contact_details.Student_ID AND
            course_details.Student_ID =academic_details.Student_ID AND
            course_details.Student_ID =extra_curricular_activities.Student_ID AND Student_ID=$student_id";

        $result = mysqli_query($connection,$sql);

        if ($result) 
        {
            if (mysqli_num_rows($result)) 
            {
                while($rows=mysqli_fetch_array($result))
                {
                    $profile_picture=$rows['profile_picture'];
                    $student_id=$rows['student_id'];
                    $admitted_date=$rows['admitted_date'];
                    $academic_year=$rows['academic_year'];
                    $course=$rows['course'];

                    $first_name=$rows['first_name'];
                    $middle_name=$rows['middle_name'];
                    $last_name=$rows['last_name'];
                    $dob=$rows['dob'];
                    $gender=$rows['gender'];
                    $nic=$rows['nic'];
                    $nationality=$rows['nationality'];
                    $religion=$rows['religion'];
                    $race=$rows['race'];

                    $p_add_line1=$rows['p_add_line1'];
                    $p_add_line2=$rows['p_add_line2'];
                    $p_add_line3=$rows['p_add_line3'];
                    $city=$rows['city'];
                    $email=$rows['email'];
                    $t_add_line1=$rows['t_add_line1'];
                    $t_add_line2=$rows['t_add_line2'];
                    $t_add_line3=$rows['t_add_line3'];
                    $stu_con_home=$rows['stu_con_home'];
                    $stu_con_mobile1=$rows['stu_con_mobile1'];
                    $stu_con_mobile2=$rows['stu_con_mobile2'];
                    $p_con_home=$rows['p_con_home'];
                    $p_con_mobile1=$rows['p_con_mobile1'];
                    $p_con_mobile2=$rows['p_con_mobile2'];

                    $o_admi_no=$rows['o_admi_no'];
                    $o_year=$rows['o_year'];
                    $o_subject1=$rows['o_subject1'];
                    $o_grade1=$rows['o_grade1'];
                    $o_subject2=$rows['o_subject2'];
                    $o_grade2=$rows['o_grade2'];
                    $o_subject3=$rows['o_subject3'];
                    $o_grade3=$rows['o_grade3'];
                    $o_subject4=$rows['o_subject4'];
                    $o_grade4=$rows['o_grade4'];
                    $o_subject5=$rows['o_subject5'];
                    $o_grade5=$rows['o_grade5'];
                    $o_subject6=$rows['o_subject6'];
                    $o_grade6=$rows['o_grade6'];
                    $o_subject7=$rows['o_subject7'];
                    $o_grade7=$rows['o_grade7'];
                    $o_subject8=$rows['o_subject8'];
                    $o_grade8=$rows['o_grade8'];
                    $o_subject9=$rows['o_subject9'];
                    $o_grade9=$rows['o_grade9'];

                    $a_admi_no=$rows['a_admi_no'];
                    $a_year=$rows['a_year'];
                    $a_subject1=$rows['a_subject1'];
                    $a_grade1=$rows['a_grade1'];
                    $a_subject2=$rows['a_subject2'];
                    $a_grade2=$rows['a_grade2'];
                    $a_subject3=$rows['a_subject3'];
                    $a_grade3=$rows['a_grade3'];
                    $a_subject4=$rows['a_subject4'];
                    $a_grade4=$rows['a_grade4'];
                    $z_score=$rows['z_score'];

                    $school_level=$rows['school_level'];
                    $zonal_level=$rows['zonal_level'];
                    $district_level=$rows['district_level'];
                    $provincial_level=$rows['provincial_level'];
                    $national_level=$rows['national_level'];
                    $international_level=$rows['international_level'];
                    $current_activities=$rows['current_activities'];
                }
            }else{
                echo ("No data are available");
            }
        }else{
            echo ("result error");
        }
    }
    //print_r($_POST);

    $connection->close();
?>

Several issues here. FROM after UPDATE is incorrect syntax. You can also not specify different tables in a comma separated format in your delete, update and select statements. Just look up correct syntax for selecting from multiple tables and apply those rules to your deletes and updates as well. — Bleach, Jul 12 '18 at 04:10
https://stackoverflow.com/questions/15209414/how-to-do-3-table-join-in-update-query — Bleach, Jul 12 '18 at 04:20

score 0 · Answer 1 · answered Jul 12 '18 at 04:08

0

You should enclose your variable $student_id in quotes to convert this Student_ID=IM/20xx/002 into this Student_ID='IM/20xx/002' and make your query valid.

Anyway, I'd recommend to use the native MySQL/MariaDB functions (provided by the PDO PHP extension) to escape your query values and avoid security issues. Also the binding of values can be done with those functions.

Take a look: http://php.net/manual/en/function.mysql-real-escape-string.php

answered Jul 12 '18 at 04:08

Daniel Duarte

464
4
12

Are my queries incorrect?How can I create queries for multiple tables? – Chapa Jul 12 '18 at 04:22
Yes, the incorrect part is where the string is not enclosed between quotes. Here Student_ID=$student_id you are generating this: Student_ID=IM/20xx/002. You should put quotes around your variable. That would be a quick workaround for your code, but I'd recommend you to use the binding functions that PDO provides. You can simply enclose the $student_id between quotes if you are sure the IDs won't have special SQL characters. – Daniel Duarte Jul 12 '18 at 04:25
I correct that problem.but the same errors were occurred. – Chapa Jul 12 '18 at 04:29
$sql ="UPDATE course_details JOIN personal_details ON course_details.Student_ID =personal_details.Student_ID JOIN contact_details ON course_details.Student_ID =contact_details.Student_ID JOIN academic_details ON course_details.Student_ID =academic_details.Student_ID JOIN extra_curricular_activities ON course_details.Student_ID =extra_curricular_activities.Student_ID SET course_details.Student_ID ='$student_id'"; – Chapa Jul 12 '18 at 05:15
this is correct except for the last part of update. Check my answer. – david Jul 12 '18 at 06:01

david · Answer 2 · 2018-07-12T06:00:33.323

There are some errors in your query.

First, notice this code Student_ID=$student_id at the end of SELECT,UPDATE,DELETE. The php part should be enclosed so it will look like this. Student_ID='$student_id'

Second, there is no FROM in UPDATE query. Remove it.

Third, this is the most fatal one. Again, notice this code Student_ID=$student_id at the end of SELECT,UPDATE,DELETE. Since these three queries include all 5 tables and each of the tables have the same field Student_ID, there is no way the queries will know which Student_ID belongs to. Put one of the tables before the field.

After the errors are fixed, the end of SELECT,UPDATE,DELETE queries should look like this course_details.Student_ID='$student_id'.

For the course_details one, you can replace with any table as long as the table have the Student_ID field.

NOTE

You can improve these three queries using INNER JOIN. It is much more effective than put all of the tables after FROM.

EDIT

Here is the improved query for SELECT,UPDATE,DELETE.

SELECT

SELECT course_details.*, personal_details.*, contact_details.*, academic_details.*, extra_curricular_activities.*
FROM course_details
INNER JOIN personal_details ON course_details.Student_ID =personal_details.Student_ID
INNER JOIN contact_details ON course_details.Student_ID =contact_details.Student_ID
INNER JOIN academic_details ON course_details.Student_ID =academic_details.Student_ID
INNER JOIN extra_curricular_activities ON course_details.Student_ID =extra_curricular_activities.Student_ID
WHERE course_details.Student_ID='$student_id'

UPDATE

UPDATE course_details
INNER JOIN personal_details ON course_details.Student_ID =personal_details.Student_ID
INNER JOIN contact_details ON course_details.Student_ID =contact_details.Student_ID
INNER JOIN academic_details ON course_details.Student_ID =academic_details.Student_ID
INNER JOIN extra_curricular_activities ON course_details.Student_ID =extra_curricular_activities.Student_ID
SET ... (there is no set in your post so i don't know which field that needs to be updated)
WHERE course_details.Student_ID='$student_id'

DELETE

For this query, I break it into five queries.

DELETE FROM course_details WHERE Student_ID='$student_id';
DELETE FROM personal_details WHERE Student_ID='$student_id';
DELETE FROM contact_details WHERE Student_ID='$student_id';
DELETE FROM academic_details WHERE Student_ID='$student_id';
DELETE FROM extra_curricular_activities WHERE Student_ID='$student_id';

can you give me an example for that..I know use it for two tables.but I have 5 tables how can I write query for five tables — Chapa, Jul 12 '18 at 04:53

Queries are not work properly .Insert query is work fine but other three queries are not working it was occurred errors

2 Answers2