My INSERT statement not working both in my php script and in phpmyadmin sql interface

Question

I have been developing a website on localhost with xampp and everything worked perfect. so i moved to a web host for the first time. I have edited my connection string to fit the web server and it connects fine but when i tried testing the registration page i designed it doesn't insert data into the database. I tried a simple insert statement on a separate script

<!DOCTYPE html>
<html>
    <body>
    <?php
    $conn = mysql_connect("localhost", "my_db_user_name", "my_db_password");
    $db = mysql_select_db("my_db_name");

    $query1 = mysql_query("INSERT INTO users firstname VALUES 'Patrick'",$conn);
    if($query1) {
        echo "Yes";
    } else {
        echo "didn't work";
    }
    echo mysql_error($query1);
    ?>
    </body>
</html>

It returned the didn't work and didn't insert anything neither did it echo any error. But when i tried a select statement and echo the result of the query it worked so its safe to say my connection is valid. I also went to my cpanel phpmyadmin interface and tried the same insert statement it didn't work but returned:

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'firstname VALUES 'Patrick'' at line 1.

I tried with and without back ticks the same thing. but i can select query.

Use the correct syntax: http://dev.mysql.com/doc/refman/5.7/en/insert.html. — Gordon Linoff, Dec 07 '16 at 13:06
***Please [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php).*** [These extensions](http://php.net/manual/en/migration70.removed-exts-sapis.php) have been removed in PHP 7. Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [PDO](http://php.net/manual/en/pdo.prepared-statements.php) and [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and consider using PDO, [it's really pretty easy](http://jayblanchard.net/demystifying_php_pdo.html). — Jay Blanchard, Dec 07 '16 at 13:36
[Little Bobby](http://bobby-tables.com/) says ***[your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php)***. Even [escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) is not safe! — Jay Blanchard, Dec 07 '16 at 13:37
@JayBlanchard - seemingly nor is PDO by default (where it emulates prepared statements for MySQL) in the same edge case. — Kickstart, Dec 09 '16 at 12:08

score 3 · Answer 1 · answered Dec 07 '16 at 13:05

You missed the correct syntax for the insert.

INSERT INTO table_name (column1, column2, column3,...)
VALUES (value1, value2, value3,...)

Example from w3schools:

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 

$sql = "INSERT INTO MyGuests (firstname, lastname, email)
VALUES ('John', 'Doe', 'john@example.com')";

if ($conn->query($sql) === TRUE) {
    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

$conn->close();
?>

http://www.w3schools.com/php/php_mysql_insert.asp

score 0 · Answer 2 · answered Dec 07 '16 at 13:06

0

Your query needs to look like this

INSERT INTO (columns) VALUES (values)

So you will get

INSERT INTO users (firstname) VALUES ('Patrick');

answered Dec 07 '16 at 13:06

Sven Buis

141
2

score -2 · Answer 3 · answered Dec 07 '16 at 13:07

Dude... one second google, and you would have your answer. http://www.w3schools.com/sql/sql_insert.asp

Also, the error says there is a SYNTAX ERROR. It is so difficult to understand? Why you don't simply check your syntax?

INSERT INTO users (firstname) VALUES ('Patrick')

My INSERT statement not working both in my php script and in phpmyadmin sql interface

3 Answers3