Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2012
    Posts
    3

    Question Unanswered: Check if item exists in DB

    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:

    PHP 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?
    I don't know how to write a Select statement for this

    Thanks in advance!

    Nathan

  2. #2
    Join Date
    Mar 2012
    Posts
    5
    Quote Originally Posted by raven78 View Post
    How do I add a check to the function that will check if the user has the item?
    I don't know how to write a Select statement for this
    You could just do a SELECT COUNT(*) from active_bufs (or whatever the table is where a character's inventory is kept). i.e., SELECT COUNT(object_name) FROM possessions WHERE character_id = ? and item_id = ?.

    Better still, wrap the use in a stored procedure that returns an error if the character doesn't have the item.

  3. #3
    Join Date
    Mar 2012
    Posts
    3

    re:

    Quote Originally Posted by DatabaseChat View Post
    Better still, wrap the use in a stored procedure that returns an error if the character doesn't have the item.
    Uhm, I am a complete retard if it comes to mysql, would you care to explain, go into details for me?

    Regards, Nathan

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    What5 is being suggested is that you run a query to find if there are existing rows mathcin that criteria

    you are already running a query
    with
    PHP Code:
    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()"); 
    whatever db abstraction class/layer
    so read up on that
    then add an additional query
    y'dont' neccesarily need to use the SQL provided by DatabaseChat... it does the job

    however I think you need to take a step out of the detail fernstance usng terms like buff doens'thelp explain what you are trying to do
    what I think you want is to efectively lock a particular value for a period of time

    so If I were you I'd make sure I only read 'buffs' whose inusetill time was less than the current system time
    as soon as a user secures a 'buff' I'd then update the inusetill column with the current system time

    MySQL :: MySQL 5.5 Reference Manual :: 11.7 Date and Time Functions
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Mar 2012
    Posts
    3
    So something like:

    PHP Code:
    Doquery("SELECT COUNT(*) as 'Amount' FROM 'resources' WHERE `CharID`='".mysql_real_escape_string($character_obj->id)."', `Resource`='".mysql_real_escape_string($kind)."', "); 
    gives me the following error:

    PHP Code:
    WarningSQL ERROR(13): You have an error in your SQL syntaxcheck the manual that corresponds to your MySQL server version for the right syntax to use near ''resources' WHERE `CharID`='10489', `Resource`='Elements',' at line 1 in 
    As you can see it pulls the ID and NAME from the DB:
    CharID`='10489', `Resource`='Elements'
    But why not the Amount of the item?

    Since the Table "resources" contains:

    CharID - Resource - Amount - Location
    user id - name of item - amount of item - location of item

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •