'INSERT INTO MySQL statement failing [closed]

I am dynamically forming MySQL statements in PHP and can't figure out why this one is failing to execute:

    INSERT INTO users ( `email`, `password`, `first_name`, `last_name`, `url`,
   `description`, `media`, `tags`, `zip`, `country`, `lat`, `lon`, `city`, `state`, 
   `datetime_joined`, `API_key`, `verified`, `likes`, `email_confirmation_code`,
   `email_confirmed`) VALUES ( '[email protected]', 
   'f1e5aeb519396a87bd2a90e6a680d18713b1ecbe', 'Brannon', 'Dorsey',
   'brannondorsey.com', 'description', 'sculpture, photography, creative code',
   'saic, chicago, richmond, young arts', '60601', 'us', '41.8858', '-87.6181',
   'Chicago', 'Illinois', '2013-06-26T23:50:29+0200',
   '7e852a3e97257b563ffbb879d764ce56110ccb70', '0', 
   '0','c35bad0dc9b058addbf47eef8dda2b124528751e', '0')

I have checked the spelling and format of the columns that I am tring to insert into as well as the table name and everything is correct. Does anyone know what might be up? I am not trying to make busy work for other people I just can't figure out whats going on here.

Here is the statement that I created my table with:

    CREATE TABLE users (
    id mediumint(8) unsigned NOT NULL auto_increment,
    email varchar(255) default NULL,
    password varchar(255),
    url TEXT default NULL,
    description TEXT default NULL,
    city varchar(255),
    state TEXT default NULL,
    country varchar(100) default NULL,
    zip varchar(10) default NULL,
    datetime_joined varchar(255),
    media varchar(255) default NULL,
    tags varchar(255) default NULL,
    API_key varchar(255),
    verified mediumint default NULL,
    first_name varchar(255) default NULL,
    last_name varchar(255) default NULL,
    API_hits mediumint default NULL,
    API_hit_date varchar(255),
    likes mediumint default NULL,
    lat varchar(30) default NULL,
    lon varchar(30) default NULL,
    email_confirmation_code varchar(255),
    email_confirmed mediumint default NULL,
    PRIMARY KEY (id),
    FULLTEXT(`first_name`, `last_name`, `email`, `url`, `description`, `media`, `tags`, `city`, `state`, `country`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;

I have tried removing the single quotes from all numbers but that didn't work. Also, most strangely, when I echo the failed query into the browser and then copy & paste it into PhpMyAdmin's sql input box the row inserts... Any ideas?

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 '\'[email protected]\', \'f1e5aeb519396a87bd2a90e6a680d18713b1ecbe\', \'B' at line 1
mysqlsql


Solution 1:[1]

I dug through all of the string manipulation I was doing to dynamically build the query and found that I was using real_escape_string for a part of the query not just the values going into it. That explains why when copied from the browser the query worked.

Solution 2:[2]

Not sure, but ur lat and long are inserted as strings, try removing the quotes and insert them as numerals. So with all ur numerical fields.

Solution 3:[3]

As far as I can tell, your columns and values match up, but unless you show us the statement used to create the table, I can't be sure it fits your schema. It would also be helpful to see the PHP that creates this query. I wouldn't be surprised if there's a mismatch somewhere that we can't see, and you're simply overlooking (can't blame you; it's quite easy to do to your own code).

Other than adding those to bits of code, I have one other possible explanation: Depending on how picky your sql implementation is, maybe it's the missing ; after your statement.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Brannon
Solution 2 Tamim Al Manaseer
Solution 3 Stspurg

Related Questions