database insert does not work

Question

hey i have a problem with inserting some data in my database:

define('SECURE', true);
include "storescripts/connect_to_mysql.php";

    $txn_id = 123456789101234567;
    $payer_email = "irgendwas@gmx.de";
    $mc_gross = "amount";

    $sql = "SELECT COUNT(*) AS count FROM `trans` WHERE `txn_id` = $txn_id";
    $q = mysqli_query($mysqli, $sql);
    $f = mysqli_fetch_array($q);

    if($f['count'] > 0) {
        echo "Transaction already processed"; 
    } else { 
        $insert = mysqli_query($mysqli, "INSERT INTO trans (`txn_id`, `payer_email`,`mc_gross`) 
                                                    VALUES ($txn_id,$payer_email,$mc_gross)");
        if($insert = 1) {
            echo "inserted";
        } else {
            echo "not inserted";
        }
    }

As a result i get: "inserted", but i have no data in my database..anyone can help me? where is the bug?

edit: this is my table:

define('SECURE', true);
require "connect_to_mysql.php";  
$sqlCommand = "CREATE TABLE trans (
             id int(11) NOT NULL auto_increment,
             txn_id varchar(255) NOT NULL,
             payer_email varchar(255) NOT NULL,
             mc_gross int(255) NOT NULL,
             PRIMARY KEY (id),
             UNIQUE KEY (txn_id))";
if ($mysqli->query($sqlCommand)) { 
    echo "Your trans table has been created successfully!"; 
} else { 
    echo "CRITICAL ERROR;".$mysqli->error; 
}

I suggest you use `email@example.com` instead of a/your probable valid Email address (wink) *Just saying* — Funk Forty Niner, Aug 01 '13 at 18:38
`$insert = 1` is _assigning_ (which always works, and always returns true), not checking equality. `if ($insert)` will test for success... — Michael Berkowski, Aug 01 '13 at 18:39
And `$payer_email` looks to be an unquoted string, which breaks your SQL, same with the string `amount`. — Michael Berkowski, Aug 01 '13 at 18:39
[Read this question](http://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-backticks) for advice on how to properly quote all these values in the SQL string. Consider switching this to a MySQLi prepared statement to avoid the quoting and escaping issues entirely. — Michael Berkowski, Aug 01 '13 at 18:44
You need to use [`bind_param`](http://php.net/manual/en/mysqli-stmt.bind-param.php) to add values like `$pid` to your query or you'll end up with massive [SQL injection bugs](http://bobby-tables.com/). **DO NOT** use string interpolation to compose queries. — tadman, Aug 01 '13 at 18:47

score 0 · Answer 1 · answered Aug 01 '13 at 18:51

0

The reason you are getting "inserted" is because your if is setting the variable to 1 and is resulting true. Use double equals to compare.

right:

if ($insert == 1)

wrong:

if ($insert = 1)

As far as your sql there seems to be errors with your queries. $txn_id and $payer_email are both varchars which require you to use quotes since it is a string

answered Aug 01 '13 at 18:51

Scarecrow

205
1
7

1

Or just: " `if ( $insert ) {` " – spencer7593 Aug 01 '13 at 19:09

spencer7593 · Answer 2 · 2013-08-01T18:59:46.090

The string literals in your SQL statement need to be enclosed in single quotes. Your generated SQL text looks like this:

    VALUES (123456789101234567,someone@email.de,amount)

But it should really look like this:

    VALUES ('123456789101234567','someone@email.de','amount')
            ^                  ^ ^                ^ ^      ^

BTW... when evaluated as an integer, that string literal 'amount' is going to be interpretted as zero.

You should consider using prepared statements with bind variables, instead of including variables in the SQL text. (There are lots of examples of that on StackOverflow.)

To check whether mysqli_query succeeded or not:

$sql = "INSERT INTO ... ";
if ( mysqli_query($mysqli, $sql) ) {
   // sql statement executed without error
} else {
   // sql statement execution raised an error
}

If the query looked like `VALUES (?,?,?)` and `bind_param` was used, this wouldn't be an issue. — tadman, Aug 01 '13 at 19:06

database insert does not work

2 Answers2