How can I INSERT data into two MySQL tables

Question

How can I insert data into two MySQL tables at once..

$username = $_POST['username'];
$user_pass = $_POST['password'];
$user_type = $_POST['tpye'];
$user_street = $_POST['street'];
$user_city = $_POST['city'];
$user_country = $_POST['country'];
$user_email = $_POST['email'];
$user_phone = $_POST['phone'];

$query = "INSERT INTO  fkrv_users 
                    (fkrv_username, fkrv_user_pass, fkrv_user_type, 
                    fkrv_user_street, fkrv_user_city, fkrv_user_cuntry, 
                    fkrv_user_email, fkrv_user_phone) 
           VALUES ('$username','$user_pass','$user_type,',
                   '$user_street','$user_city','$user_country',
                   '$user_email','$user_phone')";

$result = mysqli_query($link,$query);

I need to insert $username and $user_type into table fkrv_list..

I tried with two MySQL queries but does not work.. Here is what I tried..

$username = $_POST['username'];
$user_pass = $_POST['password'];
$user_type = $_POST['tpye'];
$user_street = $_POST['street'];
$user_city = $_POST['city'];
$user_country = $_POST['country'];
$user_email = $_POST['email'];
$user_phone = $_POST['phone'];

$query1 = "INSERT INTO  fkrv_users 
                   (fkrv_username, fkrv_user_pass, fkrv_user_type, 
                    fkrv_user_street, fkrv_user_city, fkrv_user_cuntry, 
                    fkrv_user_email, fkrv_user_phone) 
            VALUES ('$username','$user_pass','$user_type,',
                    '$user_street','$user_city','$user_country',
                    '$user_email','$user_phone')";

$query2 "INSERT INTO  fkrv_list 
                    (fkrv_list_username, fkrv_list_type) 
             VALUES ('$username','$user_type')

$result = mysqli_query($link,$query1,$query2);

I also tied with one query and with "AND INSERT INTO" but without success..

Any idea?

Thanks!

The data has not been saved in MySql tables (fkrv_users or fkrv_list) — John Stuart, Aug 18 '16 at 23:36
If you absolutely need to run them both at the same time, you can use a transaction: http://php.net/manual/en/mysqli.begin-transaction.php. And there's always `mysqli_multi_query`, too — andrewsi, Aug 18 '16 at 23:37
@JohnStuart mysqli_query($link, $query1); followed by mysqli_query($link, $query2); — independent.guru, Aug 18 '16 at 23:38
Your script is at risk of [SQL Injection Attack](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) Have a look at what happened to [Little Bobby Tables](http://bobby-tables.com/) Even [if you are escaping inputs, its not safe!](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) Use [prepared parameterized statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) — RiggsFolly, Aug 18 '16 at 23:41
Your queries are susceptible to SQL injection. Use PDO bound queries or at the very least mysqli_real_escape_string() — jedifans, Aug 18 '16 at 23:41
**Never store plain text passwords!** Please use PHP's [built-in functions](http://jayblanchard.net/proper_password_hashing_with_PHP.html) to handle password security. If you're using a PHP version less than 5.5 you can use the `password_hash()` [compatibility pack](https://github.com/ircmaxell/password_compat). Make sure you ***[don't escape passwords](http://stackoverflow.com/q/36628418/1011527)*** or use any other cleansing mechanism on them before hashing. Doing so *changes* the password and causes unnecessary additional coding. — Jay Blanchard, Aug 19 '16 at 11:34

score 2 · Answer 1 · edited Aug 18 '16 at 23:45

2

you can run each one separately like so :

$query1 = "INSERT INTO  fkrv_users (fkrv_username, fkrv_user_pass, fkrv_user_type, fkrv_user_street, fkrv_user_city, fkrv_user_cuntry, fkrv_user_email, fkrv_user_phone) VALUES ('$username','$user_pass','$user_type,','$user_street','$user_city','$user_country','$user_email','$user_phone')";

$query2 "INSERT INTO  fkrv_list (fkrv_list_username, fkrv_list_type) VALUES ('$username','$user_type')";

$result1 = mysqli_query($link,$query1);
$result2 = mysqli_query($link,$query2);

edited Aug 18 '16 at 23:45

RiggsFolly

93,638
21
103
149

answered Aug 18 '16 at 23:38

muhanna hamiad

183
1
4

you forgot to close a statement because you copied the OP's code, rather than picking up on it. – Funk Forty Niner Aug 18 '16 at 23:41
I just wrote the solution for the problem .. it's just a part thats not working with him ..thax – muhanna hamiad Aug 18 '16 at 23:43
1

`('$username','$user_type')` will produce a syntax error if not closed. Use `;` which again, should have been signaled. – Funk Forty Niner Aug 18 '16 at 23:45
Oh never mind; Riggs was kind enough to "fix it for you". – Funk Forty Niner Aug 18 '16 at 23:46
1

@Fred-ii- I just had a napp and am feeling all generous – RiggsFolly Aug 18 '16 at 23:46
Still it's vulnerable...You should post more safe answers which are not vulnerable to `SQL Attacks` As @RiggsFolly said in the comments that take a look at [**Little Bobby Tables**](http://bobby-tables.com/php.html).Learn Prepared Statements. – Umair Shah Aug 18 '16 at 23:57

score 0 · Answer 2 · answered Aug 19 '16 at 00:04

As others have said, you have to INSERT 2 times.

However, you can also handle this in a few better ways.

Option 1: Create a Trigger

delimiter #

create trigger to_list_from_users_ins_trig after insert on fkrv_users
for each row
begin
  insert into fkrv_list (fkrv_list_username, fkrv_list_type) values     (new.fkrv_username, new.fkrv_user_type);
end#

Option 2: Double Check Your Logic

This should actually be Option 1, but...

Make sure your SQL / Table schema / PHP code is as efficient and well put-together as you can put it. What parts logically go together. What logically fits into a specific domain or category?

If you need to have this field for every user, then it should probably be put in the users table.

What is the purpose of the list table? What do you use the data for?

Thirdly: a caution.

Your code currently inputs variables directly into the tables.

You should have some form of validation and sanitization going on to prevent things like SQL injection into user forms.

It is highly recommended that you escape any user input before you insert it into your database. Not doing so can leave you very vulnerable to attacks and the ability for not so nice people to have fun with your data.

To do so, you can simply do something like:

$username = mysqli_real_escape_string($link, $_POST['username']);

and etc. for each other variable going into a query.

You can read on the PHP.net manual page.

Even [if you are escaping inputs, its not safe!](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) Use [prepared parameterized statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) — RiggsFolly, Aug 19 '16 at 00:37

How can I INSERT data into two MySQL tables

2 Answers2

Option 1: Create a Trigger

Option 2: Double Check Your Logic

Thirdly: a caution.