DELIMITER //

CREATE PROCEDURE proc(IN s1 VARCHAR(32), IN s2 VARCHAR(32), OUT o INT)
BEGIN
  DECLARE a INT;

  SELECT COUNT(*) INTO a FROM TBL WHERE COL1=s1 AND COL2=s2;

  IF a = 0 THEN
    SET o = 0;
  ELSE
    SET o = 1;
  END IF;
END
//

DELIMITER ;

CALL proc('SUZUKI','ICHIRO',@a)

SELECT @a

余談だが、副問い合わせをするとものすごく遅いのは、過去のことなのか？未だにそうなのか？

Permalink | 記事への反応(1) | 09:19

2015-08-25

■ITに関係ないサラリーマンがコミュニティ サイト作っちゃいました。

私ごとですが、この度コミュニティサイトを作成いたしました。

私の持てる知識を絞り出してなんとか完成しました。

**************************************************************************************

完成したサイトがこちらです。

LapyNetニュース

http://lapynetz.net/

**************************************************************************************

私は普段webとは関係ない仕事してます。なんとか休日を利用して、

webサービス作成しました。

メインのコードはこんな感じ

----------------------------------------------------------------------------------------------------------------------



App::uses('Sanitize', 'Utility');
class RssfeedsController extends AppController {

public $helpers = array('Html','Form','Session','Number','Cache');

public $uses = array(
'Rssfeed',
'Tweet',
'Wadai',
'Rank',
'Oracle'
);
 **************************************************************************************
public function index() {

$title_for_layout = "LapyNetニュース";
$content = "Hello World!!";

$this-&gt;set(compact('title_for_layout','content'));
if(date("G")&gt;0 and date("G") &lt; 7){
$today = date("Y-m-d", strtotime('-1 day'));
}else{
$today = date("Y-m-d");
}

$params = array(
'conditions' = &gt; array(
'and' = &gt; array(
'Rssfeed.time' = &gt; $today,
)),
'order' = &gt; 'tweets desc',
'limit' = &gt; '50'
);
$this- &gt;set('posts',$this- &gt;Rssfeed- &gt;find('all',$params));

$paramz = array(
'order' = &gt; 'Rssfeed.id desc',
'limit' = &gt; '10'
);
$this-&gt;set('posts2',$this-&gt;Rssfeed-&gt;find('all',$paramz));

$paramz = $this- &gt;Rssfeed- &gt;query('SELECT id,title FROM rssfeeds order by id desc limit 10');

$this-&gt;set('posts2', $paramz);

$parapara = $this- &gt;Oracle- &gt;query('SELECT id,title FROM oracles order by id desc limit 10');

$this- &gt;set('postx', $parapara);

$funking = $this- &gt;Rank- &gt;query('SELECT id,frank FROM ranks where creation=(select max(creation) from ranks)');

$this- &gt;set('funking', $funking);


}

 **************************************************************************************
public function mizuno() {
$parapara = array(
'order' =&gt; 'Oracle.id desc',
'limit' =&gt; '30'
);
$recent_posts = $this-&gt;Oracle-&gt;find('all',$parapara);
return $recent_posts;

}


