Page 1 of 1

MySQL Insert Failing

Posted: Fri Feb 19, 2010 7:58 pm
by jthermane24
Hi I've been working on this user registration script for, I'd say about 5 hours now, and I have most of it complete but I keep running into the same problem. Not all of the form data will insert into the database. The row in the mysql table will only contain some of the data required. I have been over everything and can't find anything that would be causing this. Thanks for the help. Here's the code.

Code: Select all

<?php
// Connect to database
require('../functions.php');
database_connect('Admin');
 
// Establish Varaiables
$errors = array();
$user = stripslashes($_POST['username1']);
$user = mysql_real_escape_string($user);
$user2 = stripslashes($_POST['username2']);
$user2 = mysql_real_escape_string($user2);
$pass = stripslashes($_POST['password1']);
$pass = mysql_real_escape_string($pass);
$pass2 = stripslashes($_POST['password2']);
$pass2 = mysql_real_escape_string($pass2);
$email = $_POST['email1'];
$email = mysql_real_escape_string($email);
$email2 = $_POST['email2'];
$email2 = mysql_real_escape_string($email2);
$first = stripslashes($_POST['first']);
$first = mysql_real_escape_string($first);                           
$sur = stripslashes($_POST['surname']);
$sur = mysql_real_escape_string($sur);
$access = "10"; // Default access level = 10 (1- Admin, 2- Author, 10-Anon)
$legnth = rand(5,11);
$enabler = generateRandString($legnth);
 
if (!empty($_POST['submit'])) {
if($first=='') {
    $errors['first'] = 'Please provide your first name';
}
if($sur=='') {
    $errors['sur'] = 'Please provide your last name';
}
if($user='') {
    $errors['userblank'] = "Please provide a username.";
}
if($user2 ='') {
    $errors['user2blank'] = "Please provide a username.";
}   
if($user!==$user2) {
    $errors['usernomatch'] = "Please provide matching usernames.";
}
if($pass='') {
    $errors['passblank'] = "Please provide a username.";
}
if($pass2='') {
    $errors['pass2blank'] = "Please provide a username.";
}
if($pass!==$pass2) {
    $errors['passnomatch'] = "Please provide matching usernames.";
}
if($email='') {
    $errors['emailblank'] = "Please provide a valid email.";
}
if($email2='') {
    $errors['emailblank'] = "Please provide a valid email.";
}
if($email!==$email2) {
    $errors['emailnomatch'] = "Please provide matching email addresses.";
}
// Check username is available
$query = "SELECT * FROM `cmsusers` WHERE `user` = '$user'";
$result = mysql_query($query);
if(!$result){
    die('Error 1: ' . mysql_error());
}
else if(mysql_num_rows($result) > 0) {
    $errors['userunavail'] = 'The username ' . $user . ' is registered. Please try a different name.';
}
// Check email validity
is_valid_email($email);
if($isValid == false) {
  $errors['emailinvalid'] = 'The email address you supplied does not pass validation. Please correct your email and try again.';
}
// Check email is available
$query = 'SELECT * FROM `cmsusers` WHERE `email` = \'' . $email .'\'';
$result = mysql_query($query);
if(!$result){
    die('Error 2: ' . mysql_error());
}
else if(mysql_num_rows($result) !== 0) {
    $errors['dupeemail'] = 'The email address you supplied is alread registered. Please do not create duplicate accounts';
}
if (count($errors) == 0) {
  // Setup the insert function
  $sql = "INSERT INTO `cmsusers` (`ID`, `user`, `pass`, `thegroup`, `email`, `firstname`, `surname`, `enabled`, `enabler`) VALUES (NULL, '$user', '$pass', '$access', '$email', '$first', '$sur', 0, '$enabler');"
  // Run the insert
  mysql_query($sql) or die('MySql Error 3: ' . mysql_error());
  
  // Confirm entry, display login
  $success = '<img src="../images/tick.png" width="12" height="12" />&nbsp;Account created you may now login.';
}
else {
    $str ='<div class="formerror"><p><b>Error</b>: Your submission has returned with the following errors. Please validate your data and try again.</p><ul class="faillist" style="padding-right:15px;">';
    foreach ($errors as $error) {
        $str .="<li>$error</li>";
    }
    $str .="</ul></div>";
}
}                 
?><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="content-type" content="text/html; charset=iso-8859-1" />
<title><?php siteoption('sitename'); ?></title>
<meta name="keywords" content="<?php siteoption('keywords'); ?>" />
<meta name="description" content="<?php siteoption('slogan'); ?>" />
<meta name="author" content="<?php siteoption('name'); ?>" />
<link href="../default.css" rel="stylesheet" type="text/css" />
<style type="text/css">
.bold {
    font-weight: bold;
}
    
