Discussions on this article:

When working with programs that have a database, you face the possibility of having database errors as well as regular php errors. This guide explains what the 1064 error is, some likely causes, and general troubleshooting

Causes for the 1064 error

This error message may seem cryptic at first. That is because it is a general MySQL error pointing to a syntax error of some sort in the SQL Query statement. This error can have multiple causes which are explained below.

Using reserved words

Every version of MySQL has its own list of reserved words. These are words that are used for specific purposes or perform specific functions within the MySQL engine. If you attempt to use one of these reserved words, you will receive the 1064 error. For example, below is a short SQL query that uses a reserved word as a table name.

CREATE TABLE alter (first_day DATE, last_day DATE);

How to fix it:
Just because the word alter is reserved does not mean it cannot be used, it just has special requirements to use it as the MySQL engine is trying to call the functionality for the alter command. To fix the issue, you will want to surround the word with backticks (“`”), this is usually the button just to the left of the "1" button on the keyboard. The code block below shows how the code will need to look in order to run properly.

CREATE TABLE `alter` (first_day DATE, last_day DATE);

Inserting incompatible data types into a field

One of the most common causes for the 1064 error is when a SQL statement is trying to insert a value for a data column that is of a different type. Simply put, if you are trying to place a number into a text field, a date in a number field, or text into a numeric field you will get the error.

Below is a simple example of a query that is trying to insert text into a numeric field, meaning the table structure has the id field set to accept numbers.

UPDATE table1 SET id = 'none';

How to fix it:
Since the id field is set as an INT (integer: numeric, whole number) field, the word 'none' is not correct. Changing the value to a number will fix the issue. If you are unsure what data type the columns are, read our article on How to determine the data type of a data column in phpMyAdmin.

Below is the syntax for the correct query statement.

UPDATE table1 SET id = 0;

Obsolete Commands

Some commands that were deprecated (slated for removal but still allowed for a period of time) eventually go obsolete. This means that the command is no longer valid in the SQL statement. One of the more common commands is the 'TYPE' command. This has been deprecated since MySQL 4.1 but was finally removed as of version 5.1, where it now gives a syntax error. The 'TYPE' command has been replaced with the 'ENGINE' command. Below is an example of the old version:

CREATE TABLE t (i INT) TYPE = INNODB;
This should be replaced with the new command as below:
CREATE TABLE t (i INT) ENGINE = INNODB;

Summary

As you can see there is more than one cause for this type error within MySQL code. This list will be updated as more specific instances are reported.

Did you find this article helpful?

We value your feedback!

Why was this article not helpful? (Check all that apply)
The article is too difficult or too technical to follow.
There is a step or detail missing from the instructions.
The information is incorrect or out-of-date.
It does not resolve the question/problem I have.
How did you find this article?
Please tell us how we can improve this article:
Email Address
Name

new! - Enter your name and email address above and we will post your feedback in the comments on this page!

Like this Article?

Related Questions

Here are a few questions related to this article that our customers have asked:
Ooops! It looks like there are no questions about this page.
Would you like to ask a question about this page? If so, click the button below!
Ask a Question
2013-11-01 7:40 am
Hi, I have got this 1064 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 'ORDER BY content.created DESC' at line 25 SQL=SELECT content.id AS ID, cats.name AS cat_name, cats.alias AS cat_alias, content.catid AS cat_id, users.name AS author, users.email AS author_email, users.id AS author_id, content_rating.rating_sum AS rating_sum, content_rating.rating_count AS rating_count FROM gg1qt_k2_items AS content LEFT JOIN gg1qt_k2_categories AS cats ON cats.id = content.catid LEFT JOIN gg1qt_users AS users ON users.id = content.created_by LEFT JOIN gg1qt_k2_rating AS content_rating ON content.id = content_rating.itemID WHERE ORDER BY content.created DESC


How do i fix this? Please help
Staff
16,844 Points
2013-11-01 10:14 am
Hello Mariak,
A 1064 error is simply a syntax error in the code. The error you gave says that the error occurs near the ORDER BY statement, so you want to take a look at the code right in front of that.

Looking at the statement you have an empty WHERE clause right in front of the ORDER BY portion of the statement. The ORDER BY itself does not need the WHERE clause, so if you do not need anything else in the WHERE, simply removing it may correct the issue.

Kindest Regards,
Scott M
2013-12-03 9:13 am
Hi there i am totally new to this world of coding. And i just got this message on PHPmyadmin:

#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 ') NOT NULL DEFAULT '0', `ID` INT(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`ID`)' at line 1

sorry for my language, but what the fuck!?
can you help me fix this problem?
Staff
8,050 Points
2013-12-03 11:06 am
It appears that something in your SQL statement produced an error. Could you provide us with the SQL query that you were trying to run?
n/a Points
2014-06-02 7:09 am

i was tried this trigger query for my pgm.

CREATE TRIGGER updtrigger BEFORE UPDATE ON EMP

     FOR EACH ROW

    BEGIN

     IF NEW.Salary<=500 THEN

     SET NEW.Salary=10000;

     ELSEIF NEW.Salary>500 THEN

     SET NEW.Salary=15000;

     END IF;

     END;

but i am getting 1064 error

Staff
8,050 Points
2014-06-02 8:43 am
Could you provide me with the specific error that you are getting? The code you have provided looks fine.
2014-01-09 4:29 pm
Hi all,

I'm using concrete 5 and I've imported a backup SQL into phpMyAdmin to restore my site to the point before I was having problems. I can normally upload a csv file that creates pages for me, but I get this error now... I've never gotten it before...

mysql error: [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 'LIMIT 1' at line 1] in EXECUTE("select max(cDisplayOrder) from Pages where cParentID = LIMIT 1")
Staff
16,078 Points
2014-01-09 8:10 pm
Hello Jhood,

Sorry to hear that you're having problems with the SQL import. I unfortunately don't have enough information from you in order to find an account so that I can investigate the issue here. If you can provide us more information we would be happy to review the issue. I did go directly to the Concrete5 Forums and that very same error is reported there. There are currently no replies to it. Here's the link: Concrete5 Forum. You will need to be registered in order to make any comments on their forum.

If you are hosting with us can you please provide account information and the exact steps you're taking to get the problem? I did add to the post in the forum per the information you have reported, since it was the exact same error message. Hopefully, we'll see more information soon.

If you have any further questions, or wish to provide us further information, please reply to this post.

Regards,
Arnel C.
n/a Points
2014-02-27 11:45 am

mi problema es el siguiente instale el wampserver 2.4 y al entrar a localhost/phpmyadmin/ me sale que tengo q poner el usuario y contraseña le pongo la q trae x defecto que es "root" y me sale este error #1045 El servidor MySQL no autorizó su ingreso. Me voy al panel de control de wampserver y en Mysql abrao la consola y escribo este codigo para q supuestamente me corriga el error al abrir la consola de Mysql me pide la clave pero como no la tengo solo le doy enter despues le pongo use mysql le doy enter me abre la base raiz despues pongo UPDATE mysql.user SET Password = PASSWORD<"aqui escribo la contraseña"> WHERE User = "aqui pongo el nombre de usuario"; y despues le doy enter y me sale esto. ERROR 1064 <42000>: 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... Me puede ayudar xfavor 

Staff
8,310 Points
2014-02-27 12:21 pm
Hello Alex,

Thank you for your question. Unfortunately, I don't speak spanish, but I believe you are having trouble connecting to PHPMyAdmin after installing wampserver 2.4.

I found a solution to a similar problem via google search, you can view the post here.

If you have any further questions, feel free to post them below.
Thank you,

-John-Paul
n/a Points
2014-02-27 12:59 pm

ok here it costs translated into English and thank you very much my problem is this install wampserver 2.4 and enter localhost / phpmyadmin / q leaves me I have put the username and password I put the q brings x default is "root" and I get this error # 1045 The MySQL server is not authorized admission. I go to control panel wampserver and Mysql I open the console and write this code for q me supposedly corrects the mistake to open the console Mysql asking for the key, but as I have not only give you enter after I put use mysql you I enter root opens the database after UPDATE mysql.user SET password put = PASSWORD <'here I write the password "> WHERE user =" here I put the user name "and then give him enter and I get this. ERROR 1064 <42000>: You have an error in your SQL syntax; Manual That Corresponds the check to your MySQL server version for the right syntax to use near ... I can help xfavor

Staff
8,050 Points
2014-02-27 2:08 pm
The exact syntax for updating the password for a MySQL user is the following:

UPDATE mysql.user SET Password=PASSWORD('new-password-here') WHERE User='user-name-here';


Be sure to only replace the text here and not any surrounding quotes or parentheses.
n/a Points
2014-02-27 3:12 pm

Many thank you very much and johnpaulb-imhs1 jeffMa helped me helped me answers thank you very much. I get this message in the phpMyAdmin panel. The phpMyAdmin configuration storage is not completely configured, some extended features were disabled. Click here to find out why. and if not paying attention or let haci. if I can help with this too please thank you very much and many are the maximum ..

n/a Points
2014-03-12 5:53 pm

Please I have this error:

Could not make the query numbered : 78MySQL Error No : 1064MySQL 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 'function.is-readable'>function.is-readable]: open_basedir restriction in eff' at line 5

Am confused pls what do i do,I am new to this kind of problem

Staff
8,050 Points
2014-03-14 8:27 am
Hello Momodu Lucky,

It does appear that you do have invalid syntax there. I'm unable to see the full code that you have written on and around this line, but my best guess is that you probably missed a closing quote somewhere as the syntax that your error is referring to is far from valid.
n/a Points
2014-03-16 7:12 am

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual thatcorresponds to your MySQL server version for the right syntax to use near '--EXECUTION OF PROCEDURE FOR THE FIRST TIME IF DISE = '' THEN  SET SYM_COL = (' at line 16

---------------------------------------------------------

DROP PROCEDURE IF EXISTS DIAGNOSIS;delimiter //CREATE PROCEDURE DIAGNOSIS(IN SYM INT,IN DISE VARCHAR(250))BEGIN DECLARE I INT; DECLARE SYM_COL VARCHAR(50); DECLARE N INT; DECLARE C INT; DECLARE ST VARCHAR(250); DECLARE DID INT; CREATE TEMPORARY TABLE SYM_TEMP (SID INT, SNAME VARCHAR(50)); CREATE TEMPORARY TABLE DISE_TEMP (DID INT, DNAME VARCHAR(100)); IF DISE = '' THEN  SET SYM_COL = (SELECT SNAME FROM SYMPTOMS WHERE SID = SYM);  SET ST = CONCAT('SELECT DISTINCT DID, DNAME FROM DISEASES WHERE ',SYM_COL,'!=0');  PREPARE STMT FROM ST;  EXECUTE STMT;        --RETURNS DISEASES

.

.

.

DEALLOCATE PREPARE STMT; END IF; END//delimiter ;

------------------------------------------

Can someone fix this???

n/a Points
2014-03-17 2:34 am
i have an error in mysql error no1064 when i will join the table and then i will write select *from customer c,rental r,property p,propertytype pt where c.customerid=r.customerid and r.propertyid=p.propertyid and p.propertytypeid=pt.propertytypeid; And propertytypename ='residential';
n/a Points
2014-03-19 2:24 am
when i run this stored procedure i get error like SQL Error (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 'NULL' at line 1.Please help me DELIMITER //
DROPPROCEDUREIFEXISTScrosstab//
CREATEPROCEDUREcrosstab()
BEGIN
SET@cols=(SELECTGROUP_CONCAT(
'(TX_SHORT_NAME = ',TX_SHORT_NAME,') AS ',TX_SHORT_NAME
SEPARATOR', ')FROMlst_inv_grps);
SET@sql=CONCAT('
SELECT fac.FAC_NAME,  ',@sql,'
FROM facility fac left join  dbilling b
on fac.SEQ_ID=b.FAC_SEQID left join invoicegrps inv
on b.InvoiceGrp=inv.InvoiceGrp left join lst_inv_grps grp
on inv.invID=grp.KYS_ID
group by fac.FAC_NAME');
PREPAREstmtFROM@sql;
EXECUTEstmt;
END;
/
DELIMITER;
Staff
8,050 Points
2014-03-19 7:40 am
Hello Nandan,

Try adding a space between DELIMITER and the semicolon as you are defining ; as the argument which may be confusing MySQL. Also, check for white space at the beginning of the script as well.
n/a Points
2014-03-19 9:07 am

Hello Jeffma ,

Thanq so much for your valuable comment

n/a Points
2014-03-20 5:53 am

CREATE TABLE `equipment` (  `id` int(11) NOT NULL auto_increment,  `parent_id` int(11) NOT NULL default '0',  `description` varchar(20) NOT NULL default '',  PRIMARY KEY `id` (`id`)) TYPE=MyISAM COMMENT='Equipment' AUTO_INCREMENT=201

 

Err] 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 'TYPE=MyISAM COMMENT='Equipment' AUTO_INCREMENT=201' at line 6

Staff
8,050 Points
2014-03-20 8:06 am
Hello gaisie,

Your error may be caused by defining TYPE=MyISAM. Depending on your MySQL version, you may need to change this to ENGINE=MyISAM
n/a Points
2014-03-20 8:44 am

[SQL] CREATE TABLE equipment (  id int(11) NOT NULL auto_increment,  parent_id int(11) NOT NULL default '0',  description varchar(20) NOT NULL default '',  PRIMARY KEY  (id))ENGINE=MyISAM COMMENT= Equipment AUTO_INCREMENT=201 [Err] 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 'Equipment AUTO_INCREMENT=201' at line 6change it and still giving me error

Staff
8,050 Points
2014-03-20 8:52 am
It appears that you changed COMMENT='Equipment' into COMMENT= Equipment by accident on this second comment which would also cause an error.
n/a Points
2014-03-20 1:51 pm

@gaisie,

I tested JeffMa's comment, and it appears to fix the problem.

  1. I ran your query and was able to replicate the same exact error.
  2. I fixed the query by putting "Equipment" within quotes after COMMENT= at the end.

Here's a working query that I've run successfully:

CREATE TABLE equipment
(
id int(11) NOT NULL auto_increment,
parent_id int(11) NOT NULL default '0',
description varchar(20) NOT NULL default '',
PRIMARY KEY (id)
) ENGINE=MyISAM COMMENT="Equipment" AUTO_INCREMENT=201
n/a Points
2014-03-21 12:11 pm

i have the same problem,can anyone help me with this....

my error is ike this,

Error

 

SQL query:

 

---- PostgreSQL database dump-- '\CONNECT' - hms SET search_path = PUBLIC,pg_catalog;

 

MySQL said:

#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 ''\CONNECT' - hms SET search_path = PUBLIC,                   pg_catalog' at line 4

Staff
8,310 Points
2014-03-21 1:04 pm
Hello vishal,

According to your error, you seem to be using Postgre statement, for a mySQL engine; which will not work.

If you have any further questions, feel free to post them below.
Thank you,

-John-Paul
Staff
5,399 Points
2014-03-21 1:49 pm
@vishal - Can you give us the full query that you're running? It's a bit difficult to troubleshoot an SQL error without actually seeing the entire query that you're running.
n/a Points
2014-03-22 11:51 am

here's full query:

--
-- PostgreSQL database dump
--
 \CONNECT - hms
SET search_path = PUBLIC,
                  pg_catalog;
 
 --
-- TOC entry 2 (OID 217698)
-- Name: add_users; Type: TABLE; Schema: public; Owner: hms
--

Comment updated by moderator. Too long.

Staff
5,399 Points
2014-03-24 11:09 am

@vishal - Based upon the information you've provided, you are trying to import a PostgreSQL database.

POSSIBLE PROBLEM

System Error Codes
MySQLPostgreSQL
106442000 or 42601

The problem you're having is that you're trying to import a PostgreSQL database as a MySQL database. Because MySQL and PostgreSQL are not backwards compatible, MySQL is giving you a 1064 error, meaning a syntax error. We believe this is the case because 1064 is the code for Syntax errors in MySQL, while 42000 and 42601 are the codes for Syntax errors in PostgreSQL.

RECOMMENDED SOLUTION

If you are trying to import a PostgreSQL, please follow the directions on this page. Based upon that documentation, you'll need to run a command similar to:

psql dbname < infile
n/a Points
2014-03-25 6:50 am

Please help me with this MySQL PHPMYADMIN

#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 'BINARY NOT NULL, `aphototype` VARCHAR(200) CHARACTER SET latin1 COLLATE latin1_s' at line 1

 

thanks .. i would really appreciate it..

Staff
8,050 Points
2014-03-25 8:24 am
Could you provide us with the full query that you are trying to run?
n/a Points
2014-03-26 9:19 am

When try to install my script got this error :

 

Cannot execute MySQL query: #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 'TYPE=MyISAM' at line 5

 

When replace "TYPE" for "ENGINE" at source DB don't work, please help me

Staff
9,962 Points
2014-03-26 9:52 am
Hello Freddy,

Unfortunately it is typically the solution to replace TYPE with ENGINE when you encounter that specific error.

I've searched online a bit more for this error and also see this noted as a possible solution:

ENGINE=MyISAM DEFAULT CHARSET=utf8;



Another thing you might want to try is leaving out the TYPE or ENGINE statements altogether and let the MySQL server try to determine what should be used.

Also, taking a look at some of the .sql files you have on the server, it appears some of them have entries like:

ENGINE=ENGINE_TYPE DEFAULT CHARSET=utf8;



If this is the case of the .sql file you're trying to import, you'll need to make sure to actually enter in the ENGINE_TYPE instead of just using that placeholder text.

If you are still having any issues, if you can provide us with the location of the SQL script you are trying to restore we can take a look for you.

- Jacob
n/a Points
2014-03-26 1:35 pm

Hye..

I wants ask u about this error (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 'WHERE st_mykad_staff = ''' at line 11)

 

I dont know what the problem, I already double check the .php and database

 

but still cannot update the information. When I update the information and click update, it show this error.

 

Thank You .

Regards :)

Staff
9,962 Points
2014-03-26 1:51 pm
Hello N_Syakilah, and thank you for your question.

It looks like the specific MySQL error that you're getting is in reference to the WHERE statement in your SQL query.

Unfortunately we'd need to see the full query to see what the exact issues is, but it looks like you have your WHERE clause possibly in the wrong place, or for that particular table you are querying you can't look for a blank value.

For instance on a database that I have, if I do:

SELECT * FROM myDB.myTable
WHERE created_by = ""


I get the error:

Warning: Incorrect integer value: " for column 'created_by' at row 1


This is because my created_by column is an INT datatype, so it's looking for a number. If I instead use:

SELECT * FROM myDB.myTable
WHERE created_by = "123456"


Then the query is successfully executed, and all records with that user ID are returned.

So if you try your query without the WHERE statement, it will probably work. Then you just need modify the WHERE statement to work according to the column you're attempting to compare your query against.

- Jacob
n/a Points
2014-03-27 2:47 am

am getting this error am not good in code so am reading gibberish but i am good at following instructions

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 ') AND TAG.PUBLISHED = 1' AT LINE 4 SQL=SELECT TAG.NAME, TAG.ID FROM Y5CYM_K2_TAGS AS TAG LEFT JOIN Y5CYM_K2_TAGS_XREF AS XREF ON XREF.TAGID = TAG.ID WHERE XREF.ITEMID IN () AND TAG.PUBLISHED = 1

on this site www.procurementtoday.org

help me out

 

Staff
8,050 Points
2014-03-27 8:02 am
Could you provide us with the full query that you are running?
n/a Points
2014-03-31 2:35 pm

hello ...

i need your help..

this is my query :

SELECT jenis_kasus, COUNT( * ) AS TOTAL FROM ( SELECT *

FROM jan2012 UNION ALL SELECT * FROM feb2012 ) feb2012

WHERE jenis_kasus ="Hak Kuasa Asuh"

Err] 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 ') feb2012WHERE 0 =1'at ;line 1

 

please help me....

thank you

Staff
16,844 Points
2014-03-31 3:53 pm
Hello Auges,

The error just means the statement is not written correctly. The area you want to check is given in the error message itself. Check the area where it mentions the table named feb2012 right before the WHERE.

You may want to check the MySQL site regarding Select Syntax for exact assistance.

Kindest Regards,
Scott M
Staff
5,399 Points
2014-03-31 7:45 pm
Hi @auges, Your error messages indicates that your original query included the following text:
') feb2012WHERE 0 =1'

However, the query that you put in your comment does not include that text. Can you confirm that you pasted in the full query? Seeing the full query will help us troubleshoot the issue further for you.
n/a Points
2014-04-01 1:28 am

I have a 1064 and I can't find it:

INSERT INTO `soccer_schedule` (`season`, `game_date`, `arrive_time`, `opponent`, `ourscore`, `theirscore`) VALUES ('Spring 2014 U11B','4/26/2014','10:45','HOLTON',0,0);

Here's a similar insert that PHPMyAdmin created.

INSERT INTO `soccer_schedule` (`soccer_season_id`, `season`, `game_date`, `arrive_time`, `opponent`, `ourscore`, `theirscore`) VALUES ('Spring 2014 U11B', '4/5/2014', '10:45', 'HOLTON', 0, 0)

Here's what PHPMyAdmin said:

Error

There is a chance that you may have found a bug in the SQL parser. Please examine your query closely, and check that the quotes are correct and not mis-matched. Other possible failure causes may be that you are uploading a file with binary outside of a quoted text area. You can also try your query on the MySQL command line interface. The MySQL server error output below, if there is any, may also help you in diagnosing the problem. If you still have problems or if the parser fails where the command line interface succeeds, please reduce your SQL query input to the single query that causes problems, and submit a bug report with the data chunk in the CUT section below:----BEGIN CUT----eNotjU1ugzAQhfc+xexoJNfB/ErsKLJCJMAUQ7oERCyClJgISNXehrNwspLQzcx8b57eY1nGMw8CCoEBEUs8cMAF6rhI5Ku+zAjFv+Iz8sAmNjGd92vfokJkwAUG/8CSHMOJrc6vTkEQZjxmYJpEJ9S112FbKI19DyxCCaUoDdOnG3PxzLOIYULUqccPivzk4IFU6FV1TATL8mU+JjmHauybRg7l2Fzk+XGV1TK/VaOsx15VeJmrtr7J8lxP8kX1MHTfspy628b9/d4rqaYNHsPY9MP2mS6y+8fdMp/8qGBijdbEfehUC4ZOLSgo/dCwZu0NZ/8U1pvqnmWvO+RRzhMN61jf/QEQMWQb----END CUT--------BEGIN RAW----

ERROR: C1 C2 LEN: 6 7 167
STR:  

MySQL: 5.5.36-log
USR OS, AGENT, VER: Win CHROME 33.0.1750.154
PMA: 4.1.11
PHP VER,OS: 5.4.23 Linux
LANG: en
SQL: INSERT INTO `soccer_schedule` (`season`, `game_date`, `arrive_time`, `opponent`, `ourscore`, `theirscore`) VALUES ('Spring 2014 U11B','4/26/2014','10:45','HOLTON',0,0)

----END RAW----

SQL query:

INSERT INTO `soccer_schedule` (`season`, `game_date`, `arrive_time`, `opponent`, `ourscore`, `theirscore`) VALUES ('Spring 2014 U11B','4/26/2014','10:45','HOLTON',0,0)

MySQL said: Documentation

#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 'INSERT INTO `soccer_schedule` (`season`, `game_date`, `arrive_time`, `oppon' at line 1 

I'd appreciate any help you can give me.

 

 

 

 

Staff
8,050 Points
2014-04-01 8:55 am
It looks like you have failed to enter a space after the comma when defining your values to insert. Add a space after the comma and it should fix the issue.
n/a Points
2014-04-01 9:39 am

Hi, thanks.  I've since figured it out.

It was those stupid slashes!  

This --> ,'4/26/2014'

Should have been this --> ,'4-26-2014'

 

Thanks for your help

n/a Points
2014-04-10 12:04 pm

HI, I have created a SQL ERD, and from that I am forward engineering to create the tables.  This is the error I am gettting, can you please help me to solve this.  Thanks

 

Executing SQL script in server

 

ERROR: Error 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 ')

 

    REFERENCES `mydb`.`patient` ()

    ON DELETE NO ACTION

    ON UPDATE NO AC' at line 20

 

-- Table `mydb`.`visit`

 

-- -----------------------------------------------------

 

CREATE TABLE IF NOT EXISTS `mydb`.`visit` (

  `visitid` INT UNSIGNED NOT NULL AUTO_INCREMENT,

  `patientid` INT UNSIGNED NOT NULL,

  `visittype` VARCHAR(45) NULL,

  `visitdate` DATE NOT NULL,

  `visittime` TIME NOT NULL,

  `createdby` VARCHAR(45) NOT NULL,

  `datecreated` DATETIME NOT NULL,

  `visitstatus` VARCHAR(20) NOT NULL,

  `visitchangeddate` DATETIME NULL,

  `visitchangedby` VARCHAR(45) NULL,

  `cancelreason` VARCHAR(45) NULL,

  `cancelledby` VARCHAR(45) NULL,

  `cancelleddate` DATETIME NULL,

  PRIMARY KEY (`visitid`),

  CONSTRAINT `patientid`

    FOREIGN KEY ()

    REFERENCES `mydb`.`patient` ()

    ON DELETE NO ACTION

    ON UPDATE NO ACTION)

ENGINE = InnoDB

 

 

SQL script execution finished: statements: 6 succeeded, 1 failed

Staff
5,399 Points
2014-04-10 12:21 pm
Hi @zoey,

In your SQL query, I noticed the following:

FOREIGN KEY () REFERENCES `mydb`.`patient` ()

I believe the query is failing because you're not actually putting in a foreign key. Documentation shows it should look something like this:

FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)

