I have a problem...

[rogueusmc]

I am upgrading my forum software and got this error message...how do I edit the line?

Updating and creating indexes... Unsuccessful!
This query:

ALTER TABLE witpsmf_smileys
ORDER BY LENGTH(code) DESC;

Caused the 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 'LENGTH(code) DESC' at line 2

any ideas?

[Neat Pete]

My first guess would be that "code" is a reserved word.

[HalfaBee]

From the manual
ORDER BY enables you to create the new table with the rows in a specific order. Note that the table does not remain in this order after inserts and deletes. This option is useful primarily when you know that you are mostly to query the rows in a certain order most of the time. By using this option after major changes to the table, you might be able to get higher performance. In some cases, it might make sorting easier for MySQL if the table is in order by the column that you want to order it by later.

ORDER BY syntax allows for one or more column names to be specified for sorting, each of which optionally can be followed by ASC or DESC to indicate ascending or descending sort order, respectively. The default is ascending order. Only column names are allowed as sort criteria; arbitrary expressions are not allowed.

[Neat Pete]

My guess was wrong!

[HalfaBee]

I didn't know the answer so I read the manual ;)

[rogueusmc]

But I never changed anything...how DO I change the syntax? Can I just delete that table and regenerate it after my upgrade?


Lee

[rogueusmc]

well, I never got any ideas from anyone...so I reinstalled my forum software using the same database...now nothing is there anymore...I figured the database data would stay intact, is this not so?

[HalfaBee]

You could try

ALTER TABLE witpsmf_smileys
ORDER BY `code` DESC;

But as we have little information about what you are trying to do, this is just a guess.

[rogueusmc]

This is what I was trying to find out is how to correct the syntax...I got into the admin but couldn't see where to edit the code lines...

I have since, deduced that I could drop the php for that table down and edit in Dreamweaver?

Lee

[Neat Pete]

In Australia, this time wasting and frivolous behaviour behaviour is called "playing silly buggers". (Macquarie Dictionary 1982 edition, page 88).

The problem is the word LENGTH. If you ALTER a table by sorting it, you can only sort on the content of the columns as they stand, no functions allowed.

However, if you use a SELECT clause, you can use functions to get a display sorted by the length of a field for example.

At first glance, I read the ALTER as SELECT, and since it then looked ok, I suggested the word 'code' might be a problem. Sorry about that.

Personally, I would never use Code as the name of a field without a qualifier like CameraCode or CountryCode. And if there is a set list of these codes, then they should be in their own table CameraCodes or CountryCodes or whatever.

[rogueusmc]

But there's the rub...I never worked on the tables...I don't know even how to get to edit the lines...that's why I came here, to find out how to get to the code to edit it.

Done deal now though...I had a stock copy of the php for that table that I could have just uploaded the unedited version and been good to go (I figured this out after I wiped it all out and started over.

Thanks for all the attempts at assisting my ignorant butt...lol

Semper Fi,
Lee