i want to insert an array into a mysql table. The array is produced by script scanning all the links, converting into absolute links and then displaying them in an array. i decided to mysql_query the array into the table but now i am stuck. it only posts 'Array', instead of every row from the array into a different row. Any ideas??!
<?php
require_once('simplehtmldom_1_5/simple_html_dom.php');
require_once('url_to_absolute/url_to_absolute.php');
$connect = mysql_connect("xxxx", "xxxx", "xxx") or die('Couldn\'t connect to MySQL Server: ' . mysql_error());
mysql_select_db("xxxx", $connect ) or die('Couldn\'t Select the database: ' . mysql_error( $connect ));
$links = Array();
$URL = 'http://www.theqlick.com'; // change it for urls to grab
// grabs the urls from URL
$file = file_get_html($URL);
foreach ($file->find('a') as $theelement) {
$links[] = url_to_absolute($URL, $theelement->href);
}
print_r($links);
mysql_query("INSERT INTO pages (url) VALUES ('$links[]')");
mysql_close($connect);
Convert array to string? It's the best option I think..
$ids = join(',',$links);
$sql = "INSERT INTO pages (url) VALUES ('$ids')";
You should look at serialize()
$serialized = serialize( $yourArray );
// insert _$serialized_ into the DB
// and when retrieving it - unserialize()
$unserialized = unserialize( $retrievedValue );
You could represent the array in JSON format and insert that into your database. But really, a good database design should make the insertion of an array unnecessary. Much better to have a separate column for each key in the array and just store the values instead of the actual array structure.
You need to iterate over the array and make an INSERT query for each element in turn.
You might want to implode()
with a separator that doesn't appear in the data (non-printable characters are usually a good bet, but sometimes various symbols work). Alternatively, you can let PHP serialize()
it for you, or even save it in json_encode
d format.
If you are actually trying to insert multiple rows with one $links
entry each, you should do:
mysql_query("INSERT INTO pages (url) VALUES ('".implode("'),('",$links)."')");
Just make sure all the links are properly sanitised first!
if you want to store into the db a link each row, you should put the mysql_query("INSERT INTO pages (url) VALUES ('$link')"); inside the foreach cycle.
Otherwise use the implode method:
If you are only trying to insert one column try this. This prevents need to loop by building a proper VALUES
string.
$value_string = "'" . implode("','", $links) . "'"; // makes string like 'url1','url2','url3'
mysql_query("INSERT INTO pages (url) VALUES ($value_string)");