In the example, notice that it is:
FOREIGN KEY (P_Id)
... and not
FOREIGN KEY ()
as your example has.

You may need to enter a value here:
FOREIGN KEY (`here`)

Let us know if that helps or not.
n/a Points
2014-04-10 1:55 pm

 

 

Thanks a lot.  It worked.....Thanks

n/a Points
2014-04-10 2:31 pm

 

Hi, Another table is giving me an error.  Can you please check this one too.  Is there anyway that I can send you the ERD and you can check it and let me know whether its good or not.  Thanks.  I really appreciate it.  

 

Executing SQL script in server

ERROR: Error 1215: Cannot add foreign key constraint

 

-- Table `mydb`.`utox`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `mydb`.`utox` (

  `utoxid` INT UNSIGNED NOT NULL AUTO_INCREMENT,

  `patientid` INT UNSIGNED NOT NULL,

  `providerid` INT UNSIGNED NOT NULL,

  `labid` INT UNSIGNED NOT NULL,

  `labtestid` INT UNSIGNED NOT NULL,

  PRIMARY KEY (`utoxid`),

  INDEX `patientidutox_idx` (`patientid` ASC),

  INDEX `provideridutox_idx` (`providerid` ASC),

  INDEX `labidutox_idx` (`labid` ASC),

  INDEX `labtestidutox_idx` (`labtestid` ASC),

  CONSTRAINT `patientidutoxfk`

    FOREIGN KEY (`patientid`)

    REFERENCES `mydb`.`patient` (`patientid`)

    ON DELETE NO ACTION

    ON UPDATE NO ACTION,

  CONSTRAINT `provideridutoxfk`

    FOREIGN KEY (`providerid`)

    REFERENCES `mydb`.`provider` (`providerid`)

    ON DELETE NO ACTION

    ON UPDATE NO ACTION,

  CONSTRAINT `labidutoxfk`

    FOREIGN KEY (`labid`)

    REFERENCES `mydb`.`laboratory` (`labid`)

    ON DELETE NO ACTION

    ON UPDATE NO ACTION,

  CONSTRAINT `labtestidutoxfk`

    FOREIGN KEY (`labtestid`)

    REFERENCES `mydb`.`labtest` (`labtestid`)

    ON DELETE NO ACTION

    ON UPDATE NO ACTION)

