Page 1 of 2

PHP Reorder Script

Posted: Sat Nov 13, 2004 10:31 pm
by curseofthe8ball
I'm trying to setup a piece of code that would allow a client to reorder news stories from a mysql database. Basically the table has a few categories, 3 of which are ID (which is unique), NAME and ORDER-ID (which handles the way the news appears on the site). Below is a code that I handles the reordering via a textfield with some javascript to allow you to select an item in the textfield and move it up and/or down. This works nicely but the problem I'm having is how would you submit this to the database and update the ORDER-ID for all columns to reflect this new order that was created in the textfield?

Code: Select all

<TABLE border=0 cellpadding='4' cellspacing='0'>
<TR><TD>
<FORM action="" method=get name="form">
<select name="bla" size=5>
<option value="15">option1

<option value="10">option2

<option value="34">option3

<option value="32">option4
</select>
<input type=button value="up" onclick="moveUpList(bla)">
<input type=button value="down" onclick="moveDownList(bla)">
</form>
</TD></TR></table>

Posted: Sun Nov 14, 2004 3:32 am
by djot
-
Hi,

I am not sure about it, but that is what came up my mind.

A simple select-box will not work, because it will return only one ID, not the full ordered list. Perhaps a select-box that allows mulitple selections may work (<select name="order" size="5" multiple>). On submitting the form all IDs would have to be selected so that all IDs would be returned.

I have seen such free to order lists in textareas or multiple select-boxes.

djot
-

Posted: Sun Nov 14, 2004 4:44 am
by kettle_drum
You simply need to pass an id for how you want to order the fields and then add that to the end of the query string when you select the data:

Code: Select all

switch($_GET['order']){
   case 'name':
      $order = 'name';
      break;
   case default:
      $order = 'id';
      break;
}
$query = "SELECT blah FROM blah WHERE something = 'this' ORDER BY '$order'";

Posted: Sun Nov 14, 2004 4:47 am
by djot
-
Hi kettle_drum,

He does not mean ascending or descending, he wants to order items by users chioce that don't follow that simple or preknown orders.

djot
-

Posted: Sun Nov 14, 2004 5:43 am
by kettle_drum
Well its the same principle. If you want to remember the order that the user has choosen then you need to record it. If you want this order to be user dependent then you will need to stored the list in a seperate table, or if you want the order to be the same for all store them in the actual results.

For user dependant results simply store the id or the items seperated by a comma or other char and you can then display them in that order by looping through each id and printing the results.

For the same order for all users use a linked list, where the values are stored in the record row so that you can view the child id of the current row and then display it. To update these values you jsut need to edit which child the list points to.

Posted: Sun Nov 14, 2004 10:40 am
by curseofthe8ball
I understand the logic behind this, I just can't seem to come up with how to write the actual PHP code to update the table.

For example, say I have 3 news items in the table with the following structure:

id
order_id
title
date_full
snippet
page_content
status

What I need to do is load the 3 news items "title" and "order_id" into the option code so it looks something like:

Code: Select all

<option value="$order_id">$title
I can do that fine but the problem I have is what the code looks like when the form is submitted, to send all of the new "order_ids" to the table to update several rows at a time. If someone could help me out with a sample code, I should be able to put the pieces together.

Thanks in advance!

Posted: Tue Nov 16, 2004 5:00 pm
by curseofthe8ball
Anyone?

Posted: Tue Nov 16, 2004 5:21 pm
by jl
Its not 100% clear what you're asking, but I think what you're saying is you want the user to be able to change the order of news items in a table. If you want to reorder multiple rows from your table, then that will take:

1. An interface that allows the user to reorder more than one item at a time
2. Multiple SQL queries that rewrite the order_id of every row concerned.

This isn't a simple problem - I've solved this in the past with both a complex javascript implementation which did it client side, and a simpler implementation where there was simply a text box at the side of each row where the user could change the values by rewriting the numerical order. E.g, the user might see:

ITEM A [1]
ITEM B [2]
ITEM C [3]

(Where [1] means a text box with the number 1 in it, etc.)

They'd then be able to change those numbers, e.g. swap the 1 and 2 to put the items in order BAC. At the PHP side when this was submitted the script would re-write the order_id for all 3 lines submitted, doing UPDATE table SET ORDER_ID='2' WHERE ITEM_ID='A', etc.

Hope that makes sense, and is what you're after.

Posted: Wed Nov 17, 2004 10:06 am
by curseofthe8ball
jl,

Is there a way that I can take the values of a select form element and pass it to the database, updating all the rows?

For example, the select form element could look like this:

Code: Select all

<select name="bla"> 
<option value="2">option2 
<option value="1">option1 
<option value="4">option4 
<option value="3">option3 
</select>
Now, if I wanted to submit this to the database to update the 4 rows, what would the code look like?

Posted: Wed Nov 17, 2004 6:44 pm
by jl
That's only one select. What 4 rows do you want to update from it, and what do you want to update them to?

