insert data into two tables of different databases on same instance and same user

Question

I am currently inserting below data in brokers3_finalmain database in phpfox_user and phpfox_user_custom but now i want to insert the data full_name and country_iso in other database mobile at the same time .

public function delete_mobile($data)
{
    $params = array( ':mobile' => $data);
    $sql = "Delete FROM mobile where mobile=:mobile";
    $stmt = parent::query($sql, $params);

}

public function update_profile($data)
{
    $sql = 'UPDATE phpfox_user set full_name=:full_name, email=:email where user_id=:user_id';
    $params =(array(
        ':full_name' => $data['full_name'],
        ':email' => $data['email'],
        ':user_id' => $data['user_id']              
    ));
    //print_array($params);

    parent::query($sql, $params, '', 'main') or die(parent::$dbh->errorInfo());
}

public function register_user($data)
{
    //print_r($data);
    //$data=$data[0];
    //print_r($data);
    $params = array( ':email' => $data['email'], ':cf_mobile' => $data['mobile']);
    $sql = "SELECT * FROM phpfox_user, phpfox_user_custom where phpfox_user.email=:email AND phpfox_user_custom.cf_mobile=:cf_mobile";
    $stmt = parent::query($sql, $params, '', 'main');
    while($row = $stmt->fetch(PDO::FETCH_ASSOC)) :
        $res=$row;
    endwhile;

    if(count($res)>=1)
    {
        $result['status']='fail';
        $result['message']='user already exits';
        $result['user_id']=$res['user_id'];
    } else {
        $pass=md5(md5($data['password']).md5('78>'));
        $sql = 'INSERT INTO phpfox_user (full_name, email, password, password_salt, user_group_id, country_iso) 
        VALUES(:full_name, :email, :password, :password_salt, :user_group_id, :country_iso)';

        $params =(array(
            ':full_name' => $data['full_name'],
            ':email' => $data['email'],
            ':password' => $pass,
            ':password_salt' => '"Q>',
            ':user_group_id' => $data['user_group_id'],
            ':country_iso' => $data['location']             
        ));
        //print_array($params);

        parent::query($sql, $params, '', 'main') or die(parent::$dbh->errorInfo());
        # Affected Rows?
        $user_id=parent::$dbh->lastInsertId();

        $sql = 'INSERT INTO phpfox_user_custom (user_id, cf_mobile) 
        VALUES(:user_id, :cf_mobile)';
        $params =(array(
            ':user_id' => $user_id,
            ':cf_mobile' => $data['mobile']
        ));
        //print_array($params);

        parent::query($sql, $params, '', 'main') or die(parent::$dbh->errorInfo());
        # Affected Rows?
        //return parent::$dbh->lastInsertId();
        //return $user_id;
        $result['status']='success';
        $result['message']='Registeration successfully';
        $result['user_id']=$user_id;
    }
    return $result;
}

You need to manage the connections in your 'parent' class, and when you call query you'll define what connection to use — cmnardi
– cmnardi, Commented Apr 6, 2016 at 14:30
Sounds like a great project, let us know if you have a question! Although, if the 2 databases reside on the same mysql instance, then you can refer to a table not in the default database as databasename.tablename. If the databases reside in different mysql instances, then you need to create a separate connections to them. — Shadow
– Shadow, Commented Apr 6, 2016 at 14:34

Max · Accepted Answer · 2016-04-06 15:44:18Z

1

If both DB reside on the same mysql server and you establish the connection without picking a database, just put the name of the database in front of the tablename like

INSERT INTO myDBname.myTableName (myKey) VALUES (:myKey);

If you have a connection to one db you can change it before inserting.

$pdoConnetion->select_db('myDBName');
INSERT INTO myTableName (myKey) VALUES (:myKey);

edited Apr 6, 2016 at 15:44

answered Apr 6, 2016 at 14:41

Max

2,6431 gold badge28 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Nitin Jain Over a year ago

'INSERT INTO booking.mobile (full_name, country_iso) VALUES(:full_name, :country_iso)'; Not workin

Max Over a year ago

You have prpobably chosen one DB when establishing the PDO connection. You can either just connect to the server without picking one or change it before you insert. I'll edit my answer accordingly.

Nitin Jain Over a year ago

not working for me can you please insert in the above code and update maybe i am missing something i have also added few functions from mobile table in my code to explain better and how the script is coded

Max Over a year ago

No, I am afraid I'd have to set up a test environment to avoid adding problems myself and for that I don't have the time. Sorry.

Collectives™ on Stack Overflow

insert data into two tables of different databases on same instance and same user

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related