How to use inner join in php-CodePudding

I want all the 'acteurs' of a table in the value of a option, but I don't know how to. I have two tables, 'auteurs' and 'posts'. In the posts table I have a column named 'auteur_id' that has a foreign key to the table auteurs.

When I run this sql code that is in my php it also gives the good output in my phpmyadmin.

My code:

<html>

    <head>
        <link rel="stylesheet" type="text/css" href="style.css">
    </head>

    <body>
        <div class="container">

            <div id="header">
                <h1>Nieuwe post</h1>
                <a href="index.php"><button>Alle posts</button></a>
            </div>

        <?php
            $host = 'localhost';
            $username = 'root';
            $password = '';
            $dbname = 'foodblog';
            $dsn = 'mysql:host=' . $host . ';dbname=' . $dbname;
            
            try {
                $conn = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);
                // set the PDO error mode to exception
                $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            } catch (PDOException $e) {
                echo "Connection failed: " . $e->getMessage();
            }
            
        try {
            if (isset($_POST["submit"])) {
                $titel = $_POST['titel'];
                $img = $_POST['img_url'];
                $inhoud = $_POST['inhoud'];
                $sql = "INSERT INTO posts (titel, img_url, inhoud) 
                VALUES ('$titel', '$img', '$inhoud')";
                $conn->exec($sql);
            } else {
        ?>
                <form action="new_post.php" method="post">
                Titel:<br/> <input type="text" name="titel"><br/><br/>
                Auteurs:<br><select name='auteurs'>
                <option value='<?php
                $sql2 = "SELECT * 
                FROM posts
                INNER JOIN auteurs
                ON posts.auteur_id = auteurs.id";
                $conn->exec($sql2);
                foreach ($sql2 as $rows) {
                    $rows['auteur'];
                }
                ?>'>
                </select>
                <br><br>
                URL afbeelding:<br/> <input type="text" name="img_url"><br/><br/>
                Inhoud:<br/> <textarea name="inhoud" rows="10" cols="100"></textarea>
                <br/><br/>
                <input type="submit" name="submit" value="Publiceer">
                </form>
        <?php
            }
        } catch(error) {
            console.error(error);
        }
        ?>

    </body>
</html>

This is what I have now: But I need all the 'auteur' values from the table auteurs to be shown in the auteurs option in php. This is everything in my phpmyadmin auteurs table:

posts table:

CodePudding user response：

You have to create a new option every time you iterate over your SELECT query.

<form action="new_post.php" method="post">
    Titel:<br/> <input type="text" name="titel"><br/><br/>
    Auteurs:<br>
    <select name='auteurs'>
        <?php
        $sql2 = "SELECT * FROM posts INNER JOIN auteurs ON posts.auteur_id = auteurs.id";
        $res = $conn->query($sql2);
        foreach ($res as $row) {
            echo "<option value='".$row['auteur']."'>".$row['auteur']."</option>";
        }
        ?>
    </select>
    <br><br>
    URL afbeelding:<br/> <input type="text" name="img_url"><br/><br/>
    Inhoud:<br/> <textarea name="inhoud" rows="10" cols="100"></textarea>
    <br/><br/>
    <input type="submit" name="submit" value="Publiceer">
</form>

Also you should use the query method or the safer prepare / execute methos for executing query.

CodePudding user response：

    Use like below for select box:
       <select name='auteurs'>
              <?php
                $sql2 = "SELECT * FROM auteurs ";
                $sql2=$conn->query($sql2);
                foreach ($sql2 as $rows) { ?>
                   <option value='<?php echo $rows['id'];?>'><?php echo $rows['auteur'];?></option>
                    
                <?php } ?> 
  </select>