使用单选按钮和连接表获取MySQL数据(Fetch MySQL data using radio buttons and joining tables)

我的桌子：

表1名称：filmer字段：id（主键，AI），titel，director，year，catid

表2名称：cat字段：catid（主键，AI），类别

添加电影到数据库与提交表单工作正常，但我想能够添加一个类别以及这里的单选按钮问题是我不知道我需要做什么，我已经尝试加入表与catid之间的关系，但它不会工作，我只能得到

没有结果显示！

在我添加电影之后，我可以在数据库中看到电影，但不能在浏览器中看到它们在第一个表中都得到catid 0，在第二个表中我有6个类别，catid值从1-6开始。

我是新手，所以我真的很感激任何帮助！

这是我的HTML表单和单选按钮

<form action="movies.php" method="post"> <input type="radio" name="id" value="1" checked />Action<br> <br> <input type="radio" name="id" value="2" />Comedy<br> <br> <input type="radio" name="id" value="3" />Drama<br> <br> <input type="radio" name="id" value="4" />Horror<br> <br> <input type="radio" name="id" value="5" />Romantic<br> <br> <input type="radio" name="id" value="6" />Animated<br><br> <pre> Title<input type="text" name="titel"> Director<input type="text" name="director"> Year <input type="text" name="year"> <input type="submit" name="submit" value="Submit" /> </pre> </form>

这是我的PHP文件，用于将电影添加到数据库中。

//Post data if (isset($_POST['submit'])){ $title = htmlentities($_POST['titel'], ENT_QUOTES); $director = htmlentities($_POST['director'], ENT_QUOTES); $year = htmlentities($_POST['year'], ENT_QUOTES); // empty form = error if ($title == '' || $director == '' || $year == ''){ $error = 'ERROR: Please fill in all required fields!'; echo $error; } else { //inserts movie to database if ($stmt = $mysqlic->prepare("INSERT INTO filmer (titel, director, year) VALUES (?, ?, ?)")){ $stmt->bind_param("ssi", $title, $director, $year); $stmt->execute(); $stmt->close(); // show an error if the query has an error } else { echo "ERROR: Could not prepare SQL statement."; } // redirec the user header("Location: view.php"); } }

这是PHP文件，显示数据库中的电影

// get movies from the database if ($result = $mysqlic->query("SELECT cat.catid FROM cat INNER JOIN filmer ON cat.catid=filmer.catid")){ // display records if there are records to display if ($result->num_rows > 0){ // display records in a table echo "<table border='1' cellpadding='10'>"; // set table headers echo "<tr><th>Title</th> <th>Director</th> <th>Year</th> <th>Category</th>"; while ($row = $result->fetch_object()){ // set up a row for each record echo "<tr>"; echo "<td>" . $row->titel . "</td>"; echo "<td>" . $row->director . "</td>"; echo "<td>" . $row->year . "</td>"; echo "<td>" . $row->Category . "</td>"; echo "</tr>"; } echo "</table>"; // if there are no records in the database, display an alert message } else { echo "No results to display!"; } // show an error if there is an issue with the database query } else { echo "Error: " . $mysqlic->error; } // close database connection $mysqlic->close();

My tables:

Table 1 name: filmer Fields: id (primary key, AI), titel, director, year, catid

Table 2 name: cat Fields: catid (primary key, AI), category

Adding movies to the database works fine with the submit form, but i want to be able to add a category aswell with the radio buttons here is where the problem is i don't know what i need to do and i have tried joining the tables with the relation between catid, but it wont work and i only get

No results to display!

after i add a movie, i can see the movies in the database but not in the browser and they all get catid 0 in the first table, and in the second table i have 6 categories with catid value from 1-6.

I'm a newbie at this so i would really appreciate any kind of help!

Here is my HTML form and radio buttons

<form action="movies.php" method="post"> <input type="radio" name="id" value="1" checked />Action<br> <br> <input type="radio" name="id" value="2" />Comedy<br> <br> <input type="radio" name="id" value="3" />Drama<br> <br> <input type="radio" name="id" value="4" />Horror<br> <br> <input type="radio" name="id" value="5" />Romantic<br> <br> <input type="radio" name="id" value="6" />Animated<br><br> <pre> Title<input type="text" name="titel"> Director<input type="text" name="director"> Year <input type="text" name="year"> <input type="submit" name="submit" value="Submit" /> </pre> </form>

And here is my PHP file to add movies to the database.

//Post data if (isset($_POST['submit'])){ $title = htmlentities($_POST['titel'], ENT_QUOTES); $director = htmlentities($_POST['director'], ENT_QUOTES); $year = htmlentities($_POST['year'], ENT_QUOTES); // empty form = error if ($title == '' || $director == '' || $year == ''){ $error = 'ERROR: Please fill in all required fields!'; echo $error; } else { //inserts movie to database if ($stmt = $mysqlic->prepare("INSERT INTO filmer (titel, director, year) VALUES (?, ?, ?)")){ $stmt->bind_param("ssi", $title, $director, $year); $stmt->execute(); $stmt->close(); // show an error if the query has an error } else { echo "ERROR: Could not prepare SQL statement."; } // redirec the user header("Location: view.php"); } }

And here is the PHP file that shows the movies from the database

// get movies from the database if ($result = $mysqlic->query("SELECT cat.catid FROM cat INNER JOIN filmer ON cat.catid=filmer.catid")){ // display records if there are records to display if ($result->num_rows > 0){ // display records in a table echo "<table border='1' cellpadding='10'>"; // set table headers echo "<tr><th>Title</th> <th>Director</th> <th>Year</th> <th>Category</th>"; while ($row = $result->fetch_object()){ // set up a row for each record echo "<tr>"; echo "<td>" . $row->titel . "</td>"; echo "<td>" . $row->director . "</td>"; echo "<td>" . $row->year . "</td>"; echo "<td>" . $row->Category . "</td>"; echo "</tr>"; } echo "</table>"; // if there are no records in the database, display an alert message } else { echo "No results to display!"; } // show an error if there is an issue with the database query } else { echo "Error: " . $mysqlic->error; } // close database connection $mysqlic->close();

最满意答案

在表格中添加新胶片时，不要指定它的类别。谁会猜到你需要什么类别？所以你需要使用这个查询来澄清这一点：

INSERT INTO filmer (titel, director, year, catid) VALUES (?, ?, ?, ?)

我添加了字段名称catid ，如你的问题和额外的描述? 接下来你必须获得类别ID。这可以这样做：

$cat_id = intval($_POST['id']);

之后你绑定参数：

$stmt->bind_param("ssii", $title, $director, $year, $cat_id);

When adding new film to your table you don't specify it's category. Who will guess what category you need? So you need to clarify this using this query:

INSERT INTO filmer (titel, director, year, catid) VALUES (?, ?, ?, ?)

I added field name catid as described in your question and extra ? Next you have to get category id. This can be done this way:

$cat_id = intval($_POST['id']);

After that you bind parameters:

$stmt->bind_param("ssii", $title, $director, $year, $cat_id);

更多推荐