Inserting values of array into MYSQL

Question

I have a text file, who's value i have put into arrays, this is the php code:

<?php
    $homepage = file_get_contents('hourlydump.txt');
    $x = explode('|', $homepage);
    $desc = array();
    $cat = array();
    $link = array();
    $m = 1;
    $n = 2;
    $p = 3;

    for ($i = 1; $i <= count($x) / 4; $i++) {
       $m = $m + 4;
       $desc[] = $x[$m];
       $n = $n + 4;
       $cat[] = $x[$n];
       $p = $p + 4;
       if ($x[$p])
          $link[] = $x[$p];
    }
    echo "<pre>";
    print_r($desc);
    print_r($cat);
    print_r($link);
?>

output is like:

Array
(
    [0] => Kamal Heer - Facebook Official Video 720p Dual Audio [Hindi + Punjabi]76 mb by rANA.mkv
    [1] => 50 HD Game Wallpapers Pack- 1
)
Array
(
    [0] => Movies
    [1] => Other
)
Array
(
    [0] => http://kickass.to/kamal-heer-facebook-official-video-720p-dual-audio-hindi-punjabi-76-mb-by-rana-mkv-t7613070.html
    [1] => http://kickass.to/50-hd-game-wallpapers-pack-1-t7613071.html
)
//
//
//

anyone please help me i dont know how to insert the values of these three arrays $desc, $cat and $link into mysql table, columns named description, category, link

i know simple insert queries but dont how to deal with these arrays.

Answer 1

Here is a simple sample to read your file as is from the website you retrieve it as well as inserting it to the database sanitizing the data:

<?php
// fill with your data
$db_host = 'localhost';
$db_user = '';
$db_pass = '';
$db_name = '';
$db_table = 'myTable';

$file = "hourlydump.txt.gz";
if($filehandle = gzopen($file, "r"))
{
    $content = gzread($filehandle, filesize($file));
    gzclose($file);
}
else
    die('Could not read the file: ' . $file);

$con = mysqli_connect($db_host,$db_user,$db_pass,$db_name);
if($con->connect_error)
    die('Connect Error (' . mysqli_connect_errno() . ') '. mysqli_connect_error());

$sql = "INSERT INTO $db_table (description, category, link) VALUES (?, ?, ?)";
if (!$insert = $con->prepare($sql))
    die('Query failed: (' . $con->errno . ') ' . $con->error);

foreach (explode("\n", $content) as $line)
{
    list($md5hash,$desc,$cat,$link,$torrent) = explode("|", $line);
    if (!$insert->bind_param('sss',$desc,$cat,$link))
        echo 'Binding parameters failed: (', $insert->errno, ') ', $insert->error;

    if (!$insert->execute())
        echo 'Insert Error ', $insert->error;
}

$insert->close();
$con->close();

NOTE: you may want to check if the file was loaded with success, if the fields from the explode exist or not to prevent further problems but in general this should work just fine.

Also you may want to change the $sql to reflect your MySQL table aswell as the $db_table at the top.

UPDATE: to insert all values change this:

$sql = "INSERT INTO $db_table (description, category, link) VALUES (?, ?, ?)";

To:

$sql = "INSERT INTO $db_table (md5, description, category, link, torrent) VALUES (?, ?, ?, ? ,?)";

And this:

if (!$insert->bind_param('sss',$desc,$cat,$link))

To:

if (!$insert->bind_param('sssss',$md5hash,$desc,$cat,$link,$torrent))

Note above the s for each item you need a s you have 5 items so 5 s's the S means string, D double, I integer, B blob you can read more at about it here.

Also note the $sql for each item we will use on the bind_param we have a ?.

Answer 2

You can build your query while you're doing you calculations:

$query = "INSERT INTO `table` (`description`, `category`, `link`) VALUES ";
for ($i = 1; $i <= count($x) / 4; $i++) {
   $m = $m + 4;
   $query .= "('".$x[$m];
   $n = $n + 4;
   $query .= "','".$x[$n];
   $p = $p + 4;
   if ($x[$p]) $query .= "','".$x[$p]."'),";
   else $query .= "',NULL),";
}
$query = substr($query, 0, -1);//get rid of last comma
mysqli_query($query);

You can also build the arrays along with the query if you need to:

$query = "INSERT INTO `table` (`description`, `category`, `link`) VALUES ";
for ($i = 1; $i <= count($x) / 4; $i++) {
   $m = $m + 4;
   $desc[] = $x[$m];
   $query .= "('".$x[$m];
   $n = $n + 4;
   $cat[] = $x[$n];
   $query .= "','".$x[$n];
   $p = $p + 4;
   if ($x[$p]){
       $link[] = $x[$n];
       $query .= "','".$x[$p]."'),";
   } else {
       $link[] = $x[$n];
       else $query .= "',NULL),";
}
$query = substr($query, 0, -1);//get rid of last comma
mysqli_query($query);

Answer 3

Try this. I am assuming that only these much of values are there for insertion

for($i = 0;$i<2;$++) {
mysqli_query("INSER INTO tablename values(description,category,link) VALUES('$desc[$i]'
,'$cat[$i]','$link[$i]')");
}

Answer 4

make the array to a string

$description = json_encode($desc);
$category = json_encode($cat);
$link = json_encode($link);

then insert these values to database

At the time of fetching

Use json_decode to get the array again from the string

Answer 5

You can use a for statement.

 for($x =0, $num = count($desc); $x < $num; $x++){
     // build you query 
     $sql = "INSERT into your_table (description, category, link) values ".
            "(".$db->quote($desc[$x]).",".$db->quote($cat[$x]).",".
                $db->quote($link[$x].")";
     $db->query($sql);
 }

Of course you will have to use the sanitation/quoting methods appropriate for your chosen database api.

Answer 6

I will give you an example of how basic database connection is made and the insert is completed, this is for illustrative purpose only. You should reorganize this code inside a class so that every insert statement doesn't create a PDO object but re-use the object created before.

 function insertItem($desc, $cat, $link) {
    $dbh = new PDO("mysql:host=host;dbname=db", $user, $pass);
    $sql = "INSERT INTO table (description, category, link) VALUES (:desc, :cat, :link)";

    $sth = $dbh->prepare($sql);
    $sth->bindValue(":desc", $desc);
    $sth->bindValue(":cat", $cat);        
    $sth->bindValue(":link", $link);
    $sth->execute();
  }