PHP doesn't send data to MySQL

Question

Have some problem I couldn't find solution for, though searched through many sources (and questions here too). So, here it is.
With the PHP-code below I suppose to collect data from a HTML-form and send it to a local WAMP-server. But, though final check shows me "Success!", no new rows in the database's table are found, it stays empty. Names are correct, commands are (as I see it) too, so I just don't know what's wrong.
I hope you guys could help me. ^^

//Check if user submited a form
if (isset($_POST['submit'])) {
    //Check if from is properly filled
    if (empty($_POST['itemName']) || empty($_POST['itemPic']) || empty($_POST['itemPrice']) || empty($_POST['itemProvider'])) {
        echo '<script>alert ("Fill out the form please!")</script>';
    } else {
        $conn = new mysqli('localhost:3306', 'root', '', 'goods-review');
        //Check if connection established
        if (mysqli_connect_errno()) {
            exit('Connect failed: ' . mysqli_connect_error());
        }
        //Sending data
        $newItem = array('itemName' => $_POST['itemName'], 'itemPic' => $_POST['itemPic'], 'itemPrice' => $_POST['itemPrice'], 'itemProvider' => $_POST['itemProvider']);
        $sql = "INSERT INTO goods (itemName, itemPic, itemPrice, itemDate, itemProvider) VALUES ('" . $newItem['itemName'] . "', '" . $newItem['itemPic'] . "', '" . $newItem['itemPrice'] . "', date('Y:m:d, H:i:s'), '" . $newItem['itemProvider'] . "')";
        //Check if sent
        if ($sql) {
            echo '<script>alert ("Success!")</script>';
        } else {
            echo '<script>alert ("Error!")</script>';
        }
        $conn->close();
    }
}

with code as badly formatted as this, it's no wonder that you run into this sort of trouble — e4c5, Sep 07 '16 at 14:48
and because is related http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php?rq=1 — JustOnUnderMillions, Sep 07 '16 at 14:51
Thank you all, guys. I'm still newbie in PHP, so from time to time I make such emarassing mistakes. Tottaly missed that query. Will try to be more careful and mindful in future. — OLucky, Sep 07 '16 at 14:57

score 0 · Accepted Answer · answered Sep 07 '16 at 15:03

The code is just assigning a string value to a variable.

  $sql = "INSERT ...";

And the string value is not submitted to the database; it's not being executed as a SQL statement. There's nothing magical about the name of the variable. As far as PHP is concerned, the code is just assigning a value to a variable. That's it.

If you want to execute a SQL statement, you need to add code that actually does that. It shouldn't be difficult to find an example of how to do that.

IMPORTANT NOTE: The code in the question appears to create a SQL statement that is vulnerable to SQL Injection. A much better pattern is to use prepared statements with bind placeholders.

Reference: mysqli_prepare

If there's some (unfathomable) reason that you can't use prepared statements, then at a minimum, any potentially unsafe values that are included in the SQL text must be properly escaped.

Reference: mysqli_escape_string

Thank you, your answer is very educational! And I'll look further in security too. — OLucky, Sep 07 '16 at 15:15

score -2 · Answer 2 · edited May 23 '17 at 11:51

-2

If you have setup the $newItem array first.

Normaly you will validate the user-input and ensure that the user-input has no SQL injections in it. Read here about it: What is SQL injection?

After that

(You have to add $newItem['itemDate']=date('Y:m:d, H:i:s');)

$sql = "INSERT INTO goods (".implode(', ',array_keys($newItem)).")"
      ." VALUES ('".implode("', '",$newItem)."')";
if (mysqli_query($conn,$sql)){
    echo '<script>alert ("Success!")</script>';
} else {
    echo '<script>alert ("Error!")</script>';
}

If you are using this:

you dont have too keep an eye on the right field order
every field value becomes ' around them
you have less code to write
field count and order can change

Finally mysqli_query() returns FALSE if nothing is insert and you can check for that.

Sidenote: Try to use OOP Version of the MYSQLi Extention and Prepared Statments. Read about it here: mysqli, OOP vs Procedural

edited May 23 '17 at 11:51

Community

1
1

answered Sep 07 '16 at 15:00

JustOnUnderMillions

3,741
9
12

First downvote here, Why? Answered the question. How the SQL is created right AND how it add a row into Database. And in my comment above noted that SQLInjection issue. So why downvote? bad format? – JustOnUnderMillions Sep 07 '16 at 15:08
Thank you for this suggestions! I'd upvote you if I could. – OLucky Sep 07 '16 at 15:13
No problem, the downvoter has to comment here: `why?` and then im fine :) My help (if it helps) is always for free :) – JustOnUnderMillions Sep 07 '16 at 15:17
Not a downvoter, but FYI, your code is vulnerable to [SQL injection attacks](https://en.wikipedia.org/wiki/SQL_injection). Please read [this post](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) to learn more on how to prevent it. – Pang Sep 13 '16 at 00:55
@Pang In my _world_ the `$newItem` variable is normalized and validated at this point. And why we have in 2016 still always to point out: `code is vulnerable`, `dont use mysql is deprecated`, `dont mix php and html`..... maybe StuckOverflow should show an Info Box on that, when someone write a PHP Question. Next time i only drop some links to related questions&answers and just say: Try to _read_ before you _write_. And that here is all for free.... may thats the real problem. Instant, ForFree & no personal research by the OP. And uncomment downvotes too ... What is StackOverflow really about? – JustOnUnderMillions Sep 13 '16 at 10:49
@Pang Funny is that i linked this http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php?rq=1 in my comments on the OP question above and noted it here im first comment . :) – JustOnUnderMillions Sep 13 '16 at 10:54
Downvote me to hell here. Or just make a better answer. – JustOnUnderMillions Sep 13 '16 at 10:56
@Pang Updated my post. But because of the downvotes nobody will read this :) – JustOnUnderMillions Sep 14 '16 at 11:14

PHP doesn't send data to MySQL

2 Answers2