Joomla 3更新数据库

Question

Working on a drag and drop system to order items inside joomla. Getting the items from the database is not a problem. I do this with the following code

`<script type="text/javascript">
$(document).ready(function(){ 

    $(function() {
        $("#contentLeft ul").sortable({ opacity: 0.6, cursor: 'move', update: function() {
            var order = $(this).sortable("serialize") + '&action=updateRecordsListings'; 
            $.post("templates/sorteren/test/updateDB.php", order, function(theResponse){
                $("#contentRight").html(theResponse);
            });                                                              
        }                                 
        });
    });
}); 
</script>

    <?php
        $db = & JFactory::getDBO();
    $query = $db->getQuery(true)
        ->select('a.title, a.id, a.sorteren')
        ->from('#__k2_items as a')
        ->where('a.created_by =' . $user->id . ' and a.trash = 0')
        ->order('a.sorteren');
    $db->setQuery($query);
    $items = $db->loadObjectList();
    foreach($items as $item) {
        echo '<li id="recordsArray_' . $item->id . '">' . $item->title . '';

    }?>`

But the problem is somewhere in the following code but i can't not figure out what the problem could be

    <?php defined('_JEXEC') or die;
$db =& JFactory::getDBO();
$query = $db->getQuery(true);


$action                 = mysql_real_escape_string($_POST['action']); 
$updateRecordsArray     = $_POST['recordsArray'];

if ($action == "updateRecordsListings"){

    $listingCounter = 1;
    foreach ($updateRecordsArray as $recordIDValue) {

$query = "UPDATE #__k2_items SET sorteren = " . $listingCounter . " WHERE id = " . $recordIDValue;
                    $db->setQuery($query);
                    $db->query();   


        $listingCounter = $listingCounter + 1;  
    }

    echo '<pre>';
    print_r($updateRecordsArray);
    echo '</pre>';
    echo 'If you refresh the page, you will see that records will stay just as you modified.';
}
?>

UPDATE:

I use this outside Joomla and it works

<script type="text/javascript" src="jquery-1.3.2.min.js"></script>
<script type="text/javascript" src="jquery-ui-1.7.1.custom.min.js"></script>

<script type="text/javascript">
$(document).ready(function(){ 

    $(function() {
        $("#contentLeft ul").sortable({ opacity: 0.6, cursor: 'move', update: function() {
            var order = $(this).sortable("serialize") + '&action=updateRecordsListings'; 
            $.post("updateDB.php", order, function(theResponse){
                $("#contentRight").html(theResponse);
            });                                                              
        }                                 
        });
    });

}); 
</script>



<div id="contentLeft">
            <ul>

<?php
                $query  = "SELECT * FROM kxmkw_k2_items WHERE created_by = 1000 AND trash = 0 ORDER BY sorteren ASC";
                $result = mysql_query($query);

                while($row = mysql_fetch_array($result, MYSQL_ASSOC))
                {
                ?>
                    <li id="recordsArray_<?php echo $row['id']; ?>"><?php echo $row['title'] . "<br> " . $row['id']; ?></li>
                <?php } ?>
            </ul>
        </div>

inside joomla i use this

    <script type="text/javascript">
$(document).ready(function(){ 

    $(function() {
        $("#contentLeft ul").sortable({ opacity: 0.6, cursor: 'move', update: function() {
            var order = $(this).sortable("serialize") + '&action=updateRecordsListings'; 
            $.post("templates/sorteren/test/updateDB.php", order, function(theResponse){
                $("#contentRight").html(theResponse);
            });                                                              
        }                                 
        });
    });

}); 
</script>
<?php
        $db = & JFactory::getDBO();
    $query = $db->getQuery(true)
        ->select('a.title, a.id, a.sorteren')
        ->from('#__k2_items as a')
        ->where('a.created_by =' . $user->id . ' and a.trash = 0')
        ->order('a.sorteren');
    $db->setQuery($query);
    $items = $db->loadObjectList();
    foreach($items as $item) {
        echo '<li id="recordsArray_' . $item->id . '">' . $item->title . '';

    }?>

Answer 1

To start, you're not using Joomla coding standards for everything and for those bits that you are, they're old standards.

Try using this:

<?php 
defined('_JEXEC') or die;

$db = JFactory::getDbo();
$query = $db->getQuery(true);

$input = new JInput;
$post = $input->getArray($_POST);
$action = $post["action"];
$updateRecordsArray = $post["recordsArray"];

if ($action == "updateRecordsListings"){

    $listingCounter = 1;
    foreach ($updateRecordsArray as $recordIDValue) {

        $fields = array(
            $db->quoteName('sorteren') . '=' . $listingCounter
        );   
        $conditions = array(
            $db->quoteName('id') . ' = ' . $recordIDValue
        );

        $query->update($db->quoteName('#__k2_items'))->set($fields)->where($conditions);

        $db->setQuery($query);
        $db->execute();   

        $listingCounter = $listingCounter + 1;  
    }

    echo '<pre>';
    print_r($updateRecordsArray);
    echo '</pre>';
    echo 'If you refresh the page, you will see that records will stay just as you modified.';
}
?>

As you can see, I have made some changes:

• Replaced $_POST with JInput methods.
• Replaced $db->query() with $db->execute() as $db->query() is deprecated.
• Used up to date methods to update the data in the database
• made the fields and conditions an array incase you wish to add more in the future.
• Replaced the escape_string function with quoteName() in the actual query which takes care of this.

For more information on how to insert or update data in Joomla, have a read of this:

http://docs.joomla.org/Inserting,_Updating_and_Removing_data_using_JDatabase

Update 2:

Try using this instead:

$input = new JInput;
$post = $input->getArray($_POST);
$action = $post["action"];
$updateRecordsArray = $post["recordsArray"];

I've updated the full query with these changes.

If this doesn't work, try dumping the variables to see if there are any values like so:

var_dump($action);
var_dump($updateRecordsArray);

Hope this helps