0

I am struggling to insert mySQL data into multiple tables using PHP statements.

I have two tables:

user

user_ID (PK)

first_name

last_name

email

password

profile

profile_ID (PK)

user_ID (FK)

profile_image

profile_image_name

I update the user table like so:

$sql = "INSERT INTO user (first_name, last_name, email, password)
    VALUES
    ('$_POST[first_name]', '$_POST[last_name]', '$_POST[email]', '$_POST[password]')";

The user_ID is set to auto increment in the database, so when this code is executed the inserted data is automatically assigned to a unique PK, and stored in a session. At this same point though (creating the account) I need to insert the user_ID into the profile table as the FK. That way when the user uploads their profile image, it will be assigned to they will have a record in the profile table.

For example, on the image upload page I'm using this code:

        $user_ID = $_SESSION['login'];


        $ins1 = mysqli_query($db, "UPDATE profile SET `profile_image_name`='" . $profile_image_name . "' WHERE `user_ID`='$user_ID'");
        $ins2 = mysqli_query($db, "UPDATE profile SET `profile_image`='" . $profile_image . "' WHERE `user_ID`='$user_ID'");

This code only works if the user_ID FK is already present in the profile table so how do I insert it using php statements like the one I did earlier? I tried using the first approach again but it doesn't seem to be working...

Improve this question
2
  • WARNING: When using mysqli you should be using parameterized queries and bind_param to add user data to your query. DO NOT use string interpolation or concatenation to accomplish this because you will create severe SQL injection bugs. NEVER put $_POST data directly into a query. Commented Apr 3, 2016 at 1:48
  • WARNING: Writing your own access control layer is not easy and there are many opportunities to get it severely wrong. In this short example you have a number of dangerous SQL injection vulnerabilities coming from a reckless lack of proper escaping. Please, do not write your own authentication system when any modern development framework like Laravel comes with a robust authentication system built-in. Commented Apr 3, 2016 at 1:49

3 Answers 3

Reset to default
4

You can use insert_id for get last PK auto increment that you insert on Mysql

http://php.net/manual/mysqli.insert-id.php

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

Comments

0

Insert creates a new record, update changes an existing record. With your current scheme you need to INSERT into profile. Obviously this would mean that you can't use the same code to create a profile as you use to change a profile. But there is a further option REPLACE has the same syntax as INSERT, but will overwrite an existing record.

However if each user only has a single profile, the details should be held in a single table, not two.

Comment: Storing passwords as plain text is a very bad idea, go read up on password hashing. You should also have a Google for SQL injection; your authentication is trivial to bypass and easy to destroy.

Improve this answer

Comments

0

Try this:

INSERT INTO
`profile`(`user_ID`,`profile_image`,`profile_image_name`)
VALUES
('$user_ID','$profile_image','$profile_image_name')
ON DUPLICATE KEY UPDATE 
`profile_image`='$profile_image',`profile_image_name`='$profile_image_name'

user_ID must be unique

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.