43250

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.

Answer1:

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 <a href="http://php.net/manual/en/mysqli-stmt.bind-param.php" rel="nofollow">you can read more at about it here.</a>

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

Answer2:

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

Answer3:

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.

Answer4:

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

Answer5:

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

Answer6:

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

Recommend

  • how to chain functions with Jquery
  • How to optionally add a comma and whitespace to a capture group?
  • Mailto link open in new tab only if web mail client
  • anglarjs directive link function not called from compilefunction [closed]
  • update record in database using jdatabase
  • Scrolling News Ticker Jquery - Issues
  • Using extern @class in order to add a category?
  • “A GKScore must specify a leaderboard.”
  • Many to Many in Linq using Dapper
  • How can I prevent the need to copy strings passed to a avr-gcc C++ constructor?
  • What does “t” refer to in this SQL?
  • Primefaces :radioButton inside a ui:repeat
  • CERN ROOT exporting data to plain text
  • R convert summary result (statistics with all dataframe columns) into dataframe
  • MYSQ & MVC3 SQL connection error \\ ProviderManifestToken but I am using MySQL
  • Combining two different ActiveRecord collections into one
  • Reading a file into a multidimensional array
  • Android application: how to use the camera and grab the image bytes?
  • Breaking out column by groups in Pandas
  • Unable to get column index with table.getColumn method using custom table Model
  • Django simple Captcha “No module named fields” error
  • How can the INSERT … ON CONFLICT (id) DO UPDATE… syntax be used with a sequence ID?
  • QLPreviewController hide print button in ios6
  • Moving mysql files across servers
  • Why does access(2) check for real and not effective UID?
  • How to clear text inside text field when radio button is select
  • Scrapy recursive link crawler
  • Uncaught Error: Could not find module `ember-load-initializers`
  • Submit form in a displaytag pagination
  • How to check if every primary key value is being referenced as foreign key in another table
  • Google cloud sdk not working when python points python3
  • Delete MySQLi record without showing the id in the URL
  • Unanticipated behavior
  • Comma separated Values
  • InvalidAuthenticityToken between subdomains when logging in with Rails app
  • SQL merge duplicate rows and join values that are different
  • KeystoneJS: Relationships in Admin UI not updating
  • Trying to get generic when generic is not available
  • LevelDB C iterator
  • Can't mass-assign protected attributes when import data from csv file