table {
    width: auto;
    line-height: 150%;
}
 
td .right {
    text-align: right;
    width: 120px;
}
 
.right {
    text-align: right;
}
 
.objcenter {
    margin-left: auto;
    margin-right: auto;
}
 
.txtcenter {
    text-align: center;
}
label {
  width: 80px;
  text-align: right;
  float: left;
}
 
.formerror {
    background-color: #FFCCCC;
    width: auto;
    padding: 5px 5px 5px 5px;
}
.faillist {
    list-style-image: url(../images/fail_x.png);
}
 
.errortext {
  padding-left: 80px;
  font: bold smaller sans-serif;
}
 
</style>
<link rel="icon" type="image/gif" href="/images/favico.gif" />
</head>
 
<body>
<div id="header">
<div id="logo">
<img src="../images/avatar.gif" width="95" height="95" alt="icon" />
    </div>
<div id="title">
    <h1><?php siteoption('sitename'); ?></h1><br /><br />
<br />
 
        <h2><?php siteoption('slogan'); ?></h2>
</div>
 
    <div id="menu">
        <ul>
            <li class="active"><a href="../index.php" title="">Home</a></li>
            <li><a href="#" title="">About</a></li>
            <li><a href="../blog" title="">Blog</a></li>
            <li><a href="#" title="">Contact</a></li>
        </ul>
    </div>
</div>
<div id="content">
<div id="sidebar">
        <div id="login" class="boxed">
            <div class="title">
                <h2>Site Login</h2>
            </div>
            <div class="content">
              <?php require('../account/login_form.php'); ?>
            </div>
        </div>
        <!-- end #login -->
        <div id="updates" class="boxed">
            <div class="title">
                <h2>Recent Updates</h2>
            </div>
            <div class="content"><?php require('../recentnews.php'); ?>
            </div>
        </div>
        <!-- end #updates -->
