Databases
StackOverflow2013
Postsdbo
POCMS page ordering solution for custom designed CMS
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POCMS page ordering solution for custom designed CMS
    primarykey
    data
    text
    <p>I wrote my own CMS solution and I have always had problems with changing the order of pages. On my database I have a column named order which takes the automatic id of the page. For example if pageid = 16 then order = 16.</p> <pre><code>function shift_page_up($pageid, $pageorder, $db, $database_db){ mysql_select_db($database_db, $db); $sql_previouspage = "SELECT order from pages WHERE `order` &lt; $pageorder ORDER by `order` DESC LIMIT 1 "; $previouspage_result = mysql_query($sql_previouspage) or display_error(mysql_error()); $previouspage_row = mysql_fetch_assoc($previouspage_result); $newpreviouspage_pageorder = $pageorder; $newpageorder = $previouspage_row['order']; $sql_update_currentpageorder = "UPDATE pages SET `order` = $newpageorder WHERE `pageid` = $pageid"; $update_currentpageorder = mysql_query($sql_update_currentpageorder) or display_error(mysql_error()); $sql_update_previouspageorder = "UPDATE pages SET `order` = $newpreviouspage_pageorder WHERE `pageid` = ".$previouspage_row['pageid'].""; $update_previouspageorder = mysql_query($sql_update_previouspageorder) or display_error(mysql_error()); } function shift_page_down($pageid, $pageorder, $db, $database_db){ mysql_select_db($database_db, $db); $sql_nextpage = "SELECT * from pages WHERE `pageid` = $pageid AND `order` &gt; $pageorder ORDER by `order` ASC LIMIT 1 "; $nextpage_result = mysql_query($sql_nextpage) or display_error(mysql_error()); $nextpage_row = mysql_fetch_assoc($nextpage_result); $newnextpage_pageorder = $pageorder; $newpageorder = $nextpage_row['order']; $sql_update_currentpageorder = "UPDATE pages SET `order` = $newpageorder WHERE `pageid` = $subpageid"; $update_currentpageorder = mysql_query($sql_update_currentpageorder) or display_error(mysql_error()); $sql_update_nexpageorder = "UPDATE pages SET `order` = $newnextpage_pageorder WHERE `pageid` = ".$nextpage_row['pageid'].""; $update_nexpageorder = mysql_query($sql_update_nexpageorder) or display_error(mysql_error()); } </code></pre> <p>So the main idea is to swap the order of the pages on the ORDER column duplicated from their page id's which are automatically generated. I have already coded mechanisms to avoid swapping pages with the highest and lowest order using <code>SELECT MIN(order)</code> and <code>SELECT MAX(order)</code> respectively.</p> <p>To shift the order up the anchor link <code>&lt;a href="order_up.php?pageid=&lt;?php echo $row_get_pages['pageid']; ?&gt;&amp;pageorder=&lt;? echo $row_get_pages['order']; ?&gt;&amp;swapped=&lt;?php echo str_replace( " ", "-", $row_get_pages['pagename']); ?&gt;"&gt; &lt;img onclick="bounce_page('&lt;?php echo $row_get_pages['pagename']; ?&gt;');" src="images/ico/arrow-090.png" width="16" height="16" align="right" style="cursor:pointer;" /&gt;&lt;/a&gt;</code> is clicked</p> <p>To shift the order down the anchor link <code>&lt;a href="order_down.php?pageid=&lt;?php echo $row_get_pages['pageid']; ?&gt;&amp;amp;pageorder=&lt;? echo $row_get_pages['order']; ?&gt;&amp;swapped=&lt;?php echo str_replace( " ", "-", $row_get_pages['pagename']); ?&gt;"&gt;&lt;img onclick="bounce_page('&lt;?php echo $row_get_pages['pagename']; ?&gt;');" src="images/ico/arrow-270.png" width="16" height="16" style="cursor:pointer;" /&gt; &lt;/a&gt;</code> is clicked</p> <p><strong>This beautifully works at times especially when the pages table is truncated and the page id's start at 0</strong>. <em>But an emptied table or a page deleted somewhere always bring issues. When I click on any up or down link no values are swapped</em> </p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USAlexander Kimaru
    1. USAlexander Kimaru
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. POCMS page ordering solution for custom designed CMS
      1. This table or related slice is empty.
      1. VTApproveEditSuggestion
    1. CO*"beautifully works"* ... ? Have you tried SQL injection? Learn about [*prepared statements*](http://j.mp/T9hLWi) instead, and use [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli) - [this article](http://j.mp/QEx8IB) will help you decide which.
      singulars
      1. POCMS page ordering solution for custom designed CMS
      1. USKermit
    2. COThe fact that you're duplicating the IDs of the pages into the "Order" column tells me that you're over complicating things. You should have an ordering method that allows you to sort on any of the columns (page title, ID, date...). I would think that the order column should be all 0, unless specifically set in order.
      singulars
      1. POCMS page ordering solution for custom designed CMS
      1. USAxel
    3. CO@Axel The pages need to be specifically set in order. The CMS user will click on either up or down button which invokes `order-up.php` or `order-down.php` so that they can manipulate the order of pages on the website's navigation bar to their liking. Further more, as you suggsted, I would find it hard to track the changes the user has suggested. Thanks for your comment
      singulars
      1. POCMS page ordering solution for custom designed CMS
      1. USAlexander Kimaru
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload