Inserting data from array to mysql

Question

I'm having issues inserting array values into mysql. I have an array $list which holds 30 items. I'd like to insert each value in the array to a new row in the column 'title' in my database.

I've checked the php manual and it seems array_walk would be the best choice here. Here is the code which creates the array and tries to add it to the database (at the moment it creates 30 empty rows). The array does contain the 30 items.

  $nodelist = $xpath->query("//span[@class='mp-listing-title']");

  $list = array();

  $i = 0;

   foreach ($nodelist as $n) {
    if ($i >=5 && $i <=35) {
      $value = $n->nodeValue;
      $list[] = $value;
     }
    $i++;
   }

    function sql() {

     global $table_id;
     global $array_walk;

      $sql = "INSERT INTO $table_id (title) VALUES ('$array_walk')";
      }

   $array_walk = array_walk($list, 'sql' );

To my understanding the $array_walk variable should apply function sql() to each value in $list but it is not writing anything to the mysql database. Can anyone shed some light on what I'm doing wrong? Any help is much appreciated!

Answer 1

Try this code:

$nodelist = $xpath->query("//span[@class='mp-listing-title']");
$list = array();
$i = 0;
foreach ($nodelist as $n) {
    if ($i >=5 && $i <=35) {
        $value = $n->nodeValue;
        $list[] = $value;
    }
    $i++;
}

$db = mysqli_connect(<your db details here>);

foreach ($list as $item) {
    mysqli_query($db, "INSERT INTO $table_id (title) VALUES ('$item')");

EDIT 1. You must execute query
EDIT 2. Use foreach for listing array
EDIT 3. Use mysqli_connect to connect database

UPDATE 2018.06

$nodelist = $xpath->query("//span[@class='mp-listing-title']");
$list = array();

foreach ($nodelist as $n) {
    for ($i=5;$i<=35;$i++) {
        $value = $n->nodeValue;
        $list[] = $value;
    }
}

$db = mysqli_connect(<your db details here>);

$values = "";
foreach ($list as $item) {
    $values .= ", (values here)";
$values = substr($values, 1);

mysqli_query($db, "INSERT INTO table_name (field_names) VALUES {$values}");

Answer 2

There are many recommendations and refinements to offer on this question.

• Prepare your scraped data -- I will leave it to you to refine your xpath query so that only the desired values are processed. Generally speaking it is less clean to perform iterated conditionals to remove unwanted values.
• Avoid declaring global variables as much as possible (as a matter of best practice with security and cleanliness). Instead, pass all necessary variables as arguments ($titles in this case).
• Because your title data is sourced externally (and shouldn't be trusted as secure), it is essential that you implement mysqli's "prepared statements" (or PDO if you are so inclined) with placeholders and parameter binding. There are three valid ways (that I can think of) to do this. I will recommend and write two methods and only reference a third.
• I have built into my functions a full battery of error checks throughout the process. Bear in mind, that you must never display any raw error messages to users -- only give the generalized/vague error messages at most.

If you are unfamiliar with the functions or techniques displayed below, I strongly urge you to spend the time to research mysqli's prepared statements, binding, and execution via the PHP manual.

If you have specific questions after researching, please leave a comment and I'll lend you a hand.

Method #1:

function shortPreparedInsert($titles){
    return "INSERT INTO Titles_TableName (title) VALUES (?)"; // for demo only
    if(sizeof($titles)!=30){
        return "Query aborted: Titles data not = 30";
    }elseif(!$conn=new mysqli($server,$user,$password,$database)){  // apply your credentials here
        return "Connection Error: ".$conn->connect_error;  // do not echo error info when live/public
    }elseif($stmt=$conn->prepare("INSERT INTO `Titles_TableName` (`title`) VALUES (?)")){
        $stmt->bind_param("s",$title);
        foreach($titles as $title){
            if(!$stmt->execute()){  // this will execute 30 times using each title
                return "Execution Error on $title: ".$stmt->error;  // do not echo error info when live/public
            }
        }
        return "Titles created successfully";
    }else{
        echo "Prepare Error: ",$conn->error;  // do not echo when public
    }
}

Method #2:

function longPreparedInsert($titles){
    return "INSERT INTO Titles_TableName (title) VALUES ".implode(',',array_fill(0,sizeof($titles),'(?)')); // for demo only
    if(sizeof($titles)!=30){
        return "Query aborted: Titles data not = 30";
    }elseif(!$conn=new mysqli($server,$user,$password,$database)){  // apply your credentials here
        return "Connection Error: ".$conn->connect_error;  // do not echo error info when live/public
    }elseif($stmt=$conn->prepare("INSERT INTO Titles_TableName (title) VALUES ".implode(',',array_fill(0,sizeof($titles),'(?)')))){
        if($stmt->bind_param('ssssssssssssssssssssssssssssss',
                    $titles[0],$titles[1],$titles[2],$titles[3],$titles[4],
                    $titles[5],$titles[6],$titles[7],$titles[8],$titles[9],
                    $titles[10],$titles[11],$titles[12],$titles[13],$titles[14],
                    $titles[15],$titles[16],$titles[17],$titles[18],$titles[19],
                    $titles[20],$titles[21],$titles[22],$titles[23],$titles[24],
                    $titles[25],$titles[26],$titles[27],$titles[28],$titles[29]) && $stmt->execute()){
            return "Titles created successfully";
        }else{
            return "Query Failed, Syntax Error: ".$stmt->error;  // do not echo error info when live/public
        }
    }else{
        echo "Prepare Error: ",$conn->error;  // do not echo when public
    }
}

A less modern, but still valid third method using call_user_func_array(): Bind Param with array of parameters

Here is some displayed data for demonstrative purposes: (Demo Link)

echo shortPreparedInsert(array_column(array_slice($titlehit,5,30),'nodeValue')); // array_slice will re-index the keys since no 4th parameter
echo "\n\n";
echo longPreparedInsert(array_column(array_slice($titlehit,5,30),'nodeValue')); // array_slice will re-index the keys since no 4th parameter
echo "\n\n";
var_export(array_column(array_slice($titlehit,5,30),'nodeValue'));

Output:

INSERT INTO Titles_TableName (title) VALUES (?)

INSERT INTO Titles_TableName (title) VALUES (?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?),(?)

array (
  0 => 'Gitaarversterker kopen? Ontdek 18 Gitaarwinkels in Benelux',
  1 => 'Gibson Les Paul ** Nr.1 Gibson dealer ** 18 gitaarwinkels',
  2 => 'Gitaarstandaard Gitaarstandaards Gitaarstatief Aanbieding!',
  3 => 'Gravity GS01WMB Gitaar muurbeugel',
  4 => 'Gitaaronderdelenshop.nl - Betaalbare gitaaronderdelen',
  5 => 'Hartke basgitaar + versterker',...

---

EDIT:

I would like to point out that one line of Method #2 can be "smarten-ed up" a little with str_repeat(). Furthermore, if you are using php 5.6+, you can use a "splat" operator (...) on the second parameter of bind_param().
Reference: https://stackoverflow.com/a/23641033/2943403

if($stmt->bind_param(str_repeat('s',sizeof($titles)), ...$titles) && $stmt->execute()){