I Need Some Logic Help Here Php And Mysql

[dpiearcy]

Ok. Basically this is what I'm doing. From MySQL I am generating a series of events through an array.

 

My client wants to be able to change the order of events. Currently I am using a variable of $pid (it's the auto incrementing ID from mysql).

 

So I created a row I called displayorder.

 

Then in my array display on the page to reorder I am simply giving them the title of the event then the displayorder in a text box in a form. My thinking was just change the number of the display order and hit submit.

 

My problem though is since this is coming from an array, my variable $pid I can't use to update mysql.

 

So I need some logic thinking here. My thinking is to create a new variable for each item in the array that uses both the ID ($pid) and the display order variables together. Creating a unique ID for each.

 

Or I could simply update the entire database row of displayorder though as this grows it seems like a bad idea.

 

If seeing the code would help I'll post it but I figure you guys know about arrays and how to display from num counts etc. My problem is the variable $pid is not unique once I update. And since they will be doing this I can't say where $pid = 13 or whatever.

 

Any thoughts on some logic help here for me?

[dpiearcy]

I should add that the array now displays by displayorder row not ID order ASC.

[dpiearcy]

Maybe this will help. It's really a loop. As you can see I decided the best way to name my text field was to give it the $pid (ID) thinking that would be easier once I parsed the data. Here's the code after the query.

 

 

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

$num = mysql_num_rows($query);

if ($num > 0)

while($row = mysql_fetch_array($query)){

$pid = $row["id"];

$title = $row["title"];

$disporder = $row["disporder"];

echo "<h7>$title</h7>

<h4>Display Order</h4>

 

<form action='change_order_parse.php' method='post'>

<input name='$pid' type='text' value='$disporder' size='5' />

 

But then I realized once I passed the info to change_order_parse.php there would be no way to set the next variable because the name is $pid which is from my fetch array.

 

So I couldn't very well declare $new = _POST[$pid]; cause that's all in the fetch array.

 

Get what I'm after now? How can I make each looped $pid a new unique identifier I can then later use to declare a variable to update mysql?

[Psycho]

I understand what you are trying to achieve, but reading your questions/comments is confusing. I think you are making this way more complicated than it needs to be. I'll provide a general description of how you can accomplish what you want.

 

1. In the database you want to have the primary ID of the records and a separate field for the order (it seems you already have this).

 

2. When creating your form you will create a field to change the order for each record. The NAME of these fields should be an array which uses the primary id of the record as the index of the field name. Example

<input type="text" name="order[3]" value="1"> Record id#3, first in order
<input type="text" name="order[2]" value="2">Record id#2, second in order
<input type="text" name="order[5]" value="3">Record id#5, third in order
<input type="text" name="order[4]" value="4">Record id#4, fourth in order

 

3. When the user POSTS the form you will iterate over the array $_POST['order'] and verify that all the values are appropriate and then update the value for each field appropriately. Here is some sample code

foreach($_POST['order'] as $id +> $order)
{
   $id = intval($id);
   $order = intval($order);

   $query = "UPDATE table SET order = $order WHERE id = $id";
   $resutl = mysql_query($query) or die(mysql_error());
}

 

A few notes:

1. You should really never run queries in a loop. But, the format for creating an UPDATE query with multiple records being updated with different data is a little complicated and I'm too lazy to look up the format and doing this for a relatively small number of records shouldn't be a bug issue. But, I suggest you look into the process of creating a single query to update all the records rather than doing each individually in the loop.

 

2. The logic above does not take into consideration any verification that the order values submitted make sense. So a user could give multiple records the same order value or no value (no value would become a 0). So, if you want to provide some error/warning handling to ensure that all records are given a unique order value and that they are sequential you would want to do that before doing the update.

[dpiearcy]

Thanks for the reply.

 

I'll give that a whirl and see how it works out.

[Barand]

Here's a working example

 

My test table

 

CREATE TABLE `events` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(45) DEFAULT NULL,
 `displayorder` int(11) DEFAULT NULL,
 PRIMARY KEY (`id`)
)

 

Sample form code to change display order

 

<?php
include("testDBconnect.php");

/********************************
* Process the form input
*********************************/
if (isset($_POST['order'])) {
   $update = '';
   foreach ($_POST['order'] as $id => $order) {
    $update .= "WHEN $id THEN $order\n";
   }
   $sql = "UPDATE events
	    SET displayorder = CASE id
	    $update
	    END";
   $mysqli->query($sql);

}

/********************************
* display events by display order
*********************************/
$sql = "SELECT id, name, displayorder
    FROM events
    ORDER BY displayorder";
$res = $mysqli->query($sql);
$formentry = '';
while (list($id, $name, $order) = $res->fetch_row()) {
   $formentry .= "<tr><td>$name</td><td><input type='text' name='order[$id]'' value='$order' /></td></tr>\n";
}
?>

<form method='post'>
<table>
<?= $formentry?>
</table>
<input type='submit' name='btnSubmit' value='Submit' />
</form>

[dpiearcy]

Not sure what I'm doing wrong here but I get absolutely nothing to display.

[dpiearcy]

I actually gave up and decided on using jquery to drag and drop with ajax. Much easier and very effective. Works like a charm now. Thanks anyway all.