unable to retrieve sql data to use in another query

Question

I'm struggeling with a problem and maybe someone could help me :)

in the first query I want to sent data to the first table which generates a id for me, at the second quary I want to retrieve that id made by the first quary, on the 3th quary I want to use that id to insert other data in a second table with the same id as the first table.

My question is this: everything is getting inserted fine except the $id, am I missing something?

<?php
    session_start();
    //declaratie sessie variabelen
    $username = $_SESSION['username'];
    $voornaam = $_SESSION['voornaam'];
    $achternaam = $_SESSION['achternaam'];
    $firma = $_SESSION['firma'];

    //database configuratie file
    require('dbconfig.php');

    //Declaratie post variabelen
    $ticnaam = $_POST['ticket_naam'];
    $ticonderwerp = $_POST['ticket_onderwerp'];
    $ticassign = $_POST['ticket_voor'];
    $bericht = $_POST['bericht'];

    //proccesing


    //procces first quary
    $sql = "INSERT INTO `tickets` 
                        (`naam`, `onderwerp`, `maker`) 
                VALUES ('$ticnaam', '$ticonderwerp', '$username')";

    //retrieves the generated new id from the quary above
    $sql2 = "SELECT id FROM tickets where onderwerp='$ticonderwerp' AND naam = '$ticnaam';";
    $result2 = mysqli_query($mysqli,$sql2);
    $id = mysqli_fetch_array($result2,MYSQLI_ASSOC);


    //insertes the id into another quary
    $sql3 = "INSERT INTO `berichten` 
                        (`id`, `text`, `voornaam`,`achternaam`,`firma`) 
                VALUES ('$id', '$bericht', '$voornaam',
                        '$achternaam','$firma')";

    //sql3 ok? user can continu
    if($mysqli->query($sql3) == TRUE) {
        require('email_na_ticketaanmaak.php')
?>
<script>alert('nieuw ticket is gemaakt');</script>
<?php
        require('../procces_files/email_na_ticketaanmaak.php');
        header('Location: ../home/index.php');
    }else{
        echo "Error: " . $sql . "<br>" . $mysqli->error;
    }
    $mysqli->close();
?>

You need `mysqli::$insert_id` [See manual page here](https://www.php.net/manual/en/mysqli.insert-id.php) — RiggsFolly, Apr 08 '20 at 14:31
@FunkFortyNiner Oh yea missed that, I need to stop for High Tea — RiggsFolly, Apr 08 '20 at 14:34
@RiggsFolly Heh, it's normal to be preoccupied in these trying times. — Funk Forty Niner, Apr 08 '20 at 14:36
Unrelated but seriously ...please read up on PDO or at least use prepared statements to protect your SQL queries! — D. Simmons, Apr 08 '20 at 14:41
yea sorry :) iknow that the code isn't that secure yet, i want to do the security after the application is complete :) — alexander wallaard, Apr 08 '20 at 14:55
_i want to do the security after the application is complete_ Yea right, that of course wont happen. — RiggsFolly, Apr 08 '20 at 15:05

score 0 · Answer 1 · edited Apr 17 '20 at 16:14

There is a property of the mysqli object called $insert_id that will return you the id of the newly inserted row if the id column is an AutoIncrement column.

<?php
    session_start();

    //database configuratie file
    require('dbconfig.php');

    // process first query
    $sql = "INSERT INTO `tickets` 
                    (`naam`, `onderwerp`, `maker`) VALUES (?,?,?)";

    $stmt = $mysqli->prepare($sql);
    $stmt->bind_param('sss', $_POST['ticket_naam'],
                            $_POST['ticket_onderwerp'],
                            $_SESSION['username']);
    $stmt->execute();

    // retrieves the generated new id from the query above
    $new_id = $mysqli->insert_id;

    // inserts the id into another query
    $sql = "INSERT INTO `berichten` 
                        (`id`, `text`, `voornaam`,`achternaam`,`firma`) 
                VALUES (?,?,?,?,?)";
    $stmt = $mysqli->prepare($sql);
    $stmt->bind_param('isss', $new_id,
                                $_POST['bericht'],
                                $_SESSION['voornaam'],
                                $_SESSION['achternaam'],
                                $_SESSION['firma']);
    $res = $stmt->execute();
    if ($res) {
        require('email_na_ticketaanmaak.php')
        require('../procces_files/email_na_ticketaanmaak.php');
        header('Location: ../home/index.php');
    }else{
        echo "Error: " . $sql . "<br>" . $mysqli->error;
    }
?>

If you are going to redirect to another page using

header('Location: ../home/index.php');

there is no point sending this back to the page you were on, as you will never see it.

<script>alert('nieuw ticket is gemaakt');</script>

Also Your script was open to SQL Injection Attack. Even if you are escaping inputs, its not safe! You should consider using prepared parameterized statements in either the MYSQLI_ or PDO API's instead of concatenated values

So I changed the code a little to use parameterised, prepared and bound queries.

unable to retrieve sql data to use in another query

1 Answers1