How to execute sql query when clicking a link/button PHP

Question

I am using WordPress and have a table with Name and Price data.

I want a link, in a third column called "Add to my portfolio", by clicking this link the Name and Price data should be added to the table user_portfolio.

This is my function that creates the table:

function trendy2($Name, $Price, $user_id){
    echo "<tr>
    <td>$Name</td>
    <td>$Price</td>
    <td><a href=\"https://signal-invest.com/signals/macd/\">Add to portfolio</a></td></tr>";
}

$con = new mysqli(HOST,USER,PASS, DB,PORT);

Below is the part of my PHP code that I use to call $results_query_uptrend, my SQL query that gets me Name and Price.

For each row in $results_query_uptrend Name and Price is retrieved and added to the table from the function.

get_current_user_id() gets the User_ID which is used to identify the user.

    <table >
         <tr>
            <th>NAME</th>
            <th>PRICE TODAY</th>
            <th>ADD TO PORTFOLIO</th>
         </tr>
         <?php
            foreach ($results_query_uptrend as $r){
                $name = $r["Name"];
                $price = $r['Price'];
                
                $sql = ("INSERT INTO user_portfolio (Name, Price, User_ID)
                VALUES ('$name','$price',get_current_user_id())");

                $sql_preped = $con->prepare($sql);

                echo trendy2($name, $price, $sql_preped);                                               
            }
        ?>
    </table>

I can click the link, but my database does not update.

EDIT: Played around and create a different solution inspired by this answer.

Created a new file, insert_to_db.php, which includes the SQL query.


<?php
    if(isset($_POST['id'])){
        echo("You clicked button one!");
        $sql = ("INSERT INTO user_portfolio (Name, Price, User_ID)
                VALUES ('$name','$price',get_current_user_id())");
        $sql_preped = $con->prepare($sql);
        $sql_preped -> execute();
    }
    else {
    echo" dhur";
    }
?>

Updated the trendy2() function to add a button in the table instead of hyperlink:

function trendy2($Name, $Price, $percent, $trend, $user_id){//used to create the winners and losers table
    $link = "https://signal-invest.com/tick/?ticker=";
    echo "<tr>
    <td><a href=\"$link$Name\" style='color:#616161;' >$Name</a></td>
    <td>$Price</td><td style='color: "; echo ($percent < 0 ? '#FF0000' : '#4ca64c'); echo ";'>$percent%</td><td>$trend</td><td><form method='POST' action='insert_to_db.php'>
        <input type='submit' name='id' value='$user_id'/>    </form></td></tr>";}

<table >
         <tr>
            <th>NAME</th>
            <th>PRICE TODAY</th>
            <th>ADD TO PORTFOLIO</th>
         </tr>
         <?php
            foreach ($results_query_uptrend as $r){
                $name = $r["Name"];
                $price = $r['Price'];
                echo trendy2($name, $price, $user_id);                                              
            }
        ?>
    </table>

The issue here is, that I can click button, but data is not inserted to database

You should be using `wpdb` - https://developer.wordpress.org/reference/classes/wpdb/insert/ then. Regardless, your issue is likely 2 part. 1, you never actually `execute()` your query. 2, clicking the link you've created wouldn't trigger your SQL query because it would have executed by the time you echoed out the link. — prieber, May 14 '21 at 17:14
**Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) — Dharman, May 14 '21 at 18:16
Thanks. I will take this into consideration. Do you have any suggestions to my issue? — d00me, May 14 '21 at 19:06
You should for sure try to implement the query before calling the table. Perhaps this post can be of help: https://stackoverflow.com/questions/16660099/how-to-execute-mysql-on-button-click — doomdaam, May 14 '21 at 19:47
I find it difficult to implement that solution to mine but thanks anyway :) — d00me, May 14 '21 at 20:06

How to execute sql query when clicking a link/button PHP

0 Answers0