Update SQL Query Help

Charlie9809 · November 28, 2008

Hi, Im trying to make a PHP script that can update certain account details in a MYSQL database. What I want to be able to do is have the below options. The script needs to check if the Current Username matches the Current Password (in the DB) and if not say something like "ERROR: Current Username and Password do not match!" and also update all the other fields. Note that I have been trying to make this script work for hours, but I just don't have the knowledge, I also searched for ages on Google to try and find a solution.

Database Table info if you need:

Im updating the Table "accounts" with columns:

`acct` 'Account ID'

`login` 'Login username'

`email` 'User Contact Email'

`password` 'Login password'

`flags` 'Client flags',

Form options:

Current Username:

Current Password:

New Username:

New Password:

Retype Password:

New E-mail:

Update Account Type To: (dropdown menu)

CAPTCHA: (captcha image here)

Security Code:

This is my code so far, I know its not correct but its nearly there.

<?php

include("config.php");

error_reporting(E_ALL ^ E_NOTICE);

session_start();

$msg = Array();
$error = Array();

   
function addUser(){
    if (empty($_POST)) return false;
    global $config, $msg, $error;
if(($_SESSION['security_code'] == $_POST['security_code']) && (!empty($_SESSION['security_code'])) ) {

$msg[] = 'Security code accepted!';
      unset($_SESSION['security_code']);
   } else {

$error[] = 'Error, You have provided an invalid security code!';
   }
    if (empty($_POST['login'])) $error[] = 'Error, You forgot to enter a account name!';
if (empty($_POST['oldpassword'])) $error[] = 'Error, You forgot to enter your password!';
    if (empty($_POST['password'][0]) || empty($_POST['password'][1])) $error[] = 'Error, You forgot to enter a password!';
    if ($_POST['password'][0] !== $_POST['password'][1]) $error[] = 'Password does not match!';
    if (empty($_POST['email'])) $error[] = 'Please fill in a valid email adress!';
    if (!empty($error)) return false;
    $db = @mysql_connect($config['mysql_host'], $config['mysql_user'], $config['mysql_pass']);
    if (!$db) return $error[] = 'Database: '.mysql_error();
    if (!@mysql_select_db($config['mysql_dbname'], $db)) return $error[] = 'Database: '.mysql_error();
    $query = "SELECT `acct` FROM `accounts` WHERE `login` = '".mysql_real_escape_string($_POST['login'])."'";
    $res = mysql_query($query, $db);
    if (!$res) return $error[] = 'Database: '.mysql_error();
    if (mysql_num_rows($res) > 0) return $error[] = 'Username already in use.';

$query = "UPDATE `accounts` SET `login` = '".mysql_real_escape_string($_POST['newlogin'])."', AND `password` = '".mysql_real_escape_string($_POST['password'][0])."', AND `flags` = '".$_SERVER['REMOTE_ADDR']."','".mysql_real_escape_string($_POST['tbc'][0])."' WHERE (`login`='".mysql_real_escape_string($_POST['login'])."', AND `password` = '".mysql_real_escape_string($_POST['oldpassword'][0])."')";


    $res = mysql_query($query, $db);
    if (!$res) { $error[] = 'Database: '.mysql_error(); return $error; }
    $msg[] = 'The Account <span style="color:#00FF00"><strong>'.htmlentities($_POST['login']).'</strong></span> has been updated!';
    mysql_close($db);
    return true;
}
{
addUser();
}

?>


