April 1, 2012

SQL syntax error while using MySQL and PHP

Question by liamwli

I am trying to make a thing, but I have hit a problem. I have tried all I know, but I am new to MySQL, so I have hit a dead end.

This code:

<?php
    require('cfg.php');
    mysql_connect($server, $user, $pass) or die(mysql_error());
    mysql_select_db($database) or die(mysql_error());

    if (isset($_GET['name'])){
        $name = $_GET['name'];
    }
    else
        if (isset($_POST['submit'])){
            $name = $_POST['name'];
            $name1 = $_POST['name1'];
            $name2 = $_POST['name2'];
            $name3 = $_POST['name3'];
            mysql_query("INSERT INTO data (name, name1, name2, name3) VALUES($name, $name1, $name2, $name3 ) ") or die(mysql_error());
            echo ("Data entered successfully!");
        }
?>

<html>
    <head>
        <title>Random giffgaff simmer</title>
    </head>
    <body>
        <form action="" method="post">
            <p>Your Username: <input type="text" name="name"></p>
            <p>Username 1: <input type="text" name="name1"></p>
            <p>Username 2: <input type="text" name="name2"></p>
            <p>Username 3: <input type="text" name="name3"></p>
            <p>Username 4: <input type="text" name="name4"></p>
            <p>Username 5: <input type="text" name="name5"></p>
            <p>Username 6: <input type="text" name="name6"></p>
            <p><input type="submit" name="submit" value="Submit"></p>
        </form>
    </body>
</html>

Brings this error:

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 ‘ )’ at line 1

Now, that would tell me that this SQL code has a syntax error:

INSERT INTO data (name, name1, name2, name3) VALUES($name, $name1, $name2, $name3 )

But I don’t think I can see one?

Answer by Starx

You haven’t quoted your query. You should quote every field like this

INSERT INTO data (name, name1, name2, name3) VALUES('$name', '$name1', '$name2', '$name3' )

As an tribute to TheCommonSense, I am providing a mysqli version using correct prepared statement for data safety

$db = new mysqli(...);
$stmt = $db -> prepare("INSERT INTO data (name, name1, name2, name3) VALUES(?, ?, ?, ?)");
$stmt -> bind_param("ssss", $name, $name1, $name2, $name3);
$stmt -> execute();
$db -> close()

Author: Nabin Nepal (Starx)

Hello, I am Nabin Nepal and you can call me Starx. This is my blog where write about my life and my involvements. I am a Software Developer, A Cyclist and a Realist. I hope you will find my blog interesting. Follow me on Google+

...

Please fill the form - I will response as fast as I can!