-1

How should i insert this without inserting the duplicates datas ? here is the logic: if row is greater than 0 check weather there is a duplicate. if there is a duplicate check whether there are duplicates then get the unique datas and insert it on the table ese if datas are unique insert it on the table

    if(isset($_POST['addenrolment'])) 
    { 

        $courseid = $_POST['course'];
        $schoolid = $_POST['school'];

        if(mysqli_num_rows(mysqli_query($conn, "SELECT * FROM enrolments WHERE userid = userid AND courseid ='$courseid' AND schoolid ='$schoolid' ")) > 0){
            header("Location: ../admin/enrolment.php?fail=duplicate");
        }else{
            mysqli_query($conn, "INSERT INTO enrolments (userid, schoolid, courseid, status) SELECT id, schoolid, '$courseid', 1 FROM users WHERE status = 1 AND schoolid = '$schoolid'");
            header("Location: ../admin/enrolment.php?success=add");
        }
    }

i have tried this but all the data regardless if it is on the table or not (using phpmyadmin)

INSERT INTO enrolments (userid, schoolid, courseid, status) SELECT t1.id, t1.schoolid, '18', '1' FROM users t1 WHERE EXISTS(SELECT userid FROM enrolments t2 WHERE t2.userid = t1.id AND t2.schoolid = t1.schoolid AND t2.courseid = courseid )

user6001531
  • 13
  • 3
  • 2
    Just put column must be unique with unique key then use `INSERT IGNORE ....` without `WHERE` – Simone Rossaini May 11 '22 at 08:38
  • 2
    **Warning:** Your code is vulnerable to SQL Injection attacks. You should use parameterised queries and prepared statements to help prevent attackers from compromising your database by using malicious input values. http://bobby-tables.com gives an explanation of the risks, as well as some examples of how to write your queries safely using PHP / mysqli. **Never** insert unsanitised data directly into your SQL. The way your code is written now, someone could easily steal, incorrectly change, or even delete your data. – ADyson May 11 '22 at 08:39
  • https://phpdelusions.net/mysqli also contains good examples of writing safe SQL using mysqli. See also the [mysqli documentation](https://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php) and this: [How can I prevent SQL injection in PHP?](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) . Parameterising your queries will also greatly reduce the risk of accidental syntax errors as a result of un-escaped or incorrectly quoted input values. If you learnt your current technique from a tutorial or book, please don't use it again. – ADyson May 11 '22 at 08:40
  • @SimoneRossaini it inserts everything if i remove the where condition – user6001531 May 11 '22 at 23:40

0 Answers0