On Sun, Oct 16, 2011 at 2:32 PM, Stephen <stephen-d@xxxxxxxxxx> wrote:
> On 11-10-16 04:10 PM, Jim Giner wrote:
>
>> Stephen:
>>
>> What you describe is a multistep problem. There are many ways to show
>> pictures (images) in any order you want.
>> *****
>>
>> So far, the OP has only asked how to keep his "categories" in order.
>> Curious, yes, but he hasn't even asked how to display the images in order
>> -
>> maybe he doesn't care about that..
>>
> Well, I want to deal with one part of the design at a time :)
>
> Thanks to all who replied. This is a collective response.
>
> Displaying in an order is easy when you have a field called "order".
>
> SELECT descriptions FROM categories ORDER by order;
>
> My issue is, say I have three records:
>
> ID Category Order
>
> 1 B&W 1
> 2 Landscapes 2
> 3 Nudes 3
>
> I am looking for the best way to be able to change the values to
>
> ID Category Order
>
> 1 B&W 3
> 2 Landscapes 2
> 3 Nudes 1
>
> Dynamically building a form, entering the new order value, and then looping
> through the post, with a SQL UPDATE is the best I can come up with.
>
>
> A future issue will be doing the same to the table category-photograph
>
> ID Category_id photo_id order
>
> This table is needed to allow a photograph to be in more than one category.
>
> Cheers
> Stephen
>
>
Depends on the tools you have in your belt. If you want to keep it strictly
HTML + PHP, you can use the form POST. Iterate through the form's
categories collection and update to the database, this simplest application
design doesn't require you check what's the previous order is or which
category's order has changed.
/* psuedo code */
- $order = null; $id = null;
- $sql = UPDATE SET `order` = '?' WHERE `ID` = '?';
- make connection to db
- if ( [1] prepare statement === false ) throw new Exception('Error
preparing statement: '.$sql);
- if ( [2] bind param $order and $id === false ) throw new Exception('Error
binding parameters: $order '.$order.' and $id '.$id);
- iterate through the form POST collection of categories
- $id = $category_id; $order = $category_order;
- if ([3] execute prepared statement === false) throw new Exception('Error
executing prepared statement '.$sql;
- end loop / iteration
- close db connection
However, using this method will have a performance issue if the number of
records you're updating is large regardless of which 'category' was changed.
Good luck,
Tommy
[1] php.net/mysqli.prepare
[2] php.net/mysqli-stmt.bind-param
[3] php.net/mysqli-stmt.execute
[4] php.net/language.exceptions