ENGINE = InnoDB

 

 

SQL script execution finished: statements: 19 succeeded, 1 failed

 

 

 

Fetching back view definitions in final form.

 

Nothing to fetch

 

Staff
9,962 Points
2014-04-10 3:01 pm
Hello Zoey,

It looks like you are trying to define multiple FOREIGN KEY entries, so it's tough to say which one exactly is causing the ERROR: Error 1215: Cannot add foreign key constraint problem.

Are you trying to re-create your database tables from a backup of a database? Or are you attempting to create these tables for the very first time?

- Jacob

n/a Points
2014-04-10 3:18 pm

I am creating it the very first time.

Staff
9,962 Points
2014-04-10 3:23 pm
Hello Zoey,

Are you just manually creating the structure of your database, or using some database software to do so? Unfortunately there is a syntax error in your code, so you might want to try not creating the entire table at once, but in chunks until you can find the part that is causing problems.

- Jacob
n/a Points
2014-04-11 10:16 am

Hi, It worked...Thanks for your help.  I created the new schema.

n/a Points
2014-04-10 3:32 pm

I am using MYSQL workbench, So  I have to remove the error tables and add one by one at one time...right?...

Staff
9,962 Points
2014-04-10 3:38 pm
Hello Zoey,

Yes, I'd recommend creating your table in stages so that you are sure that the correct syntax is being used every step of the way.

- Jacob
n/a Points
2014-04-10 3:45 pm

ok Thanks...I will let you know, once I am done.  Thanks

n/a Points
2014-04-11 2:53 pm

Hi, It worked...Thanks for your help.  I created the new schema.

n/a Points
2014-04-11 11:26 am

Hi, from reading your article I have gained a better understanding of why this problem occurs, however my experience is very limited so I have not been able to make the neccassary changes. Could you please look at the error message I am recieving and point me in the right direction? Please note, I have no experience what so ever with databases. Do you also happen to know why this happens? The error has occured when I have moved my database from my live server and tried to import it to my local server. However when I try to import an older version of my database, from a few weeks ago, it works fine?I am an InMotionHosting customer.SQL query:

<!-- PMA-SQL-ERROR -->

    <div class="error"><h1>Error</h1>

<p><strong>SQL query:</strong>

<a href="/support/tbl_sql.php?sql_query=SHOW+TABLE+STATUS+FROM+%60caprac5_wplc%60+LIKE+%27sales_flat_quote_address_item%27&amp;show_query=1&amp;db=caprac5_wplc&amp;table=sales_flat_quote_address_item&amp;token=ed5eb71dc536407f4f31ba33b78723ff"><span class="nowrap"><img src="/support/themes/dot.gif" title="Edit" alt="Edit" class="icon ic_b_edit" /> Edit</span></a>    </p>

<p>

<span class="syntax"><span class="inner_sql"><a href="/support/./url.php?url=http%3A%2F%2Fdev.mysql.com%2Fdoc%2Frefman%2F5.5%2Fen%2Fshow.html&amp;token=ed5eb71dc536407f4f31ba33b78723ff" target="mysql_doc"><span class="syntax_alpha syntax_alpha_reservedWord">SHOW</span></a>  <span class="syntax_alpha syntax_alpha_reservedWord">TABLE</span>  <span class="syntax_alpha syntax_alpha_reservedWord">STATUS</span>  <span class="syntax_alpha syntax_alpha_reservedWord">FROM</span>  <span class="syntax_quote syntax_quote_backtick">`caprac5_wplc`</span>  <a href[...]

 

MySQL said:

#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 '<!-- PMA-SQL-ERROR -->

    <div class="error"><h1>Error</h1>

<p><strong>SQL quer' at line 1 

 

Staff
16,078 Points
2014-04-11 2:43 pm
Hello Conor,

The problem you're having has to do with the code you're using - it's HTML not SQL. That's why you're getting an immediate syntax error on line 1. MySQL can't parse the HTML code. I'm not sure where you're getting this code, but it doesn't look like it's from a MySQL export. We unfortunately can't provide the code for you. If you need further help with understanding MySQL try going through the tutorials here.

For more specific references concerning exporting/databases, check out our article which will give you references on each topic.

If you have any further questions, please let us know.

Regards,
Arnel C.
n/a Points
2014-04-12 6:49 am

Hi Arnel,

Thank you for the quick reply.

I have read through both the tutorial and the articles you suggested. I now have a better understanding of how the SQL database works and is formatted. One thing I don't understand is whether html can be used in a SQL file at all, or does it always result in an error? I took the time to look through my SQL database file to find any html in it. I found 5 different places where html is used, and then at the very bottom of the SQL file I found the html code that is causing the issue. Here is what the code looks like in the SQL file:

 

<!-- PMA-SQL-ERROR -->

    <div class="error"><h1>Error</h1>

<p><strong>SQL query:</strong>

<a href="/support/tbl_sql.php?sql_query=SHOW+TABLE+STATUS+FROM+%60caprac5_wplc%60+LIKE+%27sales_flat_quote_address_item%27&amp;show_query=1&amp;db=caprac5_wplc&amp;table=sales_flat_quote_address_item&amp;token=ed5eb71dc536407f4f31ba33b78723ff"><span class="nowrap"><img src="/support/themes/dot.gif" title="Edit" alt="Edit" class="icon ic_b_edit" /> Edit</span></a>    </p>

<p>

<span class="syntax"><span class="inner_sql"><a href="/support/./url.php?url=http%3A%2F%2Fdev.mysql.com%2Fdoc%2Frefman%2F5.5%2Fen%2Fshow.html&amp;token=ed5eb71dc536407f4f31ba33b78723ff" target="mysql_doc"><span class="syntax_alpha syntax_alpha_reservedWord">SHOW</span></a>  <span class="syntax_alpha syntax_alpha_reservedWord">TABLE</span>  <span class="syntax_alpha syntax_alpha_reservedWord">STATUS</span>  <span class="syntax_alpha syntax_alpha_reservedWord">FROM</span>  <span class="syntax_quote syntax_quote_backtick">`caprac5_wplc`</span>  <a href="/support/./url.php?url=http%3A%2F%2Fdev.mysql.com%2Fdoc%2Frefman%2F5.5%2Fen%2Fstring-comparison-functions.html%23operator_like&amp;token=ed5eb71dc536407f4f31ba33b78723ff" target="mysql_doc"><span class="syntax_alpha syntax_alpha_reservedWord">LIKE</span></a>  <span class="syntax_quote syntax_quote_single">'sales_flat_quote_address_item'</span></span></span>

</p>

<p>

    <strong>MySQL said: </strong><a href="/support/./url.php?url=http%3A%2F%2Fdev.mysql.com%2Fdoc%2Frefman%2F5.5%2Fen%2Ferror-messages-server.html&amp;token=ed5eb71dc536407f4f31ba33b78723ff" target="mysql_doc"><img src="/support/themes/dot.gif" title="Documentation" alt="Documentation" class="icon ic_b_help" /></a>

