时间戳

首页 > 解决方案 > PHP 输出和输入 AVG

php - PHP 输出和输入 AVG

问题描述

做一个电影评论网站。

我需要

  1. avg在用户页面中按表单输入数据到MYSQL database.
  2. avg在网页中输出


我无法从用户页面的表单中输入平均费率的问题。它不会avg在网页上显示汇率。

到目前为止
，我可以直接输入 avg by SQL INSERT。我猜它无法获得电影ID。

这是html代码

<!doctype html>
<html lang="en">

<head>
    <title>Award 2019</title>
    <!-- Required meta tags -->
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">

    <!-- Bootstrap CSS -->
    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css" integrity="sha384-ggOyR0iXCbMQv3Xipma34MD+dH/1fQ784/j6cY/iJTQUOhcWr7x9JvoRxT2MZw1T" crossorigin="anonymous">
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/4.7.0/css/font-awesome.min.css">
    <link rel="stylesheet" href="css/review_page.css">
    <style>
        .checked {
            color: orange;
        }
    </style>
</head>

<body>
    <header>
        <nav>
            <div class="top bg-dark text-light py-1">
                <div class="container-nav topheader">
                    <div class="d-flex justify-content-between">
                        <div class="left ml-3">
                            <a class="text-light logo lead" terget="_blank" href="dashboard.php">Academy Movie Review</a>
                        </div>
                        <div class="right mr-3">
                            <p>Logout</p>
                        </div>
                    </div>
                </div>
            </div>

            <div class="bottom bg-light sticky-top shadow-sm">
                <div class="container-nav">
                    <ul class="nav nav-bottom nav-fill">
                        <li class="nav-item dropdown dmenu">
                            <a class="nav-link text-dark dropdown-toggle" terget="_blank" href="#" data-toggle="dropdown"><span class="d-none d-sm-inline-block d-md-inline-block">Movie List</span></a>
                            <div class="dropdown-menu sm-menu">
                                <a class="dropdown-item" href="list1.php">2011～2020</a>
                                <a class="dropdown-item" href="#">2001～2010 (Coming Soon)</a>
                                <a class="dropdown-item" href="#">1991～2000 (Coming Soon)</a>
                                <a class="dropdown-item" href="#">1981～1990 (Coming Soon)</a>
                                <a class="dropdown-item" href="#">1971～1980 (Coming Soon)</a>
                                <a class="dropdown-item" href="#">1961～1970 (Coming Soon)</a>
                                <a class="dropdown-item" href="#">1950～1960 (Coming Soon)</a>
                            </div>
                        </li>
                        <li class="nav-item">
                            <a class="nav-link text-dark" terget="_blank" href="comment.php"><span class="d-none d-sm-inline-block d-md-inline-block">My Comment</span></a>
                        </li>
                        <li class="nav-item">
                            <a class="nav-link text-dark" href="recommend.php"><span class="d-none d-sm-inline-block d-md-inline-block">Our Recommend</span></a>
                        </li>
                        <li class="nav-item">
                            <a class="nav-link text-dark" href="login.php"><span class="d-none d-sm-inline-block d-md-inline-block">Login</span></a>
                        </li>
                        <li class="nav-item">
                            <a class="nav-link text-dark" href="signup.php"><span class="d-none d-sm-inline-block d-md-inline-block">Sign Up</span></a>
                        </li>
                    </ul>
                </div>
            </div>
        </nav>
    </header>
    <div class="container">
        <div class="row mt-3">
            <div class="col-lg-6 mt-3">
                <iframe width="500" height="315" src="https://www.youtube.com/embed/Wg_Ql89fWy4" frameborder="0" allow="accelerometer; autoplay; encrypted-media; gyroscope; picture-in-picture" allowfullscreen></iframe>
            </div>
            <div class="col-lg-6">
                <?php if ($Movie->displayMovie2019() == TRUE) {

                    foreach ($Movie->displayMovie2019() as $row) {
                ?>
                        <br>
                        <h2><?php echo $row['movie_name']; ?> (<?php echo $row['release_year']; ?>)</h2>
                        <p><?php echo $row['movie_info']; ?></p>
                        <p>Genre : <?php echo $row['genre']; ?></p>
                <?php    }
                }
                ?>
                <p><b>Average <span class="fa fa-star" id="avg"><?php $Movie->avgRate($movieID);?></span></b><button type="submit" class="btn btn-outline-warning ml-5 w-50" onclick="openForm()">review</button></p>

            </div>
            <div class="form-popup mx-auto p-5" style="display: none;" id="myForm">

                <form action="" method="post" class="form-container">
                    <h4>Review&comment</h4>
                    <input type="hidden" name="movie_id" value="<?php echo $_GET['movie_id'] ?>">
                    Your name
                    <input type="text" name="reviewer" placeholder="Enter your name">
                    Rate <br>
                    <select name="rate" id="">
                        <option value="1">★&lt;/option>
                        <option value="2">★★</option>
                        <option value="3">★★★&lt;/option>
                        <option value="4">★★★★</option>
                        <option value="5">★★★★★&lt;/option>
                    </select> <br>
                    <textarea name="comment" id="" cols="5" rows="5" placeholder="Enter Your Review Comment" class="form-control mt-3"></textarea>
                    <button type=" submit" name="review" class="btn mt-2">Submit</button>
                    <button type="submit" class="btn cancel mb-2" onclick="closeForm()">Close</button>
                </form>
                <script>
                    function openForm() {
                        document.getElementById("myForm").style.display = "block";
                        $("body").css("background-color", "black");

                    }
                    function closeForm() {
                        document.getElementById("myForm").style.display = "none";
                    }
                </script>
            </div>


        </div>
    </div>

    <footer class="bg-dark p-4 mt-3 w-100 text-white text-center fixed-bottom">
        Copyright © 2020 Academy Movie Review. All rights reserved.
    </footer>
    <!-- Optional JavaScript -->
    <!-- jQuery first, then Popper.js, then Bootstrap JS -->
    <script src="https://code.jquery.com/jquery-3.3.1.slim.min.js" integrity="sha384-q8i/X+965DzO0rT7abK41JStQIAqVgRVzpbzo5smXKp4YfRvH+8abtTE1Pi6jizo" crossorigin="anonymous"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.14.7/umd/popper.min.js" integrity="sha384-UO2eT0CpHqdSJQ6hJty5KVphtPhzWj9WO1clHTMGa3JDZwrnQq4sF86dIHNDz0W1" crossorigin="anonymous"></script>
    <script src="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/js/bootstrap.min.js" integrity="sha384-JjSmVgyd0p3pXB1rRibZUAYoIIy6OrQ6VrjIEaFf/nJGzIxFDsf4x0xIM+B07jRM" crossorigin="anonymous"></script>
