Index re-order problem

PHP
1

Hey guys,

I have a problem and I need to know the most common and efficient solution.

I am taking a series of pictures out of a database and displaying them. In the db I also have their id’s and an index column to use for the order.

The user is then able to re-order then using JQuery sortable and this new order is sent via GET to the page refreshed.

I should mention that I am also sending the table row id of each of the pictures.

I now need to know how I can

  1. Update the correct table entries with their new index order.

  2. How to display the pictures on the page

Both the order index and the ids are sent to the php script in arrays so here is some of the code I am trying but not working:

        if (isset($_GET['index']) && isset($_GET['id'])){
         $size = sizeof($_GET['index']);
         $indices = array();   
         $id = array();
         for($i=0;$i<$size;$i++)
         {
          $indices[$i] = $_GET['index'][$i];  //load index local array
          $id[$i] = $_GET['id'][$i];     //load pic ids into local array
           $sql_default = mysql_query("UPDATE pix SET pic_index ='$indices[$i]' WHERE id = 'id[$i]'") or die (mysql_error());
         }   
     }

Also my SQL statement to display them is probably wrong but is currently:

$sql_default = mysql_query(“SELECT id, the_pic, pic_index FROM pix ORDER BY pic_index ASC”) or die (mysql_error());

2

Try


if (isset($_GET['index']) && isset($_GET['id'])) {      
  // the two arrays must have the same size
  if (sizeof($_GET['index']) == sizeof($_GET['id']) {
    $size = sizeof($_GET['index']);       
    for ($i = 0; $i < $size; $i++) {      
      $indice = mysql_real_escape_string($_GET['index'][$i]);  
      $id = mysql_real_escape_string($_GET['id'][$i]);
      //load pic ids into local array       
      $sql_default = mysql_query("UPDATE pix SET pic_index = '$indice' WHERE id = '$id'") or die (mysql_error());       
    }        
  }        
}

I checked the size of both arrays (they must be the same) before starting to update. You might want to show some error if they are not the same length.
I also validated the index and the id with mysql_real_escape_string() before using them in the query.
And I eliminated the two arrays where you stored the indices and the ids. Do you those arrays somewhere else in the code?

3

Hi Guido,

Thanks I will try your code too because its probably more efficient than mine ,

I think I just spotted the problem, not sure yet but I believe it is because I am sending the id information after the fact. Meaning that I am using JQuery sortable and it is re-ordering the indexes and the id’s with them. I need to take the id’s, save them in the array for GET before I use sortable have a look at whats happening live:

Jake News

Drag the pix around and then push the re-order button

4

Take another look at the live version Guido, it was because of the JQuery, now I am taking the id’s before but there is still a silly math problem. Order is slightly wrong, can you help?

5

Full script is here attached :wink:

6

Hey dudes, I hope I’m not intruding on this thread, just curious as to what the following peice of code does, never used sizeof…could someone explain please…? I need to do exactly what the OP is doing soon :slight_smile:

if (sizeof($_GET['index']) == sizeof($_GET['id'])
7

PHP: sizeof - Manual

8

Hey bud, thanks for that, still would like to know what it’s use for here is?

9

Ok I’ve got…both $_GET arrays needs consist of the same amount of array elements…I think :confused:

10

That’s exactly right :slight_smile:

11

How clever I am lol :slight_smile: Thanks a lot friends…and sorry for crashing in on your thread silversurfer5150! :slight_smile:

12

Hi there,

No problem at all.

Basically anything in PHP structured with GET[‘somevalue’] is a url variable. This is one of the ways in which a variable is transferred between different scripts/web pages. Next time you are on a site and you click on a link and see something like:

http://will.site88.net/jake/update_posts.php?index[]=1&index[]=2&index[]=3&index[]=4&id[]=2&id[]=3&id[]=1&id[]=4&

What you are seeing are variables who have both been sent to the page via the GET method. You can read up on this by googling php superglobals or POST and GET.

Of course, the receiving php script must do something useful with them so tests for certain conditions, in this case:

if the array $_GET[‘index’] is the same size as the array $_GET[‘id’] then do something

hows that?

13

I’ll throw this in. :slight_smile:


<?php
foreach(array_map(null, $_GET['index'], $_GET['id']) as $pair){
  if(2 === count(array_filter($pair))){
    mysql_query(vsprintf(
      'UPDATE pix SET pic_index = %d WHERE id = %d LIMIT 1;',
      $pair
    ));
  }
}