Inserting index array to one column in MySQL

Question

How can I insert an index array into a table in MySQL? At this snippet I need to insert array items into data

$data = [5555,22,102858,12,.554,88888,99999999,12,1.5];

$temtbl = "CREATE TEMPORARY TABLE IF NOT EXISTS `dataTable` (
           `data` decimal(14,2) DEFAULT 100        
          ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 ;";
$conn->query($temtbl);

$insertTotbl= "INSERT INTO `dataTable`(`data`);

$conn->query($insertTotbl);

Update

$row =[];
$query5 = "SELECT *  FROM `dataTable`" ;
$results = $conn->query($query5);
if ($results) {
    $row = $results->fetch_array(MYSQLI_NUM);
    $row = array_map('floatval', $row); 

}
$conn->close();
 echo json_encode($row);

Do you want to insert the whole array in a single field? Or do you want to insert each entry of the array as a new row in your table? (by the way, you're missing closing quotes in that snippet) — Barranka
– Barranka, Commented Sep 18, 2015 at 18:19
Thanks Barranka, yes I want to insert whole array in one single field — Mona Coder
– Mona Coder, Commented Sep 18, 2015 at 18:20

samlev · Accepted Answer · 2015-09-18 18:36:36Z

2

An easy way to do this:

$data = [5555,22,102858,12,.554,88888,99999999,12,1.5];

$temtbl = "CREATE TEMPORARY TABLE IF NOT EXISTS `dataTable` (
           `data` decimal(14,2) DEFAULT 100        
          ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 ;";
$conn->query($temtbl);

$insertTotbl= "INSERT INTO `dataTable`(`data`) VALUES (" . implode('),(', $data) . ")";

$conn->query($insertTotbl);

update

The second part:

$nums = [];
$query5 = "SELECT *  FROM `dataTable`" ;
$results = $conn->query($query5);
while ($row = $results->fetch_array()) {
    $nums[] = floatval($row[0]); 
}
$conn->close();
echo json_encode(nums);

edited Sep 18, 2015 at 18:36

answered Sep 18, 2015 at 18:20

samlev

5,9501 gold badge28 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Mona Coder Over a year ago

Thanks samlev , can you please take a look at update? when I try to get the data like updated code I am only getting NULL on the page

samlev Over a year ago

Updated with the second part

Mona Coder Over a year ago

Thanks now I am getting this error Fatal error: Call to a member function fetch_array() on a non-object in C:\wamp\www\Shahab\index.php on line 25

Mona Coder Over a year ago

and now I am getting Fatal error: Call to undefined function float_val() in

samlev Over a year ago

Yeah, my mistake. the function should be floatval

HPierce · Accepted Answer · 2015-09-18 18:24:26Z

0

You can serialize it before storing it:

<?php
$data = [5555,22,102858,12,.554,88888,99999999,12,1.5];

$foo = serialize($data);

var_dump($foo); // Outputs: a:9:{i:0;i:5555;i:1;i:22;i:2;i:102858;i:3;i:12;i:4;d:0.5540000000000000479616346638067625463008880615234375;i:5;i:88888;i:6;i:99999999;i:7;i:12;i:8;d:1.5;}

At this point you can store $foo as a string in the database. When you query for the data, you can unserialize it get the array back:

$bar = unserialize($foo);

var_dump($bar); //Outputs the array.
?>

answered Sep 18, 2015 at 18:24

HPierce

7,4397 gold badges37 silver badges49 bronze badges

3 Comments

Mona Coder Over a year ago

Thanks HPierce and how can I insert the serialized data into table?

HPierce Over a year ago

Just a standard MySQL query: INSERT INTO dataTable VALUES ($serializedData). The examples you posted looked fine. If you need me to provide a full example, I guess I can do that. Let me know.

Mona Coder Over a year ago

I did but still getting NULL in return!

Mxx persian · Accepted Answer · 2015-09-18 18:24:46Z

0

try this :

foreach($data as $val)
{
$insertTotbl= "INSERT INTO `dataTable`(`data`) VALUES ('".$val."')   ";
$conn->query($insertTotbl);
}

answered Sep 18, 2015 at 18:24

Mxx persian

1054 bronze badges

Collectives™ on Stack Overflow

Inserting index array to one column in MySQL

Update

3 Answers 3

5 Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Update

3 Answers 3

5 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related