</p>

<code>

#2006 - MySQL server has gone away

</code><br />

</div>

 

How is this code added to the SQL file? Is it done by phpMyAdmin scanning the file and noticing an error so including this code. Or is it done when something is changed in my site which causes the database to automatically add this code because the change as caused an error? Or is it some other way?The only changes that I can think of that could have caused a problem, would be when I added a sitemap file or when I added a robots.txt file, however no problems are visible on my sites frontend or backend. Do you think this could have something to do with it?

 

Thanks

Staff
8,050 Points
2014-04-14 8:20 am
From taking a look at what you have here, it looks like something modified your SQL file to format it in a better readable format, but would not be able to directly imported. Could you clarify where you got this exact code? Was it a direct export made from within PHPMyAdmin?
n/a Points
2014-04-14 8:49 am

Thanks for the reply.

 

I found this exact code at the very bottom of the database .sql file, after I had exported it from phpMyAdmin.

 

Yes, I exported the database directly from phpMyAdmin in my InMotionHosting account. I have done this many times before without a problem so I don't think it is my method of exporting that is the problem. However I have recently upgraded from an InMotionHosting shared hosting package (the 'pro' plan) to an InMotionHosting VPS package. Could this have caused any problems? Other than that the only changed I made where to the sitemap.xml file and the .robots.txt file. I do also remember that I added some redirect code to the .htaccess file, which was simply to redirect the homepage url to use http instead or https; the code I added can be seen below.

 

#Redirect your Homepage from HTTPS to HTTPRewriteCond %{HTTPS} onRewriteRule ^$ http://%{HTTP_HOST} [L,R]

 

Thanks

Staff
8,050 Points
2014-04-14 9:35 am
It is very uncommon to see something like this from a PHPMyAdmin export. From what it looks like here, PHPMyAdmin encountered an error in the export and placed it within the SQL file instead of displaying it. If you try to do another export, do you get the same information within the .sql file?
n/a Points
2014-04-14 11:54 am

I did another export and now the code is no longer in the .sql file as it was before. I also successfully imported it to MAMP with no error message, which is strange that it now worked. However, now my actually websites URL is not working! When I try to access my live site, I get an error message saying it can not connect to server 'localhost' and it appears like it is trying to redirect me to my 'local' site! How can this have happened. All I have done is export the database?

Staff
8,050 Points
2014-04-14 12:11 pm
It sounds like at some point you used a backup from your local server and restored it to the live server which would rewrite all of your permalinks if you are using a CMS. What CMS are you using to build your site?
n/a Points
2014-04-14 12:26 pm

Thanks again for the reply.

 

I am using Magento, so when I am moving my site from live to local, or local to live, all I need to change it the 'unsecure URL' and the 'secure URL' to {{base_url}}.

I have also not imported any database to my live server for quite some time, I have only exported it, which makes this very strange. Its like as soon as I have imported the most recent database to MAMP, it had an affect on the live server, which I didn't even think was possible?

 

Thanks

 

 

Staff
8,050 Points
2014-04-14 12:37 pm
Importing your live site's database into your development environment should not affect your live site at all, unless you have something configured within the development site that accessed the live database to change something.

To clarify, when it says that it cannot connect to "localhost", is it a message output by your code and your see your domain appropriately in the address bar, or are you getting the message from your browser and are seeing "localhost" within your address bar?
n/a Points
2014-04-14 12:47 pm

I am seeing 'localhost' within the address bar and the message is from my browser. It also still appears when I turn MAMP off.

Staff
8,050 Points
2014-04-14 1:08 pm
It is being cased by something in your code redirecting your domain to localhost. Could you provide me with the domain that you see this on?
n/a Points
2014-04-15 3:10 am

Jeff, thanks for your help, it seems like the issue has now been resolved.

 

The domain is www.whiteparisluxurycandles.co.uk

 

I still am not 100% sure what was causing the problem. As mentioned before I managed to import the database to MAMP after exporting it a third time from my local server, for some reason this time the error message did not show. Once the site was working in MAMP I found that my live site was now redirecting to my 'localhost', meaning for some reason my live site was now no longer working. I restarted my computer, deleted the live database and then re-imported it. After doing this the live site was working again. I still don't know the why this problem occured but I have a feeling it was down to the following redirect code that I placed in my .htaccess file. Have you heard of anyone experiencing similar problem from redirect code in the .htaccess file, and do you have any advice when working with redirect codes?

 

# Redirect non-www to www:

RewriteCond %{HTTP_HOST} !^www\. [NC]

RewriteRule ^(.*)$ http://www.%{HTTP_HOST}/$1 [R=301,L]

 

 

#Redirect your Homepage from HTTPS to HTTP

RewriteCond %{HTTPS} on

RewriteRule ^$ http://%{HTTP_HOST} [L,R]

 

Thanks again for your help.

Staff
8,050 Points
2014-04-15 9:21 am
This code wouldn't have caused it unless your browser may have cached the .htaccess file from another time when you accessed the local development site and could have possibly carried over to your live site but only for your computer.
n/a Points
2014-04-18 12:22 pm

 

 

Hello. 

  I have this problem, I've been following some tutorial videos and have done as such as this in the videos but in the end I get this problem as not solve it, can you help me please. 

I leave the message on the page. 

 

thanks

 

Error Base de Datos: SQL no válida: CREATE TABLE `broadcastmessages` (`broadcastmessages_id` bigint (20) NO AUTO_INCREMENT NULL, `bigint broadcastmessages_user_id` (20) NOT NULL DEFAULT '0 ', `broadcastmessages_attach_id` bigint (20) NOT NULL DEFAULT '0' , `broadcastmessages_media_id` bigint (20) NOT NULL DEFAULT '0 ', `broadcastmessages_subject` varchar (255) NOT NULL DEFAULT'', texto `broadcastmessages_body` NOT NULL, `broadcastmessages_type` tinyint (4) NOT NULL DEFAULT '0', ` broadcastmessages_delivery_date `datetime NOT NULL DEFAULT '0000-00-00 00:00:00 ',` broadcastmessages_subscriber_id `bigint (20) NOT NULL DEFAULT '0',` broadcastmessages_sent `tinyint (4) NOT NULL DEFAULT '0 ',` broadcastmessages_sign_id ` bigint (20) NOT NULL DEFAULT '0 ', PRIMARY KEY (`broadcastmessages_id`)) TYPE = MyISAM MySQL Error : 1064 (Usted tiene un error en su sintaxis SQL, consulte el manual que corresponde a su versión del servidor MySQL para la sintaxis correcta utilizar cerca de 'TYPE = MyISAM' at line 14) Sesión detuvo.

Staff
8,310 Points
2014-04-18 12:39 pm
Hello edgar,

Thank you for your question. We are happy to help, but will need a little more information, such as a link to the video tutorials you are trying to follow, or more information on what you are trying to accomplish.

Can you provide a link to the error, so we can view it?

If you have any further questions, feel free to post them below.
Thank you,

-John-Paul
n/a Points
2014-05-05 3:53 pm

i have created a dev copy of my main wordpress website and have it sitting on a subdomain of my url.

when i manually edit the sql file to update all references of my domain name (i.e. changing all "domain.com" instances to "dev.domain.com") and try to import it into my database either using phpmyadmin or the cpanel mysql restore function i get this error:

#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 '‬‬/*!40000 ALTER TABLE `wp_59_postmeta` ENABLE KEYS */' at line 1

nothing about that section of the code has changed.

what could be causing my error?

is there enough information i have provided?

thanks,andrew

Staff
5,399 Points
2014-05-05 3:59 pm
Hi Andrew, those are some strange characters in your error message, ‬‬. The text editor that you're using to do the find and replace may be causing that issue. I recommend trying a different editor, such as notepad++.

Instead of the method you're taking, have you thought about doing a find and replace directly in SQL, such as what is found on this page?
n/a Points
2014-05-06 10:15 pm

#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 'release = 1 Where tid = 'CS115' AND t_type = 'Quiz2' at line 2

Thats the error I get when I try to update the field..

this is the statement:

Update GradesSet release = 1Where tid = 'CS115' AND t_type = 'Quiz2'

I even tried without the WHERE statement with the same result "1064"

Please help I am going nuts here

Staff
16,844 Points
2014-05-07 6:39 am
Hello John,

It is a small statement so there is not too much to check. The error message indicates the error occurs around the part of the statement 'release=1'. If you search for 'MySQL 5.x Reserved Words' in your favorite search engine (replacing the 5.x with your MySQL version) you see that Release is a reserved word. This means the way it is written in the statement it is trying to activate it. To use it as the column name you need to enclose it in backticks ``. It should read:
Update Grades
Set `release` = 1
Where tid = 'CS115' AND t_type = 'Quiz2'


Give that a try and it should work out, provided the release column is expecting a numerical value.

Kindest Regards,
Scott M
n/a Points
2014-05-09 11:45 pm

Im trying install IPB 2.3.6 on a new SQL database and everytime i do so i get this error.

 

mySQL query error: CREATE TABLE ibf_admin_logs (

  id bigint(20) NOT NULL auto_increment,

  act varchar(255) default NULL,

  code varchar(255) default NULL,

  member_id int(10) default NULL,

  ctime int(10) default NULL,

  note text NULL,

  ip_address varchar(255) default NULL,

  PRIMARY KEY  (id)

) TYPE=MyISAM;

 

SQL 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 'TYPE=MyISAM' at line 10

SQL error code: 1064

 

After i hit refresh at the top of the screen i get this error

 

We were unable to write your configuration information to the conf_global.php file. Please verify that this file has full read and write privileges.

 

please any help would do me alot of good!

Staff
16,844 Points
2014-05-10 8:30 am
Hello Joe,

Thank you for your question. The error says that the incorrect area is around the 'TYPE=MyISAM' portion of the statement. Removing that portion of the statement will allow the table to be created.

As for the error message about permissions you will want to check and ensure your folders are set to 755 and the php files are set to 644 for normal operating permissions.

Kindest Regards,
Scott M
n/a Points
2014-05-10 9:30 am

Well I have checked all of the file permissions for the second error and still nothing changes. For the first error I have no clue how to fix that I'm a newbie to php

Staff
16,844 Points
2014-05-12 12:23 pm
Hello Joe,

Apologies, I did not give you the fix to control the engine type when creating a table. You will want to switch the 'TYPE=MyISAM' portion of the statement to 'ENGINE=MyISAM'. The code below tested and ran fine.
CREATE TABLE ibf_admin_logs (
id bigint(20) NOT NULL auto_increment,
act varchar(255) default NULL,
code varchar(255) default NULL,
member_id int(10) default NULL,
ctime int(10) default NULL,
note text NULL,
ip_address varchar(255) default NULL,
PRIMARY KEY (id)
) ENGINE=MyISAM;


Kindest Regards,
Scott M
n/a Points
2014-05-15 11:14 am

Create table credit_card(

account_ID  char(8),

creditcard_no  char(16),

dept numeric(15,2) default 0 not null,

limit numeric(15,2) default 0 not null,

validation_no  numeric(3,0) default 0 not null,

expiration_date varchar(10) not null,

foreign key(account_id) references account(account_id),

        primary key(account_ID,creditcard_no)

 

)

 

 

#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 'limit numeric(15,2) default 0 not null, validation_no numeric(3,0) default 0 ' at line 5

 

why did is happening to me?

Staff
16,078 Points
2014-05-15 11:32 am
Hello Zeyna,

Thanks for the question. The problem you're having is related to a reserved word that you're using in your code- specifically, the word "limit".

You can fix the problem by putting back ticks around the word limit. or you can use a different variable name in place of "limit".

`limit` numeric(15,2) default 0 not null,

I hope that helps to clarify the issue! Let us know if you have any further questions or comments.

