URL to your eFiction: http://xenaverse.net/scrolls
Version of eFiction: 3.5
Have you bridged eFiction, if so with what?: SMF, and SMF to Mediawiki
Version of PHP: 5.2.5 (custom)
Version of MySQL: 5
Have you searched for your problem: yes
If so, what terms did you try: Error: (1054) Unknown column 'date' in 'field list'
State the nature of your problem:
A fatal MySQL error was encountered.
Query: SELECT *, UNIX_TIMESTAMP(date) as date FROM smf_members as author LEFT JOIN fanfiction_authorprefs as ap ON ap.uid = author.ID_MEMBER WHERE author.ID_MEMBER = '1' LIMIT 1
Error: (1054) Unknown column 'date' in 'field list'
Do you have a test account for us? n/a
The same error occurs for me in 3.5 with the SMF bridge when going to view a user:
https://efiction.org/forums/index.php?topic=6479.0
I also receive this error when I try to update my user profile:
A fatal MySQL error was encountered.
Query: SELECT * FROM smf_members as author WHERE penname = 'Amarante'
Error: (1054) Unknown column 'penname' in 'where clause'
From my research (and little knowledge), it seems like this is a bug in MySQL 5.
http://bugs.mysql.com/bug.php?id=13551
http://mattiasgeniar.be/page/3/
> #1054 - Unknown column ‘table1.columnname’ in ‘on clause’
While that would have been a valid query for previous MySQL versions, there is now a requirement to add round brackets around the tables you want to select through a “FROM”-clause.
EDIT: So I've been trying to play around with possible fixes on this section of queries.php from the bridge that I believe is causing the problems:
define ("_MEMBERCOUNT", "SELECT COUNT(DISTINCT "._UIDFIELD.") FROM "._AUTHORTABLE." LEFT JOIN ".TABLEPREFIX."fanfiction_stories as stories ON stories.validated > 0 AND (FIND_IN_SET("._UIDFIELD.", stories.coauthors) > 0 OR stories.uid = "._UIDFIELD.") LEFT JOIN ".TABLEPREFIX."fanfiction_authorprefs as ap ON "._UIDFIELD." = ap.uid");
http://dev.mysql.com/doc/refman/5.0/en/left-join-optimization.html
I have run into this with my vb bridge. The problem is in profile.php, the date field is hard-coded into the query, so if the file is not swapped you get the errors.
UNIX_TIMESTAMP(date) as date
This part should probably be split out into queries.php in later updates. However, for now you need to find what UNIX_TIMESTAMP(date) should be in SMF. It has been so long since I worked with it I am quite rusty...probably something like joindate. if you can check the smf_members table in phpmyadmin you should be able to find the field.
Thanks Calash, I will give it a try. I was also looking at that in queries.php, but I'm still learning and fiddling around, so I'm a wee bit clueless.
Hm I believe it's dateRegistered, I found it through the smf_members table like you said, and it had occurrences in both profile.php's. However I'm not entirely sure what I should be changing. Do you mean in includes/profile.php of eFiction I'm assuming, or /Sources/Profile.php of SMF?
UNIX_TIMESTAMP as you mentioned, is in queries.php
define ("_STORYQUERY", "SELECT stories.*, "._PENNAMEFIELD." as penname, UNIX_TIMESTAMP(stories.date) as date, UNIX_TIMESTAMP(stories.updated)
and that is the only occurrence.
Actually.....I may have over complicated this for you, sorry about that 🙂
The SMF bridge files in /bridges/SMF contain a profile.php file. Copy this to /user/ directory and see if that clears up your problems. Both of these will be in the default eFcition directory.
Haha, interesting that seems to have fixed the problem, thanks Calash! I guess that needs to be kept on a side note as a sort of bugfix.
