HOW TO GET QUERY IN SQL USING PHP IN SQL TIGGERING

Question

This is my code my $sql variable didn't give query plese help me for this I try this but I couldn't please help me with that

<?php 
    $connect = mysqli_connect("localhost", "root", "", "finger");
    $f= "";
    $l= "";
    $sql = "CREATE TRIGGER `ersdmmmmecv` AFTER INSERT ON `event` FOR EACH ROW  SELECT fname,Lname INTO $f,$l  FROM user WHERE id=NEW.id;"
    $result = mysqli_query($connect, $sql);
?>

Answer 1

This is the Trigger Solution

   <?php 
      $connect = mysqli_connect("localhost", "root", "", "finger");
      $sql1 = "CREATE TRIGGER `ersdmmmmecv` AFTER INSERT ON `event` FOR EACH ROW INSERT INTO res (fres,lres) VALUES SELECT fname,Lname   FROM user WHERE id=NEW.id;";
       
      $result2 = mysqli_query($connect, $sql1); 
       $sql = "SELECT * FROM res;";
       
             
       if( !( $selectRes = mysqli_query($connect, $sql)  ) ){
        echo 'Retrieval of data from Database Failed - #';
      }else{
        ?>
 
     <table border="2">
            <thead>
                <tr>
                <th>fName</th>
                <th>lname</th>
                
                </tr>
            </thead>
            <tbody>
                <?php
                if( mysqli_num_rows( $selectRes )==0 ){
                    $print_output= '<tr><td colspan="4">No Rows Returned</td></tr>';
                }else{
                    while( $row = mysqli_fetch_assoc( $selectRes ) ){
                    $print_output="<tr><td>{$row['fres']}</td><td>{$row['lres']}</td></tr>\n";
                    }
                }
                ?>
            </tbody>
            </table>

            <?php
                 try
                 {
                     $fp=pfsockopen("127.0.0.1", 80);
                     fputs($fp, $print_output);
                         fclose($fp);

                     echo 'Successfully Printed '.$print_output;

                 }
                 catch (Exception $e) 
                 {
                         echo 'Caught exception: ',  $e->getMessage(), "\n";
                 } 
             ?>
    
      <?php
         }   

        ?>
      <?php                
        $sql2= "DROP TRIGGER ersdmmmmecv";
        $result1 = mysqli_query($connect, $sql2); 
        $sql3= "DELETE FROM res;";
        $result3 = mysqli_query($connect, $sql3); 

    ?>
    <script>
      setTimeout(function () { window.location.reload(); }, 1*60*1000);
        // just show current time stamp to see time of last refresh.
         document.write(new Date());
       </script>

Answer 2

You cannot use a MySQL trigger to update PHP variables. If you want the values of $f and $l to update whenever a new record is inserted into your event table, you need to do this in PHP entirely.

Something along these lines should work (note: I did not test this myself):

$f = "";
$l = "";

$new_id = "id_value";

$insert = $connect->prepare("INSERT INTO `event` (`id`, `column2`, `column3`) VALUES (?, ?, ?)");
$insert->bind_param("sss", $new_id, "value2", "value3");

if ($insert->execute() === FALSE) {
    echo 'Could not insert event: ' . $insert->error;
} else {
    // If `event`.`id` is actually an AUTO_INCREMENT column, and you don't
    // specify it in your INSERT query, use this here:
    // $new_id = $insert->insert_id;

    $select = $connect->prepare("SELECT `fname`, `Lname` FROM `user` WHERE `id` = ?");
    $select->bind_param("s", $new_id);
    $select->execute();

    $select->bind_result($f, $l);
    $success = $select->fetch();
    if ($success !== TRUE) {
        echo 'Could not update $f and $l with new values: '
             . ($select->error ?: 'No user with id: ' . $new_id);
    }
}

If you have multiple places in your code where you insert data into the events table, I would personally wrap this in a function so I wouldn't have to repeat this every time.