How to insert data to 2 tables in database with PHP?

Question

Sorry for asking this question, I'm a beginner. But before I write this, I already search similiar question and I've tried it but it didn't work, so I decide to ask question here.

I have 2 tables here :

CREATE TABLE users (
id_user char (4) PRIMARY KEY,
username varchar (20),
password varchar (200),
role set ('admin','operator','user)
);


CREATE TABLE info_user (
    id_info char (4),
    email varchar (200),
    fullname varchar (200),
    birthday date,
    address varchar (200),
    telephone varchar (200)
    );

so each user have each info, attribute id_info is foreign key with id_user. Is that true for the foreign key? Or inverted? And this below is my code for form PHP :

<?php 
include_once('config.php');

$username   = mysql_real_escape_string($_POST['username']);
$password   = mysql_real_escape_string($_POST['password']);
$role       = mysql_real_escape_string($_POST['role']);
$email      = mysql_real_escape_string($_POST['email']);
$fullname   = mysql_real_escape_string($_POST['fullname']);
$sex        = mysql_real_escape_string($_POST['sex']);
$birthday   = mysql_real_escape_string($_POST['birthday']);
$adress     = mysql_real_escape_string($_POST['adress']);
$telephone  = mysql_real_escape_string($_POST['telephone']);
$confirm_pass   = ($_POST['confirm_password']);



if ($confirm_pass != $password) {
    echo" <script language='JavaScript'> alert ('Password didn't match!');</script>";
    echo '<script language="JavaScript"> window.location.href ="register.php" </script>';
} else {
    $dup = mysql_query("SELECT username FROM users WHERE username='".$_POST['username']."'");
        if(mysql_num_rows($dup) >0){
            echo"<script language='JavaScript'> alert ('Username is already exist');</script>";
        echo '<script language="JavaScript"> window.location.href ="register.php" </script>';
        } else {
    // simpan data ke database
    $query1 = mysql_query("insert into users values('', '$username', '".md5($_POST['password'])."', '$role'");
        $query2 = mysql_query("insert into info_user values ('', '$email', '$fullname', '$sex','$birthday','$adress','$telephone')");


    if ($query1 && $query2) {
        // if success to save data
        echo" <script language='JavaScript'> alert ('Your registration is successfull');</script>";
        echo '<script language="JavaScript"> window.location.href ="halaman_admin.php" </script>';
} else {
    // if failed to save data
     echo" <script language= 'JavaScript'> alert ('There's error occured, please try again!'); </script>";
     echo '<script language="JavaScript"> window.location.href ="register.php" </script>';
    }}
}
?>

I tried with this code, but it didn't work. How to make it works? Those tables are related. Thank you in advance for help me, I really need this.

didn't work means failed to save data into database @YogeshSuthar — ulaaaan, Mar 22 '14 at 11:29
You code is vulnerable to mysql injection [read here](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php?rq=1), you sanitize input and the throw `$_POST[]` directly in database, no sense — Fabio, Mar 22 '14 at 11:30
actually this code was working when attributes of users and info_user were merged, but I want to split them, it didn't work this code. @Fabio — ulaaaan, Mar 22 '14 at 11:33
@sunny failed to save into database, maybe there's something wrong my PHP code? — ulaaaan, Mar 22 '14 at 11:35
also for attribute id_info is foreign key with id_user in second query use mysql_last_id() instead of NULL — Haseeb, Mar 22 '14 at 11:43
The SELECT seems unnecessary. INSERT IGNORE can do the same thing — Strawberry, Mar 22 '14 at 11:52
You could make a transaction. Check the example #4 here: http://www.php.net/manual/en/mysqlnd-ms.quickstart.transactions.php — Jo Smo, Mar 22 '14 at 12:03

score 0 · Answer 1 · answered Mar 22 '14 at 15:46

As you need the key from the newly created user row to correctly create the info_user row this is what you need to do.

// simpan data ke database
$query1 = mysql_query("insert into users values('', 
                                                '$username', 
                                                '".md5($_POST['password'])."', 
                                                '$role'");

if ( $query1 ) {
    $new_id_user = mysql_insert_id();  // get id_user of newly created user row

    $query2 = mysql_query("insert into info_user values ($new_id_user, 
                                                        '$email', 
                                                        '$fullname', 
                                                        '$sex',
                                                        '$birthday',
                                                        '$adress',
                                                        '$telephone')");
    if ( ! $query2 ) {
        // insert of info_user failed, code appropriatly
    }
} else {
    // insert of new User failed, code appropriatly
}

How to insert data to 2 tables in database with PHP?

1 Answers1