Board index   FAQ   Search  
Register  Login
Board index php forum :: php coding PHP coding => General

Having trouble deleting records from a table...

Ask about general coding issues or problems here.

Moderators: macek, egami, gesf

Having trouble deleting records from a table...

Postby Dabil » Tue Oct 23, 2012 5:18 am

I have a website called Gatherling.com where Magic The Gathering Online tournaments are held. The site allows users to create their own formats and organize tournaments that Magic Online does not support. So for example players can create a Format where only commons and uncommons from the modern sets are legal. This is very fun as it allows players to ban cards that are very expensive to create events that players can join cheaply. The problem with this though is that I currently do not have any way of validating user submitting deck lists to make sure the decks they submit are legal for a given format. And players often accidentally play some cards that are illegal. Which leads to some embarrassment. So...

I am trying to create a deck validation system that will anaylize a deck list and let the user know if it is a legal list, and if not, what is wrong with it so that the user can fix it. This deck validation system will accept as input a deck list for the game (in text format), and then generate errors that are placed in a table called deckerrors if any exist. The specific data saved is a description of the error and the DeckID. When a deck list is viewed, the list of errors for that deck are then loaded. The user then has the opportunity to make edits to the deck to correct the errors given and then re-save the deck list.

So the save function when called is where the errors are generated. The first thing it needs to do though is delete all the old errors before re-parsing the newly edited list and generating any new errors. The problem is that for some reason the errors for the deck list are not being deleted. Instead the errors just keep getting added. So if a player re-saves a deck list after correcting the problem, the old error message will still appear when he views the list. Here is the snippet of the code that does the deleting:

Code: Select all
   
    $succ = $db->query("DELETE FROM deckerrors WHERE deck = {$this->id}");

    if (!$succ) {
      $db->rollback();
      $db->autocommit(TRUE);
      throw new Exception("Can't update deck contents {$this->id}");
    }


I know this code works, because if I move it to other functions, it deletes the errors. For example it works fine from this function:

Code: Select all
  function isValid() {
    $db = Database::getConnection();
    $succ = $db->query("DELETE FROM deckerrors WHERE deck = {$this->id}");

    if (!$succ) {
      $db->rollback();
      $db->autocommit(TRUE);
      throw new Exception("Can't update deck contents {$this->id}");
    }
    return (count($this->errors) == 0);
  }


So I am not sure exactly why it doesn't work from my save() function. Been racking my brains on it. I would greatly appreciate any incite. Below is my save function in its entirety. Upon request I would be happy to supply more code if needed. There are no errors generated by this code. It runs fine, it just doesn't delete the records from the deckerrors table. You will notice that just before it, I am deleting records from the deckcontents table using pretty much exactly the same code. That works fine. I tried inverting these two segments to see if they were interferring with each other and that maybe only the first one was getting done, but with the same result.

Thank you in advance for any help that I receive.

