Hey there! With my latest project (Menu Editor), I finally needed to learn ordering things so that the menu order could be user-defined.
Here is what I have so far, which just isn't acting how it should. I can explain more if it's not something obvious that's wrong with the code.
if (empty($_POST['placement']))
{
$desired_position = $_POST['placement_button'];
foreach ($context['menu_editor']['items'] as $key => $value)
{
if ($value['before_id'] >= $desired_position)
{
$smcFunc['db_query']('', '
UPDATE {db_prefix}menu_items
SET before_id={int:new_before_id}
WHERE id_button={int:id_button}',
array(
'new_before_id' => (int) ++$value['before_id'],
'id_button' => (int) $value['id_button'],
)
);
}
}
}
Thanks,
Labradoodle-360
So what is before_id and id_button?
Or better yet, what does the mod do? Add buttons or edit existing buttons?
If the former (which I think it is) then before_id is the ID of the button to insert before, yeah? id_button is obvious.
Circular reasoning... what's wrong with your programme? That little code snippet doesn't answer that, although I did find something... it's the same problem I encountered when rewriting Dream Menu. I'll give a hint: IDs are not used in PHP arrays the same as in a SQL table. So I did not rely on a random ID...
It doesn't really matter what the mod does. It's a full fledged Menu Editor; it'll allow any default buttons to be modified, or even deleted, or the order switched, or you can add your own buttons, the feature-set is only growing as I develop it.
before_id isn't really name obvious - Although it probably should be. That is the column that is used in the query to pull the array for ORDER BY before_id.
id_button is name obvious, and is the id of each button.
To be honest, I'm not positive what's wrong with it, sometimes it reacts partially how it should, other times not at all, which tells me I did something wrong in the code.
I put a LOT of thought into ordering, and this is what I came up with. I pick my placement method (this code is only for "Before"), then I pick a button, the value of the button is it's id. Since the columns id_button and before_id are almost identical, except for id_button is used as the identifier for each button, and it's an auto-increment field, while before_id changes to accommodate user-defined ordering.
So, the code below...if $_POST['placement'] is empty, which makes it a "Before - Button" then foreach button, it adds 1 to it's current "before_id" if the current before_id is greater than or equal to the $_POST['placement_button'] - the identifier. Which leaves the desired spot empty, and all the other buttons +1 so it should be laid out all nicely for the query below to place the current button into the empty spot and then the array to pull out using ORDER before_id.
Thank you for your time, John.
Best Regards,
Labradoodle-360
Quote from: live627 on March 03, 2011, 01:06:48 AM
So what is before_id and id_button?
Or better yet, what does the mod do? Add buttons or edit existing buttons?
If the former (which I think it is) then before_id is the ID of the button to insert before, yeah? id_button is obvious.
Circular reasoning... what's wrong with your programme? That little code snippet doesn't answer that, although I did find something... it's the same problem I encountered when rewriting Dream Menu. I'll give a hint: IDs are not used in PHP arrays the same as in a SQL table. So I did not rely on a random ID...
Any ideas?
Okay, it's been a couple of days, and I'd really like to finish this up. Anyone have insight on this?
Quote from: Labradoodle-360 on March 07, 2011, 12:29:47 PM
Okay, it's been a couple of days, and I'd really like to finish this up. Anyone have insight on this?
Bring it to Arantor 's attention. He seems to know everything about coding and Smf.
He has already stated he does not intend on answering this topic.
Quote from: cicka on March 07, 2011, 12:43:56 PM
Quote from: Labradoodle-360 on March 07, 2011, 12:29:47 PM
Okay, it's been a couple of days, and I'd really like to finish this up. Anyone have insight on this?
Bring it to Arantor 's attention. He seems to know everything about coding and Smf.
You have a logic error, coding is fine. From what im seeing, you need to load the list of button ids and sort/add/subtract the list. Ur over writing values. Try something like this: temp = a, a = b, b = temp. Thro an if in there to see if the before id is the index current, assign it and then increment index as desired, then save the config. Think sorting an array and youll have it.
I don't understand what you mean...is there really an error in my logic? I don't think there is...
I see better what you are after, the new buttons will be stored in the db with a place marker. I cant on my fone give you an example, but ill try and fix it tonight. You have an array of button ids. You need to by way of a table make those buttons load in a pptentially differrent order with perhaps new buttons placed appropriately. Make an array of size of new list of buttons. Assign all the button ids a sequential index 123... then assisn to each of those the in the order you wish the old and new button ids. Then when you call for the buttons, echo then using the sequential index as key. Therefore dinamically changing the order of appearence in the menu. You ofc have the new buttons in another table with their appropriate ids assigned in conjunction with the order table. I add to this if need be, but you do really nice work. I think youll get now what im thinking. I hope this helps.
Haha thank you :)
And I don't see how there is an error in my logic though...whenever a new button is added or modified, it adds +1 to every button including the id you want, and every button after.
So if I specify I want a button before button 5? if button 5 exists, it makes it button 6, if button 6 exists, button 6 becomes button 7, and so fourth. Then, there will be an empty space - button 5, which can then be inserted.
Ah i see, bare in mind im on a fone. Lol but just as a test, i would test it with a manua control and refine till it either works right or brakes again and youll be able to narrow where the fault is. And excellent idea, ill be using this mod im sure. :)
I'd still appreciate it at this point if someone could look over my shoulder at the ordering code and give suggestions as to why it's not working as I wrote it to.
New before id => int ++, ur not incrementing any value? What is (int)++?
'new_before_id' => (int) ++$value['before_id'],
Should I try removing (int) then, ah? It specifies that ++$value['before_id'] is an int.
Although, it already has to be an int, so I suppose it's not completely necessary.
Quote from: texasman1979 on March 07, 2011, 05:27:56 PM
New before id => int ++, ur not incrementing any value? What is (int)++?
It means pre-increment, and all it is doing is casting the value to int.
And give me a couple minutes I will look at it.
Yay for operator precedence.
This will be interpreted as:
* take the value of $value['before_id']
* silent cast to int or float if it won't fit in an int
* increment it
* explicit cast to int
* assign it to the array key
Is that what you want to do?
EDIT: Ninja'd
Aha! Give this a go,
if (empty($_POST['placement']))
{
$desired_position = $_POST['placement_button'];
foreach ($context['menu_editor']['items'] as $key => &$value)
{
if ($value['before_id'] >= $desired_position)
{
$smcFunc['db_query']('', '
UPDATE {db_prefix}menu_items
SET before_id={int:new_before_id}
WHERE id_button={int:id_button}',
array(
'new_before_id' => (int) ++$value['before_id'],
'id_button' => (int) $value['id_button'],
)
);
}
}
}
Turns out when your using foreach while iterating the array, the $value is passed by value and not reference!
Aside from the inevitable fact that it makes it harder to follow later and will confuse people, is it a problem that that code is PHP 5 only, and will leave a stray reference after the loop has completed?
Project Evolution's solution did not work :P Only throws errors now.
What are your ideas, Arantor?
I wasn't really following what you were doing, and I think if you're having to mess with references to make a loop update work, you're probably over-engineering the solution.
Take a step back, what problem are you trying to solve? I am not at this point in the slightest interested in anything to do with the HOW you solve it, only retracing what it is you're trying to solve, because I have the uncomfortable feeling that the design premise needs shifting slightly.
I am trying to provide a solution to ordering Menu Items.
You'll be able to select "Before" or "After" which will designate which id_button it should either be BEFORE, or AFTER. This is how before_id gets populated for each button, which is used to populate the main array. (ORDER BY before_id)
Quote from: Labradoodle-360 on March 07, 2011, 06:10:31 PM
Project Evolution's solution did not work :P Only throws errors now.
What are your ideas, Arantor?
It worked for me when I tested it. :S
What sort of errors are you getting?
Man being on a fone makes trying to read code pretty tough lol especially when it aint a smart fone :) i concur with arantor, the problem needs to be broken down to its smplest elements and approached from fresh eyes. And you might look again at the buttom index array table. I do beleive it will sinplify ur issue trmendously.
Argh. You're delving into the how already.
So you're trying to order menu buttons. OK, with you so far. Is this absolute ordering (i.e. maintaining a single master list that everything must adhere to), or relative ordering (i.e. taking the normal list given in Subs.php and given a list of changes to make)?
The $buttons array in Subs.php has been unset and it's feeding off of menu_items from the database.
So it's an absolute list of all menu items that you're working with.
Why do you think you need to have a before order? The boards list doesn't have one.
I don't...before_id is the same thing as id_order or whatever it is for the boards. It really isn't name accurate for what it does.
OK, so why not start by naming it what it is, an order column.
So, each row has an order value, that makes sense. Are you using this value also to edit items, or simply for ordering? (Hint: not a smart move to dual purpose columns in a database)
I can change the name later; that's not an important part to me.
I am using id_button for editing, and identification purposes since it doesn't change even if the order does. before_id (order) is not used for identification since it changes.
Quote from: Arantor on March 07, 2011, 06:35:47 PM
OK, so why not start by naming it what it is, an order column.
So, each row has an order value, that makes sense. Are you using this value also to edit items, or simply for ordering? (Hint: not a smart move to dual purpose columns in a database)
Awesome, good way to go.
Assuming you intend to strictly order by the order column, we need to figure out what should happen when inserting a new one and deleting an old one.
Inserting a new one:
* end of the list - find the largest order value already known and add 1 (this has its own issues in other cases, it's safe enough here)
* anywhere that isn't the end - we need to make space. So, take all the entries presently after this one, and update them to all +1 i.e. UPDATE smf_menu_table SET order = order + 1 WHERE order >= my new id. No messing with arrays, we do absolutely nothing in PHP on this other than know where the new id is. Then you just insert a new record with the new id since you've already moved everything else down one.
Deleting one:
* doesn't matter whether it's at the end of the list or not, really. Make sure you know the current order position, then delete the row. Then for everything else, UPDATE smf_menu_table SET order = order - 1 WHERE order >= my old id
No fuss, no mess, and you let MySQL do all the work in max two queries rather than a query per value you're updating. This is why I found it crucial to re-examine the problem at its core...
Great...sounds smart. And I was sure if a query like that, or the PHP like what I wrote was the better way to go.
I'll try this out when I have some time and make sure it functions as it should.
Thanks Arantor!
Quote from: Arantor on March 07, 2011, 07:02:11 PM
Awesome, good way to go.
Assuming you intend to strictly order by the order column, we need to figure out what should happen when inserting a new one and deleting an old one.
Inserting a new one:
* end of the list - find the largest order value already known and add 1 (this has its own issues in other cases, it's safe enough here)
* anywhere that isn't the end - we need to make space. So, take all the entries presently after this one, and update them to all +1 i.e. UPDATE smf_menu_table SET order = order + 1 WHERE order >= my new id. No messing with arrays, we do absolutely nothing in PHP on this other than know where the new id is. Then you just insert a new record with the new id since you've already moved everything else down one.
Deleting one:
* doesn't matter whether it's at the end of the list or not, really. Make sure you know the current order position, then delete the row. Then for everything else, UPDATE smf_menu_table SET order = order - 1 WHERE order >= my old id
No fuss, no mess, and you let MySQL do all the work in max two queries rather than a query per value you're updating. This is why I found it crucial to re-examine the problem at its core...
Don't credit me for thinking that one up, it's how the board list is managed, except they do a further step to force the table to be reordered physically so that on MyISAM it's even faster, but it's unreliable these days.
In general though, if you can optimise a routine to use an existing function in some way it's usually faster; in this case we're letting MySQL do the work instead of us doing it.
QuoteUPDATE smf_menu_table SET order = order + 1 WHERE order >= my new id
since i am learning from yall, more so than yall learning form me, i got to ask this question.
if you have 45 menu items, and you want to move the last menu item to the first position, would that then be 44 queries?
since im home i can give a proper example of what i was trying to say earlier:
//array of buttons
$arrayofbuttons = array(
'b_1' => 'b_1',
'b_2' => 'b_2',
'b_3' => 'b_3');
//sequential index 012... -- of buttons received from db
$button_index[] = 'b_1'
$button_index[] = 'b_2'
$button_index[] = 'b_3'
//lets move b_3 to b_1
$temp = $button_index[0];
$button_index[0] = $button_index[2];
$button_index[2] = temp;
//lets save the new arrangement of buttons
$rdb = $smcFunc['db_query']('', '
TRUNCATE TABLE smf_button_index_table'
);
$queryvar = 'INSERT INTO smf_button_index_table (s_index, b_id) VALUES ';
foreach($button_index as $k => $v)
$queryvar .= '(' . $k . ', ' . $v . '),';
$queryvar = strstr($queryvar, 0, strlen($queryvar)-1);
$rdb = $smcFunc['db_query']('', $queryvar);
//*********************************
1 join query to load the buttons -- in the order previously set
2 querys to save a new configuration
this ofc is different than saving the button itself, but all it would need is an id, and that new id saved into this $button_index and then smf_button_index_table
this way also could open up different orders/configurations for different pages
table structure for 2 or more dif configs
whichPage - s_index - b_id
home - s_index - b_id
admin - s_index - b_id
forum - s_index - b_id
just a thought :)
//*********************************
now i cant say that this is the way to do it, but it would definitley work. and that query above, it does appear to update all the buttons that happen to reside after the new inserted button, so from what im seeing, it may very well could turn into many queries, unless im missing something very basic from a long day at work. lol
Quoteif you have 45 menu items, and you want to move the first menu item to the last position, what that then be 44 queries?
No, it's one query. It just tells MySQL to examine all the rows that match the WHERE criteria and apply the operation (take the value of order, add 1, assign the result to order) to each row.
What you're talking about deals with adding new rows on the end, which isn't a problem. It doesn't solve when you need to insert a row into the middle, where it would effectively duplicate the order, which is where you have to shunt all the rows after it up one to fit it in.
Also note that there is a proper db_insert method for inserting multiple rows at once, which also deals with escaping values ;)
i edited arantor :)
and thx, ive made mysql work for me before, but not quite in such a way.
Here's what I have so far $smcFunc['db_query']('', '
UPDATE {db_prefix}menu_items
SET before_id={int:before_id}
WHERE before_id>={int:new_id}Should I be writing it differently? How would I pull before_id for each instance? How can I do that without using a foreach?
what arantor is saying is to make mysql query itself updating the order value.
QuoteDATE smf_menu_table SET order = order + 1 WHERE order >= my new id
order is the field name where your field name is before_id.
the query that arantor has proposed is make mysql do the foreach instead of you.
if you take order(before_id) as an array of values, it is essentially sorting and reassigning all the values in the db ">=" new_id.
tricking mysql to do the work.
$smcFunc['db_query']('', ' UPDATE {db_prefix}menu_items SET before_id={int:before_id} WHERE before_id>={int:new_id}that line will only set one record, what you are needing is a sql statement that will set many records, arantor gave that to you.
a php foreach with your query will be many querys. while arantors is one query, but mysql ends up running it many times in the background, until all updated.
but i will remind you that if you look above, you will see an example of a way to have an array of button configurations. home, forum, admin, custom pages could all have a unique button config along with the way to rearrange them like you are programming. just a suggestion, food for thought.
No...I don't think you understand it.
Arantor, what modifications should I make to my query to get it running? I'm not sure how I get the current before_id. Unless I can just specify the column ++?
Yes, Arantor originally said to use before_id = before_id + 1 (I don't think ++ works)
And rename the column already ;) to keep you and whoever helping you less confused.
Seriously, without being funny, how did you manage to misread what I said?
Surely you know at the point of the issuing the update, what position you're putting the item in? Let's say you're going to put it into position 5 in the list.
You simply issue: UPDATE smf_menu_table SET order = order + 1 WHERE order >= 5
This will move everything that is *currently 5 or more* in the table down an entry. Then you insert the new menu item. No farting about in PHP, no foreach, none of that crap. You let MySQL deal with it all.
Alternatively if you *don't* know what the order value is at that point, I'd say you have bigger things to worry about structurally...
Topic resolved. :) Figured everything out.
Thanks for the help everyone.
So what is the query actually being sent to the database? How do you know the right parameter was sent to it?
And you did run that update being moving the button, right?
I was sending id_button instead of before_id, so it wasn't ordering them properly, so I just had to change the value being send in $_POST['placement_button'].
Here's what I ended up with so far.
/* Before */
if (empty($_POST['placement']))
{
$smcFunc['db_query']('', '
UPDATE {db_prefix}menu_items
SET before_id = before_id + 1
WHERE before_id >={int:placement}',
array(
'placement' => $_POST['placement_button'],
)
);
}
And does that work as expected?
It does, although I still have to finish some other order coding so it inserts the proper value, right now, it went 1-3-4 and skipped two.
So not a big deal, still ordered them properly on the output, and the outcome is the same, but it's still something I'm going to fix.
That would suggest it's running things out of order. Add this to your Settings.php file:
$db_show_debug = true;
I actually think it was due to the fact I think it was starting at the first button being 0. When I manually changed before_id in the database to "1-2-3" and then changed around the order, I couldn't replicate the issue.
It actually shouldn't matter.
Okay, well, I am going to finish ordering up, and then test it again and see if I can produce any errors.
In which case you should definitely use the tip I just posted so you can see what actual queries are being run in what order.
I am already added $db_show_debug. :) Thanks Arantor, looks pretty darn useful.
Okay, correct me if I am wrong...but don't I need to put something in place so if I move button 2 to place 4, they move down so there is not an empty place? If I am correct, what is the smartest way to accomplish that?
Move it to the end then UPDATE them all to order = order - 1 WHERE order >= old pos
The problem once again was in the template side.