Foreign key not working with mysqli queries

Question

Please help me with this. Before setting up foreign key with this code, it's working fine. But after adding foreign key, it's not working again. I have two tables which I created relation for in the database and the relation is working fine but data is not inserted into the table again after creating the relation. This is the code. I used the column email to create the relation between the two tables.

 <?php
  session_start();
  if (isset($_POST['submit'])) {
     $pin = htmlspecialchars($_POST['pin']);
     $surName = htmlspecialchars($_POST['surName']);
     $firstName = htmlspecialchars($_POST['firstName']);
     $otherName = htmlspecialchars($_POST['otherName']);
     $email = htmlspecialchars($_POST['email']);
     $passWord = md5($_POST['passWord']);
     $confirmPass = md5($_POST['confirmPass']);

     if ((empty($pin && $surName && $firstName && $otherName && $email && $passWord) == false) && ($passWord == $confirmPass) && (filter_var($email, FILTER_VALIDATE_EMAIL))) 
{
     include "dbconnect.php";
     $sql = "SELECT * FROM `sono` WHERE `pin`='$pin'";   
     $check = mysqli_query($dbconnect, $sql);
     $numrow = mysqli_num_rows ($check);
     if ($numrow == 1)
    {
      $rows=mysqli_fetch_assoc($check);
      $Email = $rows['eMail'];
      if ($Email == false)
     {
         $query = "SELECT * FROM `sono` WHERE `eMail`='$email'";
         $check = mysqli_query($dbconnect, $query);
         $numrow = mysqli_num_rows($check);
         if ($numrow == 0)
         {
             $query = "UPDATE `sono` SET `surName`='$surName', `firstName`='$firstName', `otherName`='$otherName', `eMail`='$email', `passWord`='$passWord', `confirmPass`='$confirmPass' WHERE `pin`='$pin'";
             $query = "SELECT `id` as idmax FROM `sono`";
             $result = mysqli_query($dbconnect, $query) or die (mysqli_error($dbconnect));
             $rowa = mysqli_fetch_array($result);
             $rw = $rowa['idmax'];
             $rwo = $rw+1;
             $rwo = str_pad($rwo,4,'0',STR_PAD_LEFT);
             $sql = "UPDATE `sono` SET `id`='$rwo' WHERE `pin`='$pin'";
             $result = mysqli_query($dbconnect, $sql);
             $_SESSION['surName'] = $surName;
             $_SESSION['firstName'] = $firstName;
             $_SESSION['otherName'] = $otherName;
             $_SESSION['email'] = $email;
             $_SESSION['passWord'] = $passWord;

             $msg= "<p style='color: green; font-weight: bold; padding-left: 0px'>Registration successful,<a href='passport.php'> >>>Click here to proceed<<<</a></p>";
             } else
             $msg= "<p style = 'color: red; padding-left: 0'>Email already used, use another valid email to register</p>";
             } else
             $msg= "<p style = 'color: red; padding-left: 0'>The pin has been used</p>";
             } else
             $msg= "<p style = 'color: red; padding-left: 0'>The pin enter does not exist</p>";
             } else 
             $msg= "<p style = 'color: red; padding-left: 0'>Please Enter Your Names, Pin, Valid Email, Password and confirm your password to Start Registration</p>";
     }
     ?>

**Danger**: You are using [an unsuitable hashing algorithm](http://php.net/manual/en/faq.passwords.php) and need to [take better care](https://www.owasp.org/index.php/Password_Storage_Cheat_Sheet) of your users' passwords. — Quentin, Feb 07 '17 at 06:45
**Danger**: SQL is not HTML, use [something more apropriate for escaping data](http://stackoverflow.com/questions/60174/best-way-to-prevent-sql-injection-in-php) and leave `htmlspecialchars` until you prepare data for inserting in HTML. — Quentin, Feb 07 '17 at 06:47

score 0 · Answer 1 · answered Feb 07 '17 at 06:43

Below code always run select query because you are overriding the $query variable

  $query = "UPDATE `sono` SET `surName`='$surName', `firstName`='$firstName', `otherName`='$otherName', `eMail`='$email', `passWord`='$passWord', `confirmPass`='$confirmPass' WHERE `pin`='$pin'";
  $query = "SELECT `id` as idmax FROM `sono`";
  $result = mysqli_query($dbconnect, $query) or die (mysqli_error($dbconnect));
  $rowa = mysqli_fetch_array($result);

It should be like

 $query = "UPDATE `sono` SET `surName`='$surName', `firstName`='$firstName', `otherName`='$otherName', `eMail`='$email', `passWord`='$passWord', `confirmPass`='$confirmPass' WHERE `pin`='$pin'";
 $result = mysqli_query($dbconnect, $query) or die (mysqli_error($dbconnect));

 $query = "SELECT `id` as idmax FROM `sono`";
 $result = mysqli_query($dbconnect, $query) or die (mysqli_error($dbconnect));
 $rowa = mysqli_fetch_array($result);

It would be nice, if we get more details on your question.

Foreign key not working with mysqli queries

1 Answers1