UPDATE Query

[echoindia756]

Hi there,

I'm building an online game website and i'm having a bit of trouble getting something to work. You see when the user buys credits, it inputs that into their row in the database. They can then go to the place where they can buy things such as metal and crystal further their strength against other players etc.

When they buy something for say 50 credits, it doesn't add the product e.g metal and it doesn't minus the amount of credits it costs. Here is the code:

<form method="post" action="creditshop.php" name="f">
<tr>
<td width=20 class=mainTxt><input type="radio" name="gebruik" value="crystal"></td>
<td class=mainTxt width="208">+20.000 Crystal</td><td class=mainTxt width="199">60 Credits</td>
</tr>

<?php

$dpath = (!$user["dpath"]) ? DEFAULT_SKINPATH : $user["dpath"];


$select = mysql_query("SELECT * FROM `game_planets`");

$page = mysql_fetch_object($select);

$login=$user['username'];



  <?PHP
$gebruik = htmlspecialchars(addslashes($_POST['gebruik'])); 

$gebruik = mysql_real_escape_string($gebruik) or die 
            ('Error real escape string');

if (isset($_POST['gebruik'])) {

       $aantal = htmlspecialchars(addslashes($_POST['bieden']));
       $aantal = substr($aantal,0,2);
       if(!is_numeric($aantal)){
       echo 'Ongeldige invoer';
       exit;
       }
  if($gebruik == "crystal") {     
     $gebruik;  //=htmlspecialchars(addslashes($_POST['gebruik']));
       $kost = 60; //per aantal
       $kosttotaal = $kost*$aantal; // totale kost
       $wat = 20000; // hoeveel krijg je per keer
       $krijgen = $wat*$aantal;  // totaal aantal dat men krijgt
}




if($gebruik == "crystal") {
mysql_query("UPDATE `game_planets` SET `belcredits`=`belcredits`-'$kosttotaal' WHERE `name`='$login'");
mysql_query("UPDATE `game_planets` SET `crystal`=`crystal`+'$krijgen' WHERE `name`='$login'");


print "  <tr><td class=\"mainTxt\" align=\"center\">You have bought <b>".$wat." crystal</b> <b>$aantal</b> times <br></td></tr>\n";
}




include("config.php");

//mysql_query("INSERT INTO `[bestellingen]` ( `login` , `datum`, `ip` , `gameid`, `belcredits`, `wat`, `aantal`) VALUES ('$data->login', NOW(), '{$_SERVER['REMOTE_ADDR']}', '{$gameid}', '{$kosttotaal}', '{$gebruik}', '{$aantal}')") or die(mysql_error());

}
exit;



?> 

It's giving me an error with the real_escape_string and also an "Undefined index: gebruik"

I can't spot what's wrong, can somebody help me out? Thanks!

[leatherback]

Hey,

What you are doing here is double-overkill and will mess up your database:

$gebruik = htmlspecialchars(addslashes($_POST['gebruik']));
$gebruik = mysql_real_escape_string($gebruik) or die
            ('Error real escape string'); 

$gebruik - mysql_real_escape_string($_POST['gebruik']);

should do you. Make sure that the server does not already addslashes, in which case you will need to remove them first

function encode($text)  

  {
  if (get_magic_quotes_gpc()) 
     {
     $text = stripslashes($text);
     }
  return mysql_real_escape_string($text);
  }

[echoindia756]

It's giving me the "Error real escape string" and "Notice: Undefined index: gebruik" This is what I have now:

function encode($text)  

  {
  if (get_magic_quotes_gpc())
     {
     $text = stripslashes($text);
     }
  return mysql_real_escape_string($text);
  } 



$gebruik = mysql_real_escape_string($_POST['gebruik']) or die 
            ('Error real escape string');

