I enjoy simplicity, in everything. Unnecessary complexity is evil. Have a look at my WordPress plugins.

jQuery drag and drop inventory (database interaction) [2/2]

It’s about time I posted the 2nd part of my jQuery drag and drop inventory tutorial. In the first part of this tutorial we’ve created the ability to drag certain items to certain spots. Now, we’ll look at saving the position of the inventory items so they will remain in their place after reloading the page.

To remember the item positions we’ll need to interact with a database and store the items in each spot. There are dozens of ways to handle something like this but I hope this tutorial should give you an idea where to start.

Adding the database

To remember the items a certain player has and into which spot the player stored the item we need to add a database and make a call to a script that updates the database everytime something in the inventory has changed.

Database structure

Let’s start with how we’re going to setup our database. We will create two tables, one for the inventory items and one for the different items available inside our “game”.

CREATE TABLE IF NOT EXISTS `inventory` (
  `player_id` int(11) NOT NULL,
  `inventory` text NOT NULL,
  `weapon_spot` text NOT NULL,
  `helmet_spot` text NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `items` (
  `name` text NOT NULL,
  `type` enum('weapons','helmets') NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

1INSERT INTO `inventory` (`player_id`, `inventory`, `weapon_spot`, `helmet_spot`) VALUES
(1, 'Dagger', 'Sword', 'Helmet');

INSERT INTO `items` (`name`, `type`) VALUES
('Sword', 'weapons'),
('Dagger', 'weapons'),
('Helmet', 'helmets');

This SQL statement creates the two tables and adds some sample data to both tables. We added three items to the game and gave the player with ID 1 some items in its inventory and on the helmet and weapon spot.

Updating our database

Deciding when to update the database can be a little tricky. I’ve decided to add it to our itemInSpot function, which basically means everytime an item is dropped onto a certain spot the database also gets updated. The thing is, I didn’t choose to just add the dropped item to it’s new spot and remove it from it’s old spot. I’ve decided to check the position of all the items and send that to our database update script.

The function I wrote that loops trough all the different items looks something like this.

function update_spots()
{
	// Create a list of all the items
	var inventory_items = '';
	var helmet_spot_item = ($('#helmet_spot img').length > 0) ? $('#helmet_spot img').attr('id') : '';
	var weapon_spot_item = ($('#weapon_spot img').length > 0) ? $('#weapon_spot img').attr('id') : '';
	$('#inventory img').each(
		function(i) {
			if($(this).attr('id') != '') { 
				if(inventory_items.length > 1) inventory_items += ',';
				inventory_items += $(this).attr('id');	
			}
		}
	);	
	var uri = 'update_inventory.php?helmet_spot='+helmet_spot_item+'&weapon_spot='+weapon_spot_item+'&inventory='+inventory_items;
	$.ajax({ url: uri });
}

This function creates 3 different strings, one for each spot. When there is more then one item in the inventory it seperates the different items by a comma. As you can see we’re using the ID attribute of our images to get the names of our items. This name has to be exactly the same as the name we’ve used in our database.

When we’ve created the list of items we make an ajax call to update_inventory.php along with the 3 different strings as a GET request. Now all that’s left is to update the database.

The script that updates the database: update_inventory.php

If you’re thinking about creating your own PBBG you’re probably quite familiar with PHP, so this shouldn’t be a problem. Let’s get right to the whole script. Of course if you have any questions, just leave me a comment!

So, we’re reading the items for each of our spots from the database. We put everything in a list and then make another select request to get which type the items belong to. Now we’ve got ourself some php variables that hold the different items for each spot.

Outputting the items for the helmet or weapon spot.

If you take a closer look at the above code snippet you’ll see that we stored the name for the items in the helmet or weapon spot in variables respectively called $weapon_spot or $helmet_spot. Now inside our helmet or weapon div all we have to do is output this variable, this goes as followed.

	$name = $weapon_spot;
	$type = $item[$weapon_spot];
	echo "<img src=\"$name.png\" class=\"$type\" alt=\"$name\" id=\"$name\">";

Outputting the inventory items

Outputting the different inventory items goes somewhat different because we stored these into an array (hence the explode).

foreach($inv_item as $name) {
	$type = $item[$name];
	?><img src="<?php echo $name; ?>.png" class="<?php echo $type; ?>"><?php
}

The result; a drag and drop inventory that remembers it’s items and positions

If you’ve followed along and did everything right, good job. I’m pretty sure I forgot to mention some things I done but i’m also pretty sure you won’t do this 100% the same as I did. There are many different ways to handle interacting with the database so feel free to expirement a little, this tutorial should give you a good idea on how to start. If you think we could save ourself a few requests to the database, please share how you would have done it!

Oh, and before I forget, want to check the result of what we’ve just made? Check out the demo: jQuery inventory with database interaction

Share this post: on Twitter on Facebook

19 Responses to “jQuery drag and drop inventory (database interaction) [2/2]”

  1. Hi Danny!
    Congratulations on the tutorials,they are very nice and helpful.
    I got one small issue through…when you drag and drop an item from your inventory to its slot,a blank image(just the .png extension) will remain in its place.
    But if you refresh the page,the .png blank image will dissapear and everything will look fine.
    Any ideea on how to make sure that the blank image wont appear when equipping an item?

    Thanks,
    Alexander

  2. Hi Alexander,

    Yeah, seems to be some kind of bug I resolved using somewhat dirty code. I’ll post an easy fix as soon as I get home!

    Best, Danny

  3. I found a fix for it(dunno how good it is):
    When outputting the items for the helmet or weapon spot, I check if the item exists in the inventory,if it doesnt,then it wont echo the image:

    $name = $helmet_spot;
    $type = $item[$helmet_spot];
    if($type)
    echo “”;

  4. Hey danny, i like your tutorial :), But i have a little problem, i’ve done everything in the tutorial says but mysql is not updating, if item moved the spot.

  5. hi, really nice tutorial.
    but can you create an online mysql-database, because your demo want to connect to localhost and so there is an error :/
    would be very nice :)

  6. I am moving my website to WordPress, is there a plugin or script that will automatically upload an inventory file to the database every day? The new inventory file is FTP’s to my server every day. Thanks

  7. I want to set inventory item limitation.
    How can i do?

    I can echo how many items in my inventory with count(explode(‘,’,$inventory_items)) but cant limit it.

    Thanks.

  8. I love this, I would love a little addition showing how to drag and drop ‘potion’ classes onto an avatar div and it removes them (uses them)

  9. Great tutorial, however there’s a lot of security problems, like if I change the class name of an image in the source code, I can easely equip an helmet at the weapon spot. I don’t advise this method for begginers who whant to create an rpg based game, but you can take the idea and add some security to the code :)

    Anyway, thks for the tutorial ;)

  10. Hi Danny van Kooten,

    Really wonderful script, and very helpful… I have a suggestion for you. Can you give this complete script as a pack to download so that your fans can download easily and use it?

    Thanks in advance,

    Morris Walter

  11. guess i’m a little dumb but cant seem to get it to work desent nice demo tho
    any chance on a full repack with db and without

Leave a Reply