why is mysqli insert not working?

Question

I'm trying to insert data into my MySQL table, but with no luck. My code looks like:

$mysqli = mysqli_connect("localhost","login info");

if ($mysqli->connect_errno) {
    echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}

if (isset($_POST['submit'])) {
    $number = $_POST['number'];
    $road = $_POST['road'];
    $postcode=$_POST['postcode'];
    $price=$_POST['price'];
    $type=$_POST['type'];
    $bedrooms=$_POST['bedrooms'];
    $agent=$_POST['agent'];
    $featured=$_POST['featured'];
    $keywords=$_POST['keywords'];

    $mysqli->query("
        INSERT INTO listings-rent
            (number, road, postcode, price, type, bedrooms, agent, featured, keywords)
        VALUES
            ('$number','$road','$postcode','$price','$type','$bedrooms','$agent','$featured','$keywords')");
}

The connection is fine, returns no errors.

You need to read about [SQL Injection](http://php.net/manual/en/security.database.sql-injection.php) vulnerability. — Michal M, Feb 09 '13 at 16:00
Why does it seem that so few PHP/MySQL developers have heard of parametrised queries? — Bruno, Feb 09 '13 at 16:00
Use backticks for your table name as @JW. suggested: `INSERT INTO \`listings-rent\`` — juergen d, Feb 09 '13 at 16:01
@juergend you certainly get the point. I don't know why others don't. — John Woo, Feb 09 '13 at 16:02
[Permitted characters in quoted and unquoted identifiers](http://dev.mysql.com/doc/refman/5.0/en/identifiers.html) — John Woo, Feb 09 '13 at 16:03
Unrelated to the question per se, but whoever comes this far having trouble with inserts make sure you got everything sorted out with transactions, it might be that you forget to commit the transaction — nakajuice, May 28 '17 at 09:34

score 6 · Accepted Answer · edited Feb 09 '13 at 16:07

6

Your table name should be enclosed with backtick since it contains a non-alphanumeric character.

INSERT INTO `listings-rent` (...) VALUES (...)

and also please do parameterize the values.

edited Feb 09 '13 at 16:07

juergen d

201,996
37
293
362

answered Feb 09 '13 at 15:56

John Woo

258,903
69
498
492

well I don't know why this answer gets massive downvotes `:D` – John Woo Feb 09 '13 at 16:06
will accept as first to answer + correct. will look into parametized values - anyone suggest a good, easy to understand resource? – rpsep2 Feb 09 '13 at 16:08
1

this article talks about [How to prevent SQL injection in PHP?](http://stackoverflow.com/questions/60174/how-to-prevent-sql-injection-in-php) but clearly shows some basic example and links on the extensions. – John Woo Feb 09 '13 at 16:10

dnapierata · Answer 2 · 2013-05-10T17:19:10.667

You table name has a - which is not a supported character for an unquoted table name in MySQL as stated in this documentation. Try this:

$mysqli->query("INSERT INTO `listings-rent` (number, road, postcode, price, type, bedrooms, agent, featured, keywords) VALUES ('$number','$road','$postcode','$price','$type','$bedrooms','$agent','$featured','$keywords')");

To view the error you can use echo $mysqli->error; mentioned here.

score 1 · Answer 3 · answered Feb 09 '13 at 16:05

probably the `` are missing in your table name

i will suggest :

    if (TRUE == $mysqli->query("INSERT INTO `listings-rent` (number, road, postcode, price, type, bedrooms, agent, featured, keywords) VALUES ('$number','$road','$postcode','$price','$type','$bedrooms','$agent','$featured','$keywords')"))

        echo "its working!"

    else

         echo $mysqli->error;

this way you will see the problem

other option i would suggest it to print the query that fails and insert it manually using phpmyadmin and check why it isnt working

score 0 · Answer 4 · answered Jun 24 '17 at 06:14

try this instead:

$mysqli = mysqli_connect("localhost", "your_db_username" , "your_db_password" , "database_name")

for local server, usually the username is root and password is null. So in that case copy paste this:

$mysqli = mysqli_connect("localhost", "root" , "" , "login info")

why is mysqli insert not working?

4 Answers4