PHP looping over a list and store in a MYSQL database

Question

I am a self taught CS student and I am currently working on a job listing website. I am now working on the back-end page where the business owner will be able to see, add or remove a job. this is how the back end looks like.

Each jobs has 9 attributes allocated between three different tables jobs, keywords, requirements :

Job-ID (jobs table - primary key)
Title (jobs table)
Type (jobs table)
Location (jobs table)
Salary (jobs table)
Description (jobs table)
Date (jobs table)
Keyword (keywords table since each jobs can have multiple keywords)
Requirement (requirements table since each jobs can have multiple requirements)

Right now, I am able to submit a new job and store all the attributes for the jobs table but I have problems with the other two tables.

My issue is unlike the jobs table, the user can add multiple keywords. So I have implemented a JavaScript Function in add-jobs.js to create a new innerHTLM ul .list-keywords whenever the user will submit. What I need to do, is to loop over the .list-keywords and take the NodeValue of each li item. Then, I will have create an equal number of rows in my database as there isli.

It was much more easier for the jobs table since I just had to do $attribute = $_POST["attribute_name"] but with a list I have no idea how to do it

keywords table

Add-jobs.js

const keywords = document.querySelector(".keywords");
const addKeyword = document.querySelector(".add-keyword");
const listKeywords = document.querySelector(".list-keywords");

 const generateTemplate = (word, location) => {
     const html = `
        <li><span>${word}</span>
        <i class="far fa-times-circle delete"></i>
        </li> `;

     location.innerHTML += html;
 };


addKeyword.addEventListener("click", (e)=>{
    e.preventDefault();
    const word = keywords.value.trim();
    console.log(word);
    keywords.value = "";
    generateTemplate(word, listKeywords);
});


listKeywords.addEventListener("click", e =>{
    if(e.target.classList.contains("delete")){
        e.target.parentElement.remove();
    };

});

Add-jobs.php

</p>
    <div class="add">
        <label for="keywords">Keywords : </label>     
        <input type="text" class="keywords" name="keywords" value=""/>   
        <button class="add-keyword">Add</button>               
    </div>
        <ul class="list-keywords">
        </ul>
<p>

Add-databse.php

<?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;


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


?>

You are wide open to [**SQL injection**](https://www.owasp.org/index.php/SQL_Injection). You need to use prepared statements, rather than concatenating variables into your query. (Note that simply escaping your variables—which you are not currently doing—is not enough.) See [How can I prevent SQL injection in PHP?](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php?rq=1). — elixenide, Aug 23 '19 at 12:49
Thank you Ed, I saw it already but I am thinking to protect my code once I will understand how can I add the keywords in the DB — Jean Vreux, Aug 23 '19 at 12:50
Famous last words. And see: [Why should I provide an MCRE for what seems to me to be a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query) — Strawberry, Aug 23 '19 at 12:51
Have you made any attempt yet to do the looping you describe? You have the right idea; have you started implementing the loop? (And, as @Strawberry hinted, it's a much better idea and far easier to write secure code *first* than to try to make insecure code safe later.) — elixenide, Aug 23 '19 at 12:52
No I haven't started the loop because I has not sure if I has to do the loop with php or it will be with JS. I will prevent the SQL injections right now then, probably a better idea. — Jean Vreux, Aug 23 '19 at 13:02

arkuuu · Answer 1 · 2019-08-23T15:07:47.483

0

Change your keyword inputs to

<input type="text" class="keywords" name="keywords[]" value=""/>

(Notice the [] after the name attribute)

PHP will put all added keywords as an array into $_POST['keywords']. Now you can loop through the array and insert each keyword one by one:

$jobId = ... // <- get last insert id here for job id
foreach($_POST['keywords'] as $keyword) {
   // insert each $keyword and $jobId relation into db separately
}

And of course you should use Prepared Statements to insert your data safely into your database (as the commenters already noted, use them right from the beginning, don't add them later, as this mostly does not happen).

edited Aug 23 '19 at 15:07

answered Aug 23 '19 at 12:54

arkuuu

619
7
23

Thank you Arkuuu, I have a question. I do not really why the jobID is 0 by default. job_id is actually the primary key of the jobs table and job_id is also in the keywords table as a foreign key. – Jean Vreux Aug 23 '19 at 13:58
Sorry, that was misleading. It's not 0 by default, I was just unsure how to write that it is infact the last insert id (and thus primary key) ob jobs table. – arkuuu Aug 23 '19 at 15:07
Thanks and also seems the name="keywords[]" is not working because the size is always 0. I added a class "indivKeywords" to the innerHTML on the JavaScript when I am creating the
and I am able to get the size of the list with indivKeywords.lenght

Jean Vreux

Aug 23 '19 at 16:31

However, it's on the JS file so I can't use POST for that, I am a bit confused – Jean Vreux Aug 23 '19 at 16:32

PHP looping over a list and store in a MYSQL database

1 Answers1