PHP Help Needed - Replacing Row in MySQL

VPNsh

Hey guys,

Please tell me if I'm doing something completely stupid as I've been looking at this for far too long now and I'm stumped as to what I'm doing wrong.

$rank2 = $rank / $people;
mysql_select_db("test", $con);
$result = mysql_query("SELECT id,username,rank FROM members WHERE username = '$user'");
if (!$result) {
  echo 'Could not run query: ' . mysql_error();
  exit;
}
$rank3 = $row[2];
$user = $_SESSION['username'];
$row = mysql_fetch_row($result);
mysql_query("UPDATE members SET rank=$rank2 + $rank3
WHERE username='$user'");

The idea is to replace the current entry in the "rank" row for a certain user, with the total of what the current rank is ($row[2] aka $rank3) plus $rank2.

The code seems to work fine if I just try to replace the "rank" row with $rank2, but when I try putting the total of the two together, it doesn't play ball.

I've also tried declaring $rank3 as:
$rank3 = $rank2 + $row[2]

Then tried calling $rank3 into the "SET rank" section of my code.

Stumped. Help?

BlueVM

Try This:

$rank2 = $rank / $people;

mysql_select_db("test", $con);
$result = mysql_query("SELECT id,username,rank FROM members WHERE username = '$user'") or die(mysql_error());
$row = mysql_fetch_array($result);
$id = $row["id"];
$total = $row[2] + $rank2;
mysql_query("UPDATE members SET rank='$total' WHERE id='$id'");

Crab

Have you tried to echo '"UPDATE members SET rank=$rank2 + $rank3
WHERE username='$user'"' string to see whether the SQL is properly formulated?

jcaleb

total it in PHP before passing to SQL

VPNsh

@BlueVM just tried that, no luck .

Just to clarify, let's say the rank row contains the number 1000, and rank2 contains 2000, it should be editing the row to contain 3000.

Seems as though it won't take a variable which contains the row number

NickM

mysql_query("UPDATE members SET rank = (rank + $rank2) WHERE id = {$row[0]} LIMIT 1");

jcaleb

$rank3 = $row[2];
$user = $_SESSION['username'];
$row = mysql_fetch_row($result);

I think should be

$row = mysql_fetch_row($result);
$rank3 = $row[2];
$user = $_SESSION['username'];

VPNsh

@NickM said: mysql_query("UPDATE members SET rank = (rank + $rank2) WHERE id = {$row[0]} LIMIT 1");

Thanks, think I must have been fast asleep.

Sorted now, thread can be closed .

netomx

cof cof

@joepie91

NickM

@netomx said: cof cof

@joepie91

I think I can fill in for him here...

DON'T USE MYSQL_*, USE PDO BLAH BLAH BLAH INSECURE BLAH INJECTION BLAH BLAH DEPRECIATED

eastonch

@nickM

Trololol.

Damian

@NickM said: DON'T USE MYSQL_*, USE PDO BLAH BLAH BLAH INSECURE BLAH INJECTION BLAH BLAH DEPRECIATED

At least, use mysqli_*.

There's a big red block of text at the top of http://us1.php.net/mysql_query about being removed soon.

kamalnasser

Or use a sexy ORM called Eloquent (which utilizes PDO).

Wintereise

Laravel is nice, but there's no point in taking it up now if you're already halfway through it.

That said, what @Damian said, please at-least use mysqli_*, or write a DB layer in PDO itself.

Adduc

// Assuming $rank and $people are defined in an unincluded part of script.
$user = mysql_real_escape_string($_SESSION['username']);
$rank2 = $rank / $people;
mysql_select_db("test", $con);
$sql = "SELECT id, username, rank FROM members WHERE username = '{$user}'"
$result = mysql_query($sql);
if (!$result) {
  echo 'Could not run query: ' . mysql_error();
  exit;
}
$row = mysql_fetch_row($result);
$desired_rank = floatval($row[2] + $rank2);

mysql_query("UPDATE members SET rank={$desired_rank} WHERE username='{$user}'");

netomx

@NickM said: DON'T USE MYSQL_*, USE PDO BLAH BLAH BLAH INSECURE BLAH INJECTION BLAH BLAH DEPRECIATED

the funy thing is that... he's right!

Damian

@Adduc said: mysql_real_escape_string

Deprecated. I don't see a call to mysql_set_charset() either.

@Adduc said: WHERE username = '{$user}'"

Hopefully the user doesn't suffix their username with OR 1.

Adduc

@Damian said: @Adduc said: WHERE username = '{$user}'"

Hopefully the user doesn't suffix their username with OR 1.

OR 1 wouldn't matter within a quoted string.

@Damian said: @Adduc said: mysql_real_escape_string

Deprecated. I don't see a call to mysql_set_charset() either.

According to the PHP docs, the function itself isn't deprecated (the entire mysql extension is, but it's already been pointed out in this thread that MySQLi or PDO are the suggested alternatives; I stuck with mysql as he was already utilizing it). You do have a valid point regarding mysql_set_charset(), though.

winston

@Adduc said: MySQLi or PDO are the suggested alternatives

yeah I really prefer PDO, you kinda have to try to be vulnerable to SQL injection with it.

Crab

PDO and prepared statements is the way to go.