Is it just me or the categories admin doesn't work how I thought it's supposed to work? 😕
1. First of all, should all admins be assumed to have posted a story? And a validated one at that?
2. The Admins link on the Categories page should take us to a page that listed the admins for that category only, right? And not all admins?
3. Then, the Add New Admin link on the admin list page should take us to a list of Admins not administering the current category, right? So that new admins can be added?
Then I found several things wrong with the queries in /admin/admins.php.
For point 1, on line 100 ($authorquery = "SELECT...), why would it be stories.uid, and later '...AND validated = 1...'? I've no problem with LEFT JOINing the stories table, because we need the story count, but for admins that have not written any story, stories.uid will be empty, and the members_list.php will have empty uid links.
I think it's better to change stories.uid with _UIDFIELD and remove the 'validated = 1' check altogether.
For point 2, have you found a way to select ONLY the admins whose 'category' field contain the category id and not other admins? If not, then let me share with you my hours of swimming in the MySQL documentation and direct you to the FIND_IN_SET() function. Here's its entry in the documentation
FIND_IN_SET(str,strlist)
Returns a value in the range of 1 to N if the string str is in the string list strlist consisting of N substrings. A string list is a string composed of substrings separated by ‘,’ characters. If the first argument is a constant string and the second is a column of type SET, the FIND_IN_SET() function is optimized to use bit arithmetic. Returns 0 if str is not in strlist or if strlist is the empty string. Returns NULL if either argument is NULL. This function does not work properly if the first argument contains a comma (‘,’) character.
mysql> SELECT FIND_IN_SET('b','a,b,c,d');
-> 2
And since the categories column is a comma-delimited list of categories the author is administering, hey, presto 😆 any row that has a non-zero FIND_IN_SET() is currently administering the selected category. Tammy-sama, did you plan this or is it just a big coincidence?
Considering points 1 and 2, in /admin/admins.php line 100 right after '$countquery = "SELECT...' I have:
$authorquery = "SELECT count(stories.sid) as stories, "._PENNAMEFIELD." as penname, "._UIDFIELD." FROM ".TABLEPREFIX."fanfiction_authorprefs as ap, "._AUTHORTABLE." LEFT JOIN ".TABLEPREFIX."fanfiction_stories as stories ON ("._UIDFIELD." = stories.uid) AND "._UIDFIELD." > 0 WHERE ap.level > 0 ".(isset($letter) ? " AND $letter" : "")." AND ap.uid = "._UIDFIELD." AND FIND_IN_SET(".$_GET['category'].",ap.categories) > 0 GROUP BY "._UIDFIELD;
Finally, after figuring out how to do points 1 and 2, it's easy to come up with what to do for point 3. In line 70 ('$query = dbquery("SELECT...') right after 'if($do == "new")', just add FIND_IN_SET() and you're all, uh, set 🙂
My /admin/admins.php line 70 reads:
$query = dbquery("SELECT "._PENNAMEFIELD." AS penname, "._UIDFIELD." AS uid, ap.level FROM "._AUTHORTABLE." LEFT JOIN ".TABLEPREFIX."fanfiction_authorprefs AS ap ON "._UIDFIELD." = ap.uid WHERE FIND_IN_SET(".$_GET['category'].", ap.categories) = 0 ORDER BY "._PENNAMEFIELD);
I'm not sure if this is a fix or a mod. So, my question to the forum is...which one is it? 😕
Next, I'll be pondering how to boot admins from administering a certain category. Or, perhaps Tammy-sama has already thought of this too? It'll be better for my sanity if you already do. But please don't make me search. I'll soon give up and went out of my way to write my own code. How did you think I got to find FIND_IN_SET()? 🙁
Cheers,
SQ
I think it doesn't work how you thought it did.
As for your first point:
For point 1, on line 100 ($authorquery = "SELECT...), why would it be stories.uid, and later '...AND validated = 1...'? I've no problem with LEFT JOINing the stories table, because we need the story count, but for admins that have not written any story, stories.uid will be empty, and the members_list.php will have empty uid links.
I think it's better to change stories.uid with _UIDFIELD and remove the 'validated = 1' check altogether.
Users are assigned a uid when they register not when they submit a story and you've got to have the validated=1 part or else it will count stories that aren't validated. (I'm assuming the part of the code you are referring to is the author list with the number of stories they've posted in parenthesis?)
Ah, so that's why there's a validated = 1 there. Alright, I suppose it's ought to be there.
Users are assigned a uid when they register not when they submit a story
Yes, which is why I wonder why's there a stories.uid there instead of authors.uid. Members who have never posted a story, admin or not, will not have a stories.uid. Then when the uid is used for the uid parameter in the members_list.php, it will show up empty. Links would show up like http://domain/viewuser.php?uid= and mess things up.
I think you are misunderstanding how the query works. You didn't copy the whole thing and I'm not about to look it up, but I'm pretty sure it's still the query that counts the stories so it needs to get the uid from the stories table and match it up with the uid from the authors table to know which author wrote which stories. Otherwise the output would just be a list of numbers and you'd have to guess which member was which.
I think you are misunderstanding how the query works. You didn't copy the whole thing and I'm not about to look it up, but I'm pretty sure it's still the query that counts the stories so it needs to get the uid from the stories table and match it up with the uid from the authors table to know which author wrote which stories. Otherwise the output would just be a list of numbers and you'd have to guess which member was which.
Of course I copied the whole thing. I copied from variable assignment to the last semicolon. I even put the filename and line number.
But anyway, I do understand that it counts the stories and match it with the author's uid. What I'm having a problem with is that the query is set so that the uid parameter in members_list.php is using stories.uid instead of author.uid, and so the uid won't show if the particular author has never uploaded a story ever, thus having no match between stories table's uid and author table's uid.
...Are we even in the same page? I'm confused... 😕
You are very confused. I explained why above. I'm not going to argue with you...I'll only say that if it worked the way you think it does, it would have been reported as a bug long ago. Don't believe me? Make an admin who has no stories posted and see what happens. If there is an empty uid, then report it as a bug.
You are very confused. I explained why above. I'm not going to argue with you...I'll only say that if it worked the way you think it does, it would have been reported as a bug long ago. Don't believe me? Make an admin who has no stories posted and see what happens. If there is an empty uid, then report it as a bug.
I did. How did you think I found out about it? I'm not a theory guy who looks at code and predicts how it will turn out. I write code and will only know if it breaks when I try it.
I made the resolution of trying all of eFiction's many functions before using it live. I made an admin account, posted no stories (yet), added a category, adds the account to the category...and everything happened as I said in my first post. If you it's a bug then it's a bug. I believe no one's reported it because no one's as hard-headed as me. My nature as a programmer is to not trust any code (Sorry if I sound obnoxious, but hey, I'm a guy. We're obnoxious.) I make a living writing code and trying to break it by inputting normally impossible situations. Like admins with no stories.
Actually, it's not supposed to be so abnormal. Some admins would like to stay admins and not authors. Thus, I propose a deeper change: separating admins and authors altogether. If admins want to post a story, do it as an author. On a different account. Then, no one will ever have a story posted under their admin account, and all this will go away. It could even be more secure if admins and authors data are in separate tables.
I do love a good argument, though, coming from a somewhat defunct high school debate club, but I feel that you're not reading my arguments at all. Well, maybe a little at first. If you did, you'd notice the phrase I purposely gave a boldface.
Anyway, I'm really sorry if I rub anyone the wrong way. I'm a socially-inept male software engineer and self-proclaimed Dilbert clone. Sue me.
Cheers,
SQ
I actually didn't address points number 2 and 3 at all because I felt Tammy would be the better person to do so.
Right now, though, I feel like you are trying to provoke me and I'm not going to take the bait (or perhaps I already did just by replying). I was on the debate team in high school too (and college), and I know the difference between debate and arguing. I'm only going to ask this. Did you test the unmodified code? I ask because I have admins with no stories posted and I'm not having any issue with empty user ids. I haven't updated to 3.3 yet though, so if you did test with the unmodified code and you are having that issue, then it's a bug and needs to be reported in the bugs forum.
Your new idea about separating admins and authors, as well as your points 2 and 3 from your original post need to go in the new feature suggestions forum because it doesn't work like you are suggesting it should, thus making it work like that would be a new feature.
I suppose now I'm provoking you. I'm a bitchy woman with PMS. Sue me.
I'm kidding about the last part. Sort of.
Yes, where is Tammy-sama, anyway? Not that I'm anywhere near important enough to be graced by her presence. Heck, I'm nowhere near important enough to be noticed by anyone 😀 And I like my anonymity just fine, thanks.
Anyway, I'm sorry if I made you feel like that. I guess I can't really blame my tone on my stupid project at work. As I said, my debate club was a defunct bunch of boys (one of many disadvantages of an all-boys school) and most debates did end up in an argument 😛 Alright, I promise to be more civil. The world needs more civilians. Down with the military! I'm going off topic.
I did test unmodified code, and perhaps I made the mistake of not saying that it was of version 3.3 so that may (nay, does) contribute to the ensuing confusion. Alright, I'll go and post something on the bugs forum, then. If my procrastinative nature didn't kick in first. Or work. Or lack of sleep.
Points 2 and 3 are new features? Hmm, I guess as a fresh user with selective blindness (especially towards manuals) I kinda assumed a lot. My lecturer was right. I should have never assumed anything. Assumptions are bad. To ASSUME makes an ASS out of U and ME. Pearl of wisdom, there. Although perhaps not so wise grammatically 😆
Cheers,
SQ
I was trying to stay out of this cause I felt Carissa was handling it just fine.
1. First of all, should all admins be assumed to have posted a story? And a validated one at that?
First, you're right that it shouldn't be stories.uid, and I'll correct that in the next update. Carissa has already explained why it needs to be validated = 1. Though it should be validated > 0. It's simply code that didn't get updated.
2. The Admins link on the Categories page should take us to a page that listed the admins for that category only, right? And not all admins?
That's correct. The $where clause got left off the second query. It will be fixed in the next update.
3. Then, the Add New Admin link on the admin list page should take us to a list of Admins not administering the current category, right? So that new admins can be added?
No, it's taking you to a page that lets you ADD a new admin not EDIT the admin privileges of an existing admin. If you want to edit an existing admin's privileges click on the "Edit Admin Privileges" link in their profile. If you want to mass change admin privileges, you can write it yourself as a MOD. There's nothing in the script that will do that now, and I don't intend to write it. The logistics would be fugly.
First, you're right that it shouldn't be stories.uid, and I'll correct that in the next update. Carissa has already explained why it needs to be validated = 1. Though it should be validated > 0. It's simply code that didn't get updated.
Validated > 0? 😕 Can a story be validated more than once? Or is it just a precaution?
That's correct. The $where clause got left off the second query. It will be fixed in the next update.
Yay! I have contributed! Now I feel no guilt in using eFiction to my heart's (and sick mind's) content 😀
No, it's taking you to a page that lets you ADD a new admin not EDIT the admin privileges of an existing admin. If you want to edit an existing admin's privileges click on the "Edit Admin Privileges" link in their profile. If you want to mass change admin privileges, you can write it yourself as a MOD. There's nothing in the script that will do that now, and I don't intend to write it. The logistics would be fugly.
No, actually, I meant that add admin page as well, but the combo box should contain admins not currently administering that category, and leave out admins who are administering that category, so you can't re-add an admin to a category...right? I guess the relieving an admin from a category can be done in the "Edit Admin Privileges" link, so that kills the last paragraph of my original post 🙂
Cheers,
SQ
P.S.: Kyaa! Tammy-sama! (slaps self) I need to stop this fanboy act. Glory to eFiction!
