I am having a little trouble figuring something out in MySQL and 90% of the online manuals I've read don't really help at all.
First of all, I have a 4.X version of MySQL--I think 4.1.something or other.
Here's what I am trying to do: I want to limit a database query to only include stories that have not been updated for "X" amount of time (most likely 6 months and beyond). However, every suggestion I have tried from manuals gives me a syntax error. Boo.
The current code looks like this:
$result = dbquery("SELECT story.title as storytitle, story.uid, story.sid, story.catid, story.updated, story.completed, "._PENNAMEFIELD." as penname FROM (".TABLEPREFIX." fanfiction_stories as story, "._AUTHORTABLE.") WHERE story.completed = '0' AND story.uid = "._UIDFIELD." ORDER BY story.updated;");
And I want to limit it to only include stories where story.updated is 6 months or more older than the current system time.
If you'd like to see what specifically I am trying to do, here's the info:
http://www.mickiclark.com/efiction33/
login: efiction, password: test
Select "inactive fictions" from the admin menu or use this link to go straight there: http://www.mickiclark.com/efiction33/admin.php?action=inactive
So...if anyone knows of a MySQL manual that doesn't expect you to already know what you're doing, or knows how to modify my code to do what I'm looking for, I would be eternally grateful. 🙂
URL to your eFiction: http://www.mickiclark.com/efiction33
Version of eFiction: 3.3.1
Have you bridged eFiction, if so with what?: SMF
Version of PHP:
Version of MySQL: MySQL 4.1.14
I removed the brackets and the space...see if it works?
$result = dbquery("SELECT story.title as storytitle, story.uid, story.sid, story.catid, story.updated, story.completed, "._PENNAMEFIELD." as penname FROM ".TABLEPREFIX."fanfiction_stories as story, "._AUTHORTABLE." WHERE story.completed = '0' AND story.uid = "._UIDFIELD." ORDER BY story.updated;");
archive:
site:
Available for skin/mod commission! 🙂
I think you misunderstood me. The existing code works, I'm just trying to figure out how to add the time limit to it.
URL to your eFiction: http://www.mickiclark.com/efiction33
Version of eFiction: 3.3.1
Have you bridged eFiction, if so with what?: SMF
Version of PHP:
Version of MySQL: MySQL 4.1.14
Oh! Sorry.
AND (DATE(`updated`) BETWEEN DATE(SUBDATE(CURDATE(), INTERVAL 6 MONTH)) AND CURDATE())
That's something I use on another script.
archive:
site:
Available for skin/mod commission! 🙂
THANK YOU!!!!
I modified it slightly to fit my purposes, but that worked much better than what I was trying to do with the timestamp!
AND (DATE(`updated`) <= DATE(SUBDATE(CURDATE(), INTERVAL 6 MONTH)))
URL to your eFiction: http://www.mickiclark.com/efiction33
Version of eFiction: 3.3.1
Have you bridged eFiction, if so with what?: SMF
Version of PHP:
Version of MySQL: MySQL 4.1.14
Awesome, glad I could be of some help. 😀
archive:
site:
Available for skin/mod commission! 🙂
Um. Take the recent browse panel and change > to <.
That would have worked if we only wanted a list, but we wanted something similar to the queue. Our inactive.php is a modification of submitted.php.
URL to your eFiction: http://www.mickiclark.com/efiction33
Version of eFiction: 3.3.1
Have you bridged eFiction, if so with what?: SMF
Version of PHP:
Version of MySQL: MySQL 4.1.14
The query is the same for both I believe.
