Problem with a function not working correct

[Ravendk]

Good evening,

I have a piece of code where users activate a certain buff, but the check if users actually have the item for this is missing

This is the code:

        if( $kind=='Unnatural' OR $kind=='Inner' OR $kind=='Artificial' OR $kind=='Final' OR $kind=='Pandemonium' OR $kind=='Forced' OR $kind=='Liquid' OR $kind=='Elements' ) {
        DoQuery("INSERT INTO `active_bufs` SET `CharID`='".mysql_real_escape_string($character_obj->id)."', `BufKind`='Potion', `BufName`='".mysql_real_escape_string($kind)."', `Date`=now(), `Duration`='6' ON DUPLICATE KEY UPDATE `BufName`='".mysql_real_escape_string($kind)."', `Date`=now()");
        $used = 1;
      }

        if ($used == 1){
        $error = '<div class="error_green">'._("Resource succesfully used").'</div>';
        }else{
          if ( !isset($error) ){ $error = '<div class="error_red">'._("Sorry..you don't have this item in your inventory, or can't use it").'</div>'; }
              $character_obj->AddResource(1,$kind,1);

Now what happens;

A user uses the buff called "Forced" and it will be activated for 6 hours.
But right now it is possible that after the buff expires, or even don't currently have the buff, they can use the link to the item in the browser to activate it anyway.

How do I add a check to the function that will check if the user has the item?

Thanks in advance!

Nathan

 

[jMerliN]

PHP is just not a good language for good state management, especially not if you're trying to use MySQL to do it. You'll have to query their active buffs and see if they already have it.

You can store it in their session but that session isn't DB backed which makes it volatile (unless you have something doing session management in the DB transparently, in which case you should use that and not a buffs table). Volatile state should almost never be stored in a database table, but that's basically all you can do in PHP aside from sessions which have tons of problems.

You could throw up a memcached and use it as a disk-backed KV store to persist a session object for each user to put commonly updated/accessed fields in instead of using MySQL, just key it by account ID.

Alternatively, you could use your current approach but just do a

SELECT COUNT(*) FROM `active_buffs` WHERE `CharID` = ... AND `BufKind` = .... AND <<Date + duration < now>>

To get any active buffs. But now you're in a table management issue (those buffs that aren't active should be purged), that table will grow large with any usage, and as this will likely be invoked often, you will need to index it so a query like this will be fast, but because it will eventually be huge, inserts will take a long time and your application will crawl.

PHP + MySQL backed state management just doesn't have a good solution at all.

 

[TimeBomb]

I agree with jMerliN.

As jMerliN said, with PHP, your two main choices are causing a lot of strain on the database, or allowing for lots of things to go wrong by storing it as a session.

With an actual client game, you have the database, perhaps the website or other web-based scripts that may interact with the database, and a server application. The user uses the game client, which associates with the server, which associates with the database. In this situation, the server application would keep an eye on buffs and what-not.

Perhaps it may be a good idea to create an external server application and weave that in with the rest of the PHP/MySQL application. The main difference between your scenario and the example I described above being your game client is written in PHP and served over a website.

If you must stay solely with PHP/MySQL, I would suggest looking into database design and optimization - more so on the MySQL side than the web server side.

 

[Ravendk]

Thanks for the answers,

It's like this:

Users have an inventory on they're characters, with items in it like a potion called Forced.
After they click/use the potion it last 6 hours. Now when the effect expires, they can simply use the link to activate the buff again from the potion they don't have in they're inventory anymore:

        if( $kind=='Unnatural' OR $kind=='Inner' OR $kind=='Artificial' OR $kind=='Final' OR $kind=='Pandemonium' OR $kind=='Forced' OR $kind=='Liquid' OR $kind=='Elements' ) {
        DoQuery("INSERT INTO `active_bufs` SET `CharID`='".mysql_real_escape_string($character_obj->id)."', `BufKind`='Potion', `BufName`='".mysql_real_escape_string($kind)."', `Date`=now(), `Duration`='6' ON DUPLICATE KEY UPDATE `BufName`='".mysql_real_escape_string($kind)."', `Date`=now()");
        $used = 1;

So basically, I need a line in there that checks the DB if the potion is in they're inventory or not

In the DB this info is stored as follow:

CharID - Resource - Amount - Location
000001 - Forced - 1 - 3

The Table is called "resources"

I was told a "SELECT statement" would do the trick, but I am a complete "noob" with mysql.

Sorry if I didn't got your answer right, like I said, i'm terrible with mysql.
But this one little issue is for me the most important "bug" to get solved.

So if you or anyone has the solution to the problem, I would be most greatfull.

With kind regards, Nathan

 

[TimeBomb]

snip

If you do this with only PHP and MySQL, your database may get heavily pressured very easily. The chances of this happening goes way up if you do not design the database well.

What this in turn means is that the database, server, or/and website constantly slow down to a crawl, or they just plain crash.

 

[Ravendk]

I understand what you mean, and that is not really a problem since al temporarely stored date is removed after 30 minutes like chat logs automatically.

Yet I appreciate your answer to that, i'm still in search for a solution to my actual issue, the "check if item exist in users inventory"

Regards, Nathan

 

[Mucski]

currently

if ($used == 1){
$error = '<div class="error_green">'._("Resource succesfully used").'</div>';
}else{
if ( !isset($error) ){ $error = '<div class="error_red">'._("Sorry..you don't have this item in your inventory, or can't use it").'</div>'; }
$character_obj->AddResource(1,$kind,1);

if the item isused gives the green error , if not it gives the "sorry you don't have this item in your inventory" error , so basically it checks for nothing , you should define the item , define the inventory as array , see if the array has the item , if item present in the array , user has the item , if not , user doesnt have the item , de activate buff ... this is just an example of what you could do , or you could add it to a temp table , on the same principles , and if user has the item in temp db use it , and delete it , or if you want to keep it move it to permanent inventory . etc.

Thanks for the answers,

It's like this:

Users have an inventory on they're characters, with items in it like a potion called Forced.
After they click/use the potion it last 6 hours. Now when the effect expires, they can simply use the link to activate the buff again from the potion they don't have in they're inventory anymore:

        if( $kind=='Unnatural' OR $kind=='Inner' OR $kind=='Artificial' OR $kind=='Final' OR $kind=='Pandemonium' OR $kind=='Forced' OR $kind=='Liquid' OR $kind=='Elements' ) {
        DoQuery("INSERT INTO `active_bufs` SET `CharID`='".mysql_real_escape_string($character_obj->id)."', `BufKind`='Potion', `BufName`='".mysql_real_escape_string($kind)."', `Date`=now(), `Duration`='6' ON DUPLICATE KEY UPDATE `BufName`='".mysql_real_escape_string($kind)."', `Date`=now()");
        $used = 1;

So basically, I need a line in there that checks the DB if the potion is in they're inventory or not

In the DB this info is stored as follow:

CharID - Resource - Amount - Location
000001 - Forced - 1 - 3

The Table is called "resources"

I was told a "SELECT statement" would do the trick, but I am a complete "noob" with mysql.

Sorry if I didn't got your answer right, like I said, i'm terrible with mysql.
But this one little issue is for me the most important "bug" to get solved.

So if you or anyone has the solution to the problem, I would be most greatfull.

With kind regards, Nathan

Using the select :

Here is a good example on how to use select statement on what you need

php - Check if value exists In MySQL row - Stack Overflow

for example

SELECT `header` FROM `data` WHERE `var` = '$foo'

$db = mysqli_connect('localhost', 'username', 'password', 'database');
if($query = mysqli_query($db, "SELECT `header` FROM `data` WHERE `var` = '$foo'")){
  while($row = mysqli_fetch_assoc($query)){
    echo $row['header'];
  }
  mysqli_free_result($query);
}

let say the item is $foo , so if , and pretend that $foo returns 1 from querying the mysql database

 if( $kind=='Unnatural' OR $kind=='Inner' OR $kind=='Artificial' OR $kind=='Final' OR $kind=='Pandemonium' OR $kind=='Forced' OR $kind=='Liquid' OR $kind=='Elements' ) { 
        DoQuery("INSERT INTO `active_bufs` SET `CharID`='".mysql_real_escape_string($character_obj->id)."', `BufKind`='Potion', `BufName`='".mysql_real_escape_string($kind)."', `Date`=now(), `Duration`='6' ON DUPLICATE KEY UPDATE `BufName`='".mysql_real_escape_string($kind)."', `Date`=now()"); 
        $used = 1; 
      }

        if ($foo!=1) { echo "sorry you don't have the item required to use this buff" } else { echo "you have successfully used this item"; } $character_obj->AddResource(1,$kind,1);

 

[Ravendk]

So, I did like this:

$db = mysqli_connect('-------', '--------', '-------', '--------');
if($query = mysqli_query($db, "SELECT `Resource` FROM `resources` WHERE `var` = '$kind'")){
  while($row = mysqli_fetch_assoc($query)){
    echo $row['header'];
  }
  mysqli_free_result($query);
}  
		
		
		
	    if( $kind=='Unnatural' OR $kind=='Inner' OR $kind=='Artificial' OR $kind=='Final' OR $kind=='Pandemonium' OR $kind=='Forced' OR $kind=='Liquid' OR $kind=='Elements' ) {
        DoQuery("INSERT INTO `active_bufs` SET `CharID`='".mysql_real_escape_string($character_obj->id)."', `BufKind`='Potion', `BufName`='".mysql_real_escape_string($kind)."', `Date`=now(), `Duration`='6' ON DUPLICATE KEY UPDATE `BufName`='".mysql_real_escape_string($kind)."', `Date`=now()");
        $used = 1;
      }

        if ($kind!=1) { echo "sorry you don't have the item required to use this buff"; } else { echo "you have successfully used this item"; } $character_obj->AddResource(1,$kind,1);  
			  	      
	    }

		}

Now That message shows up in the upper left corner of the screen instead of where it should be and in plain black text.

I can still use the item, but it doesn't delete it from my inventory anymore, better yet.. it ADDS it to my inventory :/ when I delete it first