<html xmlns="http://www.w3.org/1999/xhtml" >
<head>
    <title>Account Upgrade Form</title>
    <meta http-equiv="Pragma" content="no-cache"/>
    <meta http-equiv="Cache-Control" content="no-cache"/>
    <style type="text/css" media="screen">
    @import url(server_stats.css);.style4 {color: #00CCFF; font-weight: bold; }
    .style5 {
color: #00CCFF;
font-size: 12pt;
font-weight: bold;
}
    </style>
    <!--[if lt IE 7.]>
    <script defer type="text/javascript" src="pngfix.js"></script>
    <![endif]-->
</head>
<body>
<br>
<br>
<br>
    <center>
      <div style="width:380px">
        <form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="POST">
        <table width="100%" border="0" cellspacing="1" cellpadding="3">
            <tr class="head">
              <th colspan="2"><span class="style5">Account Upgrade Form</span></th>
          </tr>
            <tr>
                <th>Current Username: </th><td align="center"><input class="button" type="text" name="login" size="30" maxlength="16"/></td>
            </tr>
            <tr>
                <th>Current Password: </th><td align="center"><input class="button" type="password" name="oldpassword" size="30" maxlength="16"/></td>
            </tr>
            <tr>
                <th>New Username: </th><td align="center"><input class="button" type="text" name="newlogin" size="30" maxlength="16"/></td>
            </tr>
            <tr>
                <th>New Password: </th><td align="center"><input class="button" type="password" name="password[]" size="30" maxlength="16"/></td>
            </tr>
            <tr>
                <th>Retype Password: </th><td align="center"><input class="button" type="password" name="password[]" size="30" maxlength="16"/></td>
            </tr>
            <tr>
                <th>New E-mail: </th><td align="center"><input class="button" type="text" name="email" size="30" maxlength="30"/></td>
            </tr>

<th>Update Account Type To:</th><td align="center">
<select name="tbc" type="select">
<option value="0">Normal</option>
<option value="8">Burning Crusade</option>
<option selected value="44">Wrath of the Lich King</option>
</select></td>

<TR>
<th>CAPTCHA: </th><td align="center"><img src="CaptchaSecurityImages.php" />
  </td>
          </tr>
          <TR>
<th>Security Code: </th><td align="center"><input name="security_code" type="text" class="button" id="security_code" />
  </td>
          </tr>

             				
        </table>
        <center>
        <input type="button" class="button" value="Back" onClick="history.go(-1)" />
        <input type="submit" value="Submit" class="button"/></center>
        </form>

	<?php
        if (!empty($error)){
            echo '<table width="100%" border="0" cellspacing="1" cellpadding="3"><tr><td class="error" align="center">';
            foreach($error as $text)
                echo $text.'</br>';
            echo '</td></tr></table>';
        };
        if (!empty($msg)){
            echo '<table width="100%" border="0" cellspacing="1" cellpadding="3"><tr><td align="center">';
            foreach($msg as $text)
                echo $text.'</br>';
            echo '</td></tr></table>';
            exit();
        };
        ?>

    </div>
    </center>
<center>
<table width="380" border="0">
  <tr>
    <td width="163"><div align="center" class="style4">Client:</div></td>
    <td width="201"><div align="center" class="style4">Realmlist:</div></td>
  </tr>
  <tr>
    <td><div align="center">Burning Crusade 2.4.3</div></td>
    <td><div align="center">wow-pwnage.game-server.cc</div></td>
  </tr>
  <tr>
    <td><div align="center">Burning Crusade 3.0.3</div></td>
    <td><div align="center">wow-pwnage.game-server.cc:3725</div></td>
  </tr>
  <tr>
    <td><div align="center">Wrath of the Lich King 3.0.3</div></td>
    <td><div align="center">wow-pwnage.game-server.cc:3725</div></td>
  </tr>
</table>
</center>
</body>
</html>

And the config file:

<?php

$config['mysql_host'] = 'localhost'; //MySQL Host
$config['mysql_user'] = 'user'; //MySQL Username
$config['mysql_pass'] = 'pass'; //MySQL Password
$config['mysql_dbname'] = 'logon'; //Database Name

?>

Cheers, and thanks heaps if you can help me.

Mark Baker · November 28, 2008

In SQL, the UPDATE command can only be used to change the data in records that already exist, not to create new records. To do the latter, you need to use the SQL INSERT command.

Charlie9809 · November 28, 2008

Yea I know that, thanks anyway, but this tool will be used by users to change their account details, not to create new accounts.

Cheers

waynew · November 28, 2008

What's going wrong?

Mark Baker · November 28, 2008

this tool will be used by users to change their account details, not to create new accounts.

Apologies. Your use of a function called addUser() threw me.

Charlie9809 · November 28, 2008

Well ive developed this from a script a friend of mine wrote, which is used to create accounts, this is it:

<?php

include("config.php");

error_reporting(E_ALL ^ E_NOTICE);

session_start();

$msg = Array();
$error = Array();

   
function addUser(){
    if (empty($_POST)) return false;
    global $config, $msg, $error;
if(($_SESSION['security_code'] == $_POST['security_code']) && (!empty($_SESSION['security_code'])) ) {
<br>
$msg[] = 'Security code accepted!';
      unset($_SESSION['security_code']);
   } else {
<br>
$error[] = 'Error, You have provided an invalid security code!';
   }
    if (empty($_POST['login'])) $error[] = 'Error, You forgot to enter a account name!';
    if (empty($_POST['password'][0]) || empty($_POST['password'][1])) $error[] = 'Error, You forgot to enter a password!';
    if ($_POST['password'][0] !== $_POST['password'][1]) $error[] = 'Password does not match!';
    if (empty($_POST['email'])) $error[] = 'Please fill in a valid email adress!';
    if (!empty($error)) return false;
    $db = @mysql_connect($config['mysql_host'], $config['mysql_user'], $config['mysql_pass']);
    if (!$db) return $error[] = 'Database: '.mysql_error();
    if (!@mysql_select_db($config['mysql_dbname'], $db)) return $error[] = 'Database: '.mysql_error();
    $query = "SELECT `acct` FROM `accounts` WHERE `login` = '".mysql_real_escape_string($_POST['login'])."'";
    $res = mysql_query($query, $db);
    if (!$res) return $error[] = 'Database: '.mysql_error();
    if (mysql_num_rows($res) > 0) return $error[] = 'Username already in use.';

$query = "INSERT INTO `accounts` (`login`,`password`,`lastip`, `flags`) VALUES ('".mysql_real_escape_string($_POST['login'])."', '".mysql_real_escape_string($_POST['password'][0])."', '".$_SERVER['REMOTE_ADDR']."','".mysql_real_escape_string($_POST['tbc'][0])."')";


    $res = mysql_query($query, $db);
    if (!$res) return $error[] = 'Database: '.mysql_error();
    $msg[] = 'The Account <span style="color:#00FF00"><strong>'.htmlentities($_POST['login']).'</strong></span> has been created!';
    mysql_close($db);
    return true;
}
{
addUser();
}

?>


<html xmlns="http://www.w3.org/1999/xhtml" >
<head>
    <title>Account Registration</title>
    <meta http-equiv="Pragma" content="no-cache"/>
    <meta http-equiv="Cache-Control" content="no-cache"/>
    <style type="text/css" media="screen">
    @import url(server_stats.css);.style4 {color: #00CCFF; font-weight: bold; }
    .style5 {
color: #33CCFF;
font-size: 12pt;
font-weight: bold;
}
    </style>
    <!--[if lt IE 7.]>
    <script defer type="text/javascript" src="pngfix.js"></script>
    <![endif]-->
</head>
<body>
<br>
<br>
<br>
    <center>
      <div style="width:380px">
        <form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="POST">
        <table width="100%" border="0" cellspacing="1" cellpadding="3">
            <tr class="head"><th colspan="2">Account Creation</th></tr>
            <tr>
                <th>Username: </th><td align="center"><input class="button" type="text" name="login" size="30" maxlength="16"/></td>
            </tr>
            <tr>
                <th>Password: </th><td align="center"><input class="button" type="password" name="password[]" size="30" maxlength="16"/></td>
            </tr>
            <tr>
                <th>Retype Password: </th><td align="center"><input class="button" type="password" name="password[]" size="30" maxlength="16"/></td>
            </tr>
            <tr>
                <th>E-mail: </th><td align="center"><input class="button" type="text" name="email" size="30" maxlength="30"/></td>
            </tr>

<th>Account Type:</th><td align="center">
<select name="tbc" type="select">
<option value="0">Normal</option>
<option value="8">Burning Crusade</option>
<option selected value="32">Wrath of the Lich King</option>
</select></td>

<TR>
<th>CAPTCHA: </th><td align="center"><img src="CaptchaSecurityImages.php" />
  </td>
          </tr>
          <TR>
<th>Security Code: </th><td align="center"><input name="security_code" type="text" class="button" id="security_code" />
  </td>
          </tr>

             				
        </table>
        <input type="button" class="button" value="Back" onClick="history.go(-1)" />
        <input type="submit" value="Submit" class="button"/>
        </form>

	<?php
        if (!empty($error)){
            echo '<table width="100%" border="0" cellspacing="1" cellpadding="3"><tr><td class="error" align="center">';
            foreach($error as $text)
                echo $text.'</br>';
            echo '</td></tr></table>';
        };
        if (!empty($msg)){
            echo '<table width="100%" border="0" cellspacing="1" cellpadding="3"><tr><td align="center">';
            foreach($msg as $text)
                echo $text.'</br>';
            echo '</td></tr></table>';
            exit();
        };
        ?>

    </div>
    </center>

<center>
<table width="380" border="0">
  <tr>
    <td width="163"><div align="center" class="style4">Client:</div></td>
    <td width="201"><div align="center" class="style4">Realmlist:</div></td>
  </tr>
  <tr>
    <td><div align="center">Burning Crusade 2.4.3</div></td>
    <td><div align="center">wow-pwnage.game-server.cc</div></td>
  </tr>
  <tr>
    <td><div align="center">Burning Crusade 3.0.3</div></td>
    <td><div align="center">wow-pwnage.game-server.cc:3725</div></td>
  </tr>
  <tr>
    <td><div align="center">Wrath of the Lich King 3.0.3</div></td>
    <td><div align="center">wow-pwnage.game-server.cc:3725</div></td>
  </tr>
</table>
</center>

</body>
</html>

Thats why it may look weird.

Charlie9809 · November 28, 2008

What's going wrong?

I get a database error when I hit Submit.

Mark Baker · November 28, 2008

I get a database error when I hit Submit.

What database error?

Charlie9809 · November 28, 2008

MYSQL Syntax error

Charlie9809 · November 28, 2008

I really need to sleep, early start for work tomorrow. Thanks for your help so far.

Cheers

Charlie

revraz · November 28, 2008

And the rest of the error?

MYSQL Syntax error

Sign In

Update SQL Query Help

Recommended Posts

Charlie9809

Link to comment

Share on other sites

Mark Baker

Link to comment

Share on other sites

Charlie9809

Link to comment

Share on other sites

waynew

Link to comment

Share on other sites

Mark Baker

Link to comment

Share on other sites

Charlie9809

Link to comment

Share on other sites

Charlie9809

Link to comment

Share on other sites

Mark Baker

Link to comment

Share on other sites

Charlie9809

Link to comment

Share on other sites

Charlie9809

Link to comment

Share on other sites

revraz

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information