nice_restaurant/ranking.php at master · hyowonii/nice_restaurant · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
<!DOCTYPE html>
    <html lang="en">
    <head>
        <meta charset="UTF-8">
        <meta http-equiv="X-UA-Compatible" content="IE=edge">
        <meta name="viewport" content="width=device-width, initial-scale=1.0">
        <title>Document</title>
        <link rel="stylesheet" href="ranking.css?after">
        <script src="https://use.fontawesome.com/releases/v5.2.0/js/all.js"></script>
        <?php include "header.php";?>
    </head>
    <body>
        <?php
        $mysqli = mysqli_connect("localhost", "team08", "team08", "team08");

        // 리뷰 개수 상위 10개
        $sql_r = "SELECT restName,COUNT(restName) as cnt FROM review GROUP BY restName ORDER BY cnt DESC limit 10";
        $res_r = mysqli_query($mysqli, $sql_r);

        while($reviewRank = mysqli_fetch_array($res_r)){
            $searchKeyword = $reviewRank['restName'];
            $cnt = $reviewRank['cnt'];
            // reviewrank table에 랭킹 등록
            $sql = "SELECT * FROM reviewrank WHERE searchKeyword='$searchKeyword'";
            $res = mysqli_query($mysqli, $sql);
            if($res->num_rows > 0){
                continue;
            } else {
                $sql_rank = "INSERT INTO reviewrank (searchKeyword, cnt) VALUES ('$searchKeyword', '$cnt')";
                mysqli_query($mysqli, $sql_rank);
            }

        };

        // reviewrank table에서 상위 10개만 남기고 delete
        $sql = "SELECT * FROM reviewrank ORDER BY cnt DESC";
        $res = mysqli_query($mysqli, $sql);
        $i = 0;
        while($newArray = mysqli_fetch_array($res)){
            if($i>9){
                $searchKeyword = $newArray['searchKeyword'];
                $sql_d = "DELETE FROM reviewrank WHERE searchKeyword='$searchKeyword'";
                mysqli_query($mysqli, $sql_d);
            }
            $i++;
        };


        // 별점 평균 점수 상위 10개
        $sql_s = "SELECT restName,AVG(starPoint) as avg FROM review GROUP BY restName ORDER BY avg DESC limit 10";
        $res_s = mysqli_query($mysqli, $sql_s);

        while($reviewRank = mysqli_fetch_array($res_s)){
            $restName = $reviewRank['restName'];
            $avg = $reviewRank['avg'];
            // starrank table에 랭킹 등록
            $sql = "SELECT * FROM starrank WHERE restName='$restName'";
            $res = mysqli_query($mysqli, $sql);
            if($res->num_rows > 0){
                continue;
            } else {
                $sql_rank = "INSERT INTO starrank (restName, avg) VALUES ('$restName', '$avg')";
                mysqli_query($mysqli, $sql_rank);
            }

        };

        // starrank table에서 상위 10개만 남기고 delete
        $sql = "SELECT * FROM starrank ORDER BY avg DESC";
        $res = mysqli_query($mysqli, $sql);
        $i = 0;
        while($newArray = mysqli_fetch_array($res)){
            if($i>9){
                $restName = $newArray['restName'];
                $sql_d = "DELETE FROM starrank WHERE restName='$restName'";
                mysqli_query($mysqli, $sql_d);
            }
            $i++;
        }


        ?>

        <div class="main_content">
            <h1><a href="main.php"><i class="fas fa-utensils"></i> 모음</a></h1>
            <h2>모범음식점 Ranking</h2>
            <div class="rank">
                <div id="res_cnt">
                    <h3>개수</h3>
                    <div class="hGraph">
                        <ul>
                            <li><span class="gTerm">강남구</span><span class="gBar" style="width:100%"><span>382</span></span></li>
                            <li><span class="gTerm">마포구</span><span class="gBar" style="width:30%"><span>115</span></span></li>
                            <li><span class="gTerm">송파구</span><span class="gBar" style="width:84%"><span>322</span></span></li>
                            <li><span class="gTerm">용산구</span><span class="gBar" style="width:34%"><span>131</span></span></li>
                            <li><span class="gTerm">종로구</span><span class="gBar" style="width:23%"><span>89</span></span></li>
                        </ul>
                    </div>
                </div>
                <div id='review'>
                    <h3>리뷰 개수</h3>
                    <ul>
                        <?php
                        $n=1;
                        $sql = "SELECT * FROM reviewrank ORDER BY cnt DESC";
                        $res = mysqli_query($mysqli, $sql);
                        while($newArray = mysqli_fetch_array($res)){
                            $searchKeyword = $newArray['searchKeyword'];
                            $cnt = $newArray['cnt'];
                            echo "<li>".$n++."위<br/>
                            <p>".$searchKeyword." (".$cnt."개)</p>
                            </li>";
                        }
                        ?>

                    </ul>
                </div>
                <div id='star'>
                    <h3>별점순</h3>
                    <ul>
                    <?php
                        $n=1;
                        $sql = "SELECT * FROM starrank ORDER BY avg DESC";
                        $res = mysqli_query($mysqli, $sql);
                        while($newArray = mysqli_fetch_array($res)){
                            $restName = $newArray['restName'];
                            $avg = $newArray['avg'];
                            echo "<li>".$n++."위<br/>
                            <p>".$restName." (".$avg."점)</p>
                            </li>";
                        }
                        ?>
                    </ul>
                </div>
            </div>
        </div>

        <div id="line"></div>

        <form>
            <div class="mymenu">
                <input type="button" value="마이페이지" onclick="location.href='mypage.php'">
                <input type="button" value="리뷰" onclick="location.href='review2.php'">
                <input type="button" value="착한가격식당" onclick="location.href='kind_price2.php'">
                <input type="button" value="순위" onclick="location.href='ranking.php'">
            </div>
        </form>
    </body>
</html>