</div>
<!-- end #sidebar -->
<div id="main">
<div id="welcome" class="post">
    <h2 class="title"><span>Welcome!</span></h2>
    <h3 class="date"><span class="month">Mar.</span> <span class="day">8</span><span class="year">, 2007</span></h3>
    <div class="story">
      <p>This is the page for site account. Completeing the following form will create an account that only you will be able to access. Benefits to creating an account include: 
      <ul>
      <li>a customized user experience</li>
      <li>access to subscriber-only content</li>
      </ul>
      </p>
      <p>
      <?php 
      session_start(); 
      if(strlen($_SESSION['xxxxxx']) > 0){
      ?>
      <blockquote>You are seeing this message because you are currently logged into this website. This means that you already have an account and should have no need for having a second one. Please refrain from creating multiple acocunts. Thank you.</blockquote>
      <?php 
      }
      else { ?>
<p>All fields of the form are required for successful account creation and must conatin valid data.</p>
      <form name="account" method="post" action="<?php echo($PHP_SELF); ?>"> 
        <?php 
        if (!empty($str)) {
            echo $str;
        }
        else if(!empty($success)){
            echo $success;
        }
        ?>
        <table width="438" cellpadding="5" cellspacing="5" class="objcenter">
          <tr>
            <td width="50%"<?php if(!empty($errors['first'])) { echo ' class="formerror"'; }?>>
              First name:
            </td>
            <td width="50%"<?php if(!empty($errors['sur'])) { echo ' class="formerror"'; }?>>
              Last Name:
            </td>
            </tr>
            <tr>
            <td width="50%"<?php if(!empty($errors['first'])) { echo ' class="formerror"'; }?>>
              <input style="width:auto;"  type="text" name="first" id="first" value="<?php if(!empty($success)){ echo($_POST['first']);}?>"/>
            </td>            
            <td width="50%"<?php if(!empty($errors['sur'])) { echo ' class="formerror"'; }?>>
              <input style="width:auto;"  type="text" name="surname" id="surname" value="<?php if(!empty($success)){ echo($_POST['surname']);}?>"  />
            </td>
          </tr>  
          <tr>
            <td colspan="2"<?php if(!empty($errors['userblank']) || !empty($errors['user2blank']) || !empty($errors['usernomatch'])) { echo ' class="formerror"'; }?>>
            Enter your perfered username twice </td>
          </tr>
            <td width="50%"<?php if(!empty($errors['userblank']) || !empty($errors['usernomatch'])) { echo ' class="formerror"'; }?>>
              <input style="width:auto;"  type="text" name="username1" id="username1" value="<?php if(!empty($success)){ echo($_POST['username1']);}?>"  />
            </td>
            <td width="50%"<?php if(!empty($errors['user2blank']) || !empty($errors['usernomatch'])) { echo ' class="formerror"'; }?>>
              <input style="width:auto;"  type="text" name="username2" id="username2" value="<?php if(!empty($success)){ echo($_POST['username2']);}?>"  />    
            </td>            
          </tr>
          <tr>
            <td colspan="2"<?php if(!empty($errors['passblank']) || !empty($errors['pass2blank']) || !empty($errors['passnomatch'])) { echo ' class="formerror"'; }?>>
              Enter your password twice
            </td>
          </tr>
            <td width="50%"<?php if(!empty($errors['passblank']) || !empty($errors['passnomatch'])) { echo ' class="formerror"'; }?>>
              <input style="width:auto;"  type="password" name="password1" id="password1"  />    
            </td>
            <td width="50%"<?php if(!empty($errors['pass2blank']) || !empty($errors['passnomatch'])) { echo ' class="formerror"'; }?>>
              <input style="width:auto;"  type="password" name="password2" id="password2"  />    
            </td>            
          </tr> 
          <tr> 
            <td colspan="2"<?php if(!empty($errors['emailblank']) || !empty($errors['dupeemail']) || !empty($errors['email2blank']) || !empty($errors['emailnomatch'])) { echo ' class="formerror"'; }?>>
              Enter your email. Be sure it's correct.
            </td>
          </tr>
            <td width="50%"<?php if(!empty($errors['emailblank']) || !empty($errors['dupeemail']) || !empty($errors['emailnomatch'])) { echo ' class="formerror"'; }?>>
              <input style="width:auto;"  type="text" name="email1" id="email1" value="<?php if(!empty($success)){ echo($_POST['email1']);}?>"  />    
            </td>
            <td width="50%"<?php if(!empty($errors['email2blank']) || !empty($errors['dupeemail']) || !empty($errors['emailnomatch'])) { echo ' class="formerror"'; }?>>
              <input style="width:auto;"  type="text" name="email2" id="email2" value="<?php if(!empty($success)){ echo($_POST['email2']);}?>"  />    
            </td>            
          </tr>  
          <tr>
            <td colspan="2" class="txtcenter">
              <input name="submit" type="submit" id="submit" style="padding: 2px 5px; color: #666666" value="Submit"/>
              <input name="reset" type="reset" style="padding: 2px 5px; color: #666666" value="Reset"/>
            </td>
          </tr>       
        </table>
      </form>
      <?php
      }
      ?>
      </table>
      </p>
  </div>
  </div>
  <!-- end #welcome -->
  </div>
<!-- end #main -->
<div id="sidebar2">
                <div id="login" class="boxed">
                        <div class="title">
                            <h2>Site Search</h2>
                        </div>
                        <div class="content">
<form action="http://jthermane24.byethost8.com/search.php" id="cse-search-box">
  <div>
    <input  type="hidden" name="cx" value="partner-pub-xxxxxx" />
    <input  type="hidden" name="cof" value="FORID:9" />
    <input  type="hidden" name="ie" value="UTF-8" />
    <input  type="text" name="q" style="width:auto;" />
    <input  type="submit" name="sa" value="Search" />
  </div>
</form>
<script type="text/javascript" src="http://www.google.com/cse/brand?form=cse-search-box&lang=en"></script>
 
</div>
        <div id="sponsors" class="boxed">
            <div class="title">
                <h2>Sponsors</h2>
            </div>
            <div class="content">
              <script type="text/javascript"><!--
              google_ad_client = "pub-xxxxxx";
              /* 125x125, 3.0 Sidebar created 7/30/09 */
              google_ad_slot = "0246256189";
              google_ad_width = 125;
              google_ad_height = 125;
              //-->
              </script>
              <script type="text/javascript"
              src="http://pagead2.googlesyndication.com/pagead/show_ads.js">
              </script>
            </div>
        </div>
        <!-- end #sponsors -->
        <div id="ad120x600">
          <script type="text/javascript"><!--
          google_ad_client = "xxxxxx";
          /* 120x600, Sidebar Skyscraper created 7/30/09 */
          google_ad_slot = "4748043710";
          google_ad_width = 120;
          google_ad_height = 600;
          //-->
          </script>
          <script type="text/javascript"
          src="http://pagead2.googlesyndication.com/pagead/show_ads.js">
          </script>
        </div>
    </div>
    <div style="clear: both;">&nbsp;</div>
