$dbh = new PDO で接続、stmt->executeでsql文を実行
$stmt = $dbh->prepare(“insert into rss (title,link,site_title,site_link,date) values(?,?,?,?,?)”);
$stmt->execute(array($title,$link,$site_title,$site_link,$date));
reference
FETCH_ASSOC:Fetch a result row as an associative array
create table rss(
id int not null auto_increment primary key,
title varchar(255),
link varchar(255),
site_title varchar(64),
site_link varchar(64),
date datetime
);
<?php
try {
$dbh = new PDO('mysql:host=localhost;dbname=test','dbuser','xxxx');
} catch(PDOException $e){
var_dump($e->getMessage());
exit;
}
$num = 5;//RSS取得件数
$rssUrl=array(
'http://x6xo.hatenablog.com/rss',//サイトURL
);
//magpierss
require_once('./magpierss-master/rss_fetch.inc');
define('MAGPIE_OUTPUT_ENCODING', 'UTF-8');//encode
define('MAGPIE_CACHE_AGE','30');//cache
foreach($rssUrl as $no => $rss_url){
if($rss_url != ''){
//urlからRSSを取得
$rss = @fetch_rss($rss_url);
if($rss != NULL){
for ($i=0; $i<count($rss->items); $i++){
$rss->items[$i]["site_title"] = $rss->channel["title"];
$rss->items[$i]["site_link"] = $rss->channel["link"];
}
// itemsを格納
$rssItemsArray[] = $rss->items;
}
}
}
$concatArray = array();
if (is_array($rssItemsArray)) {
for($i=0;$i<count($rssItemsArray);$i++){
$concatArray = array_merge($concatArray,$rssItemsArray[$i]);//配列を統合する
}
foreach ($concatArray as $no => $values) {
//RSSの種類によって日付を取得
if($values['published']){$date = $values['published'];}
elseif($values['created']){$date = $values['created'];}
elseif($values['pubdate']){$date = $values['pubdate'];}
elseif($values['dc']['date']){$date = $values['dc']['date'];}
$date=date("Y-m-d H:i:s",strtotime($date));
//Filter
$nowtime = date("Y-m-d H:i:s",strtotime( "now" ));//現在時刻の取得
if($date > $nowtime){//未来記事の排除
}elseif(preg_match("/AD/", $values["title"])){//広告記事の排除
}elseif(preg_match("/PR/", $values["title"])){
}else{
//値の定義
$title=$values["title"];
$link=$values["link"];
$site_title=$values["site_title"];
$site_link=$values["site_link"];
//記事ごとに必要な項目を抽出
$rssArray[]=array($date, $title, $link, $site_title, $site_link);
}//
}//
//ソート
function cmp($a, $b) {
if ($a[0] == $b[0]) return 0;
return ($a[0] > $b[0]) ? -1 : 1;
}
if($rssArray) { usort($rssArray, 'cmp'); }
if(count($rssArray) > $num){$count=$num;}
else{$count=count($rssArray);}
for ($i=0; $i<$count; $i++) {
$date=date("Y-m-d H:i:s",strtotime($rssArray[$i][0]));
$title=$rssArray[$i][1];
$link=$rssArray[$i][2];
$site_title=$rssArray[$i][3];
$site_link=$rssArray[$i][4];
$datelink = "<div>$date";
$titlelink = "<a href='$link'>$title</a>";
$site_titlelink = "<a href='$site_link'>[$site_title]</a></div>";
echo "$datelink$titlelink$site_titlelink</div>";//(確認用)
$stmt = $dbh->prepare("insert into rss (title,link,site_title,site_link,date) values(?,?,?,?,?)");
$stmt->execute(array($title,$link,$site_title,$site_link,$date));
}
}
?>
$stmt->fetchAll(PDO::FETCH_ASSOC) as $dataで、mysqlのデータを表示
<?php
// 接続
try {
$dbh = new PDO('mysql:host=localhost;dbname=test','dbuser','xxxx');
} catch(PDOException $e){
var_dump($e->getMessage());
exit;
}
?>
<html>
<meta http-equiv="Content-Type" content="text/html;charset=utf-8">
<title>rss</title>
<body>
<?php
date_default_timezone_set('Asia/Tokyo');
$sql = "select * from rss order by date desc limit 10";
echo '<table>';
$stmt = $dbh->query($sql);
foreach($stmt->fetchAll(PDO::FETCH_ASSOC) as $data){
$date = date("m/d H:i",strtotime($data['date']));
$title = ($data['title']);
$link = ($data['link']);
$site_link = ($data['site_link']);
$site_title = ($data['site_title']);
echo "<tr><td>$date</td><td><a href='$link'>$title</a></td><td><a href='$site_link'>[$site_title]</a></td></tr>";
}
echo "</table>";
// 切断
$dbh = null;
?>
