Building CMS problem

Problem with position of articles in my CMS

Page 1 of 1

3 Replies - 587 Views - Last Post: 22 July 2009 - 11:55 PM Rate Topic: -----

#1 floutag  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 8
  • Joined: 11-June 09

Building CMS problem

Post icon  Posted 20 July 2009 - 10:45 PM

Hello everyone,
I'm trying to build my own CMS in PHP and MySQL and I came across one problem. I have mysql database with columns such as header, content, date, position, visible etc. I'm trying to make positioning working now and i cant find a solution. What I need is when I'm inserting new article and select position which already exist I need to move all articles below down. Position no. 1 is on the top and so on. I made simple code which check if selected position already exist and if yes use position=position+1, wchich works just fine. Problem comes when I try to edit position of already existing articles. Whenever I move article, every erticle below gets +1 even though it's not necessary. When I move article up (at first time), everything is ok, but when i move it again or move down it starts messing up all my position numbers. It usually end up like position: 1,4,5,6,10,11 etc. What I need it to do is whenever I change position it will just create room for this position if it already exist by moving existing articles down/up to keep my position in order at all the time (like this: 1,2,3,4,5,6). Can anyone please help me? I'm not sure if my explanation is clear, but hopefuly it is (I'm not native english speaker).

Here is the code I'm using for moving other articles when inserting new article:
//udate position
$query = "SELECT MAX(position) AS highestPosition FROM cms";
$result = mysql_query($query, $connection);
$row = mysql_fetch_array($result);
if ($position <= $row['highestPosition']) {
  $query = "UPDATE cms SET position=position+1 WHERE position>={$position}";
  $result = mysql_query($query, $connection);
}


"$position" is selected position from the submitting form

Thank you very much!!!

Is This A Good Question/Topic? 0
  • +

Replies To: Building CMS problem

#2 Martyr2  Icon User is offline

  • Programming Theoretician
  • member icon

Reputation: 4319
  • View blog
  • Posts: 12,101
  • Joined: 18-April 07

Re: Building CMS problem

Posted 20 July 2009 - 11:12 PM

I think I get what you are doing. You are looking to switch two article positions, moving the lower one up above the other. The problem is that you have the where clause where position >= $position which is going to effect all the articles following the one you are moving. Try doing a multi-step process where you look up the position of the article you want to move, find the article with the position - 1, get that article id and then do two updates. Add 1 to the article and subtract 1 from the other. Essentially switching the order and affecting only those two articles. Just make sure that position of the one you are moving is not already at the top of the stack.

:)
Was This Post Helpful? 0
  • +
  • -

#3 shezzy  Icon User is offline

  • D.I.C Head

Reputation: 27
  • View blog
  • Posts: 194
  • Joined: 28-January 07

Re: Building CMS problem

Posted 20 July 2009 - 11:20 PM

When I read this I thought it would be a good time to use a Stack data structure so you can push the newest artictle and still pop starting from newest to oldest (lifo). Just an idea thought might help you.
Was This Post Helpful? 0
  • +
  • -

#4 floutag  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 8
  • Joined: 11-June 09

Re: Building CMS problem

Posted 22 July 2009 - 11:55 PM

View PostMartyr2, on 20 Jul, 2009 - 10:12 PM, said:

I think I get what you are doing. You are looking to switch two article positions, moving the lower one up above the other. The problem is that you have the where clause where position >= $position which is going to effect all the articles following the one you are moving. Try doing a multi-step process where you look up the position of the article you want to move, find the article with the position - 1, get that article id and then do two updates. Add 1 to the article and subtract 1 from the other. Essentially switching the order and affecting only those two articles. Just make sure that position of the one you are moving is not already at the top of the stack.

:)

I'm not really sure if I exactly understand what do you mean, but I think that I need to change position of more than those two positions in order to keep my position list well organized. For example if I move article with position 9 to position number 5 it has to affect all articles in between these two (to keep position numbers in order), but not affect any other. Something like this (a=article, p=position):
- exiting order:
a1 = p1
a2 = p2
a3 = p3
a4 = p4
a5 = p5
a6 = p6
a7 = p7
a8 = p8
a9 = p9
a10 = p10
-i decide to move article9 into position5:
a1 = p1
a2 = p2
a3 = p3
a4 = p4
a9 = p5
a5 = p6
a6 = p7
a7 = p8
a8 = p9

a10 = p10 (must keep same position)
-but when i decide to move article2 into position8:
a1 = p1
a3 = p2
a4 = p3
a5 = p4
a6 = p5
a7 = p6
a8 = p7
a2 = p8

a9 = p9
a10 = p10
-etc...

My code mentioned in my first post works well for inserting and deleting articles, but I need something for updating to change position numbers only between two articles, which are going to affected. Position numbers must always stay in the same order (1,2,3,4,5,6,7,8,9,10).
Anyone has got any idea how to do that please? Or am I missing anything? Is there a better way how to accomplish my task? Thank a lot guys fot any help!!!

This post has been edited by floutag: 23 July 2009 - 12:11 AM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1