dongronge3732
dongronge3732
2015-01-24 14:11
浏览 32
已采纳

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

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();
  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

2条回答 默认 最新

  • dongshiqin1352
    dongshiqin1352 2015-01-24 14:27
    已采纳

    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);
    
    点赞 评论
  • donglu9445
    donglu9445 2015-01-24 14:27

    If i have understood correctly you want to post the cat id along with the other fields. I have added the name of the cat id so it is caught in the $_POST. Then put it into the insert query.

    //Post data
    if (isset($_POST['submit'])){
    
    $title = htmlentities($_POST['titel'], ENT_QUOTES);
    $director = htmlentities($_POST['director'], ENT_QUOTES);
    $year = htmlentities($_POST['year'], ENT_QUOTES);
    $catid= htmlentities($_POST['id'], ENT_QUOTES); //gets the catid
    
    
    // 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. catid) VALUES (?, ?, ?, ?)")){
    
            $stmt->bind_param("ssi", $title, $director, $year, $catid);
            $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");
    }
    

    }

    点赞 评论

相关推荐