300_php_MySQL_全件表示_OK!★

投稿日時: 投稿者:

php_MySQL_全件表示_です。
いまさら、復習。。コピペで対処！

/var/www/html/photo_check_20170731
20170809_rank_test.php
をいじくる。
表示は、
http://153.126.154.106/photo_check_20170731/20170809_rank_test.php

————————————–
SQL 合計値からランキングを取得する例
指定ユーザーの順位を取得する
http://qiita.com/hmuronaka/items/1afc132ddf400363efc2
参考

SELECT MAX( happiness ) FROM `emo_list` WHERE 1

SELECT * FROM emo_list where (id = (select MAX( id ) from emo_list))

SELECT (select count(happiness) FROM emo_list b WHERE a.happiness < b.happiness) + 1 rank FROM emo_list a ORDER BY rank ASC （下記サンプル） set @c:=0; SELECT tmp.rank FROM ( SELECT id, @c := @c +1 rank FROM emo_list ORDER BY happiness DESC )tmp WHERE `id`=111 ＝＝＝＝＝＝＝＝＝＝＝＝＝ ＝＝＝＝＝＝＝＝＝＝＝＝＝ id最大値のhappinessの順位を求める（１） 指定ユーザーの順位を取得するOK ＝＝＝＝＝＝＝＝＝＝＝＝＝ set @c:=0; SELECT tmp.rank FROM ( SELECT id, @c := @c +1 rank FROM emo_list ORDER BY happiness DESC )tmp WHERE (id = (select MAX( id ) from emo_list)) ＝＝＝＝＝＝＝＝＝＝＝＝＝ 全順位取得を求める（２）OK ＝＝＝＝＝＝＝＝＝＝＝＝＝ set @c=0; select @c:=@c+1 as rank, id, @p:=happiness as happiness_scores from emo_list ORDER BY happiness DESC ; ＝＝＝＝＝＝＝＝＝＝＝＝＝ 指定した順位を取得する（３） 1位から10位までの表示OK ＝＝＝＝＝＝＝＝＝＝＝＝＝ set @c:=0; SELECT tmp.id id, tmp.photoname photoname,tmp.happiness happiness, tmp.rank rank FROM (SELECT id, photoname, happiness, @c:=@c+1 rank FROM emo_list ORDER BY happiness DESC) tmp ORDER BY rank asc LIMIT 0, 10; ＝＝＝＝＝＝＝＝＝＝＝＝＝ http://www.phpbook.jp/tutorial/pdo/index7.html を参考にSELECT表示OK！ ファイル名： 20170809_rank_test.php ＝＝＝＝＝＝＝＝＝＝＝＝＝ $sql1 = 'select id from emo_list where (id = (select MAX( id ) from emo_list)) '; $stmt1 = $dbh->query($sql1);

$result = $stmt1->fetch(PDO::FETCH_NUM);
print($result[0]);
print($result[1]);
print($result[2]);
print($result[3]);
＝＝＝＝＝＝＝＝＝＝＝＝＝

set @c:=0;
SELECT tmp.id, tmp.rank rank FROM
(SELECT id, @c:=@c+1 rank FROM emo_list ORDER BY happiness DESC) tmp
WHERE (id = (select MAX( id ) from emo_list));

これも、MySQL-adminではOK！
＝＝＝＝＝＝＝＝＝＝＝＝＝

＝＝＝＝＝＝＝＝＝＝＝＝＝
同スコアは順位同じにする場合
は、MySQL-adminではOK！
＝＝＝＝＝＝＝＝＝＝＝＝＝