Posted: Wed Nov 17, 2004 9:24 pm
by curseofthe8ball
jl,

I make a mistake with the select form element code. It would actually stare=t with:

<select name="bla" multiple>

This select form element would house all of the news items. I'd run a WHILE statement to fill the select form and would load the values with something like:

<option value="$order_id">$title

The usage of this select form element is so that the client could use a javascript code to reorder the titles in the select element. Then when they hit the submit button it would send the new order to the database, updating ONLY the "order_id" column for EACH row.

Any ideas how/if this can be done?

Posted: Fri Nov 19, 2004 10:29 am
by curseofthe8ball
Any ideas?

Posted: Sun Nov 21, 2004 12:42 am
by jl
Ah - that you want to use javascript to reorder the items in the select was the missing link.

Yes, you can do what you want to do

- The name of the select must end in [] - I'm not sure if this is for the client side or PHP, but you need it. If the HTML name of the select is foo[], this will appear as an array in PHP called $_GET['foo'] (or post).
- Set size="5" or whatever in your select tag to show 5 items at the same time.

Here's an example of some javascript that moves the currently selected item up in a select (i.e. it swaps with the one above it in the list) as long as it's not already the top one.

Code: Select all

ind=document.questionedit&#1111;'qvalsel&#1111;]'].selectedIndex ; 

if (ind != 0 && lastClicked!='A') &#123;
	
tempVal=document.questionedit&#1111;'qvalsel&#1111;]'].options&#1111;ind].text ;
document.questionedit&#1111;'qvalsel&#1111;]'].options&#1111;ind].text=document.questionedit&#1111;'qvalsel&#1111;]'].options&#1111;ind-1].text ;
document.questionedit&#1111;'qvalsel&#1111;]'].options&#1111;ind].value=document.questionedit&#1111;'qvalsel&#1111;]'].options&#1111;ind-1].value ;
document.questionedit&#1111;'qvalsel&#1111;]'].options&#1111;ind-1].text=tempVal ;
document.questionedit&#1111;'qvalsel&#1111;]'].options&#1111;ind-1].value=tempVal ;
Hope that helps. For some reason in my code when the user submits the form with this data I first copy all the items from the select the user can see to an invisible one, and then the PHP reads from the invisible one. Can't remember why I did that, but if somethings behaves oddly for you you might want to try that.

Posted: Fri Nov 26, 2004 10:19 am
by curseofthe8ball
So far I've been able to come up with the below code thanks to multiple people. Using javascript, I am able to reorder the titles via a SELECT field using the MULTIPLE tag. Unfortunately when I try to submit the new order, I get the below error:

Code: Select all

Warning: Invalid argument supplied for foreach() in order.php on line 36
This is probably a result of my inexperience with ARRAY so I'm undoubtly missing something somewhere. I'm hoping someone out there can help me!

Code: Select all

<html>
<head>
<SCRIPT LANGUAGE="JavaScript" SRC="selectbox.js"></SCRIPT>

</head>

<body leftmargin="10" topmargin="10" marginwidth="10" marginheight="10">
<script> 
function selectall() &#123; 
    
   obj=document.getElementById('s1') ; 
    
   length=obj.options.length; 
    
   for(i=0;i<length;i++) &#123; 
       
      obj.options&#1111;i].selected=true ; 
       
   &#125; 
    
&#125; 
</script>

<?PHP

		include("../includes/dbconnect.php");

if ($action == "reorder") &#123;

$array = $_GET&#1111;'sel'];

$counter=0; 

foreach ($array as $id) &#123; 

$query="UPDATE news_articles2 SET order_id=".$counter." WHERE id=".$id."";

	$result	= mysql_query($query)
		or die (mysql_error());

	&#125;

&#125;
else &#123;

echo "<form method="POST" name="fm" action="$_SERVER&#1111;PHP_SELF]?action=reorder" onsubmit="selectall();"> 
<input type="hidden" name="t" value="a"><select name="sel&#1111;]" size="5" id="s1" multiple>"; 

$query = "SELECT * FROM news_articles2 WHERE status = 'active' ORDER BY 'order_id' ASC"; 
$result = mysql_query($query) or die (mysql_error());
while ($data = mysql_fetch_assoc($result)) &#123; 
$id = $data&#1111;'id']; 
$name = $data&#1111;'name']; 
$name = stripslashes($name);

echo "<option value=$id>$name"; &#125;
echo " 
</select> 
<INPUT TYPE="button" VALUE="&nbsp;Up&nbsp;" onClick="moveOptionUp(this.form&#1111;'sel&#1111;]'])">
<INPUT TYPE="button" VALUE="Down" onClick="moveOptionDown(this.form&#1111;'sel&#1111;]'])">
<input type="submit"> 
</form>";
&#125;
?>

Posted: Fri Nov 26, 2004 10:29 am
by jl
That had me stumped for a while.. everything looked fine.. and it nearly is.. except:

You're POSTing the form, so you should be reading with $_POST not $_GET...

I think that must be what it is.