Regards,
Arnel C.
n/a Points
2014-05-16 7:48 pm

First sorry for my bad english.

I´m trying to find <p style=\"text-align: center;\"> and replace with <p class="aligncenter"> in a wp_posts table. The error that im getting is:

UPDATE .`wp_posts` SET `post_content` = REPLACE(`post_content`, '<p style=\', '<p class=') WHERE `post_content` LIKE '%<p style=\%' COLLATE utf8_bin

 

Can you please help me to solve this issue. Thanks in advance

Staff
16,078 Points
2014-05-16 9:13 pm
Hello Mike,

Thanks for the comment. There are a couple ways to do this. You can use a MYSQL client like phpMyAdmin to open the table and then manually replace the data. I'd recommend using a free MySQL database client like HeidiSQL. The built in filters make it easy to find a value in the table and quickly replace it.

You can also export the table into text, then use a text editor to do a quick find and replace. Then re-import the table back into the database.

The method you're using appears to be code in either a shell or MySQL client. I believe that the issue has to do with the start of the update line. There's a period right after Update. The collation should already be set for the table, so, you don't need to set it again in the update statement. We provide information on changing collation for the database - it shouldn't be done just for the table.

Try this statement:


UPDATE wp_posts
SET post_content = REPLACE(post_content, 'style=\"text-align: center;\"', 'class=\"aligncenter\" ')
WHERE post_content LIKE ('%<p style=\%' );


You may need to specify the database (at the beginning of the code), depending on how you're running this update. I wasn't clear on the environment you're using to update the table.

I hope this helps. As you can see, there are a number of different ways to make this update. Hopefully, one of them provides the answer you seek.

Regards,
Arnel C.
n/a Points
2014-05-20 11:00 pm

hi what could be the sulution for this error?

can you help me please...

Error

 

SQL query:

 

<!DOCTYPE html> <html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en" dir="ltr"> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <link rel="icon" href="/support/./favicon.ico" type="image/x-icon" /> <link rel="shortcut icon" href="/support/./favicon.ico" type="image/x-icon" /> <title>phpMyAdmin</title> <link rel="stylesheet" type="text/css" href="/support/phpmyadmin.css.php?server=1&amp;token=8140c0684aaa82fd739d71f91c5c2ddb&amp;js_frame=right&amp;nocache=5549790171" /> <link rel="stylesheet" type="text/css" href="/support/print.css" media="print" /> <link rel="stylesheet" type="text/css" href="/support/./themes/pmahomme/jquery/jquery-ui-1.8.16.custom.css" /> <meta name="robots" content="noindex,nofollow" /> <script src="/support/./js/cross_framing_protection.js?ts=1375007690" type="text/javascript"></script> <script src="/support/./js/jquery/jquery-1.6.2fix-9521.js?ts=1399144421" type="text/javascript"></script> <script src="/support/./js/jquery/jquery-ui-1.8.16.custom.js?ts=137500[...]

 

MySQL said: Documentation

#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 '<!DOCTYPE html> <html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="e' at line 1

Staff
8,050 Points
2014-05-21 7:46 am
The code you have provided is HTML and not a valid SQL statement, therefore it will not be able to be imported.
n/a Points
2014-05-21 4:39 pm

Hi, please help me solve this error!.

 

Error:

1) 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 'condition, image, review) VALUES (' at line 2

2) 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 'condition = 'Used', image = 'images/cisco/CISCO CP-7962G.JPG', review = 'The Cis' at line 3

SQL query:

-- phpMyAdmin SQL Dump
-- version 3.5.1
-- http://www.phpmyadmin.net
--
-- Machine: localhost
-- Genereertijd: 04 mrt 2013 om 19:05
-- Serverversie: 5.5.24-log
-- PHP-versie: 5.3.13
 
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";
 
 
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
 
--
-- Databank: `cisco`
--
 
-- --------------------------------------------------------
 
--
-- Tabelstructuur voor tabel `cisco`
--
 
CREATE TABLE IF NOT EXISTS `cisco` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `product` varchar(255) DEFAULT NULL,
  `type` varchar(255) DEFAULT NULL,
  `price` varchar(255) DEFAULT NULL,
  `quantity` double DEFAULT NULL,
  `condition` varchar(255) DEFAULT NULL,
  `image` varchar(255) DEFAULT NULL,
  `review` text,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;
 
--
-- Gegevens worden uitgevoerd voor tabel `cisco`
--
 
INSERT INTO `cisco` (`id`, `product`, `type`, `price`, `quantity`, `condition`, `image`, `review`) VALUES
(1, 'Cisco Catalyst 2948G WS-C2948G-GE-TX ', 'Gigabit Switch', '80 K.D', '15', 'Used', 'Images/Cisco/Catalyst 2948G.jpg', 'WS-C2948G-GE-TX bootrom version 6.1(6), built on 2003.09.19 15:52:35. Board type is WS-C2948G-GE-TX. Power-on-self-test for Module 1: WS-C2948G-GE-TX. Power-on-self-test for Module 2: WS-C2948G-GE-TX....'),
(2, 'CISCO CP-7962G', 'VoIP PoE PHONE', '45 K.D', '2', 'Used', 'Images/Cisco/CISCO CP-7962G.jpg', 'The Cisco Unified IP Phone 7962G is a full-featured IP phone with speakerphone and handset designed for wideband audio. It is intended to meet the needs of managers and administrative assistants. It has six programmable backlit line/feature buttons and four interactive soft keys that guide you through all call features and functions. The phone has a large, 4-bit grayscale graphical LCD that provides features such as date and time, calling party name, calling party number, digits dialed, and presence information.'),
(3, 'Cisco 2811', 'Router', '400 K.D', '60', 'New', 'Images/Cisco/Cisco 2811.jpg', 'Cisco 2811 Router Includes Rack Mount Kit, Power Cord and Console Cable Clean Database (NO PASSWORDS) flash:c2800nm-ipbase-mz.124-3b.bin, Cisco 2811 (revision 53.50) with 251904K/10240K bytes of memory. Processor board ID FTX1001C3J5 2 FastEthernet interfaces DRAM configuration is 64 bits wide with parity enabled. 239K bytes of non-volatile configuration memory. 62720K bytes of ATA CompactFlash (Read/Write).'),
(4, 'PVDM-16', 'Accessories', '55 K.D', '110', 'Used', 'Images/Cisco/PVDM-16.jpg', '72x Cisco PVDM-16 73-8538-05 16-Channel Packet Fax and Voice DSP Modules');
 
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
 
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

 

Staff
16,078 Points
2014-05-21 4:58 pm
Hello Ali Zaghir,

Thanks for the question. It appears that the word "condition" is a reserved word that can not be escaped with the back ticks. If you have not already, make sure that you have backticks around ANY instance of the word "condition." If it's in your code somewhere without the backticks, then you will definitely get an error. The syntax error is definitely identifying a problem with your MYSQL code. If this continues to be an issue, then try using a different variable name other than "condition".

I hope this helps to resolve your problem!

Kindest Regards,
Arnel C.
n/a Points
2014-05-22 3:15 am

Hello Arn,

Thanks a lot for your help!, I really spent the whole day trying to figure out what is causing the issue, and now it is simply solved. I have add the backticks for two of my "condition" in two function:

1) function Update($id, Entity $Products)

