PHP Mysqli Query execute from table data query

Question

I have sql query that saved on table.

tbl_query

SELECT SUM(simpanan_wajib) AS TOTAL FROM tb_simpanan WHERE badgeid_fk = '$getBadgeID'

Then on PHP code:

$query = mysqli_query($con, "SELECT * FROM tbl_query");
while($data = mysqli_fetch_array($query))
{
    //SELECT SUM(simpanan_wajib) AS TOTAL FROM tb_simpanan WHERE badgeid_fk = '$getBadgeID'
    $getQuery = $data['sql_query'];

    $qTotal = mysqli_query($con, $getQuery);
    $dTotal = mysqli_fetch_array($qTotal);

    echo $dTotal['TOTAL'];
}

When I tried to run that code, it show me result of total is 0. But if I remove this WHERE badgeid_fk = '$getBadgeID' on query data, the result is OK not 0.

How to keep execute the query even if there is an variable '$getBadgeID'

if you run with `$getBadgeID` then you must need to define in your php file otherwise it will give u undefined variable — devpro, Mar 19 '19 at 08:26
I recommend you to use prepared statment http://php.net/manual/en/pdo.prepare.php — executable, Mar 19 '19 at 08:27
second, is this query `SELECT SUM(simpanan_wajib) AS TOTAL FROM tb_simpanan WHERE badgeid_fk = '$getBadgeID'` inside the `tbl_query` table? as a raw query? — devpro, Mar 19 '19 at 08:27
if $getBadgeID is already defined, then it will work but again check what are u getting `print_r($dTotal)` and share the result — devpro, Mar 19 '19 at 08:27
I have set $getBadgeID value. And the result of print is: Array ( [0] => [TOTAL] => ) — HiDayurie Dave, Mar 19 '19 at 08:29
then u must need to check this query in your phpmyadmin `SELECT SUM(simpanan_wajib) AS TOTAL FROM tb_simpanan WHERE badgeid_fk = 'enter your assumption value'` — devpro, Mar 19 '19 at 08:30
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/190285/discussion-between-hidayurie-dave-and-devpro). — HiDayurie Dave, Mar 19 '19 at 08:30
You wrote `WHERE badgeid_fk = '$getBadgeID'` so of course it will filter rows and sum() only them. — nice_dev, Mar 19 '19 at 08:31

devpro · Accepted Answer · 2019-03-19T09:34:29.313

3

PHP treating this variable as a string, thats why result generating this query

SELECT SUM(simpanan_wajib) AS TOTAL FROM tb_simpanan WHERE badgeid_fk = '$getBadgeID' // its not converting your variable with 150502

Here you can use alternate name or you can use with delimiter like:

Your current query is:

SELECT SUM(simpanan_wajib) AS TOTAL FROM tb_simpanan WHERE badgeid_fk = '$getBadgeID'

Change your query with:

SELECT SUM(simpanan_wajib) AS TOTAL FROM tb_simpanan WHERE badgeid_fk = ':getBadgeID'

Now, you need to use str_replace to replace delimiter with your variable like:

while($data = mysqli_fetch_array($query))
{
    $getQuery = str_replace(":getBadgeID", $getBadgeID , $data['sql_query']);    
}

Why i am using delimiter here, because your variable $getBadgeID having defined value inside your php script and its not dynamic.

In our chat conversation, @executable suggest an another solution to use prepared statement.

Edit:

As per discussion with @Bananaapple, i am adding this comment for future visitors, Prepared Statement is an another solution which is more secure, if you want to avoid SQL injection, then choose prepared statement.

edited Mar 19 '19 at 09:34

answered Mar 19 '19 at 08:55

devpro

16,184
3
27
38

This makes the code vulnerable to SQL injection attacks. Instead of `str_replace` check out this https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php – Bananaapple Mar 19 '19 at 09:06
1

@Bananaapple: yes, i already shared this suggestion to OP, check our long conversation, about the possible solution, OP ask as to share example with delimeter. he ask us which one is the best one, i suggest him, prepared statement , check conversation.. and i also suggest to other person to post your answer with prepared statement. conversation https://chat.stackoverflow.com/rooms/190285/discussion-between-hidayurie-dave-and-devpro – devpro Mar 19 '19 at 09:09
Yeah I saw that but you should mention it in your answer as well as anyone else that comes across this question with a similar problem will not be reading the chat :-) – Bananaapple Mar 19 '19 at 09:17
@Bananaapple: thank you for this, i have added in answer, please check – devpro Mar 19 '19 at 09:34

executable · Answer 2 · 2019-03-19T15:14:50.410

The recommend way is to use the prepared statements to sanitize the query and protect you from SQL injection. The following comic give an example of what is SQL injection.

For answering the question we discover that in your query the variable $getBadgeID was read as text and not as variable. I recommend you to use this code which use the prepared statements :

<?php
$conn = new mysqli("HOST", "USER", "SECRET", "DATABASE");
if($stmt = $conn->prepare("SELECT SUM(simpanan_wajib) AS TOTAL FROM tb_simpanan WHERE badgeid_fk = ?")) {
    $stmt->bind_param("s", $getBadgeID);
    $stmt->execute(); 
    $result = $stmt->get_result();
    while($row = $result->fetch_assoc()) {
        $total = $row['TOTAL'];
    }
    $stmt->close();
}
$conn->close();
var_dump($total);

If you want more debugging :

<?php
if(isset($getBadgeID) and $getBadgeID != ""){
    $conn = new mysqli("HOST", "USER", "SECRET", "DATABASE");
    if($stmt = $conn->prepare("SELECT SUM(simpanan_wajib) AS TOTAL FROM tb_simpanan WHERE badgeid_fk = ?")) {
        $stmt->bind_param("s", $getBadgeID);
        $stmt->execute(); 
        $result = $stmt->get_result();
        while($row = $result->fetch_assoc()) {
            $total = $row['TOTAL'];
        }
        $stmt->close();
    }else{
        echo "Query is wrong";
    }
    $conn->close();
    var_dump($total);
}else{
    echo 'Variable $getBadgeID is empty';
}

correct my friend, for security this is the best solution. thumbs up — devpro, Mar 19 '19 at 09:10

PHP Mysqli Query execute from table data query

2 Answers2