insert images in another table with foreign key

Question

I have two tables in my db, telephones(id, title, price) and images(id, tp_id, photos) I went in the images table and put a foreign key on the tp_id column to match the id in the telephones table so that every image is linked to a telephone. But the problem is my images go into the table fine but the tp_id column always has the value of 0, what I am missing here? can somebody guide me? Thanks

PS: I know about the security vulnerability of my code I am just doing some test here!

<?php

if (isset($_POST['submit'])) {

    include 'dbconnect.php';


    for ($i = 0; $i < count($_FILES["photo"]["name"]); $i++) {


        $target = "img/"; //This is the directory where images will be saved 
        $target_files = $target . basename($_FILES['photo']['name'][$i]); //This gets all the other information from the form
        $ad_title = $_POST['title'];
        $ad_price = $_POST['price'];
        $ad_photo = $target . ($_FILES['photo']['name'][$i]);

        if (!move_uploaded_file($_FILES['photo']['tmp_name'][$i], $target_files)) { //Tells you if its all ok 
            echo "Sorry, there was a problem uploading your file.";
        } else { //Gives and error if its not 
            $sql = "INSERT INTO telephones (title, price) VALUES ('$ad_title', '$ad_price')";
            $conn->query($sql);

            $sql1 = "INSERT INTO images (photos) VALUES ('$ad_photo') ";
            $conn->query($sql1);
//Writes the photo to the server

            header('location: addconfirm.php');
        }
    }
}
?>

**WARNING**: This code has severe [SQL injection bugs](http://bobby-tables.com/) because you're putting `$_POST` data directly into your query. Whenever possible use **prepared statements**. These are quite straightforward to do in [`mysqli`](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and [PDO](http://php.net/manual/en/pdo.prepared-statements.php) where any user-supplied data is specified with a `?` or `:name` indicator that’s later populated using `bind_param` or `execute` depending on which one you’re using. — tadman, Jul 16 '16 at 07:42

score 1 · Answer 1 · answered Jul 16 '16 at 07:42

1

get the last inserted primary key value using this

$last_id = $conn->insert_id;

answered Jul 16 '16 at 07:42

Arun Kumaresh

6,211
6
32
50

on whcih query the second one? – Juju Jul 16 '16 at 07:44
@Juju get last inserted Id from first query and use in second query. – munsifali Jul 16 '16 at 07:54

score 1 · Answer 2 · edited May 23 '17 at 10:28

1

You need to get last insert id form telephones table using $conn->insert_id; and the insert into images table as

 $sql = "INSERT INTO telephones (title, price) VALUES ('$ad_title', '$ad_price')";                      
            $conn->query($sql);
            $tp_id=$conn->insert_id;// get last insert id

 $sql1 = "INSERT INTO images (photos,tp_id) VALUES ('$ad_photo',$tp_id) ";
            $conn->query($sql1);

Note:- Your script is Open for sql injection check How can I prevent SQL injection in PHP? to prevent it

edited May 23 '17 at 10:28

Community

1
1

answered Jul 16 '16 at 07:42

Saty

22,443
7
33
51

Read http://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work – Saty Jul 16 '16 at 08:06

munsifali · Answer 3 · 2016-07-16T07:57:05.530

Question has already been answered many times Use : MySQL: LAST_INSERT_ID()

 $sql = "INSERT INTO telephones (title, price) VALUES ('$ad_title', '$ad_price')";                      
 $conn->query($sql);
 $tp_last_insert_id = $conn->LAST_INSERT_ID;// get last insert id

you should call this function right after you insert to get the latest added id

 $sql1 = "INSERT INTO images (photos,tp_id) VALUES ('$ad_photo',$tp_last_insert_id) ";
  $conn->query($sql1);

insert images in another table with foreign key

3 Answers3