We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.
  1. Home
  2. Beginners

How to insert data in multiple mysql tables

Hi @all,

i develop a register form with several tables, i took the "invo" project as my base.

  1. Table "account" includes accout_id, password, email and username -> here i can insert data with no problem.
  2. Table "profile" includes profile_id, account_id(foreign key), gender, birthday -> can't insert

I created 1 Controller RegisterController.php and 2 Model Account.php and Profile.php.

How can i insert data into the second table and set the account_Id as foreign_Key into its.

RegisterController:

class RegisterController extends ControllerBase{

        $form = new RegisterForm;

        if ($this->request->isPost()) {

            $username = $this->request->getPost('username', 'alphanum');
            $email = $this->request->getPost('email', 'email');
            $password = $this->request->getPost('password');
            $repeatPassword = $this->request->getPost('confirmPassword');
            $gender = $this -> request -> getPost('gender');

            if ($password != $repeatPassword) {
                $this->flash->error('Passwords are different');
                return false;
            }

            $user = new Account();
            $user->username = $username;
            $user->password = sha1($password);
            $user->email = $email;
            $user->date = new Phalcon\Db\RawValue('now()');
            $user->active = 'Y';
           if ($user->save() == false) {
                foreach ($user->getMessages() as $message) {
                    $this->flash->error((string) $message);
                }
            } else {
                //$this->tag->setDefault('email', '');
                //$this->tag->setDefault('password', '');
                $this->flash->success('Thanks for sign-up, please log-in to start generating invoices');
                return $this->forward('session/index');
            }
        }

        $profile = new Profile();
        $profile -> gender = $gender;
        $profile -> save();

        $this->view->form = $form;

    }

}

Account.php:

use Phalcon\Mvc\Model; use Phalcon\Mvc\Model\Validator\Email as EmailValidator; use Phalcon\Mvc\Model\Validator\Uniqueness as UniquenessValidator;

class Account extends Model { public function validation() { $this->validate(new EmailValidator(array( 'field' => 'email' ))); $this->validate(new UniquenessValidator(array( 'field' => 'email', 'message' => 'Sorry, The email was registered by another user' ))); $this->validate(new UniquenessValidator(array( 'field' => 'username', 'message' => 'Sorry, That username is already taken' ))); if ($this->validationHasFailed() == true) { return false; } }

}

Profile.php:

use Phalcon\Mvc\Model;

class Profile extends Model { public $gender;

public $birthday;

}



77.7k
edited Aug '15

Hi there, check the docs! ;]

Additional info in this thread



59.9k

Hi Lajos,

thx for your very fast reply, i will try it today :-)

Rgds

Stefan



59.9k

Sorry i can't get it run.

I also tried "belongsTo" and "hasMany". The only "insert into" is the user account, but nothing in the "profile".

Profile.php:

   $this->belongsTo('fid', 'Account', 'id', array(
        'alias' => 'account',
        'reusable' => true
    ));

Account.php:

    $this->hasMany('id', 'Profile', 'fid', array(
        'alias' => 'profile',
        'foreignKey' => array(
            'message' => 'Profile cannot be deleted because it\'s used on Users'
        )
    ));

ProfileController.php:

        $profile         = new Profile();
        $profile->assign(array(
            'fid' => $this->request->getPost('id', 'int'),
            'name' => "rfrfr",
            'birthday' => "eee",
            'country' =>  'dxde',

        ));

        $profile->save();

AccountController.php:

                $user        = new User();
                $user->assign(array(
                'username' => $username,
                'email' => $email,
                'password' => $password,
                'date' => new Phalcon\Db\RawValue('now()'),
                'active' => 'Y'

            ));

            $user->save();

Thx for your help :-)

Rgds

Stefan



77.7k
Accepted
answer
edited Aug '15

As far as I understand your code, you should do something like this:

// Profile.php (Model)
$this->belongsTo('fid', 'Account', 'id', array(
    'alias' => 'account',
    'reusable' => true
));
// Account.php (Model)
$this->hasMany('id', 'Profile', 'fid', array(
     'alias' => 'profile',
     'foreignKey' => array(
         'message' => 'Account cannot be deleted because it\'s used on Profile'
     )
));
// RegisterController.php (Controller)
public function registerAction() {
   $form = new RegisterForm;
   if ($this->request->isPost()) {
      $username = $this->request->getPost('username', 'alphanum');
      $email = $this->request->getPost('email', 'email');
      $password = $this->request->getPost('password');
      $repeatPassword = $this->request->getPost('confirmPassword');
      $gender = $this -> request -> getPost('gender');
      if ($password != $repeatPassword) {
         $this->flash->error('Passwords are different');
         return false;
      }
      $user = new Account();
      $user->username = $username;
      //$user->password = sha1($password);
      $user->password = $this->security->hash($password); // see: https://docs.phalcon.io/en/latest/reference/security.html
      $user->email = $email;
      $user->date = new Phalcon\Db\RawValue('now()');
      $user->active = 'Y';
      $profile = new Profile();
      $profile ->gender = $gender;
      $user->profile = array($profile); // hasMany relationship, define as array
      if ($user->save() == false) {
         foreach ($user->getMessages() as $message) {
            $this->flash->error((string) $message);
         }
      } else {
           $this->flash->success('Thanks for sign-up, please log-in to start generating invoices');
           return $this->forward('session/index');
        }
    } 
    $this->view->form = $form;
}

Hope it helps!



59.9k

YES YES YES!!! Lajos you are my best friend .... for now :-))))))

Thank you soooo much, it was only the array (= $user->profile = array($profile); // hasMany relationship, define as array)

Kind Rgds

Stefan



13.9k
in reply to Lajos Bencz

Hello! Thanks for adding this useful information.

That I'm trying to do is very similar to this, but I got no records on my second tables, in this example would be the "Profile" table, also, I'm not getting any errors, why this happens? How can I debugg this? Thank you!

As far as I understand your code, you should do something like this:

// Profile.php (Model)
$this->belongsTo('fid', 'Account', 'id', array(
   'alias' => 'account',
   'reusable' => true
));
// Account.php (Model)
$this->hasMany('id', 'Profile', 'fid', array(
    'alias' => 'profile',
    'foreignKey' => array(
        'message' => 'Account cannot be deleted because it\'s used on Profile'
    )
));
// RegisterController.php (Controller)
public function registerAction() {
  $form = new RegisterForm;
  if ($this->request->isPost()) {
     $username = $this->request->getPost('username', 'alphanum');
    $email = $this->request->getPost('email', 'email');
    $password = $this->request->getPost('password');
    $repeatPassword = $this->request->getPost('confirmPassword');
    $gender = $this -> request -> getPost('gender');
    if ($password != $repeatPassword) {
       $this->flash->error('Passwords are different');
        return false;
     }
    $user = new Account();
    $user->username = $username;
    //$user->password = sha1($password);
    $user->password = $this->security->hash($password); // see: https://docs.phalcon.io/en/latest/reference/security.html
    $user->email = $email;
    $user->date = new Phalcon\Db\RawValue('now()');
    $user->active = 'Y';
    $profile = new Profile();
    $profile ->gender = $gender;
    $user->profile = array($profile); // hasMany relationship, define as array
    if ($user->save() == false) {
       foreach ($user->getMessages() as $message) {
          $this->flash->error((string) $message);
       }
    } else {
         $this->flash->success('Thanks for sign-up, please log-in to start generating invoices');
         return $this->forward('session/index');
      }
  } 
  $this->view->form = $form;
}

Hope it helps!