 **************************************************************************************
public function wadai() {
if(date("G")&gt;0 and date("G") &lt; 7){
$today = date("Y-m-d", strtotime('-1 day'));
}else{
$today = date("Y-m-d");
}

$params = array(
'conditions' =&gt; array(
'and' =&gt; array(
'Rssfeed.time' =&gt; $today,
'Rssfeed.kubetu' =&gt; 4
)),
'order' =&gt; 'tweets desc',
'limit' =&gt; '100'
);
$this-&gt;set('posts',$this-&gt;Rssfeed-&gt;find('all',$params));





$this-&gt;set("title_for_layout","2CHニュース-LapyNetニュース");


$paramz = array(
'order' =&gt; 'Rssfeed.id desc',
'limit' =&gt; '10'
);
$this-&gt;set('postyy',$this-&gt;Rssfeed-&gt;find('all',$paramz));

$parapara = array(
'order' =&gt; 'Oracle.id desc',
'limit' =&gt; '10'
);


$this-&gt;set('postyx',$this-&gt;Oracle-&gt;find('all',$parapara));

$funking = $this-&gt;Rank-&gt;query('SELECT * FROM ranks where creation=(select max(creation) from ranks)');

$this-&gt;set('funking', $funking);



}
 **************************************************************************************
public function hot() {
if(date("G")&gt;0 and date("G") &lt; 7){
$today = date("Y-m-d", strtotime('-1 day'));
}else{
$today = date("Y-m-d");
}

$params = array(
'conditions' =&gt; array(
'and' =&gt; array(
'Rssfeed.time' =&gt; $today,
'Rssfeed.kubetu' =&gt; 3
)),
'order' =&gt; 'tweets desc',
'limit' =&gt; '20'
);
$title_for_layout = "LapyNetニュース";
$this-&gt;set("title_for_layout","ニュース-LapyNetニュース");
$this-&gt;set('posts',$this-&gt;Rssfeed-&gt;find('all',$params));

$parapara = array(
'order' =&gt; 'Oracle.id desc',
'limit' =&gt; '10'
);
$this-&gt;set('postxx',$this-&gt;Oracle-&gt;find('all',$parapara));

$paramz = array(
'order' =&gt; 'Rssfeed.id desc',
'limit' =&gt; '10'
);
$this-&gt;set('postsxxx',$this-&gt;Rssfeed-&gt;find('all',$paramz));

$funking = $this-&gt;Rank-&gt;query('SELECT * FROM ranks where creation=(select max(creation) from ranks)');

$this-&gt;set('funking', $funking);


}

 **************************************************************************************
public function newnew() {
if(date("G")&gt;0 and date("G") &lt; 7){
$today = date("Y-m-d", strtotime('-1 day'));
}else{
$today = date("Y-m-d");
}

$params = array(
'conditions' =&gt; array(
'and' =&gt; array(
'Rssfeed.time' =&gt; $today,
'Rssfeed.kubetu' =&gt; 2
)),
'order' =&gt; 'tweets desc',
'limit' =&gt; '20'
);
$this-&gt;set("title_for_layout","エンタメニュース-LapyNetニュース");
$this-&gt;set('posts',$this-&gt;Rssfeed-&gt;find('all',$params));

$paramz = array(
'order' =&gt; 'Rssfeed.id desc',
'limit' =&gt; '10'
);
$this-&gt;set('postszz',$this-&gt;Rssfeed-&gt;find('all',$paramz));

$paramz = $this-&gt;Oracle-&gt;query('SELECT id,title FROM rssfeeds order by id desc limit 10');

$this-&gt;set('postszz', $paramz);

$parapara = $this-&gt;Oracle-&gt;query('SELECT id,title FROM oracles order by id desc limit 10');

$this-&gt;set('postzy', $parapara);


$funking = $this-&gt;Rank-&gt;query('SELECT * FROM ranks where creation=(select max(creation) from ranks)');

$this-&gt;set('funking', $funking);



}
 **************************************************************************************
public function top() {
if(date("G")&gt;0 and date("G") &lt; 7){
$today = date("Y-m-d", strtotime('-1 day'));
}else{
$today = date("Y-m-d");
}
$params = array(
'conditions' =&gt; array(
'and' =&gt; array(
'Rssfeed.time' =&gt; $today,
'Rssfeed.kubetu' =&gt; 1
)),
'order' =&gt; 'tweets desc',
'limit' =&gt; '20'
);
$this-&gt;set('posts',$this-&gt;Rssfeed-&gt;find('all',$params));

$param = array(
'conditions' =&gt; array('times &gt; current_timestamp + interval -600 minute and Kubetu = 4'),
'order' =&gt; 'tweets desc',
'limit' =&gt; '30'
);
$this-&gt;set('posts2',$this-&gt;Rssfeed-&gt;find('all',$param));
}