</body>

</html>

这是类 php 文件



  public function displayMovie2019()
    {
        $sql = "SELECT * FROM movies WHERE movie_id = 1";
        $result = $this->conn->query($sql);

        if ($result->num_rows > 0) {
            $row = array();
            while ($rows = $result->fetch_assoc()) {
                $row[] = $rows;
            }
            return $row;
        } else {
            return FALSE;
        }
    }


    public function displayMovie2018()
    {
        $sql = "SELECT * FROM movies WHERE movie_id = 5";
        $result = $this->conn->query($sql);

        if ($result->num_rows > 0) {
            $row = array();
            while ($rows = $result->fetch_assoc()) {
                $row[] = $rows;
            }
            return $row;
        } else {
            return FALSE;
        }
    }

    // review page

    public function postReview($movieID, $reviewer, $reviewRate, $reviewComment)
    {
        $sql = "UPDATE reviews SET reviewer = '$reviewer', review_rate = '$reviewRate', review_comment = '$reviewComment' WHERE movie_id = $movieID";
        $result = $this->conn->query($sql);

        if ($result == TRUE) {
            echo "review was submitted";
        } else {
            echo "Error: Your reviewing";
        }
    }

    public function avgRate($movieID)
    {
        $sql = "SELECT AVG(review_rate) FROM reviews INNER JOIN movies ON reviews.movie_id=movies.movie_id WHERE movies.movie_id = '$movieID'";
        $result = $this->conn->query($sql);

        if ($result->num_rows > 0) {
            $row = array();
            while ($rows = $result->fetch_assoc()) {
                $row[] = $rows;
            }
            return $row;
        } else {
            echo "No Data";
        }
    }

}
?>

CREATE TABLE `movies` (
 `movie_id` int(11) NOT NULL AUTO_INCREMENT,
 `movie_name` varchar(100) NOT NULL,
 `movie_info` text NOT NULL,
 `release_year` varchar(100) NOT NULL,
 `genre` varchar(100) NOT NULL,
 `movie_img` varchar(100) DEFAULT NULL,
 PRIMARY KEY (`movie_id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb4


CREATE TABLE `reviews` (
 `review_id` int(11) NOT NULL AUTO_INCREMENT,
 `review_name` varchar(100) NOT NULL,
 `review_rate` int(10) NOT NULL,
 `review_comment` text NOT NULL,
 `movie_id` int(11) NOT NULL,
 `user_id` int(11) NOT NULL,
 `review_date` datetime NOT NULL,
 PRIMARY KEY (`review_id`),
 KEY `movie_id` (`movie_id`),
 KEY `user_id` (`user_id`),
 CONSTRAINT `reviews_ibfk_1` FOREIGN KEY (`movie_id`) REFERENCES `movies` (`movie_id`),
 CONSTRAINT `reviews_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`),
 CONSTRAINT `reviews_ibfk_3` FOREIGN KEY (`movie_id`) REFERENCES `movies` (`movie_id`),
 CONSTRAINT `reviews_ibfk_4` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4

CREATE TABLE `users` (
 `user_id` int(11) NOT NULL AUTO_INCREMENT,
 `username` varchar(100) NOT NULL,
 `password` varchar(100) NOT NULL,
 `email` varchar(100) NOT NULL,
 `user_status` varchar(1) NOT NULL DEFAULT 'U',
 PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4

标签： phpmysqlsql

解决方案

您可以执行以下操作：

  • 在电影表中添加两列：new_rating、voters_count

  • 添加评分：每次用户为一部电影投票时，您将他的评分 int 值（例如 10 分）插入 new_rating 列并将 voters_count 增加一。

  • 获取平均评分： new_rating 除以 voters_count。

推荐阅读