1

I am trying to insert the same data into the second table based on the first table created UserID (auto-increment).

I created an INSERT query which inserts the user registration data into the users_tb table. It works successfully. Then I need to insert the address describing data into the address_tb table, based on the UserID in users_tb. But unfortunately it doesn't work.

//Rady to insert:
$results = $Street. ", " .$Apt. ", " .$City. ", " .$State. ", " .$ZipCode. ", " .$Country; 
$query = mysql_query("INSERT INTO users_tb (UserId , FirstName , LastName , Email , Username , Password , Street , Apt , City , State , ZipCode , Country , Address , HomePhone , MobilePhone , Lat, Lng, Membership) VALUES ('' , '$First_Name' , '$Last_Name' , '$Email' , '$Username' , '$hashedPW' , '$Street' , '$Apt' , '$City' , '$State' ,'$ZipCode' ,'$Country' , '$results' , '$Ph_Number' , '$Mb_Number' , '0' , '0' , '1')");

// Insert Data into address_tb
$ad = mysql_query("SELECT UserID , Username FROM users_tb WHERE Username = $Username");
$adad = mysql_fetch_row($ad);
$UserID = mysql_real_escape_string($adad['UserID']);
$query = mysql_query("INSERT INTO address_tb (AddressID , Street , Apt , City , State , ZipCode , Country , Address , Lat , Lng , UserID) VALUES ('' , '$Street' , '$Apt' , '$City' , '$State' ,'$ZipCode' ,'$Country' , '$results' , '0' , '0' , '$UserID')");

I need to have the address fields also in my address_tb table with the respective UserID.

Tom Udding
  • 2,264
  • 3
  • 20
  • 30
George
  • 19
  • 3
  • Please **parameterise** your statements, you are probably vulnerable to SQL injections. See this [question & answer](https://stackoverflow.com/q/60174/5914775) for more information. Also there is **no more support** for `mysql_*` functions, they are [**officially deprecated**](https://wiki.php.net/rfc/mysql_deprecation), **no longer maintained** and [**removed**](https://php.net/manual/en/function.mysql-connect.php#warning) in PHP 7.0.0. You should update your code with [PDO](https://php.net/pdo) or [MySQLi](https://php.net/mysqli) to ensure the functionality of your project in the future. – Tom Udding Jan 15 '19 at 08:49
  • Why do you want to **duplicate** datas? Can't you normalize your DB? – Cid Jan 15 '19 at 08:50

3 Answers3

0

$Username should be enclosed in single quotes since it's a string.

$ad = mysql_query("SELECT UserID , Username FROM users_tb WHERE Username = '$Username'");

Second problem is, [mysql_fetch_row][1] returns enumerated array i.e. key as numeric indexes. Change is mysql_fetch_assoc.

$adad = mysql_fetch_assoc($ad);

Another approach is to get last insert id and use it to add address. After inserting into users table, use below method to retrieve last user id

$last_user_id = mysql_insert_id();

Use $last_user_id directly into the insert query on address_tb.

Please note mysql is deprecated. You should use mysqli with parameterized queries ( https://www.w3schools.com/php/php_mysql_prepared_statements.asp ).

Samir Selia
  • 7,007
  • 2
  • 11
  • 30
  • 2
    (or even better, parameterized) – Cid Jan 15 '19 at 08:41
  • Thank you for the consideration. I make it with single quotes, but again my table is empty. – George Jan 15 '19 at 08:44
  • 1
    @Cid, was drafting it. Added it as a note. – Samir Selia Jan 15 '19 at 08:51
  • @George, Check my updated answer. Also, move away from `mysql` and use prepared statements. I gave the solution as per your current implementation to give you an idea on how it will work. – Samir Selia Jan 15 '19 at 08:52
  • @Samir, thank you for your help. I appreciate. I just make the respective change but no results for the first solution: $ad = mysql_query("SELECT UserID , Username FROM users_tb WHERE Username = '$Username'"); $adad = mysql_fetch_assoc($ad); $UserID = mysql_real_escape_string($adad['UserID']); $query = mysql_query("INSERT INTO address_tb (AddressID , Country , Address , Lat , Lng , UserID) VALUES ('' , ' , '$Country' , '$results' , '0' , '0' , '$UserID')"); – George Jan 15 '19 at 08:59
  • You need to check if insert query is successful or if there is any error in the query or mysql connection is broken. Use `mysql_error` to debug. Also before inserting echo the value of `userid`. You will have to debug further on these points because the code looks good. – Samir Selia Jan 15 '19 at 09:29
0

This can be done in 1 query, using INSERT SELECT :

INSERT INTO address_tb (AddressID,
                        Street,
                        Apt,
                        City,
                        State,
                        ZipCode,
                        Country,
                        Address,
                        Lat,
                        Lng,
                        UserID)
SELECT (AddressID,
        Street,
        Apt,
        City,
        State,
        ZipCode,
        Country,
        Address,
        Lat,
        Lng,
        UserID)
FROM users_tb
WHERE users_tb.Username = ?;
Cid
  • 14,968
  • 4
  • 30
  • 45
  • Dear Cid, thank you for the consideration. I just past the provided code into my php. But it runs "Parse error: syntax error, unexpected 'INTO' (T_STRING) in /home/ezdelivery/public_html/Do_Register.php on line 48" . Where the 48 line is the 1st line in your code. – George Jan 15 '19 at 09:17
  • Did you wrap it in `""` ? – Cid Jan 15 '19 at 09:20
  • I have the code in this view. "$query = mysql_query("INSERT INTO address_tb (AddressID, Street, Apt, City, State, ZipCode, Country, Address, Lat, Lng, UserID) SELECT (AddressID, Street, Apt, City, State, ZipCode, Country, Address, Lat, Lng, UserID) FROM users_tb WHERE users_tb.Username = '$Username'");" But unfortunantly it does not insert the data into the address_tb table. – George Jan 15 '19 at 09:33
0

I did it. Thank you all of you. To tell the truth I didn't understand what was the reason that the second INSERT query didn't complete the data in address_tb. I only took the first query and edited as the second query and it starts working. As I see there aren't any differences in my new query, which I got from editing the first one. Here is my second query:

<< $sql2 = mysql_query("INSERT INTO address_tb (AddressID , Street , Apt , City , State , ZipCode , Country , Address , Lat , Lng , UserId) VALUES ('' , '$Street' , '$Apt' , '$City' , '$State' ,'$ZipCode' ,'$Country' , '$results' , '0' , '0' , '$UserID')"); >>

George
  • 19
  • 3