[SOLVED] Logic Problem? MYSQL not updating

Nexy · June 14, 2008

Why Hello There! Not sure if this is a PHP or Mysql problem, everything in the below code works fine, except the variables and the MySQL Update parts (near the bottom).

<?php

$eusearch = mysql_real_escape_string(stripslashes($_POST['eusearch']));
$euser = mysql_real_escape_string(stripslashes($_POST['euser']));
$eemail = mysql_real_escape_string(stripslashes($_POST['eemail']));
$erank = mysql_real_escape_string(stripslashes($_POST['erank']));
$eavatar = mysql_real_escape_string(stripslashes($_POST['eavatar']));
$ecurrency = mysql_real_escape_string(stripslashes($_POST['ecurrency']));

echo "<form action='#' method='post'>
<fieldset id='account'>
<legend>Edit User Information Here:</legend>

<p>Please type a username to edit.</p>
<label for='eusearch'>Search Users:</label>
<input type='text' id='eusearch' name='eusearch' tabindex='6' />

<input type='submit' id='esub' name='esub' value='Search' tabindex='7' />";

$esql = "SELECT * FROM users WHERE username = '$eusearch'";
$eres = mysql_query($esql) OR die(mysql_error());

if($_POST['esub'] && !empty($eusearch) && mysql_num_rows($eres) == 1) {
          if($user = mysql_fetch_array($eres)) {			
                echo "<p><div class='eusers'></p>";
        echo "<p>User Information: "; 
	echo "<span style='color: white; font-size: .8em'>" . $user['ip'] . "</span>";
	echo "</p>
		<label for='euser'>Username:</label>
		<input type='text' id='euser' name='euser' value='".$user['username']."' tabindex='8' />
		<label for='eemail'>Email:</label>
		<input type='text' id='eemail' name='eemail' value='".$user['email']."' tabindex='9' />

		<br /><br />
		<label for='erank'>Rank:</label>
		<select name='erank' id='erank' style='border: 1px solid #005585'>";

		if($user['rank'] == 2) { echo "<option selected value='member' style='color: #ADD8E6'>Member</option>"; }
		else if($user['rank'] == 3) { echo "<option selected value='moderator' style='color: #ADD8E6'>Moderator</option>"; }
		else if($user['rank'] == 4) { echo "<option value='global' style='color: #551033'>Global Moderator</option>"; }
		else if($user['rank'] == 5) { echo "<option value='admin' style='color: #CD5C5C'>Administrator</option>"; }

	       echo "<option value='member' style='color: #ADD8E6'>Member</option>
			<option value='moderator' style='color: green'>Moderator</option>
			<option value='gmod' style='color: #551033'>Global Moderator</option>
			<option value='admin' style='color: #CD5C5C'>Administrator</option>
		</select>

		<br /><br />
		<div class='create' style='width: 300px'>
		<br />";
	echo	$user['username'] . "'s Current Avatar:<p>";
	echo	"<img src='".$user['avatar']."' alt= ''";

		$ava = $user['avatar'];
		$size = getimagesize($ava);
		if($size[0] > "100" || $size[1] > "100") {
     		echo " class='bigav' /><br />";
		}
		else {
     		echo " /><br />";
		}

	echo "<label for='eavatar'>Avatar Link:</label><br />
		<input type='text' name='eavatar' id='eavatar' value='".$user['avatar']."' tabindex='10' style='width: 200px' />
		</p></div>";

	echo "<label for='ecurrency'>Currency: <span style='color: gold'>&#36;</span></label>
		<input type='text' id='ecurrency' name='ecurrency' value='".$user['currency']."' tabindex='11' style='color: gold; background: #005585' />";

	echo "<p><br /><input type='submit' id='esubmit' name='esubmit' value='Save Changes' tabindex='12' />
		<input type='reset' value='Undo Changes' tabindex='13' /> :-:
		<input type='submit' id='edelete' name='edelete' value='Delete User' tabindex='13' style='color: #CD5C5C; background: #005585; border: 1px solid #FFF' />";
	echo " :-:</p>";

	echo "</div></fieldset></form>";

	if($_POST['esubmit'] && !empty($euser) && !empty($eemail) && !empty($erank) && !empty($eavatar) && !empty($ecurrency))
               {
		mysql_query("UPDATE users SET username = '$euser' WHERE username = '".$user['username']."'");
		mysql_query("UPDATE users SET email = '$eemail' WHERE username = '".$user['username']."'");
                               
		if($erank == "member")		{ mysql_query("UPDATE users SET rank = '2' WHERE username = '".$user['username']."'"); }
		else if($erank == "moderator")	{ mysql_query("UPDATE users SET rank = '3' WHERE username = '".$user['username']."'"); }
		else if($erank == "gmod")	{ mysql_query("UPDATE users SET rank = '4' WHERE username = '".$user['username']."'"); }
		else if($erank == "admin")	{ mysql_query("UPDATE users SET rank = '5' WHERE username = '".$user['username']."'"); }
                       
		mysql_query("UPDATE users SET avatar = '$eavatar' WHERE username = '".$user['username']."'");
		mysql_query("UPDATE users SET currency = '$ecurrency' WHERE username = '".$user['username']."'");
	}

              }
}
else if($_POST['esub'] && mysql_num_rows($eres) == 0) { echo "<p style='color: white'>User not found or field left blank</p>"; }

