MySQL Insert Failing

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
jthermane24
Forum Newbie
Posts: 16
Joined: Wed Feb 17, 2010 8:15 pm
Location: New Jersey

MySQL Insert Failing

Post 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.
User avatar
Weiry
Forum Contributor
Posts: 323
Joined: Wed Sep 09, 2009 5:55 am
Location: Australia

Re: MySQL Insert Failing

Post 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.
User avatar
jthermane24
Forum Newbie
Posts: 16
Joined: Wed Feb 17, 2010 8:15 pm
Location: New Jersey

Re: MySQL Insert Failing

Post 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
JakeJ
Forum Regular
Posts: 675
Joined: Thu Dec 10, 2009 6:27 pm

Re: MySQL Insert Failing

Post 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.
User avatar
jthermane24
Forum Newbie
Posts: 16
Joined: Wed Feb 17, 2010 8:15 pm
Location: New Jersey

Re: MySQL Insert Failing

Post 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.
JakeJ
Forum Regular
Posts: 675
Joined: Thu Dec 10, 2009 6:27 pm

Re: MySQL Insert Failing

Post 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.
User avatar
jthermane24
Forum Newbie
Posts: 16
Joined: Wed Feb 17, 2010 8:15 pm
Location: New Jersey

Re: MySQL Insert Failing

Post 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.
User avatar
Weiry
Forum Contributor
Posts: 323
Joined: Wed Sep 09, 2009 5:55 am
Location: Australia

Re: MySQL Insert Failing

Post 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.";
}
 
User avatar
jthermane24
Forum Newbie
Posts: 16
Joined: Wed Feb 17, 2010 8:15 pm
Location: New Jersey

Re: MySQL Insert Failing

Post 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.
Post Reply