Retrieving values of php var in mysql trigger

Question

I have php file which insert the sentence and perform trigger operation as below:

    < html > <body > 
    <?php 

    $s = $_POST['sent'];
    echo "Entered sentence : $s";

    if (preg_match_all('/[^=]*=([^;@]*)/', shell_exec("/home/technoworld/Videos/LinSocket/client '$s'"), $matches)) //Values stored in ma.
    {
        $x = (int) $matches[1][0];  //optionally cast to int
        $y = (int) $matches[1][1];
    }

    $con = mysqli_connect('127.0.0.1:3306', 'root', 'root', 'test');
    if (mysqli_connect_errno()) {
        echo "Failed to connect to MySQL: ".mysqli_connect_error();
    }


//    $sql2 = "CREATE TRIGGER MysqlTrigger AFTER INSERT ON table1 FOR EACH ROW BEGIN INSERT INTO temp1(sent,pindex,nindex) VALUES (NEW.sent,".$x.",".$y.");";
    mysqli_query($con,$sql2);

    $sql1 = "INSERT INTO table1 (sent)VALUES('$_POST[sent]')";

    if (!mysqli_query($con, $sql1)) {
        die('Error: '.mysqli_error($con));
    }
    echo "1 record added";
    mysqli_close($con);

    ?>
    </html > </body >

And trigger in mysql:

DELIMITER $$

DROP TRIGGER /*!50032 IF EXISTS */ `test`.`MysqlTrigger`$$

CREATE
    /*!50017 DEFINER = 'root'@'localhost' */
    TRIGGER `MysqlTrigger` AFTER INSERT ON `table1` 
    FOR EACH ROW BEGIN
    INSERT INTO temp VALUES(New.sent,'$x','$y');
    END;
$$

DELIMITER ;

I want that $x and $y from test.php should be inserted in database on insert operation. NEW.sent could be inserted in existing code while x and y as 0,0.

If possible then I want to insert sent,x and y from test.php itself on trigger.

I tried it with above test.php removing the comment on trigger part and removing the trigger from mysql database. But it does insert the sentence in table1 only while temp does not get updated.

see this link can help you better http://stackoverflow.com/questions/7750208/php-mysql-triggers-how-to-pass-variables-to-trigger — Rajeev Ranjan, Jul 10 '13 at 06:43
@RajeevRanjan: `USE `baemer_emr`$$ CREATE DEFINER=`baemer_emr`@`localhost`` this is for what can you please tell me? — user123, Jul 10 '13 at 07:09
@RajeevRanjan: Thanks for this link. It was really helpful. I was not knowing about blackhole table. Check out my next answer if you like upvote it! — user123, Jul 11 '13 at 06:27

score 1 · Accepted Answer · answered Jul 11 '13 at 06:34

----------This code is prone to SQL INJECTION(I ignored it as it's not issue for me rightnow)----------------------------

This is very easy to do if you know the concept of mysql Blackhole table. You create it which does not store any value but you can use it values to perform any other task.

So insert what ever values(variables) you want to insert into table insert into creating blackhole table. And in mysql you can use it like: NEW.<variablename>

Creating blackhole table:

CREATE TABLE bh_newusers (
  username varchar(255) not null,
  password varchar(255) not null,
  idn integer not null,
  patient_id integer not null,
  user_id integer not null) ENGINE = BLACKHOLE;



 My solution for main question:

    php.test:



 < html > <body > 
    <?php 
    if (!empty($_POST['insert'])) {
    echo "Insert"; echo "<br/>";
    $s = $_POST['sent'];
    $flag=0;
    echo "Entered sentence : $s";

    if (preg_match_all('/[^=]*=([^;@]*)/', shell_exec("/home/technoworld/Videos/LinSocket/client '$s'"), $matches)) //Values stored in ma.
    {
        $x = (int) $matches[1][0];  //optionally cast to int
        $y = (int) $matches[1][1];
    }

    echo "<br/>"; echo $x;
    echo "<br/>"; echo $y; echo "<br/>";

    //---------------DB stuff --------------------

    $con = mysqli_connect('127.0.0.1:3306', 'root', 'root', 'test');
    if (mysqli_connect_errno()) {
        echo "Failed to connect to MySQL: ".mysqli_connect_error();
    }

    $sql1 = "INSERT INTO table2 (id,sent,pcount,ncount,flag) VALUES('','$_POST[sent]','".$x."','".$y."','".$flag."')";

    if (!mysqli_query($con, $sql1)) {
        die('Error: '.mysqli_error($con));
    }
    echo "1 record added";
    mysqli_close($con);
    }
    // -------------------------------UPDATE --------------------------

    if (!empty($_POST['update'])) {
    echo "update";echo "<br/>";

    $s = $_POST['sent'];
    $flag=1;
    echo "Entered sentence : $s";

    if (preg_match_all('/[^=]*=([^;@]*)/', shell_exec("/home/technoworld/Videos/LinSocket/client '$s'"), $matches)) //Values stored in ma.
    {
        $x = (int) $matches[1][0];  //optionally cast to int
        $y = (int) $matches[1][1];
    }

    echo "<br/>"; echo $x;
    echo "<br/>"; echo $y; echo "<br/>";

    //---------------DB stuff --------------------

    $con = mysqli_connect('127.0.0.1:3306', 'root', 'root', 'test');
    if (mysqli_connect_errno()) {
        echo "Failed to connect to MySQL: ".mysqli_connect_error();
    }

    $sql1 = "INSERT INTO table2 (id,sent,pcount,ncount,flag)VALUES('$_POST[id]','$_POST[sent]','".$x."','".$y."','".$flag."')";

    if (!mysqli_query($con, $sql1)) {
        die('Error: '.mysqli_error($con));
    }
    echo "1 record added";
    mysqli_close($con);

    }
    ?>
    </html > </body >

Retrieving values of php var in mysql trigger

1 Answers1