?>

Like I said above, everything works fine except this part:

<?php

if($_POST['esubmit'] && !empty($euser) && !empty($eemail) && !empty($erank) && !empty($eavatar) && !empty($ecurrency))
{
mysql_query("UPDATE users SET username = '$euser' WHERE username = '".$user['username']."'");
mysql_query("UPDATE users SET email = '$eemail' WHERE username = '".$user['username']."'");
                               
if($erank == "member")		{ mysql_query("UPDATE users SET rank = '2' WHERE username = '".$user['username']."'"); }
else if($erank == "moderator")	{ mysql_query("UPDATE users SET rank = '3' WHERE username = '".$user['username']."'"); }
else if($erank == "gmod")	{ mysql_query("UPDATE users SET rank = '4' WHERE username = '".$user['username']."'"); }
else if($erank == "admin")	{ mysql_query("UPDATE users SET rank = '5' WHERE username = '".$user['username']."'"); }
                       
mysql_query("UPDATE users SET avatar = '$eavatar' WHERE username = '".$user['username']."'");
mysql_query("UPDATE users SET currency = '$ecurrency' WHERE username = '".$user['username']."'");
}

?>

I had "OR die(mysql_error());" but it gave me no error, so I took it out because it looked messy. So as you can see, the problem is MySQL won't update. I also echoed the variables, and it returned nothing, which puzzled me even more. Any help would be appreciated.

Thank You!

webbiedave · June 14, 2008

I also echoed the variables, and it returned nothing

I will assume you are referring to the $user array. If so, you'll need to ensure that:

"SELECT * FROM users WHERE username = '$eusearch'"

is returning useful rows. Also try echoing the UPDATE SQL to investigate. If the SQL is:

UPDATE users SET rank = '2' WHERE username = ''

then you can understand why no rows are being updated.

Nexy · June 14, 2008

Actually, by variables I meant these:

$euser = mysql_real_escape_string(stripslashes($_POST['euser']));
$eemail = mysql_real_escape_string(stripslashes($_POST['eemail']));
$erank = mysql_real_escape_string(stripslashes($_POST['erank']));
$eavatar = mysql_real_escape_string(stripslashes($_POST['eavatar']));
$ecurrency = mysql_real_escape_string(stripslashes($_POST['ecurrency']));

The $user array works fine. I wouldn't be able to see if I echoed the UPDATE sql, since the form disappears once you click "Save Changes" because of the if statement here:

if($_POST['esub'] && !empty($eusearch) && mysql_num_rows($eres) == 1)

Hope this helped a bit. Thank You!

Nexy · June 14, 2008

Nexy · June 14, 2008

Wouldn't let me edit above post, but I got the update working to some extent.

<?php

$eusearch = mysql_real_escape_string(stripslashes($_POST['eusearch']));
$euser = mysql_real_escape_string(stripslashes($_POST['euser']));
$eemail = mysql_real_escape_string(stripslashes($_POST['eemail']));
$erank = mysql_real_escape_string(stripslashes($_POST['erank']));
$eavatar = mysql_real_escape_string(stripslashes($_POST['eavatar']));
$ecurrency = mysql_real_escape_string(stripslashes($_POST['ecurrency']));

echo "<form action='#' method='post'>
<fieldset id='account'>
<legend>Edit User Information Here:</legend>

<p>Please type a username to edit.</p>
<label for='eusearch'>Search Users:</label>
<input type='text' id='eusearch' name='eusearch' tabindex='6' />

<input type='submit' id='esub' name='esub' value='Search' tabindex='7' />";

$esql = "SELECT * FROM users WHERE username = '$eusearch'";
$eres = mysql_query($esql) OR die(mysql_error());

if($_POST['esub'] && !empty($eusearch) && mysql_num_rows($eres) == 1) {
          if($user = mysql_fetch_array($eres)) {

	$_SESSION['usere'] = $user['username'];

                echo "<p><div class='eusers'></p>";
        echo "<p>User Information: "; 
	echo "<span style='color: white; font-size: .8em'>" . $user['ip'] . "</span>";
	echo "</p>
		<label for='euser'>Username:</label>
		<input type='text' id='euser' name='euser' value='".$user['username']."' tabindex='8' />
		<label for='eemail'>Email:</label>
		<input type='text' id='eemail' name='eemail' value='".$user['email']."' tabindex='9' />

		<br /><br />
		<label for='erank'>Rank:</label>
		<select name='erank' id='erank' style='border: 1px solid #005585'>";

		if($user['rank'] == 2) { echo "<option selected value='member' style='color: #ADD8E6'>Member</option>"; }
		else if($user['rank'] == 3) { echo "<option selected value='moderator' style='color: #ADD8E6'>Moderator</option>"; }
		else if($user['rank'] == 4) { echo "<option value='global' style='color: #551033'>Global Moderator</option>"; }
		else if($user['rank'] == 5) { echo "<option value='admin' style='color: #CD5C5C'>Administrator</option>"; }

	       echo "<option value='member' style='color: #ADD8E6'>Member</option>
			<option value='moderator' style='color: green'>Moderator</option>
			<option value='gmod' style='color: #551033'>Global Moderator</option>
			<option value='admin' style='color: #CD5C5C'>Administrator</option>
		</select>

		<br /><br />
		<div class='create' style='width: 300px'>
		<br />";
	echo	$user['username'] . "'s Current Avatar:<p>";
	echo	"<img src='".$user['avatar']."' alt= ''";

		$ava = $user['avatar'];
		$size = getimagesize($ava);
		if($size[0] > "100" || $size[1] > "100") {
     		echo " class='bigav' /><br />";
		}
		else {
     		echo " /><br />";
		}

	echo "<label for='eavatar'>Avatar Link:</label><br />
		<input type='text' name='eavatar' id='eavatar' value='".$user['avatar']."' tabindex='10' style='width: 200px' />
		</p></div>";

	echo "<label for='ecurrency'>Currency: <span style='color: gold'>&#38;#36;</span></label>
		<input type='text' id='ecurrency' name='ecurrency' value='".$user['currency']."' tabindex='11' style='color: gold; background: #005585' />";

	echo "<p><br /><input type='submit' id='esubmit' name='esubmit' value='Save Changes' tabindex='12' />
		<input type='reset' value='Undo Changes' tabindex='13' /> :-:
		<input type='submit' id='edelete' name='edelete' value='Delete User' tabindex='13' style='color: #CD5C5C; background: #005585; border: 1px solid #FFF' />";
	echo " :-:</p>";

	echo "</div></fieldset></form>";
              }
}
else if($_POST['esub'] && mysql_num_rows($eres) == 0) { echo "<p style='color: white'>User not found or field left blank</p>"; }

        if($_POST['esubmit'] && !empty($euser) && !empty($eemail) && !empty($erank) && !empty($eavatar) && !empty($ecurrency))
        {
         mysql_query("UPDATE users SET username = '$euser', 
					email = '$eemail',
					avatar = '$eavatar',
					currency = '$ecurrency' 
	                   WHERE username = '".$_SESSION['usere']."'");
                               
	if($erank == "member")		{ mysql_query("UPDATE users SET rank = '2' WHERE username = '".$_SESSION['usere']."'"); }
	else if($erank == "moderator")	{ mysql_query("UPDATE users SET rank = '3' WHERE username ='".$_SESSION['usere']."'"); }
	else if($erank == "gmod")	{ mysql_query("UPDATE users SET rank = '4' WHERE username = '".$_SESSION['usere']."'"); }
	else if($erank == "admin")	{ mysql_query("UPDATE users SET rank = '5' WHERE username = '".$_SESSION['usere']."'"); }
}

?>

I made a session of the username and update now works. Only problem is if I was to change username, email, avatar, currency and rank, rank would not be changed unless I do it alone without touching the other fields. Any reason to why that is?

Thank You!

darkfreaks · June 14, 2008

try removing the space between

 =variablehere

:

<?php

if($erank == "member")		{ mysql_query("UPDATE users SET rank ='2' WHERE username = '".$_SESSION['usere']."'"); }
	else if($erank == "moderator")	{ mysql_query("UPDATE users SET rank ='3' WHERE username ='".$_SESSION['usere']."'"); }
	else if($erank == "gmod")	{ mysql_query("UPDATE users SET rank ='4' WHERE username = '".$_SESSION['usere']."'"); }
?>

darkfreaks · June 14, 2008

Fix:


<?php

if($erank == "member")		
{ mysql_query("UPDATE users SET rank = '2' WHERE username = '".$_SESSION['usere']."'"); }
	else if($erank == "moderator")
{ mysql_query("UPDATE users SET rank = '3' WHERE username ='".$_SESSION['usere']."'"); }
	else if($erank == "gmod")
{ mysql_query("UPDATE users SET rank = '4' WHERE username = '".$_SESSION['usere']."'"); }
?>

darkfreaks · June 14, 2008

try removing the where clause

   <?php         
if($erank == "member")	
{ mysql_query("UPDATE users SET rank = '2'");}
else if($erank == "moderator")	{ mysql_query("UPDATE users SET rank = '3'"); }
else if($erank == "gmod")	{ mysql_query("UPDATE users SET rank = '4'"); }
else if($erank == "admin")	{ mysql_query("UPDATE users SET rank = '5'"); }
                  ?>

Nexy · June 14, 2008

Removing the space didn't work. If I removed the WHERE clause, how would it know which row to update?

Thank You!

darkfreaks · June 14, 2008

there is a row called rank under users right?

Nexy · June 14, 2008

Rank is a column. Rows would be all the username entries, right?

| id | username | rank |

-----------------------

| 1 | Nexy | 5 |

| 2 | dark | 4 |

| 3 | freaks | 2 |

-----------------------

darkfreaks · June 14, 2008

users is the table row is the field like rank or username column is like thge type default attributes etc

darkfreaks · June 14, 2008

UPDATE users SET rank=2

will update the rank

Nexy · June 14, 2008

You misunderstood me. I removed the WHERE clause, and all the ranks turned into "2". I just want the specific username I'm editing to turn to 2.

darkfreaks · June 14, 2008

well did you do that to all of them? isit checking to see if the rank is equal to 2? or 4? or whatever

Try:

   <?php         
if($erank == "member")	
{ mysql_query("UPDATE users SET rank = '2'");}
else if($erank == "moderator")	{ mysql_query("UPDATE users SET rank = '3'"); }
else if($erank == "gmod")	{ mysql_query("UPDATE users SET rank = '4'"); }
else if($erank == "admin")	{ mysql_query("UPDATE users SET rank = '5'"); }
                  ?>

Nexy · June 15, 2008

The thing I'm trying to do in the above code is I have a dropdown menu, that says "Member, mod, etc...". Whatever you choose, will update the username you are editing. Member = 2, Mod= 3, Global Mod = 4, Admin = 5. If I don't put a WHERE clause, then it will update all the users in the table to whatever I chose.

Now, another problem arose where some members don't even get updated, and I didn't change any code except what you told me.

Hope this makes things a little more clear.

darkfreaks · June 15, 2008

its abit easier instead of rank using numbers you have member_type and if member_type=="mod" or "member" or "admin" to update accordingly. i hate the numbers game

Nexy · June 15, 2008

I don't follow you.

darkfreaks · June 15, 2008

if($erank == "member")	{ mysql_query("UPDATE users SET  member_type  = 'member'");

Nexy · June 15, 2008

That would still update ALL usernames in the table. I just want one. I think we are getting away from the actual question here o.O

darkfreaks · June 15, 2008

but you can do it both ways.

however what you need is:

<?php 
$sql=mysql_query("SELECT rank FROM users");
while($row=mysql_fetch_array($sql)){

if($row['rank']=="2") { echo 'you are a member';}

?>

Nexy · June 15, 2008

That way still didn't work, but I put the if statements b4 the first query, like so:

<?php

if($_POST['esubmit'] && !empty($euser) && !empty($eemail) && !empty($erank) && !empty($eavatar) && !empty($ecurrency))
{

if($erank == "member")	{ mysql_query("UPDATE users SET rank ='2' WHERE username = '".$_SESSION['usere']."'"); }
else if($erank == "moderator"){ mysql_query("UPDATE users SET rank ='3' WHERE username ='".$_SESSION['usere']."'"); }
else if($erank == "gmod")	{ mysql_query("UPDATE users SET rank ='4' WHERE username = '".$_SESSION['usere']."'"); }
else if($erank == "admin")	{ mysql_query("UPDATE users SET rank ='5' WHERE username = '".$_SESSION['usere']."'"); }

mysql_query("UPDATE users SET username = '$euser', 
		     email = '$eemail',
		     avatar = '$eavatar',
		     currency = '$ecurrency' 
	WHERE username = '".$_SESSION['usere']."'");

echo "<div class='create' style='width: 400px; text-align: center; padding: .6em'>
<img src='images/update.png' alt='' /> " . $_SESSION['usere'] . ' has been updated.</div>';

unset($_SESSION['usere']);
}

?>

That seemed to work, now I don't have to update rank alone. But some members don't get updated, not just with rank, but the rest of the fields also. Any ideas about that? Sorry if I'm being a nuisance.

Thank You!

darkfreaks · June 15, 2008

you have not defined the WHERE clause in the other variables will session variable declare all the fields or just username ???

darkfreaks · June 15, 2008

should be like

UPDATE users SET value1 = $value1, value2 = $value2 WHERE username = $username

Nexy · June 15, 2008

you have not defined the WHERE clause in the other variables will session variable declare all the fields or just username ???

I did define with the $_SESSION['usere']

mysql_query("UPDATE users SET username = '$euser', 
		 email = '$eemail',
		avatar = '$eavatar',
		currency = '$ecurrency' 
	WHERE username = '".$_SESSION['usere']."'");

Session variable is only for the username as set on this line:

$_SESSION['usere'] = $user['username'];

Sign In

[SOLVED] Logic Problem? MYSQL not updating

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information