{

     require 'Credentials.php';

     mysqli_connect($host,$user,$passwd) or die(mysqli_error($link));

     mysqli_select_db($link,$database);

    

    $query = sprintf("UPDATE cisco

                        SET product = '%s', type = '%s', price = '%s', quantity = '%s', 

                        `condition` = '%s', image = '%s', review = '%s'

                      WHERE id = $id",

 

2) function InsertCoffee(CoffeeEntity $Products)

{

    require 'Credentials.php';

    mysqli_connect($host,$user,$passwd) or die(mysqli_error($link));

    mysqli_select_db($link,$database);

.......etc

    

    $query = sprintf("INSERT INTO cisco

                      (product, type, price, quantity, `condition`, image, review)

                      VALUES

                      ('%s','%s','%s','%s','%s','%s','%s')",

                       .........etc

 

And now it is working fine and am very happy. Thanks man.

n/a Points
2014-05-22 9:46 pm

So heres my problem, and im not sure if it is #1064, but i know its similar.

I am trying to update a data base from a website using php, But when it comes to acctually updating it, i get this error every time:

 

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 'WHERE id='1'' at line 1

 

this is my code for the query:

 

 

<?php// update data in mysql database $sql="UPDATE SALES SET SALE_DTM='".$_POST['SALE_DTM']."', SALE_DEPT='".$_POST['SALE_DEPT']."',  SALE_ITEM='".$_POST['SALE_ITEM']."', NUMBER_OF_ITEMS='".$_POST['NUMBER_OF_ITEMS']."', COST_PER_ITEM='".$_POST['COST_PER_ITEM']."', SALES_PERSON='".$_POST['SALES_PERSON']."',  CUSTOMER_NAME='".$_POST['CUSTOMER_NAME']."', WHERE id='".$_POST['id']."'";$result=mysql_query($sql)or  die ("this stuffedup. ".mysql_error());// if successfully updated. if($result){echo "Successful";echo "<BR>";echo "<a href='list_records.php'>View result</a>";}else {echo "ERROR";}

 

?>

 

 

Thanks.

Staff
8,050 Points
2014-05-23 7:57 am
I have found the error in your syntax. The issue is caused by an unnecessary comma. You'' have to scroll over to the right to see the error. I have highlighted it in red for you:

$sql="UPDATE SALES SET SALE_DTM='".$_POST['SALE_DTM']."', SALE_DEPT='".$_POST['SALE_DEPT']."', SALE_ITEM='".$_POST['SALE_ITEM']."', NUMBER_OF_ITEMS='".$_POST['NUMBER_OF_ITEMS']."', COST_PER_ITEM='".$_POST['COST_PER_ITEM']."', SALES_PERSON='".$_POST['SALES_PERSON']."', CUSTOMER_NAME='".$_POST['CUSTOMER_NAME']."', WHERE id='".$_POST['id']."'";$result=mysql_query($sql)or die ("this stuffedup. ".mysql_error());
// if successfully updated.
if($result){echo "Successful";echo "
";echo "<a href='list_records.php'>View result</a>";}else {echo "ERROR";}

n/a Points
2014-05-23 7:04 pm

Greetings,

I went through all the errors other people have submitted but I was not able to find the error I'm getting. 

#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 ’<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN""http://www.w’ at line 1

 

Any help will be much appreciated.

Regards,

Manny

Staff
16,078 Points
2014-05-23 7:26 pm
Hello Manny,

Thanks for the question. The error message that you're seeing involves a syntax error. The message indicates that it's at line 1. Notice that the code should be in SQL syntax. If it's not using that syntax, then the error will occur. Since you have not have not given us any information about your account, we cannot check it ourselves to see the error. If the code is from a third party, you may need to contact the author to determine where the problem lies.

If you can provide further information then we may be able to investigate the issue further.

Kindest regards,
Arnel C.
n/a Points
2014-06-14 6:17 am

Hello, I'm having the same problem. I've exported my db from my web server and trying to upload it on my local server to test the new features but I got this error. Is there a way to make sql accept this syntax? I'm using joomla and the db comes from an old 1.5 version of joomla.

Thanks

Staff
16,844 Points
2014-06-15 8:26 am
Hello Eleanor,

I may be able to tell where the error is if you can post the error message like Manny did above. Be sure to check things like quotation marks. That was the issue with the above error. Since the text is stored within double quotes like " anything inside needs to be single quotes ' so as not to break the SQL statement.

Kindest Regards,
Scott M

Question: Missing data

n/a Points
2014-06-17 1:04 pm

Hello,

Hoping i can find some help here. I am runing a joomla site v3.3.1

Whenever i try to access one of my compoenents, i get the following error:

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 'AND rgt <= AND extension = 'com_content' AND parent_id= ORDER BY lft ASC' at line 1 SQL=SELECT id, title, lft, rgt, level FROM mislw_categories WHERE lft >= AND rgt <= AND extension = 'com_content' AND parent_id= ORDER BY lft ASC

 

Any help would be greatly appreciated.

 

Staff
16,844 Points
2014-06-17 1:16 pm
Hello Rami,

It appears the structure of the sentence is correct, however there is some data missing. For example, 'AND rgt <= ' but it has no data to compare it to before beginning the next AND portion. As a shot in the dark, try and rebuild the categories to see if it fills in data for the rgt, lft, and parent_id fields. Then it should work properly for you.

Kindest Regards,
Scott M
n/a Points
2014-06-20 9:15 am

Hi,

I am creating trigger but showing this error,

#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 'END' at line 6

CREATE TRIGGER before_pan_pet_t_insert

BEFORE INSERT ON pet

FOR EACH ROW

BEGIN

INSERT INTO event set name=new.name;

END;

Staff
9,962 Points
2014-06-20 2:21 pm
Hello Harish,

I think you need to remove the semi-colons from your code so that it's interpreted by MySQL as a single statement. If you wanted to keep the semi-colons you could create another temporary delimiter.

DELIMITER $$
CREATE TRIGGER before_pan_pet_t_insert
BEFORE INSERT ON pet
FOR EACH ROW
BEGIN INSERT INTO event set name=new.name;
END$$
DELIMITER ;


Please let us know if that does the trick or not.

- Jacob
n/a Points
2014-06-23 1:04 pm

#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 'TYPE=MyISAM AUTO_INCREMENT=30' at line 19

The error above is what mysql is reporting: please in need your assistance.

This is the script below

 

CREATE TABLE `Announcements` (

  `AnnounceId` int(11) NOT NULL auto_increment,

  `UserId` int(11) NOT NULL default '0',

  `Date` datetime NOT NULL default '0000-00-00 00:00:00',

  `Title` varchar(64) NOT NULL default '',

  `Message` text NOT NULL,

  `Priority` int(3) NOT NULL default '0',

  PRIMARY KEY  (`AnnounceId`)

) TYPE=MyISAM AUTO_INCREMENT=30 ;

Staff
16,844 Points
2014-06-23 1:15 pm
Hello Erabor, the error message says the error is occurring around the 'Type=MyISAM' portion of the query. You will want to change that to read 'ENGINE=MyISAM'

Kindest Regards,
Scott M
n/a Points
2014-06-23 1:45 pm

Thanks a great deal for the correction, Now i have another error that am battling with in running a file titled:sess_h.inc.php

this is the error below:

Error! to turn this detailed error message off, unset the DEBUG global in includes/config.inc.php.

Date: 23-06-2014 19:26:01
Error type: 8192
Error message: mysql_pconnect(): The mysql extension is deprecated and will be removed in the future: use mysqli or PDO instead
Script: C:\xampp\htdocs\cronus\cronus\includes\sess_h.inc.php(60)
Staff
16,078 Points
2014-06-23 1:53 pm
Hello Erabor,

The error message you are seeing is in relation to the DEPRECATED code. This is noted in the error. Currently, your settings show an error when deprecated code is detected. If you don't want the error to appear, then either remove the code, or turn off the error message. Deprecation errors are common when you use code that is older because a keyword/function may have been removed in the newer version that you are using to run the code.

Also, we don't mind helping, but in the interests of keeping the subject matter related to the posted article, please ask a new question if it is not related to the subject matter of the article where you are asking your question. If you need to refer to a previous post, then simply add the link to that post within the body of the question.

Kindest regards,
Arnel C.
n/a Points
2014-06-26 7:41 am

#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 'FROM `ilp_order_product` op WHERE op.order_id = o.order_id ) GROUP BY o.order_id' at line 10

 

SELECT *, MIN(o.date_added) AS date_start, MAX(o.date_added) AS date_end, (SELECT CONCAT(op.order_id ,' ') FROM `ilp_order_product` op WHERE op.order_id = o.order_id GROUP BY op.order_id) AS order_ord_id, (SELECT GROUP_CONCAT(op.order_id SEPARATOR '') FROM `ilp_order_product` op WHERE op.order_id = o.order_id GROUP BY op.order_id) AS order_ord_idc

 

plz reply and thanks in advance

Staff
16,844 Points
2014-06-26 8:58 am
Hello roopa,
The error message always tells you where the error occurs in the statement. In your case, it is in the FROM portion of the statement. 'FROM `ilp_order_product` op' specifically. Start there and ensure that the everything is correct for the FROM and WHERE clause (most likely the FROM).

Kindest Regards,
Scott M
n/a Points
2014-06-27 3:25 am

hello thanks for your replay

 

its working fine in mysql but problem is when add to php file getting blank page dont know why so please tell me any other possibalities

 GROUP_CONCAT((SELECT GROUP_CONCAT(IFNULL((SELECT por.sku FROM `ilp_product_option_relation` por , (SELECT `order_id` , GROUP_CONCAT( `product_option_value_id` SEPARATOR ':' ) AS `product_option_value_id` FROM `ilp_order_option` oo GROUP BY oo.order_id ) AS `ODR_OPT` WHERE por.var = `ODR_OPT`.product_option_value_id and `ODR_OPT`.order_id = op.order_id)

just i add this query in old query

but old query working fine in both mysql and php but when i add this query its getting blank page dont know why and not getting also

so pls help me

Staff
16,844 Points
2014-06-27 9:04 am
Hello Roopa,
Just glancing it appears to be syntactically correct, however I am unable to test anything in regards to the specifics (tables and columns) as I do not know your database structure. I also do not know what the expected result set is to be able to test properly. But I would go through the newly added section carefully and see if it runs independently of the original query. Once it does, adding it should not be a problem.

Kindest Regards,
Scott M
n/a Points
2014-06-26 8:55 pm

I received the following error and I am not sure how to fix it. Any thoughts? It does not clearly point to where the issue lies. I received it while importing a wordpress database from my old host to my new one. They are obviously running different versions of mySQL. Thanks

There is a chance that you may have found a bug in the SQL parser. Please examine your query closely, and check that the quotes are correct and not mis-matched. Other possible failure causes may be that you are uploading a file with binary outside of a quoted text area. You can also try your query on the MySQL command line interface. The MySQL server error output below, if there is any, may also help you in diagnosing the problem. If you still have problems or if the parser fails where the command line interface succeeds, please reduce your SQL query input to the single query that causes problems, and submit a bug report with the data chunk in the CUT section below:

----BEGIN CUT----

eNo1jcEKAiEYhIO9/U8xD2CSmhTeRGT3oKvp1j0iIojtFNTb5wbNcfi+GV9KKgZOwEkEPxoISAhB

dWp1RxQ/9RAMNNdc7daP542OtSBVBtv7cWI4+QbG8wVuKCn6Rm640pSjNVDN2nNJecgLx1JdlrZc

KoT7/HpTsGNvcJ3pd0Ld6p8vi84hlA==

----END CUT----

----BEGIN RAW----

ERROR: C1 C2 LEN: 1 2 11
STR: 

MySQL: 5.5.37-log
USR OS, AGENT, VER: Mac CHROME 5.0.35
PMA: 3.5.8.2
PHP VER,OS: 5.4.23 Linux
LANG: en
SQL: 


----END RAW----

SQL query:



MySQL said:

#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 '' at line 1

Staff
9,962 Points
2014-06-26 9:58 pm
Hello Brad,

Unfortunately it looks like if you tried to include your SQL query, it got stripped by our commenting system. However the MySQL error that you're getting states that there is a syntax error on line 1, so that's where I would begin looking.

Seems like you probably have some mis-matching quotes in your syntax. I'd suggest using a tool like phpMyAdmin at your host and seeing if you can get the first line of your SQL backup to go through without an error. If it creates the same error, then double-check the syntax to ensure there is nothing off with the quotes.

- Jacob
n/a Points
2014-06-27 7:43 am

I will try that. Thank you.

 

- Brad

Staff
9,962 Points
2014-06-28 2:25 am
Hey Brad,

I just actually ran into this same MySQL error myself when trying to import a database in phpMyAdmin, and after a bit more research I think I found the problem.

It's confusing because the SQL error mentions line 1, but if you actually open up the .sql file and then go to the very last line, do you see something that looks like this:

ETXNULNULNULNULNULNULNULNULNUL


It seems like sometimes there is a bit of file corruption right at the end of the exported MySQL database file. It looks like all of my tables still got imported without a problem, but just to double-check I dropped them all, then removed this line from the end of the .sql file and tried again and it went through 100% successful.

Please let me know if that does the trick for you, if this is a common issue we'd like to add that to this article to hopefully help out other people with the same problem.

- Jacob
n/a Points
2014-06-30 9:55 am

Hi nim trying bto migrate a website for a client as one of the old partners in the firm has "gone rogue" shall we say.

Im trying to import a db dump into myphpmyadmin and im getting the following result.

"-- MySQL dump 10.13  Distrib 5.5.34, for debian-linux-gnu (x86_64)"
--
#NAME?
-- ------------------------------------------------------
"-- Server version	5.5.34-0ubuntu0.12.04.1"

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

 

MySQL said:

#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 '"-- MySQL dump 10.13  Distrib 5.5.34, for debian-linux-gnu (x86_64)" -- #NAME?' at line 1

Any help would be appreciated.

Staff
16,844 Points
2014-06-30 11:48 am
Hello David,
The error message seems to point to the first line, which is a comment. That is unusual as comments are not used in interpreting the statements. Take a look round the comment. The '#name? ' may be out of place. Try moving that under into a comment and trying again. Also, look through the file and see if there is any code that just seems out of place either at the top or the bottom.

Without seeing all content on the file I am just taking a guess, but please let us know if you have any further questions.

Kindest Regards,
Scott M
n/a Points
2014-07-03 3:25 am
#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 'HAVING distance
Staff
9,962 Points
2014-07-03 4:27 am
Hello bunny,

In order to help you troubleshoot your MySQL syntax problem we'd need to see the part of your query like the error mentions near the words HAVING distance.

- Jacob
n/a Points
2014-07-04 12:19 am

Hello there. i have a news site in my local language. if i import my sql file with my local language in sql file it show error. what can i do? :(

#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 ''<strong>????:</strong> ??????? ????? ???' at line 47

Staff
9,962 Points
2014-07-04 12:22 am
Hello mehedi,

In order to help you out, we'd have to see your SQL query around that line. As the error states, you have a syntax error at line 47 relating to your usage of ''<strong>????:</strong> ??????? ????? ???'.

Just looking at the error itself, I would guess you have an extra single quote ' on that line.

- Jacob
n/a Points
2014-07-06 8:31 am

Good Morning

I am trying to fill out my online time sheet at work. I was able to to it last week with no issues but this week i am getting this error message :

DB function failed with error number 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 '' at line 1 SQL=SELECT css FROM jos_formmaker_themes WHERE id=

Since I haven't done anything but attempt to open the time sheet is this issue with my computer or my boss's program?  I don't know what SQL is or where to even begin to look to fix it.

Thanks

Mychal

Staff
9,962 Points
2014-07-06 4:16 pm
Hello Mychal,

When your website needs to interact with a database you use SQL or Structured Query Language to talk to the database.

In this case it looks like you have a web application probably written in PHP on your website that is trying to use a SQL query to select some information, but there is a syntax error in that question.

MySQL is trying to tell you that the improper syntax happened on line 1 where it looks like SQL=SELECT css FROM jos_formmaker_themes WHERE id=

You usually would need to modify the SQL code to use the proper syntax, but unfortunately you wouldn't know what the proper syntax is unless you saw the entire query or compared it to the database.

For instance if that line simply ended with WHERE id= then it would be an incomplete WHERE clause, as it would need to be passed an id number. So possibly your website form isn't correctly passing that value to your script and that's why it's failing.

- Jacob
n/a Points
2014-07-09 4:45 pm

Hello to all. :-) I have a fairly simply query in phpMyAdmin:

explain UPDATE `articles` SET `text` REPLACE (`text`, 'a', 'a');

and this what I got

#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 'UPDATE `articles` SET `text` REPLACE (`text`, 'a', 'a')' at line 1 

I tried with ` and without but the same error appears. I was trying to replace â€“ with - and since I got an error I came down to simple replace a with a but the error is still here. Do you have any advice?

 

Thank you.

Staff
16,078 Points
2014-07-09 8:33 pm
Hello Histerius,

The syntax error is being indicated by the error message and it depends on the version of MySQL that you're using. Check the MYSQL - update documentation. When you view the documentation, the versions are in a column at left. Match the version you're using and check the documentation for examples and updates to the required syntax.

I hope this helps to provide the information you need.

Regards,
Arnel C.
n/a Points
2014-07-15 4:34 am

HI,

In stock file have following fields 

-SKU

-Model these 5 field from xml file 

-Quantity

-status

-price

-special price(have a formula for this)

-shipping cost(have a formula for this)

-allow cod(have a formula for this)

we are doing manually importing daily in our admin page.but we need automatically for reducing time. I have loaded xml file data to database(mysql 5.6).but now am struck next step is what? how to import from database.Am using php.So please help me.I need guide for this.....please help me...atleast not for php i need help for query(database)

for example 

in quantity field need to check frst if it is < 25 yes set 0 or else set same value 

in special price 1.25* Cashand price(this data is in xml file)  this ans is more than MRP put same as cashand price nly

Staff
16,844 Points
2014-07-15 8:47 am
Hello Roopa,

You will want to prep all your data and make any changes to it via the php. Once all data is in the form you need, then you can enter it into the database with a SQL query statement. The scope of this article does not go over how to create a MySQL query and at the time we do not have such an article. You can, however, check out site such as W3 Schools as they have great beginning tutorials for creating MySQL queries.

Kindest Regards,
Scott M
n/a Points
2014-07-25 5:30 am

use pm;

DROP PROCEDURE IF EXISTS calc_cm_quality_matrixview_v3;

DELIMITER //

-- *********************** PROCEDURE: calc_cm_quality_matrixview **********************************

too long - edited by moderator

Staff
8,310 Points
2014-07-25 9:42 am
Hello preethy,

Thank you for contacting us today. We have your code now, but we are not sure what you are asking.

Did you need help with this code, or do you have a question?

If you have any further questions, feel free to post them below.
Thank you,

-John-Paul
n/a Points
2014-07-28 8:49 pm

Hi all!! I,m having troubles with an update (sorry for my English) . Im having this error: #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 'from pedidos where lista_grupoid_27.coditem = pedidos.coditem and nropedido =29' at line 3.

I used to work with SQL Server, so I wrote the sentence as usual but it doesnt working.

This is the query that im trying to do:

update lista_grupoid_27

set tipostock = (lista_grupoid_27.tipostock - pedidos.cantidad)

from pedidos where lista_grupoid_27.coditem = pedidos.coditem

and nropedido =294399

thanks!!!!!

Staff
16,078 Points
2014-07-28 9:21 pm
Hello Florencia,

Thanks for the question. Unfortunately, we have no access to your account, so we can't take a look to see what version of MySQL you are running. You will need to verify it and then update the syntax. You can find the correct syntax version here. The error is most likely a result of the updated code syntax.

Regards,
Arnel C.
n/a Points
2014-07-29 9:05 pm

Hello Arn! Thanks for the answer, it was very helpfull!!! My error was use an inner join insetad a comma. Once again sorry for my poor english and thanks a lot for the help!

n/a Points
2014-07-31 2:00 am
Thank you so much for this article :-)

I think this will solve my issue. As about to improve this site more precise you can put in some more articles and Error solving replies, So any needy can see in and take the favor you do provide...

hmm.. and also good articles and error solving increase your visitor and traffics on your site ( "As I am here :-) " ) .. and this will bring free SEO for your site..
n/a Points
2014-08-04 8:54 am

Hi I have got this error and I'm kinda new to the world of coding. Is there a fix for this?#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 '?0?,

Staff
16,844 Points
2014-08-04 11:25 am
Hello WCS,

The general causes and information on where to look are listed in the article above. In your specific case, the error message shows you where to check your query. There is some code that appears as '?0?,. This is where your error is. Check around it to be sure you do not have all the quotes in the right place. This is a common cause for syntax errors and can be difficult to spot right away.

Again, be sure to read about the common causes above and see if any of those apply as well.

Kindest Regards,
Scott M
n/a Points
2014-08-08 2:52 am

I'm getting error 1604 type in mysql

my procedure is

DELIMITER $$USE `coordinates`$$DROP PROCEDURE IF EXISTS `getcoordinates`$$CREATE DEFINER=`root`@`localhost` PROCEDURE `getcoordinates`(IN in_cods VARCHAR(225))BEGINDECLARE cods VARCHAR(225);SET cods = in_cods;INSERT INTO singlecoordinates(COORDINATES)VALUES(cods);END$$DELIMITER ;

 

and custom function is

DELIMITER $$CREATE    /*[DEFINER = { user | CURRENT_USER }]*/    FUNCTION `coordinates`.`splitcods`(str VARCHAR(225),delim VARCHAR(2))    RETURNS VARCHAR(225) DETERMINISTIC        BEGIN        DECLARE C1 VARCHAR(225) DEFAULT '';    DECLARE C2 VARCHAR(225) DEFAULT '';    DECLARE C3 VARCHAR(225) DEFAULT '';    DECLARE C4 VARCHAR(225) DEFAULT '';    DECLARE C5 VARCHAR(225) DEFAULT '';    DECLARE C6 VARCHAR(225) DEFAULT '';    DECLARE C7 VARCHAR(225) DEFAULT '';    DECLARE C8 VARCHAR(225) DEFAULT '';    DECLARE C9 VARCHAR(225) DEFAULT '';    DECLARE C10 VARCHAR(225) DEFAULT '';    DECLARE TEMP VARCHAR(225)DEFAULT '';    DECLARE delcounter INT DEFAULT 0;    /*no of delimiters*/       DECLARE lcounter INT DEFAULT 0;    DECLARE choice INT;        SET delcounter = (LENGTH(str)-LENGTH(REPLACE(str,delim,'')))/LENGTH(delim) + 1;        WHILE(lcounter <= delcounter) DO       SET lcounter = lcounter+1;   SET choice = lcounter;       CASE choice           WHEN  choice=1 THEN SET C1 = SELECT REPLACE(SUBSTRING(SUBSTRING_INDEX(str,',',lcounter),LENGTH(SUBSTRING_INDEX(str,',',lcounter - 1))+ 1),',','');     WHEN  choice=2 THEN SET C2 = SELECT REPLACE(SUBSTRING(SUBSTRING_INDEX(str,',',lcounter),LENGTH(SUBSTRING_INDEX(str,',',lcounter - 1))+ 1),',','');      WHEN choice=3 THEN SET C3 = SELECT REPLACE(SUBSTRING(SUBSTRING_INDEX(str,',',lcounter),LENGTH(SUBSTRING_INDEX(str,',',lcounter - 1))+ 1),',','');       WHEN choice=4 THEN SET C4 = SELECT REPLACE(SUBSTRING(SUBSTRING_INDEX(str,',',lcounter),LENGTH(SUBSTRING_INDEX(str,',',lcounter - 1))+ 1),',','');        WHEN  choice=5 THEN SET C5 = SELECT REPLACE(SUBSTRING(SUBSTRING_INDEX(str,',',lcounter),LENGTH(SUBSTRING_INDEX(str,',',lcounter - 1))+ 1),',','');         WHEN  choice=6 THEN SET C6 = SELECT REPLACE(SUBSTRING(SUBSTRING_INDEX(str,',',lcounter),LENGTH(SUBSTRING_INDEX(str,',',lcounter - 1))+ 1),',','');          WHEN  choice=7 THEN SET C7 = SELECT REPLACE(SUBSTRING(SUBSTRING_INDEX(str,',',lcounter),LENGTH(SUBSTRING_INDEX(str,',',lcounter - 1))+ 1),',','');           WHEN  choice=8 THEN SET C8 = SELECT REPLACE(SUBSTRING(SUBSTRING_INDEX(str,',',lcounter),LENGTH(SUBSTRING_INDEX(str,',',lcounter - 1))+ 1),',','');            WHEN  choice=9 THEN SET C9 = SELECT REPLACE(SUBSTRING(SUBSTRING_INDEX(str,',',lcounter),LENGTH(SUBSTRING_INDEX(str,',',lcounter - 1))+ 1),',','');             WHEN  choicep=10 THEN SET C10 = SELECT REPLACE(SUBSTRING(SUBSTRING_INDEX(str,',',lcounter),LENGTH(SUBSTRING_INDEX(str,',',lcounter - 1))+ 1),',','');                                       ELSE                          INSERT INTO splitcoordinates('0','0','0','0','0','0','0','0','0','0');              SELECT * FROM splitcoordinates;         END CASE;    END WHILE;        INSERT INTO splitcoordinates(C1,C2,C3,C4,C5,C6,C7,C8,C9,C10);    SELECT * FROM splitcoordinates;        END$$DELIMITER ;

 

Error is:

 

Error Code : 1064You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT REPLACE(SUBSTRING(SUBSTRING_INDEX(str,',',lcounter),LENGTH(SUBSTRING_INDE' at line 28Execution Time : 00:00:00:000Transfer Time  : 00:00:00:000Total Time     : 00:00:00:000eg table structure:

table t1                    table t2

col 1                             col1  col2  col3  col4  col5

22,8,1,8,812                   22    8      1     8      812

Staff
16,844 Points
2014-08-08 9:07 am
Hello Rajesh,

Unfortunately I am not able to diagnose the entire procedure, but do take note in the error message. It states:

near 'SELECT REPLACE(SUBSTRING(SUBSTRING_INDEX(str,',',lcounter),LENGTH(SUBSTRING_INDE' at line 28

Open your procedure fully and look for the SELECT statement at line 28. Step through the query there and check for the things listed in the article above. This should narrow down the query and help you find the offending code.

Kindest Regards,
Scott M
n/a Points
2014-08-09 5:18 am

Thanking you scott for replying but still getting the error. I want a solution for this

 

1)      Create  a TABLE  name as MYSTRING

C1

12,23,45,67,88,34,67,55,232,21

COLUMN C1 VARCHAR(225);

Example :   I want to extract  12 from Table MYSTRING and  I want to insert into c1 of TABLE split_string.

 And I want to extract 23  from Table MYSTRING and  I want to insert into c2 of TABLE split_string

2)      Create TABLE name as split_string which as 10 columns from C1 to C10

C1

C2

C3

C4

C5

C6

C7

C8

C9

C10

12

23

45

67

88

34

67

55

232

21

 

 

Staff
16,844 Points
2014-08-11 12:53 pm
Hello Rajesh,

Unfortunately we cannot provide coding solutions. The best we can do is to point you in the right direction. Using the error message to determine the location of the error and then reviewing the possible causes above in the article should give you the tools to diagnose any issue you are having with the procedure.

Kindest Regards,
Scott M
n/a Points
2014-08-08 3:11 am

I received the following error and I am not sure how to fix it,while submiting my wordpress form.Please help.

DB function failed with error number 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 '@gmail.com AND name = "email"' at line 1 SQL=SELECT * FROM wp_facileforms_subrecords WHERE value = abc@gmail.com AND name = "email"

Staff
16,844 Points
2014-08-08 9:12 am
Hello Sonali,

The error message states:

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 '@gmail.com AND name = "email"' at line 1

Since the entire query is a single line, the "at line 1" doesn't help much. But take a look at where it says " near '@gmail.com AND name = "email"'. This is the section where the error occurs.

Taking a look at your entire query string you can notice that the 'abc@gmail.com' portion does not have any quotes around it. As it is a string, it needs to, otherwise the server will try to process it as avaliable. This will fail, which is why you are getting the error. It falls under the "Incompatible data types into a field" section above.

Kindest Regards,
Scott M
n/a Points
2014-08-08 2:50 pm

i keep getting error 1064 and i dont know why any help would be appreciated 

 

 

GRANT USAGE ON 'mysql6.000webhost.com'* TO 'a7893964_dolphin'@'mysql6.000webhost' IDENTIFIED BY 'pass';

Staff
8,310 Points
2014-08-08 5:04 pm
Hello ty,

Thankyou for your question. We are happy to help, but will need some additional information. Can you provide a link to the error?

What are you trying to accomplish?

Are you using a CMS such as WordPress, Joomla, or Concrete5?

If you have any further questions, feel free to post them below.
Thank you,

-John-Paul
n/a Points
2014-08-09 9:04 pm

well i actually figured it out right after i posted that message. it was simple i was trying to make a database for a php file i had already made a database for. but ran into another problem,  i am installing a script that i plan to use using 000 webhost. anyway this is the new problem 

 

Fatal error: require_once() [function.require]: Failed opening required '/usr/local/apache/htdocs/Dolphin-v.7.1.4/inc/security.inc.php' (include_path='.:/usr/lib/php:/usr/local/lib/php') in /home/a7893964/public_html/Dolphin-v.7.1.4/inc/header.inc.php on line 163

 

 

not sure why this is showing up ive installed this script a few times b4 and have never seen this. i believe the file it can not find is there but i could be looking into the wrong place, honestly idk.. and thanks in advance!!

Staff
16,844 Points
2014-08-11 12:38 pm
Hello Ty,

That particular error usually means one of these three things:

The file doesn't exist.
The root path is set incorrectly.
The current working directory isn't what is expected, which can cause the full path to break if the root path is relative.

Kindest Regards,
Scott M
n/a Points
2014-08-10 11:50 pm

A Database Error Occurred

Error Number: 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 '.news_id, `n`.`news_title`, `n`.`author_name`, `n`.`cat_publish`, `n`.`breaking_' at line 2

SELECT * FROM (`tbl_add_news`, `SELECT` n.news_id, `n`.`news_title`, `n`.`author_name`, `n`.`cat_publish`, `n`.`breaking_news`, `n`.`featured_news`, `c`.`cat_name` FROM tbl_add_news as n, `tbl_add_category` as c WHERE n.cat_id=c.cat_id)

Filename: C:\xampp\htdocs\newsportal\system\database\DB_driver.php

Line Number: 330

 

How i fix it?

This is my sql join query.. it seem ok in my localhost/phpmyadmin but browser show me all time above message. I can't fix it. please help me some one to fix that problem

"SELECT n.news_id,n.news_title,n.author_name,n.cat_publish,n.breaking_news,n.featured_news, c.cat_name FROM tbl_add_news as n, tbl_add_category as c WHERE n.cat_id=c.cat_id";

Staff
16,844 Points
2014-08-11 11:46 am
Hello Riyad,

Look at the error message and you can see that it says the error starts near n.news_id. That is where you need to evaluate what is incorrect. Read the above to see if any of the causes fit. Just eyeballing it, I can see that it is the only field you have that is not using the backticks around it like the others. Maybe that has something to do with it.

Kindest Regards,
Scott M
n/a Points
2014-08-12 12:57 pm

Hello 

Mr Scott 

 

Thank you for replay me. But mine sql query work perfectly in my phpmyadmin database sql. This commen smoothly find out the data from database when i checked in phpmyadmin. I try so many time to fix it but same problem has been apperead all the time.

Would you please specify breifly discuss Sir.

 

Best

Rgs

Riyad 

Staff
16,078 Points
2014-08-12 1:25 pm
Hello Riyad,

The phpMyAdmin is a GUI that is separate from the MySQL command line. It processes the MySQL command line differently. If you're having continued errors in the script as indicated by the error message, then the problem is most likely related to the syntax you're using NOT being compatible with your version of MySQL. You can view the the different versions of MySQL Syntax here. You will need to review your code per your version of MySQL as they continually deprecate older code as they upgrade.

Apologies that we can't provide a direct answer. Hopefully, this will give you the information you need.

Regards,
Arnel C.
n/a Points
2014-08-13 5:06 am

Hi fellows.

I do have a problem when i want to insert new records into the database.

It shows :

 

#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 '[value-1],[value-2],[value-3],[value-4],[value-5],[value-6],[value-7],[value-8],' at line 2

 

The SQL query is:

INSERT INTO `invois`(`id_invois`, `idProjek`, `iAlamatPelanggan`, `iBandar`, `iPoskod`, `iNegeri`, `iNoTelefon`, `iNoFaks`, `iemel`, `no_sebutharga`, `tarikh_harga`, `no_kontrak`, `tarikh_kontrak`, `no_invois`, `tarikh_invois`, `no_do`, `tarikh_do`, `terma_bayaran`, `bayaran`, `jumlah_invois`, `kos_kasar`, `margin_kasar`, `status_bayaran`, `tarikh_bayaran`, `kaedah_bayaran`, `deposit_kpd`, `no_cek`, `tarikh_cek`, `kenyataan`) VALUES ([value-1],[value-2],[value-3],[value-4],[value-5],[value-6],[value-7],[value-8],[value-9],[value-10],[value-11],[value-12],[value-13],[value-14],[value-15],[value-16],[value-17],[value-18],[value-19],[value-20],[value-21],[value-22],[value-23],[value-24],[value-25],[value-26],[value-27],[value-28],[value-29])Can you guys help me to fix it?

Thanks

-Shira

Staff
16,844 Points
2014-08-13 11:42 am
Hello Shira,

The error message is telling you that the incorrect syntax begins with '[value-1]'. My guess is the square braces, I have never seen an INSERT statement with square braces around the values. Also, ensure that the values are variables that are previously defined and had values assigned in your code.

Be sure to test your changes by fixing the first value. If it works, the error will refer to the second value. Then you know you can go ahead and change the rest.

Kindest Regards,
Scott M
n/a Points
2014-08-14 8:40 am

Hai scott, hw ru ,

Finally I solved my procedure problem how to split the string in small chunks based upon the delimiter and inerting into another table with different columns posted on 2104-08-08.

Staff
16,844 Points
2014-08-14 9:12 am
Hello Rajesh,

We are glad to hear that! It just makes you that much more awesome at MySQL! Be sure to visit again if we can help with anything.

Kindest Regards,
Scott M
n/a Points
2014-08-15 11:30 am

Dear Scott,

                  Thanks for the reply Mr.Scott. By the way I 'm having one problem that i have to calculate the idle time from the table t1 using the triggers in MYSQL and I have to insert the values in table t2.

FOR EXAMPLE:

FROM TABLE T1 FROM ID 438 TO 442 THE VALUE OF LATITUDE & LONGITUDE REMAINS SAME FOR A PERIOD OF TIME OF A PARTICULAR USER_ID=25.

I NEED TO CALCULATE THE IDLE TIME OF USER ID OR IMEINO LIKE

IDLE TIME = DATTIME VALUE OF ROW ID 442 - DATTIME VALUE OF ROW ID 438

START TIME = DATTIME VALUE OF ROW ID 438

END-TIME = DATTIME VALUE OF ROW ID 442

LATITUDE  & LONGITUDE - DECIMAL(12,6)

I'm waiting for ur reply Mr.SCOTT

table t2 structure

id , userid, starttime , endtime, idletime, imei_no,latitude,longitude.

 

 

table t1 columns are       id , user_id  , latitude  , longitude , date_time_received , 

imei_no                                    

 

433 25 13.041857 80.19887 2014-08-07 15:52:40   352944054874253 434 25 13.041864 80.198868 2014-08-07 15:52:53   352944054874253 435 25 13.041868 80.198868 2014-08-07 15:53:00   352944054874253 436 25 13.041862 80.198868 2014-08-07 15:54:00   352944054874253 437 25 13.041869 80.198868 2014-08-07 15:55:00   352944054874253 438 25 13.041887 80.198871 2014-08-07 15:55:38   352944054874253 439 25 13.041887 80.198871 2014-08-07 15:55:42   352944054874253 440 25 13.041887 80.198871 2014-08-07 15:55:47   352944054874253 441 25 13.041887 80.198871 2014-08-07 15:55:52   352944054874253 442 25 13.041887 80.198871 2014-08-07 15:56:00   352944054874253
Staff
16,844 Points
2014-08-15 11:42 am
Hello Rajesh,

While I am happy to help point you in the direction of finding a syntactical error, what you are asking for is a custom code request. This is something you would need to find a developer to create for you.

The Support center is designed for one to many support, meaning answering general questions that will apply to many people. You need someone that can take the time to create not only the code, but a testing environment so they can give you the exact working code you desire.

Please feel free to visit us, however, if you have any general questions or errors when working with your code or MySQL.

Kindest Regards,
Scott M
n/a Points
2014-08-22 12:01 pm

Hi,

I just got this error on phpmyadmin in xampp.

Error

 

SQL query:

 

CREATE TABLE `tbl_admin` (

`id` int( 5 ) NOT NULL AUTO_INCREMENT , `username` varchar( 200 ) NOT NULL default '', `password` varchar( 200 ) NOT NULL default '', PRIMARY KEY ( `id` )

) TYPE = MYISAM AUTO_INCREMENT =2;

MySQL said: Documentation

#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 'TYPE=MyISAM AUTO_INCREMENT=2' at line 6

 

Please me Solve This error..

 

 

Staff
16,078 Points
2014-08-22 1:00 pm
Hello Jay,

Sorry for the problems with MySQL. When you run XAMPP all of the server things (including MySQL) are loaded locally on your computer. We generally provide support for issues more closely associated to our hosting services. However, we'll try to help in this case because the problem is actually something that you can determine by simply looking at the error message.

The error that you're seeing indicates that the problem with the syntax is associated with your MySQL code near 'type=isam'. Your code needs to be compliant to the version of MySQL per your server.

The most likely issue is that you should not be using "TYPE=MYISAM" but "ENGINE=MYISAM". If you're running MYSQL at least 5.1 then ENGINE would be the appropriate syntax. The code that you're using was deprecated in MYSQL version 4.1 and the code finally became obsolete in version 5.1. You can find references to this issue here:

MySQL 5.1 Reference for MYISAM storage engine
MYSQL 5.0 Reference for MYISAM storage engine

If you don't correct your MYSQL code, then you will more than likely run into the same problem with other MYSQL code on your website.

I hope that helps to clarify the issue!

Regards,
Arnel C.

Post a Comment

Name:
Email Address:
Phone Number:
Comment:
Submit

Please note: Your name and comment will be displayed, but we will not show your email address.

153 Questions & Comments

Missing data

Post a comment

Back to first comment | top

Need more Help?

Search

Ask the Community!

Get help with your questions from our community of like-minded hosting users and InMotion Hosting Staff.

Current Customers

Chat: Click to Chat Now E-mail: support@InMotionHosting.com
Call: 888-321-HOST (4678) Ticket: Submit a Support Ticket

Not a Customer?

Get web hosting from a company that is here to help. Sign up today!