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.

Refer this link it may help u stackoverflow.com/questions/7818570/… — Bharu
– Bharu, Commented Jul 17, 2013 at 4:55

DevZer0 · Accepted Answer · 2013-07-17 04:56:47Z

4

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();
  }

answered Jul 17, 2013 at 4:56

DevZer0

13.5k7 gold badges29 silver badges54 bronze badges

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

1 Comment

michaelb958--GoFundMonica Over a year ago

+1 for not being vulnerable to SQL injection.

Orangepill · Accepted Answer · 2013-07-17 04:57:09Z

1

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.

answered Jul 17, 2013 at 4:57

Orangepill

24.7k3 gold badges44 silver badges64 bronze badges

Comments

Prix · Accepted Answer · 2013-07-17 14:33:28Z

0

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

edited Jul 17, 2013 at 14:33

answered Jul 17, 2013 at 5:20

Prix

19.6k16 gold badges79 silver badges136 bronze badges

6 Comments

Yugesh M Luv Over a year ago

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 Over a year ago

I tried that but it says... Insert Error Column 'torrent' cannot be null

Prix Over a year ago

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

Yugesh M Luv Over a year ago

thanks a lot.... i enabled null value in my database... and it worked perfectly... :)

Yugesh M Luv Over a year ago

Hey... I have came up across another issue.... this above query is working perfectly with small amount of data... but when i am trying to process bigger amount of data.. its giving me trouble.. it inserts the data till the 2461 rows... after that it stops... i tried deleting that row to see if it have some problematic data... then it stopped after 2462nd row... you have any idea whats wrong...? i tried to change the max execution time and max allowed packet data nothing really worked... the problem remains the same...

|

Vishnu Sureshkumar · Accepted Answer · 2013-07-17 04:58:18Z

0

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]')");
}

answered Jul 17, 2013 at 4:58

Vishnu Sureshkumar

2,3266 gold badges37 silver badges52 bronze badges

Comments

Expedito · Accepted Answer · 2013-07-17 06:49:55Z

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);

Goutam Pal · Accepted Answer · 2013-07-17 04:57:40Z

-1

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

answered Jul 17, 2013 at 4:57

Goutam Pal

1,7531 gold badge10 silver badges14 bronze badges

Collectives™ on Stack Overflow

Inserting values of array into MYSQL

6 Answers 6

1 Comment

Comments

6 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

1 Comment

Comments

6 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related