Code: Select all
 
    function save() {
    $db = Database::getConnection();
    $db->autocommit(FALSE);

    if ($this->name == NULL || $this->name == "") {
      $this->name = "{$this->playername}'s Deck for {$this->eventname}";
    }
    if ($this->archetype != "Unclassified" && !in_array($this->archetype, Deck::getArchetypes())) {
      $this->archetype = "Unclassified";
    }
   
    if ($this->id == 0) {
      // New record.  Set up the decks entry and the Entry.
      $stmt = $db->prepare("INSERT INTO decks (archetype, name, notes)
        values(?, ?, ?)");
      $stmt->bind_param("sss", $this->archetype, $this->name, $this->notes);
      $stmt->execute();
      $this->id = $stmt->insert_id;

      $stmt = $db->prepare("UPDATE entries SET deck = {$this->id} WHERE player = ? AND event = ?");
      $stmt->bind_param("ss", $this->playername, $this->eventname);
      $stmt->execute();
      if ($stmt->affected_rows != 1) {
        $db->rollback();
        $db->autocommit(TRUE);
            throw new Exception('Entry for '. $this->playername .' in '. $this->eventname .' not found');
      }
    } else {
      $stmt = $db->prepare("UPDATE decks SET archetype = ?, name = ?,
        notes = ? WHERE id = ?");
      if (!$stmt) {
        echo $db->error;
      }
      $stmt->bind_param("sssd", $this->archetype, $this->name, $this->notes, $this->id);
      if (!$stmt->execute()) {
        $db->rollback();
        $db->autocommit(TRUE);
        throw new Exception('Can\'t update deck '. $this->id);
      }
    }
   
   $succ = $db->query("DELETE FROM deckcontents WHERE deck = {$this->id}");

    if (!$succ) {
      $db->rollback();
      $db->autocommit(TRUE);
      throw new Exception("Can't update deck contents {$this->id}");
    }
   
    $succ = $db->query("DELETE FROM deckerrors WHERE deck = {$this->id}");

    if (!$succ) {
      $db->rollback();
      $db->autocommit(TRUE);
      throw new Exception("Can't update deck contents {$this->id}");
    }
   
    $newmaindeck = array();
    $this->maindeck_cardcount = 0;
   
    foreach ($this->maindeck_cards as $card => $amt) {
      $card = stripslashes($card);
      $cardar = $this->getCard($card);
      if (is_null($cardar)) {
        $this->errors[] = "Could not find maindeck card: {$amt} {$card}";

        if (!isset($this->unparsed_cards[$card])) {
          $this->unparsed_cards[$card] = 0;
        }
        $this->unparsed_cards[$card] += $amt;
        continue;
      }
      $this->maindeck_cardcount += $amt;
      $stmt = $db->prepare("INSERT INTO deckcontents (deck, card, issideboard, qty) values(?, ?, 0, ?)");
      $stmt->bind_param("ddd", $this->id, $cardar['id'], $amt);
      $stmt->execute();
      $newmaindeck[$cardar['name']] = $amt;
    }

    $this->maindeck_cards = $newmaindeck;

    $newsideboard = array();
    $this->sideboard_cardcount = 0;
   
    foreach ($this->sideboard_cards as $card => $amt) {
      $card = stripslashes($card);
      $cardar = $this->getCard($card);
      if (is_null($cardar)) {
        $this->errors[] = "Could not find sideboard card: {$amt} {$card}";

        if (!isset($this->unparsed_side[$card])) {
          $this->unparsed_side[$card] = 0;
        }
        $this->unparsed_side[$card] += $amt;
        continue;
      }
      $this->sideboard_cardcount += $amt;
      $stmt = $db->prepare("INSERT INTO deckcontents (deck, card, issideboard, qty) values(?, ?, 1, ?)");
      $stmt->bind_param("ddd", $this->id, $cardar['id'], $amt);
      $stmt->execute();
      $newsideboard[$cardar['name']] = $amt;
      }

    $this->sideboard_cards = $newsideboard;

    $stmt = $db->prepare("UPDATE decks SET notes = ? WHERE id = ?");
    if (!$stmt) {
      echo $db->error;
    }
    $stmt->bind_param("sd", $this->notes, $this->id);
    if (!$stmt->execute()) {
      $db->rollback();
      $db->autocommit(TRUE);
      throw new Exception('Can\'t update deck '. $this->id);
    }

    $this->deck_contents_cache = implode('|', array_merge(array_keys($this->maindeck_cards),
                                                          array_keys($this->sideboard_cards)));

    $stmt = $db->prepare("UPDATE decks set deck_contents_cache = ? WHERE id = ?");

    $stmt->bind_param("sd", $this->deck_contents_cache, $this->id);
    $stmt->execute();

    $db->commit();
    $db->autocommit(TRUE);
    $this->calculateHashes();
   
    if ($this->maindeck_cardcount < 60) {
        $this->errors[] = "There must be at least 60 Maindeck Cards";
    }
   
    if ($this->sideboard_cardcount != 15 && $this->sideboard_cardcount != 0) {
        $this->errors[] = "If you have a sideboard there must be 15 cards exactly";
    }
   
    foreach($this->errors as $error) {
        $stmt = $db->prepare("INSERT INTO deckerrors (deck, error) values(?, ?)");
        $stmt->bind_param("ds", $this->id, $error);
        $stmt->execute();       
    }

    return true;
  }
Last edited by Dabil on Tue Oct 23, 2012 6:51 am, edited 1 time in total.
Dabil
New php-forum User
New php-forum User
 
Posts: 3
Joined: Tue Oct 23, 2012 4:53 am

Re: Having trouble deleting records from a table...

Postby Dabil » Tue Oct 23, 2012 6:01 am

I set this code up on my beta site if anyone would like to see it in action: beta.

Steps to test:

1) Click this link to register: This Link
2) Then log in.
3) You will then be directed to the player cp. Under the column "PREREGISTER FOR EVENTS" on the left hand side, click the register link for "Dabils test Series 1.10"
4) This will open the deck submission form. Put the following deck list in:

Main Deck

1 Forest
3 Lightning Bolt
2 Swamp
4 Treetop Village
2 Terminate
4 Dark Confidant
1 Overgrown Tomb
1 Stomping Ground
1 Blood Crypt
4 Tarmogoyf
2 Thoughtseize
3 Kitchen Finks
2 Twilight Mire
2 Jund Charm
4 Bloodbraid Elf
2 Maelstrom Pulse
1 Marsh Flats
1 Misty Rainforest
4 Verdant Catacombs
2 Raging Ravine
4 Inquisition of Kozilek
4 Blackcleave Cliffs
3 Liliana of the Veil
2 Hutmaster of the Fells

Sideboard

2 Obstinate Baloth
3 Ancient Grudge
3 Thrn of Amthyst
2 Thrun, the Last Troll
3 Torpor Orb

There are errors in the above deck list. Try correcting them and see what happens. The old errors will remain even upon correction.
Dabil
New php-forum User
New php-forum User
 
Posts: 3
Joined: Tue Oct 23, 2012 4:53 am

Re: Having trouble deleting records from a table...

Postby Dabil » Tue Oct 23, 2012 7:22 am

umm I feel kind of stupid, but yeah sorry I just figured it out on my own.

I forgot to reset the $this->errors array in the class that I was using to save the generated errors. So just adding:

Code: Select all
$this->errors = array();


to remove the old errors fixed the problem. As it turns out the deckerrors table records were being deleted, but because I never removed the errors from the class errors array, they were just being put right back plus any new errors.

Silly programmer.
Dabil
New php-forum User
New php-forum User
 
Posts: 3
Joined: Tue Oct 23, 2012 4:53 am

Re: Having trouble deleting records from a table...

Postby seandisanti » Tue Oct 23, 2012 2:52 pm

Good job figuring it out, those little things can be killers. My most time consuming issues are typically resolved by typing either a single character, or a single line. Sounds like a pretty cool idea for a site too, good work.
seandisanti
php-forum Fan User
php-forum Fan User
 
Posts: 838
Joined: Mon Oct 01, 2012 12:32 pm


Return to PHP coding => General

Who is online

Users browsing this forum: Google [Bot] and 2 guests

Sponsored by Sitebuilder Web hosting and Traduzioni Italiano Rumeno and antispam for cPanel.

cron