Problem with sql entry...

[Hall of Famer]

Well this sounds weird, but it happens. I have an itemshop script, and I wanna code a system that automatically delete the database entry once the variable item amount falls to 0. However, this doesnt happen when I check phpmyadmin, and the item remains in the database even after the amount becomes 0. In fact, the amount can become negative at times, which annoys me.

 

 

The code of deleting sql entry looks like this:

 

if($continue == "yes"){
$query = "SELECT * FROM ".$prefix."user_inventory WHERE item_owner = '$loggedinname' AND item_name = '$item_name'";
                  $result = mysql_query($query);
                  $num = mysql_numrows($result);
                  $item_data = mysql_fetch_array($result);
                  
                  $item_amount = $item_data['item_amount'];
                  
                  $newquantity = $item_amount - $quantity;

$query = "UPDATE ".$prefix."user_inventory SET item_amount = '$newquantity' WHERE item_owner = '$loggedinname' AND item_name = '$item_name'";
                  $result = mysql_query($query);
                  
                  if($newquantity == "0"){
                    $query = "DELETE FROM ".$prefix."user_inventory WHERE item_owner = '$loggedinname' AND item_name = '$item_name'";
                  $result = mysql_query($query);
                  }
                  

 

What part of the codes should I edit to fix this issue? Thanks.

[Twitch]

I haven't really looked at the whole code, but is the amount field an int or varchar field? 

 

Have you tried getting rid of the " around the 0, also maybe try less than or equal in case the value goes negative like you say is sometimes does:

 if($newquantity <= 0){

 

 

[Pikachu2000]

The logic looks flawed to me. You run a SELECT query that checks the quantity, then an immediate UPDATE query, regardless of the quantity, then check what the total quantity is to conditionally run a DELETE query.

 

It seems like it should SELECT the current quantity, do the math, then conditionally run either the UPDATE or DELETE query. Obviously, this is untested, but it should get you pointed in the right direction.

 

<?php
if($continue == "yes"){
$query = "SELECT * FROM ".$prefix."user_inventory WHERE item_owner = '$loggedinname' AND item_name = '$item_name'";
$result = mysql_query($query);
$num = mysql_numrows($result);
$item_data = mysql_fetch_array($result);
$item_amount = $item_data['item_amount'];
$newquantity = $item_amount - $quantity;

if( $newquantity > 0 ) {
	$query = "UPDATE ".$prefix."user_inventory SET item_amount = '$newquantity' WHERE item_owner = '$loggedinname' AND item_name = '$item_name'";
	$result = mysql_query($query);
} else {
	$query = "DELETE FROM ".$prefix."user_inventory WHERE item_owner = '$loggedinname' AND item_name = '$item_name'";
	$result = mysql_query($query);
}
?>

[rwwd]

^^^ agreed, there are some spelling error's in the function names too check out your version on mysql_num_rows() too, as you have mysql_numrows() which really should have thrown an error if you had error_reporting(E_ALL); set...

 

Also you have two queries with the SAME variable NAME attached to it, so the second query IS confused therefore will only take the latter of the two, which fortunately for this excerpt is right, but honestly, get your naming conventions sorted byt naming the vars to something that echo's the job it's doing, as this will make your code easier to read when you come back to it in a few months time...

 

Rw

 

 

[Hall of Famer]

Thank you for all these replies guys and gals, I really appreciate. Will test the codes given by Pikachu2000 on my site later tonight. ^^

[Hall of Famer]

umm got these two error messages...

 

 

Warning: mysql_numrows(): supplied argument is not a valid MySQL result resource in /home7/pkmnhofc/public_html/adoptables/fishing.php on line 1062

 

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home7/pkmnhofc/public_html/adoptables/fishing.php on line 1063

 

BTW, these were the codes around the line 1062 before I made this thread, in which the delete sql query command did not work properly. Anyway idea how to fix this?

 

else if($act == "capturemagikarp"){

$promocode = "none";
$quantity = "1";

$article_content ="<center><table style='color: #FFFF31; background: Green; width: 400px; border: 1px solid #9BDDFF;'><tr><td>
<br><center><img src='http://www.pokemonelite2000.com/sprites/plmfa/129.png' border='0'>
<br><form name='form1' method='get' action='doadopt.php'>
  <p>Name: 
    <input name='name' type='text' id='name'>
    <input name='id' type='hidden' id='id' value='129'>
    <input name='promocode' type='hidden' id='promocode' value='".$promocode."'>
  </p>
  <p>
    <input type='submit' name='Submit' value='Capture'>
</p>
</form></center><br><br></td></tr></table></center>";



$query = "SELECT * FROM ".$prefix."user_inventory WHERE item_owner = '$loggedinname' AND item_name = 'Pokeball'";
                  $result = mysql_query($query);
                  $num1 = mysql_numrows($result);
                  $item_data = mysql_fetch_array($result);
                  
                  $item_amount = $item_data['item_amount'];
                  
                  $newquantity = $item_amount - $quantity;

$query = "UPDATE ".$prefix."user_inventory SET item_amount = '$newquantity' WHERE item_owner = '$loggedinname' AND item_name = 'Pokeball'";
                  $result = mysql_query($query);
                  
                  if($newquantity == "0"){
                    $query = "DELETE FROM ".$prefix."user_inventory WHERE item_owner = '$loggedinname' AND item_name = '$item_name'";
                  $result = mysql_query($query);
                  }


}

[BlueSkyIS]

typically means your query failed. find out why:

 

$result = mysql_query($query) or die(mysql_error());

[Hall of Famer]

umm what do you mean?

[A1SURF.us]

I think you need something like this

 

// post

if ($_POST["Pokeball"] != "") {

//sanitize the variable

$Pokeball = securedata($_POST["Pokeball"]);
$Pokeball = limitatexto($Pokeball,1);

$check = mysql_query("SELECT user_inventory FROM item_owner WHERE user_inventory='$loggedinname'");
$check_item = mysql_num_rows($check);


if ($check_item>0) {

$sqlz = "SELECT * FROM user_inventory WHERE user_inventory='$loggedinname'";
$resultz = mysql_query($sqlz);        
$myrowz = mysql_fetch_array($resultz);

$numero=$myrowz["user_inventory"];

$sqlex = "UPDATE user_inventory SET user_inventory='$numero' +1 WHERE user_inventory='$loggedinname'";
      $resultex = mysql_query($sqlex);
}

}

[rwwd]

as you have mysql_numrows() which really should have thrown an error if you had error_reporting(E_ALL);

 

I did tell you that you would get that error...

 

Rw

[Hall of Famer]

Thanks for this post dude. Would you mind explaining your codes a little bit since it seems somewhat hard for me to interpret. Where are the codes that delete a row in mysql table once the item amount becomes 0? XD

[Hall of Famer]

umm can anyone please help? ^^

[Pikachu2000]

I added some error checking . . . See what happens.

 

<?php
if($continue == "yes"){
$query = "SELECT * FROM ".$prefix."user_inventory WHERE item_owner = '$loggedinname' AND item_name = '$item_name'";
$result = mysql_query($query) or die( '<br>Query: ' . $query . '<br>Error: ' . mysql_error() . '<br>');
$num = mysql_num_rows($result); // not mysql_numrows
$item_data = mysql_fetch_array($result);
$item_amount = $item_data['item_amount'];
$newquantity = $item_amount - $quantity;
if( $newquantity > 0 ) {
	$query = "UPDATE ".$prefix."user_inventory SET item_amount = '$newquantity' WHERE item_owner = '$loggedinname' AND item_name = '$item_name'";
	$result = mysql_query($query) or die( '<br>Query: ' . $query . '<br>Error: ' . mysql_error() . '<br>');
} else {
	$query = "DELETE FROM ".$prefix."user_inventory WHERE item_owner = '$loggedinname' AND item_name = '$item_name'";
	$result = mysql_query($query) or die( '<br>Query: ' . $query . '<br>Error: ' . mysql_error() . '<br>');
}
}
?>

[mikosiko]

Picachu2000's code should work.... this one below is just my personal preference

 

<?php
  if($continue == "yes"){
      $query = "DELETE FROM ".$prefix."user_inventory WHERE item_owner = '$loggedinname' AND item_name = '$item_name' AND (item_amount - $quantity) <= 0";
      mysql_query($query) or die( '<br>Query: ' . $query . '<br>Error: ' . mysql_error() . '<br>');
  		
      if (mysql_affected_rows() == 0) {
         $query = "UPDATE ".$prefix."user_inventory SET item_amount = (item_amount - $quantity) WHERE item_owner = '$loggedinname' AND item_name = '$item_name'";
         mysql_query($query) or die( '<br>Query: ' . $query . '<br>Error: ' . mysql_error() . '<br>');
      }
  }
?>

[Hall of Famer]

Thank you so so much dude, it works perfectly. Sorry for being such a n00b, I really appreciate this.