使用单选按钮和连接表获取MySQL数据

Question

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();

Answer 1

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);