MYSQL: how to “reorder” a table with discontinued idno?
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.
加载中,请稍侯......
精彩评论