如何将数据插入两个表中

Question

I want to INSERT data into two tables. The problem is, my $sql variables don't really work as I imagined..

If I would just say that in the posts file:

$sql ="SELECT * FROM beitrag ORDER BY beitrag_id DESC"; 

and delete that $sql variable in the new_post file

$sql .= " INSERT INTO leistungen (leistung_text, leistung_warenbezugsort, leistung_kosten) 
VALUES ('$leistung_text', '$leistung_warenbezugsort', '$leistung_kosten')";

the "beitrag" data would show up in my posts, but "leistungen" data wouldnt insert into my database and also wouldnt show up in my posts...

         //this is the new_post file
<?php
    if(isset($_POST['senden'])) {
        $titel = strip_tags($_POST['titel']);
        $p_text= strip_tags($_POST['p_text']);
        $leistung_text = strip_tags($_POST['leistung_text']);
        $leistung_warenbezugsort = strip_tags($_POST['leistung_warenbezugsort']);
        $leistung_kosten = strip_tags($_POST['leistung_kosten']);

        $titel = mysqli_real_escape_string($db, $titel);
        $p_text = mysqli_real_escape_string($db, $p_text);
        $leistung_text = mysqli_real_escape_string($db, $leistung_text);
        $leistung_warenbezugsort = mysqli_real_escape_string($db, $leistung_warenbezugsort);
        $leistung_kosten = mysqli_real_escape_string($db, $leistung_kosten);

        $sql = " INSERT INTO beitrag (titel, p_text) VALUES('$titel', '$p_text')";

        $sql .= " INSERT INTO leistungen (leistung_text, leistung_warenbezugsort, leistung_kosten) VALUES ('$leistung_text', '$leistung_warenbezugsort', '$leistung_kosten')";




        if($titel == "" || $p_text == "" || $leistung_text = "" || $leistung_warenbezugsort = "" || $leistung_kosten = "") {
            echo "Bitte Beitrag vervollstaendigen!";
            return;
        }

        mysqli_multi_query($db, $sql);

        header ("Location: beitraege.php");

    }

?>

//this is the posts file

<?php

$sql ="SELECT * FROM beitrag INNER JOIN leistungen ON beitrag.beitrag_id = leistungen.beitrag_id ORDER BY beitrag.beitrag_id DESC";

$res = mysqli_query($db, $sql) or die(mysqli_error());

$beitrag = "";

if(mysqli_num_rows($res) > 0) {
    while($row =mysqli_fetch_assoc($res)){
        $beitrag_id = $row['beitrag_id'];
        $titel = $row['titel'];
        $p_text = $row['p_text'];
        $leistung_id = $row['leistung_id'];
        $leistung_text = $row['leistung_text'];
        $leistung_warenbezugsort = $row['leistung_warenbezugsort'];
        $leistung_kosten = $row['leistung_kosten'];

        if (isset($_SESSION["login"])){ 
                if($_SESSION["login"] == 1){
                    echo "<div><a href='löschen_beitrag.php?pid=$beitrag_id'>löschen</a>&nbsp;<a href='bearbeiten_beitrag.php?pid=$beitrag_id'>bearbeiten</a></div>";
                    }else{
                    echo "";
                }
            }

        $beitrag .= "<div><h2><a href='siehe_post.php?pid=$beitrag_id'>$titel</a></h2><p>$p_text</p></div";
    }
    echo $beitrag;
} else {
    echo "Keine Beiträge vorhanden";
}
?>

Answer 1

This is actually a very simple issue. When using mysqli_multi_query, the queries need to be separated by semicolons. You have semicolons at the end of each line, of course, but they're outside the strings and they're for PHP, not for your SQL.

Just add a semicolon to the end of your first SQL string:

 $sql = " INSERT INTO beitrag (titel, p_text) VALUES('$titel', '$p_text');";

Also, as a commenter points out, SQL injections are a problem. You're avoiding this issue by escaping all your strings with mysqli_real_escape_string, but this way of doing things is fairly error-prone - if you forget to escape one of the strings used in a query, your code is vulnerable. mysqli_multi_query doesn't support the simpler and saner way of avoiding SQL injections, which is using prepared statements (see mysqli_prepare) - you're probably better off not using multi queries if you can avoid it.