</div>
<!-- end #sidebar2 -->
<!-- end #content -->
<div id="footer">
<p id="legal"><?php siteoption('disclaimer'); ?></p>
    <p id="links"><a href="#">Privacy Policy</a> | <a href="#">Terms of Use</a></p>
    <p>
        <a href="http://validator.w3.org/check?uri=referer">
           <img src="http://www.w3.org/Icons/valid-xhtml10-blue" alt="Valid XHTML 1.0 Strict" height="31" width="88" />
        </a>
    </p>
</div>
</body>
</html>
Note that I have censored some of the private data in the code, which would account for the random strings of xxxxx. Thanks for the help in advanced.

Re: MySQL Insert Failing

Posted: Fri Feb 19, 2010 8:30 pm
by Weiry
Have you tried printing the query string? Are you getting any MySQL errors?

Because you haven't specifically said which values aren't being inserted, its a bit hard to figure out where your problem might start.
Throw in a print out of your sql, just after you set it.

Code: Select all

 $sql = "INSERT INTO `cmsusers` (`ID`, `user`, `pass`, `thegroup`, `email`, `firstname`, `surname`, `enabled`, `enabler`) VALUES (NULL, '$user', '$pass', '$access', '$email', '$first', '$sur', 0, '$enabler');"
 
  // Print the query to see what is inside it.
  print $sql."<br/>";
  //
 
  // Run the insert
  mysql_query($sql) or die('MySql Error 3: ' . mysql_error());
Make sure also you have

Code: Select all

// Report all PHP errors
error_reporting(E_ALL);
So you can see if there are any notices about variables not being defined etc.

Re: MySQL Insert Failing

Posted: Fri Feb 19, 2010 9:11 pm
by jthermane24
Thanks for the help. I'm not getting any mysql_error messages and I had some notice's about all of my for variables being undefined but I fixed that with moving the if($_POST['send']).
Here are the fields that won't enter into the database.
  • user
  • pass
  • email
The sql string prints with those same three fields missing.

Code: Select all

INSERT INTO `cmsusers` (`ID`, `user`, `pass`, `thegroup`, `email`, `firstname`, `surname`, `enabled`, `enabler`) VALUES (NULL, '', '', '10', '', 'Jerry', 'Smith', 0, 'Q5vC2FYJpf');
Thanks again for the help

Re: MySQL Insert Failing

Posted: Fri Feb 19, 2010 11:06 pm
by JakeJ
Check you data definitions in your table. I bet you have some data type mismatches going on.

That's where I'd start since it looks like your code is ok.

Re: MySQL Insert Failing

Posted: Sat Feb 20, 2010 10:50 am
by jthermane24
I'm still fairly new to this whole data definitions thing. I just basically know that you can insert data into the table. What would I be looking for? If you need to me post anything relating to the table feel free to ask. Thanks.

Re: MySQL Insert Failing

Posted: Sat Feb 20, 2010 12:31 pm
by JakeJ
What I meant was, look at your table and make sure that you are using a data type (float, varchar, etc. that is appropriate for the data you are attempting to insert.

Re: MySQL Insert Failing

Posted: Sat Feb 20, 2010 1:13 pm
by jthermane24
Everything matches, and the fact that not all of the info prints when I print the query leads me to believe that it's a coding error not something to do with the database.

Re: MySQL Insert Failing

Posted: Sat Feb 20, 2010 5:39 pm
by Weiry
jthermane24 wrote:Everything matches, and the fact that not all of the info prints when I print the query leads me to believe that it's a coding error not something to do with the database.
You are definitly correct about that :P

Ok well i worked backwards from the SQL keeping in mind the 3 fields which weren't showing.

I did see this bit of code which will never work :P

Code: Select all

// Check email validity
is_valid_email($email);
if($isValid == false) {
  $errors['emailinvalid'] = 'The email address you supplied does not pass validation. Please correct your email and try again.';
}
You should just change that if statement into

Code: Select all

if(!is_valid_email($email)){
 
Now for why you can't see any usernames etc..
Can you spot the difference ???

Code: Select all

if($sur=='') {
    $errors['sur'] = 'Please provide your last name';
}
if($user='') {
    $errors['userblank'] = "Please provide a username.";
}
 

Re: MySQL Insert Failing

Posted: Sat Feb 20, 2010 6:22 pm
by jthermane24
Yeah I just noticed the comparison was missing an =. I managed to solve the problem by making the variables that had to be inserted global. So it works now and that could probably explain the other problems. Thanks for the help. Update: That fix also solved the query problems, thanks for all the help guys I really appreciate it.