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.

Solution 1

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.

Solution 2

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.

Solution 3

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

Comments

[Michal M]

You need to read about SQL Injection vulnerability.

[Bruno]

Why does it seem that so few PHP/MySQL developers have heard of parametrised queries?

[juergen d]

Use backticks for your table name as @JW. suggested: INSERT INTO `listings-rent`

[John Woo]

@juergend you certainly get the point. I don't know why others don't.

[John Woo]

Permitted characters in quoted and unquoted identifiers

[nakajuice]

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

[John Woo]

well I don't know why this answer gets massive downvotes :D

[rpsep2]

will accept as first to answer + correct. will look into parametized values - anyone suggest a good, easy to understand resource?

[John Woo]

this article talks about How to prevent SQL injection in PHP? but clearly shows some basic example and links on the extensions.