可以使用数据库PHP和MYSQL进行拖拽

Question

So I'm making a Video Game online, and my players have Inventory and Profiles.

I'm trying to make it Load the Inventory into a Canvas, and then make the Items Draggable anywhere on it, then there's a Save Button to save it.

So it will only Load the Items it has Added into The Room already. So lets say I added a Chair, Desk, Bed from inventory into The Database, Room.

Those items should show up on the Profile.

But how can I make them move and Draggable with a Save Button?

This is my site right now if you want to check it out. www.lupekid.com

---

EDIT:

If you look at my site, I only have done it though PHP Clicks. But I'm trying to change it to do Draggable and I'm not sure where to start. I'm trying to do it like this:

jsfiddle.net/jakecigar/v685v9t6/31

but I'm not sure how to integrate it with my database, I'm not that familiar with jQuery.

public function DisplayMyBeds2()
{

    $myuserid = $_SESSION['user_id'];
    // This first query is just to get the total count of rows
    $sql = "SELECT COUNT(*) FROM myitems WHERE (user_id='$myuserid' AND itemCategory='bed')";
    $query = mysqli_query($this->_con, $sql);
    $row = mysqli_fetch_row($query);
    // Here we have the total row count
    $rows = $row[0];
    // This is your query again, it is for grabbing just one page worth of rows by applying $limit
    $sql = "SELECT * FROM myitems WHERE (user_id='$myuserid' AND itemCategory='bed') ORDER BY itemId ASC";
    $query = mysqli_query($this->_con, $sql);
    $list = '';
    $count = mysqli_num_rows($row);

    echo '<div id="message" style="display:none"><h1>Saved!</h1></div>';

    while($row = mysqli_fetch_array($query, MYSQLI_ASSOC)){

        echo '
            <div id="containment-wrapper">
                <div id="bed'.$row["itemId"].'" class="ui-widget-content draggable" data-left="20px" data-top="20px" style="position: absolute; left:20px; top:20px">'.$row["itemName"].'</div>
            </div>';
    }

Answer 1

Lets try to change a bit your table structure. Lets have a table where you store different types of furniture, lets name it furniture_tb:

 fur_id | furniture |  fur_image
--------+-----------+-------------
    1   |   chair   |   chair.png
    2   |   table   |   table.png
    3   |   couch   |   couch.png
    4   |   window  |   window.png

Then on your myitems table, we will add more columns for their saved position, and we will replace the itemName column with just the fur_id:

 itemId | user_id | fur_id | left_position | top_position 
--------+---------+--------+---------------+--------------
    1   |    1    |    1   |     20px      |      20px
    2   |    1    |    2   |     97px      |      102px 
    3   |    1    |    3   |     98px      |      20px
    4   |    1    |    4   |     176px     |      20px

So when you load them (lets use prepared statement), you can have it like this (using LEFT JOIN) where it will load all the furnitures that was assigned to the user. We will also use the fetched left_position and top_position column to the style tag to position them inside the containment-wrapper:

echo '<div id="message" style="display:none"><h1>Saved!</h1></div>
      <div id="containment-wrapper">';

$stmt = $con->prepare("SELECT a.itemId, 
                              a.left_position,
                              a.top_position,
                              b.furniture,
                              b.fur_image
                       FROM myitems a
                       LEFT JOIN furniture_tb b ON a.fur_id = b.fur_id WHERE user_id = ?");
$stmt->bind_param("i", $myuserid); /* REPLACE THE ? IN THE QUERY ABOVE WITH $myuserid; i STANDS FOR INTEGER */
$stmt->execute(); /* EXECUTE QUERY */
$stmt->bind_result($itemid, $leftpos, $toppos, $furniture, $furimage); /* BIND THE RESULT TO THESE VARIABLES */
while($stmt->fetch()){ /* FETCH ALL RESULTS */

    echo '<div id="'.$itemid.'" class="ui-widget-content draggable" style="position: absolute; left:'.$leftpos.'; top:'.$toppos.'">'.$furniture.'</div>';

}
$stmt->close(); /* CLOSE PREPARED STATEMENT */

echo '</div><!-- CONTAINMENT-WRAPPER -->';

Lets create your jQuery script. We will get the current position of each div.draggable and Ajax to save the new position to the database.

/* GRANT THE DIV WITH draggable CLASS TO BE DRAGGABLE */
$(document).on("ready", function(){ 
    $(".draggable").draggable({
        containment: "#containment-wrapper"
    });
})

/* WHEN USER HIT THE SAVE BUTTON */
$(document).on("ready", "#save", function(){

    $(".draggable").each(function(){ /* RUN ALL FURNITURE */

        var elem = $(this),
            id = elem.attr('id'), /* GET ITS ID */
            pos = elem.position(),
            newleft = pos.left+'px', /* GET ITS NEW LEFT POSITION */
            newtop = pos.top+'px'; /* GET ITS NEW TOP POSITION */

        $.ajax({ /* START AJAX */
            type: 'POST', /* METHOD TO PASS THE DATA */
            url: 'save-position.php', /* FILE WHERE WE WILL PROCESS THE DATA */
            data: {'id':id, 'newleft': newleft, 'newtop':newtop}, /* DATA TO BE PASSED TO save-position.php */
            success: function(result){
                $("#message").show(200); /* SHOW THE SUCCESS SAVE MESSAGE */
            }
        })            

    })

})

You will notice that we pass the data to save-position.php, so lets create this file:

/*** INCLUDE YOUR DATABASE CONNECTION HERE FIRST ***/

if(!empty($_POST["id"]) && !empty($_POST["newleft"]) && !empty($_POST["newtop"])){

    $stmt = $con->prepare("UPDATE myitems SET left_position = ?, top_position = ? WHERE itemId = ?"); /* PREPARE YOUR UPDATE QUERY */
    $stmt->bind_param("ssi", $_POST["newleft"], $_POST["newtop"], $_POST["id"]); /* BIND THESE PASSED VARIABLES TO THE QUERY ABOVE; s STANDS FOR STRINGS; i STANDS FOR INTEGER */
    $stmt->execute(); /* EXECUTE QUERY */
    $stmt->close(); /* CLOSE PREPARED STATEMENT */

}

You can look at this fiddle for example. But it will not save the position in this example because we don't have a database to save the data with.