sql not updating with update query

[arne]

:confused: What have i done wrong?
Sql won't update

Codes:
(connecting to database is done in config.php wich is included (no database connect problems, everything works on other pages).

Code (part 1)
if ($_GET['a'] == 'update')
mysql_query("UPDATE users SET status='".$_POST['userstatus']."',username='".$_POST['username']."',password='".$_POST['password']."',credit='".$_POST['credits']."',email='".$_POST['email']."',fullname='".$_POST['fullname']."',messenger='".$_POST['messenger']."',refferedby='".$_POST['refferedby']."' WHERE id='".$_POST['idkey']."'");
echo $_POST['idkey'];
(i added echo $_POST['idkey'] to see if that was filled in, and it was, i got the right id !

Here the form code:
$usercheck = mysql_query('SELECT * FROM users WHERE username="'.$_POST['username'].'"');
$num_rows = mysql_num_rows($usercheck);
if($num_rows == 0){
echo '<font color=red><b>User not found ! <br> You searched for '.$_POST['username'].'</b></font><br>';
echo"<br><br><br>
<a href='user.php'>Try Again</a>";
}else{

$query = "SELECT * FROM users WHERE username='".$_POST['username']."'";
$uitvoer = mysql_query($query) or die (mysql_error());
while($lijst = mysql_fetch_object($uitvoer))
{
echo "<form action='".$_SERVER['PHP_SELF']."?a=update' method='POST'>
<font size='4'><b>Editing user info</b></font><font> (user '".$_POST['username']."')</font><br><br>
<table width='100%' border='0' cellpadding='0' cellspacing='0'>
<tr><td width='50%' height='24'><u>User</u></td>
<td width='50%' height='24'></td></tr></table>
<br><table width='100%' border='0' cellpadding='0' cellspacing='0'>

<tr><td width='50%' height='24'>Unique ID</td>
<td width='50%' height='24'>$lijst->id</td><input type='hidden' name='idkey' value='$lijst->id'></td></tr></tr>
<tr>
<td width='50%' height='24'>User status</td>
<td width='50%' height='24'><select size='1' name='userstatus'>";
$usn=$_POST['username'];
if($lijst->status == 0){
$status=banned;
}elseif($lijst->status == 1){
$status=admin;
}elseif($lijst->status == 2){
$status=supporter;
}elseif($lijst->status == 3){
$status="normal user";
}elseif($lijst->status == 999){
$status=banned;
}
echo"
<option value='$lijst->status'>$status</option>
<option></option>
<option value='0'>Banned</option>
<option value='1'>Admin</option>
<option value='2'>Supporter</option>
<option value='3'>Normal User</option>

</select></td>
</tr>

<tr><td width='50%' height='24'>Username</td>
<td width='50%' height='24'><input type='text' name='username' value='$usn'></td></tr>
<tr><td width='50%' height='24'>Password</td>
<td width='50%' height='24'><input type='text' name='password' value='$lijst->password'></td></tr>
<tr><td width='50%' height='24'>Credits</td>
<td width='50%' height='24'><input type='text' name='credits' value='$lijst->credit'></td></tr>
<tr><td width='50%' height='24'>Email</td>
<td width='50%' height='24'><input type='text' name='email' value='$lijst->email'></td></tr>
<tr><td width='50%' height='24'>Full Name</td>
<td width='50%' height='24'><input type='text' name='fullname' value='$lijst->fullname'></td></tr>
<tr><td width='50%' height='24'>Messenger</td>
<td width='50%' height='24'><input type='text' name='messenger' value='$lijst->messenger'></td></tr>
<tr><td width='50%' height='24'>IP</td>
<td width='50%' height='24'><a href='iprun.php?ip=$lijst->ip' target='_blank'>$lijst->ip</a></td></tr>
<tr><td width='50%' height='24'>Host</td>
<td width='50%' height='24'>$lijst->host</td></tr>
<tr><td width='50%' height='24'>Reffered By</td>
<td width='50%' height='24'><input type='text' name='refferedby' value='$lijst->refferedby'></td></tr>
<tr><td width='50%' height='24'>Email</td>
<td width='50%' height='24'><a href='http://www.mafiaruler.com/admin/email.php?action=s&user=$lijst->username' target='_blank'>Click here to email user</a></td></tr>
<tr><td width='50%' height='24'>Confirm</td>
<td width='50%' height='24'><input type='submit' value='Save changes'></td>
</tr></table></form>";

[arnyinc]

Possible errors and checks:

1. If-statement returns false. Add something like echo $_GET['a']; inside your if-statement.
2. Not connected to database when you run query. Make sure you are connected before you execute this specific query.
3. Error in SQL statement. Set a variable to your sql statement and try executing it in mysql directly.

$sql="UPDATE users SET status='".$_POST['userstatus']."',username='".$_POST['username']."',password='".$_POST['password']."',credit='".$_POST['credits']."',email='".$_POST['email']."',fullname='".$_POST['fullname']."',messenger='".$_POST['messenger']."',refferedby='".$_POST['refferedby']."' WHERE id='".$_POST['idkey']."'";
echo $sql;

Copy and paste the result into your mysql interface. Maybe you have an apostrophe getting in the way.
4. Updating wrong idkey. Make sure the idkey corresponds to the record you actually want to update.



Check the value of $_GET['a'] to make sure your if-statement is returning true, so your sql should execute.

[arne]

found the problem :
......,messenger='MSN:',refferedby='' WHERE username='scripter'
the reffered by isn't correctly (it's not filled in, it's a blank field), how to avoid this/solve this problem?


(edit : it's with every field : if i leave a field blank, i get DATABASEROW="
and no second" :s but my code has two ", why is it doing that?

[arne]

nobody?

[cubsfan]

found the problem :
......,messenger='MSN:',refferedby='' WHERE username='scripter'
the reffered by isn't correctly (it's not filled in, it's a blank field), how to avoid this/solve this problem?


(edit : it's with every field : if i leave a field blank, i get DATABASEROW="
and no second" :s but my code has two ", why is it doing that?

You need to build your SQL based on the values of your POST variables


$sql = "..., messenger='MSN'";
if ($_POST['refferedby']) {
$sql .= ", refferedby='" . $_POST['refferedby'] . "'";
}

[arne]

isn't there an easier solution?

[cubsfan]

Probably. Have to wait and see if anyone else jumps in with a different idea.

[arnyinc]

Setting a field to a blank value wouldn't stop the query from executing.

It's valid to update with:
Update mytable set field1='something', field2='' where id=100

That would set the value of field1 to "something" and field2 to a blank string.

edit: When you echo your sql string, you are getting two apostrophes '' not one quotation mark "

[arne]

oh, so then why isn't it working?

[kehers]

Things could be easier if you can know the exact error returned by mysql. Atleast the wizs in the room will b able to help.
You can nab the error with a snnipet like this:
if(!$result)
echo mysql_error();
Using this:
echo mysql_affected_rows();
immediately after running the query will also help you know the number of rows updated.
Running the query in a GUI program like PhpMyAdmin may also be of help.
So, tell us the real error, and i bet someone will b around to help

[kehers]

Hey!!!
Just notice that you didnt handle your mysql_query with a variable
i.e

mysql_query("UPDATE users SET status='".$_POST['userstatus']."',username='".$_POST['username']."',password='".$_POST['password']."',credit='".$_POST['credits']."',email='".$_POST['email']."',fullname='".$_POST['fullname']."',messenger='".$_POST['messenger']."',refferedby='".$_POST['refferedby']."' WHERE id='".$_POST['idkey']."'");


why not give this a shot:
if ($_GET['a'] == 'update')
$result = mysql_query("UPDATE users SET status='".$_POST['userstatus']."',username='".$_POST['username']."',password='".$_POST['password']."',credit='".$_POST['credits']."',email='".$_POST['email']."',fullname='".$_POST['fullname']."',messenger='".$_POST['messenger']."',refferedby='".$_POST['refferedby']."' WHERE id='".$_POST['idkey']."'");
echo $_POST['idkey'];

[Kid Charming]

Just notice that you didnt handle your mysql_query with a variable


That doesn't matter -- queries run regardless of what's done with mysql_query()'s return value. (Checking for affected rows wouldn't hurt, though).

arne, do two things for us:

1. Follow keher's first suggestion and add a mysql_error() check to your query. If it returns anything, post it.

2. Echo out and post your entire query.

[kehers]

handling the query with $result will be useful for this

if(!$result)
echo mysql_error();

[arne]

suddenly it's working, haven't changed anything !
Thanx anyway !