 **************************************************************************************
public function view($id = null ) {
$this-&gt;Rssfeed-&gt;id = $id; //驥崎ｦ√↑荳譁・ｉ縺励＞
$user = AuthComponent::user();

$TwimStatus = ClassRegistry::init('Twim.TwimStatus');


$TwimStatus-&gt;getDataSource()-&gt;setToken(AuthComponent::user());

$statuses = $TwimStatus-&gt;tweet();
$rairai = $this-&gt;Rssfeed-&gt;read(title,$id);
$alcol = $this-&gt;Rssfeed-&gt;read();
$this-&gt;set('post', $alcol);

$this-&gt;set('post2', $user);
$this-&gt;set('post3', $statuses);
$this-&gt;set("title_for_layout",$rairai[Rssfeed][title]."-LapyNetニュース");

$paramz = array(
'order' =&gt; 'Rssfeed.id desc',
'limit' =&gt; '20'
);
$this-&gt;set('postszz',$this-&gt;Rssfeed-&gt;find('all',$paramz));

$parapara = array(
'order' =&gt; 'Oracle.id desc',
'limit' =&gt; '30'
);
$this-&gt;set('postzy',$this-&gt;Oracle-&gt;find('all',$parapara));

}
 **************************************************************************************
public function add() {

if($this-&gt;request-&gt;isPost()){
if($this-&gt;Tweet-&gt;save($this-&gt;data)) {
$this-&gt;Session-&gt;setFlash('seikou');
$this-&gt;redirect(array('controller'=&gt;'rssfeeds','action'=&gt;'view',$contents11));

}else{
$this-&gt;Session-&gt;setFlash('sipai');
$this-&gt;redirect(array('controller'=&gt;'rssfeeds','action'=&gt;'view',$contents11));
}
}
}
 **************************************************************************************
public function delete($id = null) {
$this-&gt;Rssfeed-&gt;id = $id; 
$this-&gt;set('set', $this-&gt;Rssfeed-&gt;read());
$this-&gt;redirect(array('controller'=&gt;'tweets','action'=&gt;'delete',$this-&gt;data['tweet']['rssfeed_id']));


}
 **************************************************************************************
public function oracle() {
if($this-&gt;request-&gt;is('post')) {

$user = AuthComponent::user();

$TwimStatus = ClassRegistry::init('Twim.TwimStatus');


$TwimStatus-&gt;getDataSource()-&gt;setToken(AuthComponent::user());

$contents1 = $this-&gt;request-&gt;data['Rssfeed']['contents'];
$contents2 = $this-&gt;request-&gt;data;
$statuses = $TwimStatus-&gt;tweet($contents1);


if($this-&gt;Rssfeed-&gt;save($contents2)) {
$this-&gt;Session-&gt;setFlash('成功です。');
$this-&gt;redirect(array('controller'=&gt;'rssfeeds','action'=&gt;'view',$contents11));
$this-&gt;redirect($this-&gt;referer());
}else{
$this-&gt;redirect(array('controller'=&gt;'rssfeeds','action'=&gt;'view',$contents11));
$this-&gt;Session-&gt;setFlash('失敗です。');
}
}


}
 **************************************************************************************
public function makelist() {
$rssfeeds = $this-&gt;paginate();
if ($this-&gt;request-&gt;is('requested')) {
return $rssfeeds;
} else {
$this-&gt;set('rssfeeds', $rssfeeds);
}
}

}

----------------------------------------------------------------------------------------------------------------------

rssフィードはこんな感じです。
----------------------------------------------------------------------------------------------------------------------

#!/usr/bin/php
&lt;?php

header('Content-type: text/html; charset=UTF-8');


require_once ('/var/www/html/s/magpierss/rss_fetch.inc');


define('MAGPIE_CACHE_AGE', 60*5);

define('MAGPIE_CACHE_DIR', '/var/www/html/s/cache/');

define('MAGPIE_OUTPUT_ENCODING','UTF-8');


$urls = array(
'*********************************');

$items = getRssList($urls);

