Need some help with my update

Ok, so I am trying to update the salary for all of my players here is what I have so far(after connection):

$today=date("Y-m-d");

$sql="SELECT * FROM upgrades";
$result=mysqli_query($cxn, $sql) or die ("couldn't execute query.");
while ($row=mysqli_fetch_assoc($result))
{
extract($row);
$sql="SELECT * FROM bank where user='$username'";
$result=mysqli_query($cxn, $sql) or die ("couldn't execute query.");
$row=mysqli_fetch_assoc($result);
extract($row);
echo"<p>$username $user $balance $upgradetype</p>";
If($upgradetype=="free")
{
$newbalance=$balance+1000;
$transaction=1000;
}
Elseif($upgradetype=="bronze")
{
$newbalance=$balance+2000;
$transaction=2000;
}
Elseif($upgradetype=="silver")
{
$newbalance=$balance+4000;
$transaction=4000;
}
Elseif($upgradetype=="gold")
{
$newbalance=$balance+6000;
$transaction=6000;
}
echo"$newbalance $transaction";

}
?>

So I have a few problems/questions:

1.It only does one player I need it to work for all of the players
2.This works, but as soon as I add a query to update the bank with the new balance I get the following:
Fatal error: Call to a member function fetch_assoc() on a non-object

3.I posted on here with questions about the above error and was told ot do this: change the while(.....) after the query selecting from upgrades to
while ($row = $result->fetch_assoc())

4.This fixed that error, but then gave me this error:
Fatal error: Call to a member function fetch_assoc() on a non-object

Here is a link to the original question:
http://forums.digitalpoint.com/showthread.php?t=1655973#post13437450

I am completely lost and I'm sure it is something I am just not seeing because I am new to this and I will have one of those moments where I think wow I'm an idiot when you guys help me as usual lol thanks in advance for help!

 

1. I think the reason it's only one player is because of this line:

$sql="SELECT * FROM bank where user='$username'";

even if it's not me, I believe there is only player with user equls $username

2-4. I believe you should change the variable name for each query.. for example in your code:

first query: $result, $row
then 2nd query is still the same variables?? try changing your variables for your 2nd query to something like $result2, $row2

something like that

 

Thanks so much. That makes so much sense I don't know why I didn't think about it. I changed the variables and it works now. Thanks again!

 

Nevermind it does not work, I have the following code now:

$today=date("Y-m-d");

$sql="SELECT * FROM upgrades";
$result=mysqli_query($cxn, $sql) or die ("couldn't execute query.");
while ($row = $result->fetch_assoc())
{
extract($row);
echo"<p>$username $upgradetype</p>";
$sql="SELECT * from bank where user='$username'";
$result2=mysqli_query($cxn, $sql) or die ("couldn't execute query.");
$row2=$result2->fetch_assoc();
extract($row2);
echo"<p>$balance</p>";
If($upgradetype=="free")
{
$newbalance=$balance+1000;
$transaction=1000;
}
Elseif($upgradetype=="bronze")
{
$newbalance=$balance+2000;
$transaction=2000;
}
Elseif($upgradetype=="silver")
{
$newbalance=$balance+4000;
$transaction=4000;
}
Elseif($upgradetype=="gold")
{
$newbalance=$balance+6000;
$transaction=6000;
}
echo"$newbalance $transaction";

This works fine, no problem here. It pulls up both members I am testing. Then when I add this:
$query="UPDATE bank set balance='$newbalance' where user='$username'";
$result=mysqli_query($cxn, $query) or die("couldn't execute query.");
$query="INSERT into transactions (username, othername, transactionamount, transactiontype,

transactiondate, transactionmemo) values ('$username', 'IE Staff', '$transaction', 'Deposit',

'$today', 'Salary')";
$result=mysqli_query($cxn, $query) or die ("Couldn't execute query.");

I get:
Fatal error: Call to a member function fetch_assoc() on a non-object in C:\AppServ\www\updatesalary.php on line 12

Line 12 is the while statement for the first query. It still inserts everything for the first member only.

 

are you sure all users have entries for the bank?

 

Yes, when I select everything from the bank for those two users they have balances. So I am sure they both have entries in the bank. It will withdraw the information from the bank and echo all of the information if the last two queries are not there.

 

Anyone? I still have no idea what is wrong with it and I have been googling it and still nothing.

 

So I figured it out, I just put everything into arrays and then did a foreach statement and it works great now!

