Explore our sites

Stack Exchange
tour help careers 2.0
Take the tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

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.

share|improve this question
 
Refer this link it may help u stackoverflow.com/questions/7818570/… –  Bharu Jul 17 at 4:55
add comment

6 Answers

up vote 0 down vote accepted

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 ?.

share|improve this answer
 
Thanks a lot... worked perfectly... without any errors :) –  Yugesh M Luv Jul 17 at 8:41
 
one more thing... this code is working fine... for inserting those three values.... but when i am trying to insert all five values... it is showing error as Query failed: (1136) Column count doesn't match value count at row 1 Any ideas...? –  Yugesh M Luv Jul 17 at 9:20
 
@YugeshMLuv see update to insert all the 5. –  Prix Jul 17 at 14:33
 
I tried that but it says... Insert Error Column 'torrent' cannot be null –  Yugesh M Luv Jul 17 at 15:49
 
@YugeshMLuv that means that row on your file does not have a value for torrent. it does not stop inserting the other results it will just skip to the next one if a field does not exist. After the line list($md5hash,$desc,$cat,$link,$torrent) = explode("|", $line); add this echo $desc, " - ", $torrent, "\n"; and you will know where it had no $torrent value and will as well see the progress. –  Prix Jul 17 at 16:00
show 3 more comments
up vote 4 down vote

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();
  }
share|improve this answer
 
Now that is how it's done. –  Orangepill Jul 17 at 4:58
 
+1 for not being vulnerable to SQL injection. –  michaelb958 Jul 17 at 4:59
add comment
up vote 1 down vote

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.

share|improve this answer
add comment
up vote 0 down vote

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]')");
}
share|improve this answer
add comment
up vote 0 down vote

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);
share|improve this answer
add comment
up vote -1 down vote

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.