SQL syntax

[mlbfan]

I am a mySQL novice and I apologize for my obvious lack of basic knowledge. I am, however, trying!

On to the problem, I am using phpMyAdmin and want to continue doing so until there is a better mySQL gui. When working with data I occassionally receive the following:

****************************************

Error
SQL-query:

SELECT PersonID,Year,Team,Uniform from Rosters where 1 and Year = 1995 and Team like \\
MySQL said: You have an error in your SQL syntax near '\\' at line 1
****************************************

What should I be looking for to fix this error? The odd thing is that the even though the error is received, the data is changed as I intended. I would like to fix the error though and need to know some items that I should be looking for.

The schema for the table above is:

CREATE TABLE Rosters (
[nbsp][nbsp] PersonID varchar(12) NOT NULL,
[nbsp][nbsp] Year smallint(4) unsigned DEFAULT '0' NOT NULL,
[nbsp][nbsp] Team char(3) NOT NULL,
[nbsp][nbsp] Uniform varchar(10) DEFAULT '-' NOT NULL,
[nbsp][nbsp] PRIMARY KEY (PersonID, Year, Team)
);

I am eagerly awaiting any advice,

Sean
http://baseball-almanac
Baseball Almanac

[Arthur]

I don't think it's the SQL syntax, but I think it's the \. PHP is escaping the slash by putting another slash in front and MySQL doesn't like the two slashes. One slash in SQL queries is allowed. So, don't put a slash in long queries.

BTW, "where 1", why is that in your query, it's always true and doesn't seem to serve any purpose?

HTH,
Arthur

[mlbfan]

Hello Arthur,

>>So, don't put a slash in long queries.
>>BTW, "where 1", why is that in your query, it's always
>>true and doesn't seem to serve any purpose?

The slashes and the 1 are simply items that appear in the routine use of phpMyAdmin. If it is simply a bug in their script then I'll have to wait for a new version or a better alternative to that interface. If its something in my actual database then it was something I wanted to fix before getting past my first script and into my third or fourth...

Sean

[dank]

If that is indeed the cause of the error -- often pops up in phpMyAdmin when re-inserting its own table dump schema -- changing the single quotes to double quotes before inserting often works.

Dan

[trafficg]

Hi

SELECT * from Rosters where Year=1995 and Team like '%team%'

This query will return all teams containing the word “team” where the year is 1995. e.g.. myteam, teams ....

If you don't want to use pattern matching don't use LIKE

SELECT * from Rosters where Year=1995 and Team='team'

If you want to return all entries where team is empty.

SELECT * from Rosters where Year=1995 and Team=''

I would also be careful using words like "Year" as a column name, this could well be a reserved keyword, I would change it to just "Y"

I have used * in the queries because it is easier than typing out all the column names, plus MySql will probably parse the query quicker.

Pete Kelly
Free Traffic and Website Promotion!
http://www.TrafficG.com

[mlbfan]

I tried:

>>SELECT * from Rosters where Team='CHN';<<

and same results - only received two syntax errors! Items still updated and worked fine in the actual data, but errors were still being mentioned.

>>There is a newer version of phpMyAdmin which you could try.<<

And upgraded to the new version as well - which makes me VERY happy regardless if I can ever get this syntax error gets fixed!

Looking forward to ANY other suggestions,

Sean

[trafficg]

Hi

I just re-created your DB and inserted some test data

<code>
CREATE TABLE Rosters (
[nbsp][nbsp] PersonID varchar(12) NOT NULL,
[nbsp][nbsp] Year smallint(4) unsigned DEFAULT '0' NOT NULL,
[nbsp][nbsp] Team char(3) NOT NULL,
[nbsp][nbsp] Uniform varchar(10) DEFAULT '-' NOT NULL,
[nbsp][nbsp] PRIMARY KEY (PersonID, Year, Team)
);

#
# Dumping data for table 'Rosters'
#

INSERT INTO Rosters VALUES ( '12345678', '1985', 'abc', 'blue');
INSERT INTO Rosters VALUES ( '1234567', '1985', 'CHN', 'gray');

</code>

I then ran this query &quot; SELECT * from Rosters where Team='CHN' &quot; and everything worked fine.

The next thing I would try if I was you is repairing the table then try your query again.

REPAIR TABLE Rosters

Pete Kelly
http://www.TrafficG.com

[Arthur]

There is a newer version of phpMyAdmin which you could try. It's a pre-release non-official version though. You can find it at: http://phpmyadmin.sourceforge.net/