Syntax error

Question

janicemurby 7 Posting Whiz in Training

8 Years Ago

Hi im attempting to copy a php code and get it to work with a script im trying to create and i have come across the error below can anyone tell me whats going off any help would be much appreicated x

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 'to = "", meettype = "", county = "", cnumber = "", fee ' at line 2

here is my insert into database line

$query = 'insert into booking set Firstname = "'.$Firstname.'", address1 = "'.$address1.'", days = "' .$days.'", months = "'.$months.'", bookingtime = "'.$bookingtime.'", address2 = "'.$address2.'", lastname = "' .$lastname.'", cdays = "' .$cdays. '", cmonths = "' .$cmonths. '", duration = "' .$duration. '", datefrom = "' .$datefrom. '", to = "' .$to. '", meettype = "' .$meettype. '", county = "' .$county. '", cnumber = "' .$cnumber. '", fee = "' .$fee. '", pcode = "' .$pcode. '", comments = "' .$comments. '", user_id = ' . $userid  ;

$insert = mysql_query ( $query ) or die(mysql_error()) ;

if($insert)
{
    //echo "added" ;
    header("location: confirm.php");
    exit;   
}

and heres the database table

CREATE TABLE IF NOT EXISTS `booking` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `Firstname` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `days` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `months` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `lastname` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `bookingtime` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `address2` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `cdays` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `cmonths` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `address1` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `town` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `cdate` date NOT NULL,
  `datefrom` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `date` date NOT NULL,
  `to` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `duration` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `meettype` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `county` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `fee` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
  `pcode` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
  `comments` text COLLATE utf8_unicode_ci NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

insert php sql-server

5 Contributors
13 Replies
285 Views
16 Hours Discussion Span
Latest Post 8 Years Ago Latest Post by diafol

All 13 Replies

broj1 356 Humble servant

8 Years Ago

You use to as the field name but to is also a mysql reserved word. So if you realy want to use it for a field name, enclose it in backticks like this:

..., `to` = "' .$to. ...

Better practice is to completely avoid using keywords for field names. And keep this link handy:

https://dev.mysql.com/doc/refman/5.1/en/keywords.html

And drop the ** deprecated mysql** extension. It is ages old, unsuported and on it's way to become history soon. Use PDO or at least mysqli and youur future as a web developer will be brighter.

Edited 8 Years Ago by broj1

broj1 356 Humble servant

8 Years Ago

Now, it is hard to tell which double quote is causing the error. I prefer to write queries in a bit cleaner form, using single quotes for mysql string values and double quotes for PHP string. This way you can omit all the concatenations and use variables within double quotes. Like this:

$query = "INSERT INTO booking SET 
    Firstname = '$Firstname', 
    address1 = '$address1', 
    days = '$days', 
    months = '$months', 
    bookingtime = '$bookingtime', 
    address2 = '$address2', 
    lastname = '$lastname', 
    cdays = '$cdays', 
    cmonths = '$cmonths', 
    duration = '$duration', 
    datefrom = '$datefrom', 
    dteCallTimeTo = '$to', 
    meettype = '$meettype', 
    county = '$county', 
    cnumber = '$cnumber', 
    fee = '$fee', 
    pcode = '$pcode', 
    comments = '$comments', 
    user_id = $userid";

Now debugging is much easier. You can also display the query and test it. Put this temporary debug code on line 2 above (just before the insert statement):

die($query);

This will display the query and stop the script. You can inspect the displayed query and/or paste it into phpmyadmin to test it. You can also post it here.

Edited 8 Years Ago by broj1

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

janicemurby 7 Posting Whiz in Training · Answer 1 · 2015-07-06T21:03:33+00:00

renamed to into dteCallTimeTo now im getting

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 2

janicemurby 7 Posting Whiz in Training · Answer 2 · 2015-07-06T21:51:11+00:00

ive put that in and im getting the following hun

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 = '', address1 = '', days = '', mon' at line 2

janicemurby 7 Posting Whiz in Training · Answer 3 · 2015-07-06T21:59:50+00:00

sussed it ty for your help it did the following

