Php array to column row in mysql table

Question

I have array named olmali

$olmali = $_POST['result'];

and print_r($olmali); Result is below :

Array ( 
    [0] => 1
    [1] => 1
    [2] => 20
    [3] => 2 
    [4] => 3
    [5] => 5
    [6] => 6 
    [7] => 7 
    [8] => 9 
    [9] => 8 
    [10] => 10
    [11] => 11
    [12] => 13
    [13] => 12 
    [14] => 12
    [15] => 14 
    [16] => 15
    [17] => 16
    [18] => 17
    [19] => 17
    [20] => 19
    [21] => 20
)

Result are going to test column in SQL table in phpmyadmin like :

id        test
1         array

But I expect :

id        test
1          1
2          1
3          20
4          2
5          3
6         ....and goes on

How can I resolve this problem ? İs there any way and how can I do it. PHP array to column row in MySQL table like that

Result are going to test column in SQL table - where is your code? — splash58
– splash58, Commented Nov 14, 2018 at 9:05
Do further reading here stackoverflow.com/questions/10054633/… — Kimigx
– Kimigx, Commented Nov 14, 2018 at 9:26

Samir Selia · Accepted Answer · 2018-11-14 09:13:05Z

1

You have to loop through the array and insert one by one.

foreach($olmali as $v)
{
    //insert query goes here
    $sql = "INSERT INTO tbl_name (test) VALUES ('$v')";

   // Then Execute the query 
}

Another approach is to use Bulk Insert,

$sql = "INSERT INTO tbl_name (test) VALUES ";   

foreach($olmali as $v)
{
    //Concatenate values in bulk
    $sql .= "('$v'),";
}

$sql = rtrim($sql, ','); // Remove extra comma at the end
// Then execute query

edited Nov 14, 2018 at 9:13

answered Nov 14, 2018 at 9:06

Samir Selia

7,1502 gold badges14 silver badges33 bronze badges

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

5 Comments

Gobbin Over a year ago

Concatting the query like @suresh is doing is more cost efficient

Samir Selia Over a year ago

Yes, it can be done using bulk insert. Making the OP understand basics first :). Added bulk insert example in my answer too.

Gobbin Over a year ago

Nice. Good answer!

askerman Over a year ago

thanks very much for your solution . Bulk insert is working perfect

Samir Selia Over a year ago

Please elaborate on it.

suresh bambhaniya · Accepted Answer · 2018-11-15 04:08:33Z

0

there are many ways 1:using Bulk insert

$qry = "INSERT INTO tableName(test) VALUES ";
    foreach($olmali as $val){
       $qry .="($val),";
    }

    $qry = preg_replace("/\,$/", ";", $qry);

    $conn->query($sql);

2:using prepare statement preferred way

$stmt =  $db->stmt_init();
$stmt->prepare("INSERT INTO tableName(test) VALUES(?)");
foreach($olmali as $val)
{
    $stmt->bind_param('i',$val);
    $stmt->execute();
}
$stmt->close();

edited Nov 15, 2018 at 4:08

answered Nov 14, 2018 at 9:06

suresh bambhaniya

1,6871 gold badge14 silver badges21 bronze badges

2 Comments

askerman Over a year ago

how can ı do this with sql update command ? not insert command

suresh bambhaniya Over a year ago

$stmt = $db->stmt_init(); $stmt->prepare("UPDATE tableName SET test=? WHERE id=?"); foreach($olmali as $key=>$val) { $stmt->bind_param('ii',$val,$key); $stmt->execute(); }

Collectives™ on Stack Overflow

Php array to column row in mysql table

2 Answers 2

5 Comments

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

2 Comments

Linked

Related