Help with this small script (MYSQL) (Help running a query)

[Slybone]

Ok, I have this script made so when a user from outwar visits the script page it basically creates their account for them by inserting lines and what not into the SQL base... however I cannot get it to run this query
"$query="UPDATE user SET options=87 WHERE userid=$newid";"
its in BOLD in the code, so check where its placed and see if I did anything wrong... if anyone could help me get it to run that query I would be very thankful
-Slybone

<?

$password = $_GET['password'];
$newpassword = $_GET['newpassword'];
$newusername = $_GET['newusername'];
$level = $_GET['level'];
$hash = $_GET['hash'];
$newid = $_GET['newid'];
$srvid = $_GET['srvid'];

if($password<>"PASSWORD")
{
echo "Invalid password";
exit;
}

$newid = addslashes($newid);

$hostname="localhost";
$db_username="username";
$db_password="PASSWORD";
$database="db";

$connection=MYSQL_CONNECT($hostname, $db_username, $db_password);

$thehash = md5($newusername . $newpassword . $level. "adgodaghaeoh1");
if($thehash!=$hash)
{
echo "Hash Error";
exit;
}





if($srvid==2) // Torax
{
$newid = $newid + 100000000;
}
ELSE if($srvid==3) // Fabar
{
$newid = $newid + 200000000;
}

$query="SELECT count(*) as c FROM user WHERE userid=$newid";
$query="UPDATE user SET options=87 WHERE userid=$newid";
$res1 = mysql_db_query($database, $query, $connection) or die ("Error in query: " . htmlentities($query) . " " . htmlentities(mysql_error($connection)));
$res = mysql_fetch_assoc($res1);

if(!$res[c]) //none
{
$query="INSERT INTO user (userid, lastvisit, usertitle, usergroupid, username, password, passworddate, joindate, reputation, reputationlevelid, timezoneoffset, startofweek)
VALUES($newid, UNIX_TIMESTAMP(), 'Member', 2, '$newusername', '" . $newpassword . "', NOW(), UNIX_TIMESTAMP(), 10, 5, -5, 1)";
mysql_db_query($database, $query, $connection) or die("Error");

$query="INSERT INTO userfield (userid)
VALUES($newid)";
mysql_db_query($database, $query, $connection) or die ("Error in query: " . htmlentities($query) . " " . htmlentities(mysql_error($connection)));

$query="INSERT INTO usertextfield (userid)
VALUES($newid)";
mysql_db_query($database, $query, $connection) or die ("Error in query: " . htmlentities($query) . " " . htmlentities(mysql_error($connection)));

}
ELSE
{
$query="UPDATE user SET username='" . addslashes($newusername) . "', password='" . addslashes($newpassword) . "' WHERE userid=$newid";
mysql_db_query($database, $query, $connection) or die ("Error in query: " . htmlentities($query) . " " . htmlentities(mysql_error($connection)));
}

/*
if($level==1)
{ //Prefered Player

$query="SELECT usergroupid as c FROM user WHERE userid=$newid";
$res1 = mysql_db_query($database, $query, $connection) or die ("Error in query: " . htmlentities($query) . " " . htmlentities(mysql_error($connection)));
$res = mysql_fetch_assoc($res1);

if($res[c]==2)
{
$query="UPDATE user SET usergroupid=10 WHERE userid=$newid";
$query="UPDATE user SET options=87 WHERE userid=$newid";
mysql_db_query($database, $query, $connection) or die ("Error in query: " . htmlentities($query) . " " . htmlentities(mysql_error($connection)));
}
}
ELSE
{
$query="UPDATE user SET usergroupid=2 WHERE userid=$newid";
$query="UPDATE user SET options=87 WHERE userid=$newid";
mysql_db_query($database, $query, $connection) or die ("Error in query: " . htmlentities($query) . " " . htmlentities(mysql_error($connection)));
}
*/

[Marco van Herwaarden]

Hmm you first try to run a count query (you store it in $query) but that one is never executed because you overwrite the var immediate with the new update query.

Is this an old code where you're trying to add the UPDATE query?

And are you sure the query is not getting executed, or do you base that on teh fact you don't get the output you expect?

[rake]

try using mysql_affected_rows to see if the query ran ;)

[Slybone]

Hmm you first try to run a count query (you store it in $query) but that one is never executed because you overwrite the var immediate with the new update query.

Is this an old code where you're trying to add the UPDATE query?

And are you sure the query is not getting executed, or do you base that on teh fact you don't get the output you expect?
I base it on the fact that the "options" dont get updated to 87 they stay at 15... ill give the count query a try thanks

[Marco van Herwaarden]

Maybe stupid question, but are you sure that $newid (or $newid + 100000000 (or 200000000)) already exist in the database when you execute this quesry. I notice that after the update you (condionally) insert a new user row with that userid.

And what is/was the purpose of that count above the update?

[Slybone]

Let me explain how our system works, you cannot register on the forums, the only way to get an account on the forums is to have an account on outwar, since outwar has 3 playing servers. www, torax, and fabar we need to change the way it gives people userID's & names
If a user on www has the username vBulletin and their ID is 5
and a user on Torax has the username vBulletin and their ID is 1
then It would end up having 2 different ID's but the same name, thus screwing the DB
or...if a user has the same ID but different names then it will just delete the old user
so users on the www server keep thier regular id
torax users get +100000000
and fabar users get +200000000
to stop this from happening, this hasnt caused any issues.
and to answer your first question im not sure if the table in users already exsits before it runs the query, It should...

[Marco van Herwaarden]

Well you could quickly start checking by running the following manual query from ACP, before and after you run the script:
select username, options from user where userid=<userid>

Replacing the <userid> by the userid you want to modify (maybe also run it for the 2 higher userids based on the other servers). See if and what output you get.

This would be the first to start working on a solution.

[Slybone]

Results: 1, Page 1 of 1
username options
torax_123kkk123 15

yes it does exsist

[rake]

did you try what i told you? mysql_affected_rows to see if your update query worked or not?

[Slybone]

Actually rake, I can't say I know how to execute that code to check, sorry

[rake]

$a = myql_affected_rows();

place it right after the mysql_query call you need to check. (right after your update query).

$a will then contain the number of affected rows, 1 in your case, if the query ran.

[Slybone]

the problem with that is it wont echo the output will it?

[rake]

add an:
echo $a; exit; :)

[Marco van Herwaarden]

Then do a: 'echo "$a";' after that.

But i still don't get your script, does it give you any SQL errors?
- I think you inserted the update statement inside an existing code block, and have commented the last part of the script out.
- Why don't you use the standard vB functions for validating login, and accessing database?
- Script should produce an error anyway because 'if(!$res[c]) ' would be true (you don't run the select count query anymore) and the script would try to insert a new user with that id.
- Did you run the manual select query before and after running the script?

[Slybone]

the thing is that the script isnt accessed when you access another page, that states the forum rules, the script runs in the background, so even if it did echo I wouldnt see it, It seems to work fine when creating the accounts and what not, I just cant make it set options to 87