$query = ("INSERT INTO booking SET 
        firstname = '$firstname', 
        address1 = '$address1', 
        days = '$days', 
        months = '$months', 
        bookingtime = '$bookingtime', 
        address2 = '$address2', 
        lastname = '$lastname', 
        cdays = '$cdays', 
        cmonths = '$cmonths', 
        duration = '$duration', 
        datefrom = '$datefrom', 
        dteCallTimeTo = '$dteCallTimeTo', 
        meettype = '$meettype', 
        county = '$county', 
        cnumber = '$cnumber', 
        fee = '$fee', 
        pcode = '$pcode', 
        comments = '$comments', 
        user_id = $userid ;
$insert = mysql_query ( $query ) or die(mysql_error())") ;

janicemurby 7 Posting Whiz in Training · Answer 4 · 2015-07-06T22:24:41+00:00

ive sorted the sql error out and after testing form nothing is going to database ive check all the form eliments against the database and copied database table name to sql string to make sure there are no typos and all is ok but again nothing is going to database

Lau_1 12 Junior Poster in Training · Answer 5 · 2015-07-06T23:53:01+00:00

can u post ur full query used for insert database?

And do u add ") symbol at the end of $query?

user_id = '$userid'") ;

janicemurby 7 Posting Whiz in Training · Answer 6 · 2015-07-07T01:07:31+00:00

hi hun i have this

$query = ("INSERT INTO booking SET 
            die($query);
            firstname = '$firstname', 
            address1 = '$address1', 
            days = '$days', 
            months = '$months', 
            bookingtime = '$bookingtime', 
            address2 = '$address2', 
            lastname = '$lastname', 
            cdays = '$cdays', 
            cmonths = '$cmonths', 
            duration = '$duration', 
            datefrom = '$datefrom', 
            dteCallTimeTo = '$dteCallTimeTo', 
            meettype = '$meettype', 
            county = '$county', 
            cnumber = '$cnumber', 
            fee = '$fee', 
            pcode = '$pcode', 
            comments = '$comments', 
            user_id = $userid ;
    $insert = mysql_query ( $query ) or die(mysql_error())") ;

    if($insert)
    {
        //echo "added" ;
        header("location: confirm.php");


    exit;   
}

janicemurby 7 Posting Whiz in Training · Answer 7 · 2015-07-07T01:14:38+00:00

hi hun i have this

$query = ("INSERT INTO booking SET 
            die($query);
            firstname = '$firstname', 
            address1 = '$address1', 
            days = '$days', 
            months = '$months', 
            bookingtime = '$bookingtime', 
            address2 = '$address2', 
            lastname = '$lastname', 
            cdays = '$cdays', 
            cmonths = '$cmonths', 
            duration = '$duration', 
            datefrom = '$datefrom', 
            dteCallTimeTo = '$dteCallTimeTo', 
            meettype = '$meettype', 
            county = '$county', 
            cnumber = '$cnumber', 
            fee = '$fee', 
            pcode = '$pcode', 
            comments = '$comments', 
            user_id = $userid ;
    $insert = mysql_query ( $query ) or die(mysql_error())") ;

    if($insert)
    {
        //echo "added" ;
        header("location: confirm.php");


    exit;   
}

Lau_1 12 Junior Poster in Training · Answer 8 · 2015-07-07T01:30:02+00:00

$query = "INSERT INTO booking SET 
            firstname = '$firstname', 
            address1 = '$address1', 
            days = '$days', 
            months = '$months', 
            bookingtime = '$bookingtime', 
            address2 = '$address2', 
            lastname = '$lastname', 
            cdays = '$cdays', 
            cmonths = '$cmonths', 
            duration = '$duration', 
            datefrom = '$datefrom', 
            dteCallTimeTo = '$dteCallTimeTo', 
            meettype = '$meettype', 
            county = '$county', 
            cnumber = '$cnumber', 
            fee = '$fee', 
            pcode = '$pcode', 
            comments = '$comments', 
            user_id = '$userid'" ;
 $insert = mysql_query($query,$your_Connection) or die("Insertion Failed:" . mysql_error());
    if($insert)
    {
        //echo "added" ;
        header("location: confirm.php");
        exit();   
    }

As @broj1 said, drop the ** deprecated mysql** extension. Use PDO or mysqli.

broj1 356 Humble servant Featured Poster · Answer 9 · 2015-07-07T06:42:04+00:00

As Lau_1 pointed out you had a double quote missing at the end of the query.

network18 15 Practically a Master Poster · Answer 10 · 2015-07-07T11:06:29+00:00

$query = "INSERT INTO booking  
            (firstname, 
            address1, 
            days, 
            months, 
            bookingtime, 
            address2, 
            lastname, 
            cdays, 
            cmonths, 
            duration, 
            datefrom, 
            dteCallTimeTo, 
            meettype, 
            county, 
            cnumber', 
            fee, 
            pcode, 
            comments, 
            user_id
            ) values ('$firstname', 
            '$address1', 
            '$days', 
            '$months', 
            '$bookingtime', 
            '$address2', 
            '$lastname', 
            '$cdays', 
            '$cmonths', 
            '$duration', 
            '$datefrom', 
            '$dteCallTimeTo', 
            '$meettype', 
            '$county', 
            '$cnumber', 
            '$fee', 
            '$pcode', 
            '$comments', 
            '$userid')";

you were mixing insert and update syntax.

diafol · Answer 11 · 2015-07-07T12:24:45+00:00

MySQL allows the SET syntax for single record INSERT. It is not peculiar to UPDATE However, VALUES syntax needs to be used for multiple insert within one query.

As noted by others, this really needs mysqli or PDO. I can't believe people are still using this deprecated code. You can use a prepared statement for this, rather than trying to sanitize each variable assuming that they are being santized, that is.

Syntax error

Recommended Answers Collapse Answers

All 13 Replies

Recommended Answers