echo '&lt;ul&gt;';
foreach ($items as $item) {

$href = mb_convert_encoding($item['link'], "UTF-8", "auto");

$title = mb_convert_encoding($item['title'], "UTF-8", "auto");
$description = mb_convert_encoding($item['description'], "UTF-8", "auto");
$megami = mb_convert_encoding($description, "UTF-8", "auto");

 

 **************************************************************************************


$all = 'http://urls.api.twitter.com/1/urls/count.json?url='.$href;
$Rits = file_get_contents($all);
$Olfe = mb_convert_encoding($Rits, 'UTF-8', 'auto');
$data = json_decode($Olfe);
$tweets = $data-&gt;count;

 **************************************************************************************

$checkman = db_check_urls($href);
$checkman2 = db_check_pr($title);

$nowtimes = date('Ymd');

 


if($checkman == 0 &amp;amp;&amp;amp; $checkman2 == 0){
#$hrefs = urlencode($href);
db_put_urls($nowtimes,$href,$title,$description,$tweets);
echo "&lt;li&gt;&lt;a href=$href&gt;$title&lt;/a&gt;&lt;br&gt;$megami&lt;p&gt;$tweets&lt;/p&gt;&lt;p&gt;$checkman&lt;/p&gt;&lt;/li&gt;";
}else{

}


}
echo '&lt;/ul&gt;';

 

 

 **************************************************************************************
function getRssList($urls)
{
$rssitems = array();
foreach ($urls as $url) {
$obj = fetch_rss($url);
$rssitems = array_merge($rssitems, $obj-&gt;items);
}
return $rssitems;
}

 **************************************************************************************
function db_check_pr($japanese){

$moji = mb_substr($japanese,0,2);
if($moji == 'PR'){
return 1;
}else{
return 0;
}
}
 **************************************************************************************

function db_check_urls($href){

require '/var/www/html/s/database_conf.php';

try {

$db = new PDO($dsn, $dbUser, $dbPass);
$db-&gt;setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$db-&gt;setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);


$sql = 'SELECT count(*) AS count FROM rssfeeds WHERE urls = :language';
$prepare = $db-&gt;prepare($sql);


$languages = array($href);
foreach ($languages as $language) {

$prepare-&gt;bindValue(':language', $language, PDO::PARAM_STR);
$prepare-&gt;execute();

$result = $prepare-&gt;fetchColumn();
if ($result &gt; 0) {
return true;
}else{
return false;
}
}

} catch (PDOException $e) {
echo 'エラーが発生しました。内容: ' . h($e-&gt;getMessage());
}

function h($var) 
{
return htmlspecialchars($var, ENT_QUOTES, 'UTF-8');
}
}

 **************************************************************************************
function db_put_urls($nowtimes,$href,$title,$description,$tweets){
require '/var/www/html/s/database_conf.php';
try {

$db = new PDO($dsn, $dbUser, $dbPass);
$db-&gt;setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$db-&gt;setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sikibetu = 5;
$moto = "アニゲー速報VIP";

$sql = 'insert into rssfeeds (id, time,urls,title,contents,tweets,times,kubetu,teikyo) values (?, ?, ?, ?, ?, ?, ?, ?, ?)';

$prepare = $db-&gt;prepare($sql);

$prepare-&gt;bindValue(1, 0, PDO::PARAM_STR);
$prepare-&gt;bindValue(2, $nowtimes, PDO::PARAM_STR);
$prepare-&gt;bindValue(3, $href, PDO::PARAM_STR);
$prepare-&gt;bindValue(4, $title, PDO::PARAM_STR);
$prepare-&gt;bindValue(5, $description, PDO::PARAM_STR);
$prepare-&gt;bindValue(6, $tweets, PDO::PARAM_STR);
$prepare-&gt;bindValue(7, 'cast( now() as datetime)', PDO::PARAM_STR);
$prepare-&gt;bindValue(8, $sikibetu, PDO::PARAM_STR);
$prepare-&gt;bindValue(9, $moto, PDO::PARAM_STR);
$prepare-&gt;execute();

} catch (PDOException $e) {
echo 'エラーが発生しました。内容: ' . h($e-&gt;getMessage());
}
}
?&gt;