How do I insert data into two tables

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";
}
?>

Follow the principles of [How can I prevent SQL injection in PHP?](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) and you won't got wrong. You can't push two SQL queries at the same time. If you want to insert from selected data, use [INSERT ... SELECT constant, field, field...](https://dev.mysql.com/doc/refman/8.0/en/insert-select.html). — danblack, Apr 11 '19 at 22:25

score 0 · Answer 1 · answered Apr 11 '19 at 22:30

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.

that dint work for me.. I tried that but now, nothing gets inserted into my tables :/ — Tatsäch lich, Apr 23 '19 at 18:48

score -1 · Answer 2 · edited Apr 12 '19 at 04:21

-1

$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.");";

this should be your query..... You put variables inside the quotes and that's why it was not working. Variables must be put outside the quotes and concat to the string with dot ".";

example:


echo "hello" . $a;

edited Apr 12 '19 at 04:21

Nasser Ali Karimi

4,462
6
34
77

answered Apr 12 '19 at 00:46

enumbin

19
2

that dint work for me.. I tried that but now, nothing gets inserted into my tables :/ – Tatsäch lich Apr 23 '19 at 18:48

How do I insert data into two tables

2 Answers2