get the foreign key

Question

I am a self taught programmer in college and I am super confused since few days. I am working on the backend of a job listing website. The user will be able to post a job and I have three tables: jobs, keywords and requirements. The job_id is the primary key of jobs and also a foreign key in the keywords table. Right now, I am only able to insert data in the jobs table and I am not able to key anything from the keywords table. Each job_id can have multiple keyword.

SQL - jobs table

CREATE TABLE `jobs` (
  `title` text NOT NULL,
  `type` text NOT NULL,
  `location` text NOT NULL,
  `salary` int(11) NOT NULL,
  `description` text NOT NULL,
  `date` date NOT NULL,
  `job_id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`job_id`)
) ENGINE=InnoDB AUTO_INCREMENT=34 DEFAULT CHARSET=latin1;

SQL - keywords table

    CREATE TABLE `keywords` (
          `keyword_id` int(11) NOT NULL AUTO_INCREMENT,
          `keyword` text NOT NULL,
          `job_id` int(11) NOT NULL,
          PRIMARY KEY (`keyword_id`),
          KEY `job_id` (`job_id`),
          CONSTRAINT `keywords_ibfk_1` FOREIGN KEY (`job_id`) REFERENCES `jobs` (`job_id`)
        ) ENGINE=InnoDB AUTO_INCREMENT=28 DEFAULT CHARSET=latin1;

PHP (I know the code is not secured yet but I just want to understand first)

<?php
    require("../config/db.php");
    require("add-jobs.php");



    $link = mysqli_connect("localhost","root","","benoit");


    $title = $_POST["position"];
    $type = $_POST["job-type"];
    $location = $_POST["location"];
    $salary = $_POST["salary"];
    $description = $_POST["description"];
    $date = $publisheddate;

    $keywords = $_POST["keywords"];


    mysqli_query($link,"INSERT INTO jobs (`title`, `type`, `location`, `salary`, `description`, `date`)
    VALUES ('$title', '$type', '$location', '$salary', '$description', CURDATE())") 
    or die(mysqli_error($link));


    foreach ($keywords as $keyword){
        mysqli_query($link, "INSERT INTO keywords (`keyword`) VALUES ('$keyword')");
    }
    ?>

create a new row in the `keywords` table with `job_id` as a foreign key, `keyword_id` as a primary key and `keyword` stored in the array — Steve120, Sep 08 '19 at 22:08
See: https://stackoverflow.com/questions/15318368/mysqli-or-die-does-it-have-to-die — Dharman, Sep 09 '19 at 07:42

score -1 · Answer 1 · answered Sep 08 '19 at 20:33

-1

Get insert id with $mysqli->insert_id and use it:

mysqli_query($link, ".....");
$insert_id = mysqli_insert_id($link);

foreach ($keywords as $keyword){
    mysqli_query($link, "INSERT INTO keywords (`job_id`, `keyword`) VALUES ($insert_id, '$keyword')");
}

answered Sep 08 '19 at 20:33

u_mulder

54,101
5
48
64

It'll work only when this column is actually autoincremented – Robert Sep 08 '19 at 21:03

get the foreign key

1 Answers1