MYSQL: how to “reorder” a table with discontinued idno?_问答_开发者

MYSQL: how to “reorder” a table with discontinued idno?

开发者 https://www.devze.com 2023-03-11 15:24 出处：网络

The following code assumes the idno(primary key) is continued and starts from 1. However, I set the id as auto increment and deletable.

相关专题：jquery list php

The following code assumes the idno(primary key) is continued and starts from 1.

However, I set the id as auto increment and deletable.

As time goes, idno may not start from 1 and could be discontinue.

How can I modify the code to cope with the situation?

jQuery:

$(document).ready(function(){   
  function slideout(){
    setTimeout(function(){
      $("#response").slideUp("slow", function () {
    });   
  }, 2000);
}

$("#response").hide();
$(function() {
  $("#list ul").sortable({ opacity: 0.8, cursor: 'move', update: function() {

    var order = $(this).sortable("serialize") + '&update=update'; 
    $.post("updateList.php", order, function(theResponse){
      $("#response").html(theResponse);
      $("#response").slideDown('slow');
      slideout();
    });
   }                                  
});});});

HTML

<body>
<div id="container">
<div id="list">

<div id="response">开发者_Python百科 </div>
<ul>
<?php
  include("connect.php");
  // $query  = "SELECT id, text FROM dragdrop ORDER BY listorder ASC";
  $query  = "SELECT id, name, type FROM project_list ORDER BY 'order' ASC";
  $result = mysql_query($query);
  while($row = mysql_fetch_array($result, MYSQL_ASSOC))
  {
    $id = stripslashes($row['id']);
    $name = stripslashes($row['name']);
    $type = stripslashes($row['type']);

?>
<li id="arrayorder_<?php echo $id ?>"> <?php echo $name?> <?php echo $type; ?>
<div class="clear"></div>
</li>
<?php 
  } 
?>
</ul>
</div>
</div>
</body>

updateList.php

<?php 
  include("connect.php");
  $array = $_POST['arrayorder'];

  if ($_POST['update'] == "update") {
    $count = 1;
    foreach ($array as $idval) {
      $query = "UPDATE project_list SET 'order' = " . $count . " WHERE id = " . $idval;
      mysql_query($query) or die('Error, insert query failed');
      $count ++;    
    }
    echo 'Updated!';
  }
?>

You have a SQL-injection hole (I hear SONY is hiring).

Change the last code block into:

<?php 
include("connect.php");
$array = mysql_real_escape_string($_POST['arrayorder']);

if ($_POST['update'] == "update"){

  $count = 1;
  foreach ($array as $idval) {
    $query = "UPDATE project_list SET `order` = '$count' WHERE id = '$idval'";
    mysql_query($query) or die('Error, update query failed');
    $count ++;  
  }
  echo 'Updated!';
}
?>

This will fix the SQL-injection hole. Note that it's vital to enclose the injected fields with ' single quotes or mysql_real_escape_string() will not work!.

Also reserved words like order need to be enclosed in backticks `, not single quotes.