if (isset($gebruik)) {

       $aantal = htmlspecialchars(addslashes($_POST['bieden']));
       $aantal = substr($aantal,0,2);
       if(!is_numeric($aantal)){
       echo 'Ongeldige invoer';
       exit;
       }
  if($gebruik == "crystal") {     
     $gebruik;  //=htmlspecialchars(addslashes($_POST['gebruik']));
       $kost = 60; //per aantal
       $kosttotaal = $kost*$aantal; // totale kost
       $wat = 20000; // hoeveel krijg je per keer
       $krijgen = $wat*$aantal;  // totaal aantal dat men krijgt
}

Any ideas on what could be causing the error?

[leatherback]

Yeah.. You are trying to run a variable through mysql_real_escape_string;

function encode($text)  

  {
  if (get_magic_quotes_gpc())
     {
     $text = stripslashes($text);
     }
  return mysql_real_escape_string($text);
  }


// In full / in ong writing;
if (isset($_POST['gebruik'])) 
  {
  $gebruik = encode($_POST['gebruik'];
  }
else
  {
  $gebruik = '';
  }

//or in short:
$gebruik = (isset($_POST['gebruik']) ? encode($_POST['gebruik']) : '' ;

// Do the same for the other vars
           $aantal = htmlspecialchars(addslashes($_POST['bieden']));
           $aantal = substr($aantal,0,2);
           if(!is_numeric($aantal)){
           echo 'Ongeldige invoer';
           exit;
           }
      if($gebruik == "crystal") {     

         $gebruik;  //=htmlspecialchars(addslashes($_POST['gebruik']));
           $kost = 60; //per aantal
           $kosttotaal = $kost*$aantal; // totale kost
           $wat = 20000; // hoeveel krijg je per keer
           $krijgen = $wat*$aantal;  // totaal aantal dat men krijgt
} 

[echoindia756]

Thanks, it's giving me an error on this line:

$gebruik = encode($_POST['gebruik'];

Its saying Parse error: syntax error, unexpected ';'

Do I have to add something to this code?

[echoindia756]

Anyone any ideas on how to get this thing going? I'm trying a few things here but nothing is working - trial and error kind of thing. Any advice much appreciated.

[leatherback]

encode() is the function, and I forgot the closing bracket,

$gebruik = encode($_POST['gebruik']);

[echoindia756]

Ok, it's still not working. Here's the code now:

<?php

function encode($text)  

  {
  if (get_magic_quotes_gpc())
     {
     $text = stripslashes($text);
     }
  return mysql_real_escape_string($text);
  }


// In full / in ong writing;
if (isset($_POST['gebruik']))
  {
  $gebruik = encode($_POST['gebruik']);
  }
else
  {
  $gebruik = '';
  }

//or in short:
//$gebruik = (isset($_POST['gebruik']) ? encode($_POST['gebruik']) : '' ;

// Do the same for the other vars


if (isset($_POST['bieden']))
{
$bieden = encode($_POST['bieden']);
}
else
$bieden = '';

       $aantal = htmlspecialchars(addslashes($_POST['bieden']));
       $aantal = substr($aantal,0,2);
       if(!is_numeric($aantal)){
       echo 'Invalid';
       exit;
       }
  if($gebruik == "crystal") {     
     $gebruik;  //=htmlspecialchars(addslashes($_POST['gebruik']));
       $kost = 60; //per aantal
       $kosttotaal = $kost*$aantal; // totale kost
       $wat = 20000; // hoeveel krijg je per keer
       $krijgen = $wat*$aantal;  // totaal aantal dat men krijgt
} 

It's giving me the "Invalid" Error from the $aantal area.

[leatherback]

You are
1) Not running the $aantal through the encode function but through a depreciated addslashes
2) taking a substring of the variable: Is it a combined string that you are expecting?

The error comes from $aantal not being a number, but a string.

Run print_r($_POST); at the top of the page, look at the page SOURCE and you will see the content of the posted data array nicely layed out. Check whether $aantal is what you expect it to be. If it is already a number, you can just remove the substr command

[echoindia756]

Ok, this is what I got:

Array ( [gebruik] => crystal [bieden] => 1 [submit] => Buy )

I guess that's not right, we're looking for a number so what should I change in the code?
The "Invalid" error dissapears once I hit the buy button, so I guess we're ok there. And even the "You have bought 20000 crystal 1 time" message appears, but the users credits remain the same and no crystal is added. Its puzzling me!

[leatherback]

EchoIndia,

Unfortunately I am of for a few weeks by tomorrow. Otherwise I'd have said: Send me the script. Basicaly what you need to have in your processing: (Note: type these replies straight into the response box without options for bracket pairing: There probabaly are missing things. THis is a structure, not a full working example

if(isset($_POST['submit']) && $_POST['submit'] == 'Buy')
  {
  // We are in buying mode: Process each variable:
  // If you have a series of different actions for this script, you might want to leave out the submi='buy' part and make that a condition somewhere else

// First check that $_POST['bieden'] is a number, else set to 0
$aantal = (is_numeric($_POST['bieden'])) ? $_POST['bieden'] : 0;

  // if it is > 0, continue processing
  if($aantal > 0)
    {
    $gebruik = encode($_POST['gebruik'];

// Looks like 'gebruik' is a category thing or something, so you might want to use a switch.
// As I do not know what the rest of the script is lik, I'll keep your struct

$kost = 60; //per aantal
$kosttotaal = $kost*$aantal; // totale kost
$wat = 20000; // hoeveel krijg je per keer
$krijgen = $wat*$aantal;  // totaal aantal dat men krijgt

// define your query

// run the query
$res - mysql_query($query);

   if($res)
     {
      // OK, succes: Display success message
     }
    }
  else
    {
    // not a number: Either exit or display an error message
    }
  }

[echoindia756]

Thanks for the above! That seems to be working, except the WHERE clause is stopping it from deducting credits and adding crystal.
Here is my query:

$res1 =("UPDATE `game_planets` SET `belcredits`=`belcredits`-'$kosttotaal' WHERE `name` = '$login' limit 1");
$res2 =("UPDATE `game_planets` SET `crystal`=`crystal`+'$krijgen' WHERE `name` = '$login' limit 1");


print "  <tr><td class=\"mainTxt\" align=\"center\">You have bought <b>".$wat." crystal</b> <b>$aantal</b> times <br></td></tr>\n";
}


// run the query
$res1 = mysql_query($res1);
$res2 = mysql_query($res2);

You see the

WHERE `name` = '$login' limit 1

?
The $login gets the current user that is logged in, but there is no field for username in game_planets, so is there anyway I can say select username from game_users instead of selecting name from game_planets?

You see the name in the where clause gets the planet name, not the username, so the query can't match anything up. Thanks so far!

[leatherback]

Hi,

You need to look into your tables & database optimization.

Imagine what happens when a user decides to change the username. All of the sudden you need to modify a lot of fields in the table. THis is not desireable. THerefor, it is good database practice to NOT link tables using 'information fields'. Instead, you create a field with an autoincrement integer in each table, which you use to link to other tables. (I prefer to use tablenameID for primarykeys, and tablenameNR for the referring foreign keys):

users
[user_ID][userName][userStatus]
  1  'Piet klaassen' '1'

Krediet
[krediettype_Nr][user_Nr][aantal][vervaldatum]
  1 1 200 2006-06-01

Krediettypes (In this case, I would shorten it: Ktypes, with id: Ktypes_id)
[krediettype_Id][kredietLabel]
  1  crystals

$sql = "select userName, aantal, kredietlabel
            from users 
                   left join Krediet        

                           on user_nr = user_id
                   left join Krediettypes 
                           on krediettype_nr = krediettype_Id
            where user_ID = ".$loginID."  ";

So in your $loginID you could place $row['userID'] and use this in your other queries when you need to refer to the current user. I realize this is not the answer you are looking for, but I think this will help you towards solving the question you posted, and it should help you towards a more properly normalized database..

[echoindia756]

Ok, I see how that would work but i'd have to change a lot of other code in other pages so that everything is working on the same line.

What if I got the current planet rather than the current user? Each user has a planet and each planet is assigned an id to link it to the username.
So for example in the game_planets table I have:

|name| |id_owner|

Alpha Prime 1

And in the game_users table I have:

|username| |id|
admin 1

EDIT:

I got it working :-) I made a variable called

$currentplanet=$user['current_planet'];

And I changed the query to this:

$res1 =("UPDATE `game_planets` SET `belcredits`=`belcredits`-'$kosttotaal' WHERE `id` = '$currentplanet' limit 1");
$res2 =("UPDATE `game_planets` SET `crystal`=`crystal`+'$krijgen' WHERE `id` = '$currentplanet' limit 1");

Thanks very much for your help.