How to insert data as array values into MySQL?

Question

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();

}
?>

Primary key is unique. If `$job_no` is always the same, you will have an issue here. — , Jun 08 '15 at 12:27
Why you are inserting all data in one table?? you can make separate table for job description and save more than one job description of same job with job_id. — Raja Usman Mehmood, Jun 08 '15 at 12:27
you should have given all the "description" input same name `descrip`. so change it to `descrip[]` as name array. then it shouls work. — krishna, Jun 08 '15 at 12:57

Just Lucky Really · Answer 1 · 2015-06-08T12:57:05.140

1

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

edited Jun 08 '15 at 12:57

answered Jun 08 '15 at 12:43

Just Lucky Really

1,341
1
15
38

In case the user wants to search through all this data / make statistics on a later time, this is the way to go! Otherwise, a simple json / serialized string would be enough as a solution. – Tanase Butcaru Jun 08 '15 at 13:00
1

Also, the only reason why you would use a json array is if the data is not going to be changed ... The process to select the json array, edit it in a controller, then re-inserting it is just too much work when you can do it using the RDBMS model – Just Lucky Really Jun 08 '15 at 13:09
i have created like other table call job_description and i try to insert data it wont save showing error in insert function,but if i try to insert student table and job_description table separate i can insert but i think their is problem in foreach loop can u short out..? – Hamelraj Jun 10 '15 at 11:17
Ensure you are using the .= (dot equals) in the foreach loop. Basically what you are doing here is creating a long sql statement into the $sql variable. If you can, could you post the output of the $sql variable if it errors, and I'll try help from there. – Just Lucky Really Jun 10 '15 at 13:51
`if(!$_POST["submit"]{echo"pleasefillout";header('Location:index.php');} else{$sql="INSERTINTOinvoice(invoice_type,reference_no,job_no,call_id,po_no,invoice_date,invoice_attn,subject,des_title)VALUES('$invoice_type','$reference_no','$job_no','$call_id','$po_no','$invoice_date','$invoice_attn','$subject','$des_title');";foreach($description as $c){ $sql .="INSERT INTO ivnoice_description(reference_no,description) VALUES ('$reference_no','$c');"; if ($conn->query($sql) === TRUE) { echo "New record created successfully"; }else { echo "Error: " . $sql . "
" . $conn->error; } }$conn->close();}` – Hamelraj Jun 11 '15 at 07:49
im getting this error **Error: INSERT INTO invoice(..) VALUES(...);INSERT INTO ivnoice_description(reference_no,description) VALUES ('ref7','des1'); You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INSERT INTO ivnoice_description(reference_no,description) VALUES ('ref7','des1')' at line 2Error: INSERT INTO invoice(...) VALUES(...);INSERTINTOivnoice_description(reference_no,description) VALUES('ref7','des1');INSERTINTOivnoice_description(reference_no,description) VALUES ('ref7','des2');** – Hamelraj Jun 11 '15 at 07:55
You've spelt invoice wrong ... You've spelt it 'ivnoice' ... Check if your table name is spelt the same – Just Lucky Really Jun 11 '15 at 09:10
@Stretch spelling not a problem its same as table name ... :( – Hamelraj Jun 11 '15 at 09:56
Okay, my next thought is the use of spaces. Try put a space after `INTO`, and after the `;` – Just Lucky Really Jun 11 '15 at 10:03

score 0 · Answer 2 · answered Jun 08 '15 at 12:37

0

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

answered Jun 08 '15 at 12:37

Andizer

344
1
7

Can you send me how to use serialize function here {} this is my form to insert description – Hamelraj Jun 09 '15 at 05:32
just `$data = serialize($_POST['descript']` and in query you can use `$data`. When you want to use this data you can do `$orig_data = unserialize( $result_from_db['description'] )` – Andizer Jun 09 '15 at 08:11

score 0 · Accepted Answer · edited May 23 '17 at 12:05

0

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.

edited May 23 '17 at 12:05

Community

1
1

answered Jun 08 '15 at 13:05

joy d

408
2
13

Glad to be of any help to you.Please mark my anwer as Correct. – joy d Jun 09 '15 at 07:40
now i have create two table like student and {CREATE TABLE job_description ( job_no VARCHAR (30) NOTNULL, description VARCHAR(30), FOREIGN KEY (job_no) REFERENCES student(job_no) )} when i try to insert data i coudnt insert but i can insert data separately canu give me idea to use foreach loop and insert dynamically – Hamelraj Jun 10 '15 at 11:22
`code` foreach($description as $c){ $sql = "INSERT INTO student ....."; if ($conn->query($sql) === TRUE) { echo "New record created successfully"; $sql = "INSERT INTO job_description....."; } else { echo "Error: " . $sql . "
" . $conn->error; } `code` Thumb rule of managing parent-child relationship is that first you insert data in parent that is student and only when that insert query successfully executed then insert into child that is job_description. – joy d Jun 10 '15 at 13:20
i have add this code 'foreach($description as $c){ $sql = "INSERT INTO invoice(ref_no,job_no,call_id,po_no,in_date,subject)VALUES('$ref_no','$job_no','$call_id','$po_no','$inv_date','$subject')";if ($conn->query($sql) === TRUE) { echo"Newrecordcreatedsuccessfully";$sql="INSERTINTOivnoice_description(reference_no,description) VALUES('$reference_no','$c')"; } else {echo "Error: " . $sql . "
" . $conn->error; } }' but when i insert data using my form it will say tis error **Warning: Invalid argument supplied for foreach() in H:\Xampp\htdocs\sample\insert.php on line 26** – Hamelraj Jun 10 '15 at 17:34

I think, $description is not an array. "foreach" loop is giving error due to that. Try echo '

';print_r($description);die; before foreach loop to debug the cause. Also you are trying to save $description in the second query. If its an array, then SQL will throw error or not save the data too.

– joy d Jun 11 '15 at 05:21

score 0 · Answer 4 · answered Jun 08 '15 at 13:11

0

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.

answered Jun 08 '15 at 13:11

AmBeam

332
1
9

can u send me link or sample code for using Json_encode for this – Hamelraj Jun 09 '15 at 05:40
@HamelRaj If you're going to update the row, you should read the record first, decode description like this: `json_decode($row['descrip'], true);` and update it using `array_merge` or similar. To insert a row, just do: `$query = 'INSERT INTO student (job_no, descrip) VALUES ('.$job_no.', "'.json_encode($descriptions).'");';`. Same method of encoding for update. – AmBeam Jun 09 '15 at 11:05

How to insert data as array values into MySQL?

4 Answers4