count(total_score)の場合の結果/OK!<---これが良いかも？？一般的！★ SELECT id, photoname, happiness, (select count(happiness) FROM emo_list b WHERE a.happiness < b.happiness) + 1 rank FROM emo_list a ORDER BY rank ASC は、MySQL-adminではOK！ count(DISTINCT total_score)の場合の結果/OK! SELECT id, photoname, happiness, (select count(DISTINCT happiness) FROM emo_list b WHERE a.happiness < b.happiness) + 1 rank FROM emo_list a ORDER BY rank ASC は、MySQL-adminではOK！ ＝＝＝＝＝＝＝＝＝＝＝＝＝ ＝＝＝＝＝＝＝＝＝＝＝＝＝ 13.2.5.1 INSERT ... SELECT 構文 https://dev.mysql.com/doc/refman/5.6/ja/insert-select.html でOK ＝＝＝＝＝＝＝＝＝＝＝＝＝ DELETE FROM emo_list_rank WHERE 1; INSERT INTO emo_list_rank SELECT id, photoname, happiness, (select count(happiness) FROM emo_list b WHERE a.happiness < b.happiness) + 1 rank FROM emo_list a ORDER BY rank ASC ; ＝＝＝＝＝＝＝＝＝＝＝＝＝★★★★★ ついにできました！！ このPhotoは、全22件のうち 第 1位のHappiness度です！ の表示！！！ ＝＝＝＝＝＝＝＝＝＝＝＝＝★★★★★ $sql1 = 'SELECT id, photoname, happiness, (select count(happiness) FROM emo_list b WHERE a.happiness < b.happiness) + 1 rank FROM emo_list a ORDER BY rank ASC '; $stmt1 = $dbh->query($sql1);
$result = $stmt1->fetch(PDO::FETCH_NUM);

$sql2 = ‘SELECT count(*) FROM emo_list_rank ‘;
$stmt2 = $dbh->query($sql2);
$result2 = $stmt2->fetch(PDO::FETCH_NUM);

print(‘#1—- :’.$result[0].”
“);
print(‘#2—- :’.$result[1].”
“);
print(‘#3—- :’.$result[2].”
“);
print(‘#4—- :’.$result[3].”
“);

print(‘このPhotoは、全’.$result2[0].”件のうち
“);
print(‘第 ‘.$result[3].”位のHappiness度です！
“);

＝＝＝＝＝＝＝＝＝＝＝＝＝★★★★★

DELETE FROM emo_list_rank WHERE 1; INSERT INTO emo_list_rank SELECT id, photoname, happiness, (select count(happiness) FROM emo_list b WHERE a.happiness < b.happiness) + 1 rank FROM emo_list a ORDER BY rank ASC ; $sql0 = <<query($sql0);
$result0 = $stmt0->fetch(PDO::FETCH_NUM);

$sql1 = <<query($sql0);
$result0 = $stmt0->fetch(PDO::FETCH_NUM);

$sql1 = <<query($sql1);
$result = $stmt1->fetch(PDO::FETCH_NUM);

$sql2 = ‘SELECT count(*) FROM emo_list_rank ‘;
$stmt2 = $dbh->query($sql2);
$result2 = $stmt2->fetch(PDO::FETCH_NUM);

$sql3 = ‘SELECT rank FROM emo_list_rank WHERE (id = (select MAX( id ) from emo_list))’;
$stmt3 = $dbh->query($sql3);
$result3 = $stmt3->fetch(PDO::FETCH_NUM);

print(‘
‘);

print(‘このPhotoは、全’.$result2[0].”件のうち
“);
print(‘第 ‘.$result3[0].”位のHappiness度です！
“);

★★★★★★★★★★★★★★★★★★★★

————————————–

下記、生コード。

ーーーーーーーーーーーーー
$sql4 = ‘select * from emo_list_rank’;
$stmt4 = $dbh->query($sql4);

print(‘
emo_list_rankテーブル全表示
‘);

?>

fetch(PDO::FETCH_NUM)){
print(‘

‘);
print(‘

‘);
print(‘

‘);
print(‘

‘);
print(‘

‘);
print(‘

‘);
}
?>

id photoname happiness rank
‘.$result4[0].’ ‘.$result4[1].’ ‘.$result4[2].’ ‘.$result4[3].’