CODEWITHSUNDEEP
javascriptphpmysql
Hamelraj
Hamelraj

Reputation: 4826

How to insert data as array values into MySQL?

job_no is primaary key

i want save description as array, when i insert many description to one job in database, last description only saved in saved database i can't see other descriptions.

var_dump($description): ??

html: ??

php:

<?php
include ("connection.php");

$sel_type = $_POST["type"];
$ref_no = $_POST["refno"];
$in_date = date("Y-m-d", strtotime($_POST['date']));
$inv_to = $_POST["to"];
$inv_attn  = $_POST["attn"];
$job_no = $_POST["jobno"];
$subject = $_POST["subject"];
$description = $_POST["descrip"];


if(!$_POST["submit"]){
    echo "please fill out";
    header('Location: index.php');
    }
    else{
        if($description){
            foreach($description as $c){
$sql = "INSERT INTO student(sel_type,ref_no,in_date,inv_to,inv_attn,job_no,subject,description) 
VALUES('$sel_type','$ref_no','$in_date','$inv_to','$inv_attn','$job_no','$subject','$c')";
        }}
if ($conn->query($sql) === TRUE) {
    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

$conn->close();

}
?>

Upvotes: 1

Views: 1144

Answers (4)

AmBeam
AmBeam

Reputation: 332

According to Your question, You should definitely use relationship table.

CREATE TABLE job_description (
  id INT NOT NULL AUTO_INCREMENT,
  description TEXT NULL,
  job_no INT NOT NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (job_no) REFERENCES student(job_no) ON DELETE CASCADE
);

But if You really need to, use json_encode (on insert/update) and json_decode (on reading) to store arrays in MySQL. The advantage is that jsoned arrays are more human-readable than serialized arrays.

Upvotes: 0

joy d
joy d

Reputation: 418

You are creating insert SQL inside foreach loop. But the execution of the insert SQL is happening outside the foreach loop. So you need to move query execution function call inside foreach loop.

foreach($description as $c){
$sql = "INSERT INTO  student(sel_type,ref_no,in_date,inv_to,inv_attn,job_no,subject,description) 
VALUES('$sel_type','$ref_no','$in_date','$inv_to','$inv_attn','$job_no','$subject','$c')";
if ($conn->query($sql) === TRUE) {
    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

}

However keep in mind, executing database operation inside a loop is strictly no-no. It hampers performance. You can try with mapping table or multi-insert queries.

Upvotes: 0

Just Lucky Really
Just Lucky Really

Reputation: 1401

You want to be using a relationship table.

Create a new table, called job_description:

CREATE TABLE job_description (
    job_no_FK BIGINT,
    description TEXT,
    FOREIGN KEY (job_no_FK) REFERENCES student(job_no)
)

Then, you can add multiple descriptions for a job number

Your php will then become something like:

<?php
include ("connection.php");

$sel_type = $_POST["type"];
$ref_no = $_POST["refno"];
$in_date = date("Y-m-d", strtotime($_POST['date']));
$inv_to = $_POST["to"];
$inv_attn  = $_POST["attn"];
$job_no = $_POST["jobno"];
$subject = $_POST["subject"];
$description = $_POST["descrip"];


if(!$_POST["submit"]){
    echo "please fill out";
    header('Location: index.php');
    }
    else{
    $sql = "INSERT INTO student(sel_type,ref_no,in_date,inv_to,inv_attn,job_no,subject) 
VALUES('$sel_type','$ref_no','$in_date','$inv_to','$inv_attn','$job_no','$subject');";
        if($description){
            foreach($description as $c){
         $sql.="INSERT INTO job_description(job_no_FK,description) 
VALUES('$job_no','$c');";
        }}
if ($conn->query($sql) === TRUE) {
    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

$conn->close();

}
?>

Also, you would then want to remove the description column from your first table:

ALTER TABLE student DROP COLUMN description

Upvotes: 1

Andizer
Andizer

Reputation: 344

You can use the serialize function to store arrays to the database and use unserialize to use them in your PHP code as array.

Upvotes: 0

Related Questions