So here is the new code:
$today=date("Y-m-d");
$sql="SELECT * FROM upgrades";
$result=mysqli_query($cxn, $sql) or die ("you died");
$num=mysqli_num_rows($result);
echo"$num=num";
for($i=1;$i<=$num;$i++)
{
$row=mysqli_fetch_assoc($result);
extract($row);
echo"<p>$username $upgradetype</p>";
$sql2="SELECT * from bank where user='$username'";
$result2=mysqli_query($cxn, $sql2) or die ("couldn't execute query.");
$row2=mysqli_fetch_assoc($result2);
extract($row2);
echo"<p>$balance</p>";
If($upgradetype=="free")
{
$newbalance=$balance+1000;
$transaction=1000;
}
Elseif($upgradetype=="bronze")
{
$newbalance=$balance+2000;
$transaction=2000;
}
Elseif($upgradetype=="silver")
{
$newbalance=$balance+4000;
$transaction=4000;
}
Elseif($upgradetype=="gold")
{
$newbalance=$balance+6000;
$transaction=6000;
}
echo"$newbalance $transaction";
$b[$username]=$newbalance;
$t[$username]=$transaction;
}
foreach($b as $use => $nb)
{
echo"<p>$use=use $nb=nb</p>";
$query="UPDATE bank set balance='$nb' where user='$use'";
$result=mysqli_query($cxn, $query) or die("couldn't execute query.");
}
foreach($t as $us => $tran)
{
echo"<p>$us=us $tran=tran</p>";
$query="INSERT into transactions (username, othername, transactionamount, transactiontype,

transactiondate, transactionmemo) values ('$us', 'IE Staff', '$tran', 'Deposit', '$today', 'Salary')";
$result=mysqli_query($cxn, $query) or die ("Couldn't execute query.");
}
?>

 

Hi

I am new in this programming game, I am designing my social network for my website and I have a problem with updating the user details, the code below gives an error :
"you have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 5",
can anyone help about this problem?

thanks
tldmic
<?php
session_start();
include "connect.php";
?>

<html>
<head>
<title>Update account</title>
</head>
<body>
<h1>Update Account Information</h1>
Please update account information as shown below for your profile<br><br>

<?php
if ($_POST['submit'] == "Update")
{
$query_update = "UPDATE userlocation SET
country = '" . $_POST['country'] . "',
province = '" . $_POST['province'] . "',
city = '" . $_POST['city'] . "'
WHERE email = '" .$_SESSION['email']. "' AND pass = (pass('" .$_SESSION['pass'] . "';";
$result_update = mysql_query($query_update) or die(mysql_error());



?>
<b>Your account information has been updated.</b><br>
<a href="user_personal.php">Click here</a> to return to your account.

<form action="update_account.php" method="post">
Email: <input type="text" name="email" value="<?php echo $row['email']; ?>"><br>
City: <input type="text" name="city" value="<?php echo $row['city']; ?>"><br>
State: <input type="text" name="state" value="<?php echo $row['state']; ?>"><br>

<p>
<input type="submit" name="submit" value="Update"> &nbsp;
<input type="button" value="Cancel" onclick="history.go(-1);">
</form>
<?php
}
else
{
//check the logged user for this information
$query = "SELECT * FROM login WHERE uid = '" .
$_SESSION['uid']. "' AND password = (password('" .
$_SESSION['password'] . "'));";
$result = mysql_query($query) or die(mysql_error());

$row = mysql_fetch_array($result);
?>
<form action="update_account.php" method="post">
country: <input type="text" name="email" value="<?php echo $row['country']; ?>"><br>
province: <input type="text" name="city" value="<?php echo $row['province']; ?>"><br>


<input type="submit" name="submit" value="Update"> &nbsp;
<input type="button" value="Cancel" onclick="history.go(-1);">
</form>
<?php
}
?>
</body>
</html>

 

The problem is the update query, it's crazy and far too overcomplicated, what is the pass part meant to do?

 

ok!, the update code need to remember the logged user,so the pass= password user and email is the login user detail, I am currently trying various methods,

thanks you very much for your reply,

 

do you have any idea what could be wrong,I can't figure that one out

 

Try changing:

$query_update = "UPDATE userlocation SET 
country = '" . $_POST['country'] . "', 
province = '" . $_POST['province'] . "',
city = '" . $_POST['city'] . "' 
WHERE email = '" .$_SESSION['email']. "' AND pass = (pass('" .$_SESSION['pass'] . "';";

PHP:

To

$query_update = "UPDATE userlocation SET country = ".$_POST['country'].", province=".$_POST['province'].", city=".$_POST['city']." WHERE email=".$_SESSION['email']." AND pass=".$_SESSION['pass'];

PHP:

Also the code you are using means your site is very vulnerable to sql injections.

 

hi,
I have tried, it seems to take no where,I a confused by this code behavior , this is what i get now
"
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' province=, city= WHERE email= AND pass=' at line 2"

thank you again for your help ,

 

hI,

I just tried to write a smalll code now, please have a look at it and tell me what am missing


<html>
<head>
<title>update information</title>
</head>
<body>
<h1>Update Your Account Information</h1>
<br><br>

<?php
session_start();
include "connect.php";

if ($_POST['submit'] == "Update") {

$result=mysql_query("update userlocation set country='countryname' where country='country'")
or die(mysql_error());

$result=mysql_query("select * from userlocation where country='countryname'")
or die(mysql_error());

$row= mysql_fetch_array($result);
echo $row['country'];
}
?>

<form action="update_account.php" method="post">
country: <input type="text" name="countryname" value="<?php echo $row['country']; ?>"><br>
<input type="submit" name="submit" value="Update"> &nbsp;
<input type="button" value="Cancel" onclick="history.go(-1);">
</form>

thanks

 

To patch the OP could use -> http://php.net/manual/en/function.mysql-real-escape-string.php

More: http://ditio.net/2008/06/29/clean-input-variable-php/