how to write values of an array to mysql table inside for each loop?

Question

i have recived some data via post method i wrote the data to array now how i can write da data to mysql table ? sample of data recive is shown below. i keep getting this error:Error, query failed

if (isset($_POST['submit'])) {
    $data = [];
    foreach($_POST['checkbox'] as $rowNum) {
        $data[] = explode("::", $_POST['opt'][$rowNum]);
    }
    var_dump($data);

    $sku =$data[$rowNum][0];
    $description =$data[$rowNum][1];
    $location =$data[$rowNum][2];
    $quantitydate =$data[$rowNum][3];

    $link = mysqli_connect("somesite", "****", "*******", "******");
    // Check connection
    if($link === false){
        die("ERROR: Could not connect. " . mysqli_connect_error());
    }

    $query ="SELECT * FROM Test WHERE sku = '$sku'";
    $testResult = mysqli_query($link, $query) or die('Error, query failed');    

    if(mysqli_fetch_array($testResult) == NULL){
        $sql = "INSERT INTO test (ID, sku, description, location, quantitydate) VALUES ('$ID','$sku', '$description','$location', '$quantitydate',NOW())";
        if(mysqli_query($link, $sql)){
            echo "Records added successfully.<br /><br />";
        } else{
            echo "ERROR: Could not able to execute $sql. " . mysqli_error($link);
        }

        // close connection
        mysqli_close($link);
    }else
        {
            echo "Record Already Exist<br /><br />";
        }
}

output sample:

// Output sample (selected row 2 and 4):
array (size=2)
  0 => 
    array (size=4)
      0 => string 'SKU2' (length=4)
      1 => string 'DESC2' (length=5)
      2 => string 'LOC2' (length=4)
      3 => string 'QUAN2' (length=5)
  1 => 
    array (size=4)
      0 => string 'SKU4' (length=4)
      1 => string 'DESC4' (length=5)
      2 => string 'LOC4' (length=4)
      3 => string 'QUAN4' (length=5)

What's the problem? Loop over $data and do an INSERT INTO TABLE query with the values from each row. — Barmar
– Barmar, Commented Sep 18, 2015 at 0:43
i edit my post i tried the above but keep getting this error:Error, query failed — user1788736
– user1788736, Commented Sep 18, 2015 at 1:04
Change die('Error, query failed') to die(mysqli_error($link)) so you see the reason for the failure. — Barmar
– Barmar, Commented Sep 18, 2015 at 1:07
Why are you passing both $quantitydate and NOW() in the INSERT query? You have more values than columns you're assigning to. — Barmar
– Barmar, Commented Sep 18, 2015 at 1:12
Where is the $ID variable? Is that supposed to be an auto-increment column? You can just leave it out of the INSERT and it will be incremented automatically. — Barmar
– Barmar, Commented Sep 18, 2015 at 1:15

Barmar · Accepted Answer · 2015-09-18 01:39:23Z

2

Use a prepared statement:

$stmt = mysqli_prepare($link, "INSERT IGNORE INTO tableName (sku, description, location, quantitydate) VALUES (?, ?, ?, ?)";
mysqli_stmt_bind_param($stmt, "ssss", $sku, $desc, $location, $quantitydate);
foreach ($_POST['checkbox' as $rowNum) {
    $row = explode('::', $_POST['opt'][$rowNum]);
    $sku = $row[0];
    $description = $row[1];
    $location = $row[2];
    $quantitydate = $row[3];
    mysqli_stmt_execute($stmt);
    if (mysqli_affected_rows($stmt) == 0) {
        echo "SKU $sku already exists <br/><br/>";
    }
}

If you have a unique index on the sku column, you don't need to perform the SELECT query first. INSERT IGNORE will simply ignore the attempt to add a duplicate row, and mysqli_affected_rows will return 0 to indicate that nothing was inserted.

edited Sep 18, 2015 at 1:39

answered Sep 18, 2015 at 0:46

Barmar

789k57 gold badges554 silver badges669 bronze badges

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

1 Comment

user1788736 Over a year ago

thanks for reply. i have edited my post . i am not familiar with PDO. could you tell me what part is wrong in my above code?

Collectives™ on Stack Overflow

how to write values of an array to mysql table inside for each loop?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related