INSERTing into database not working

Aftiii · November 28, 2010

Hi, first time poster here. Pretty new to PHP.

Yesterday my PHP code was inserting into my MySQL database fine and as of today it isn't inserting anything into the database.

Is there any common error in my PHP code that i'm forgetting?

I'm using XAMPP.

I think I may be posting in the wrong area.

Also any pointers on my code would be appreciated.

Below is my code:

<html>
<head>
<title> Sign up! </title>
</head>
<body>
<form id = "signup"  method = "post" action = "<?php echo $_SERVER['PHP_SELF'];?>" onsubmit= "return formValidator()">
Please enter your name:
<input type = "text" id = "name">
<br />
Please enter the password you would like:
<input type ="password" id = "password"/>
<br />
Please enter your Date Of Birth :
<select type ="text" size = "1" id = "dayofbirth"/>
<option value = "1"> 01 </option>
<option value = "2"> 02 </option>
<option value = "3"> 03 </option>
<option value = "4"> 04 </option>
<option value = "5"> 05 </option>
<option value = "6"> 06 </option>
<option value = "7"> 07 </option>
<option value = "8"> 08 </option>
<option value = "9"> 09 </option>
<option value = "10"> 10 </option>
<option value = "11"> 11 </option>
<option value = "12"> 12 </option>
<option value = "13"> 13 </option>
<option value = "14"> 14 </option>
<option value = "15"> 15 </option>
<option value = "16"> 16 </option>
<option value = "17"> 17 </option>
<option value = "18"> 18 </option>
<option value = "19"> 19 </option>
<option value = "20"> 20 </option>
<option value = "21"> 21 </option>
<option value = "22"> 22 </option>
<option value = "23"> 23 </option>
<option value = "24"> 24 </option>
<option value = "25"> 25 </option>
<option value = "26"> 26 </option>
<option value = "27"> 27 </option>
<option value = "28"> 28 </option>
<option value = "29"> 29 </option>
<option value = "30"> 30 </option>
<option value = "31"> 31 </option>
</select>
<select type ="text" size = "1" id = "monthofbirth"/>
<option value = "1">January</option>
<option value = "2">February</option>
<option value = "3">March</option>
<option value = "4">April</option>
<option value = "5">May</option>
<option value = "6">June</option>
<option value = "7">July</option>
<option value = "8">August</option>
<option value = "9">September</option>
<option value = "10">October</option>
<option value = "11">November</option>
<option value = "12">December</option>
</select>

<select type ="text" size = "1" id = "yearofbirth"/>
<option value = "1994">1994</option>
<option value = "1993">1993</option>
<option value = "1992">1992</option>
<option value = "1991">1991</option>
<option value = "1990">1990</option>
<option value = "1989">1989</option>
<option value = "1988">1988</option>
<option value = "1987">1987</option>
<option value = "1986">1986</option>
<option value = "1985">1985</option>
<option value = "1984">1984</option>
<option value = "1983">1983</option>
<option value = "1982">1982</option>
<option value = "1981">1981</option>
<option value = "1980">1980</option>
<option value = "1979">1979</option>
<option value = "1978">1978</option>
<option value = "1977">1977</option>
<option value = "1976">1976</option>
<option value = "1975">1975</option>
<option value = "1974">1974</option>
<option value = "1973">1973</option>
<option value = "1972">1972</option>
<option value = "1971">1971</option>
<option value = "1970">1970</option>
<option value = "1969">1969</option>
<option value = "1968">1968</option>
<option value = "1967">1967</option>
<option value = "1966">1966</option>
<option value = "1965">1965</option>
<option value = "1964">1964</option>
<option value = "1963">1963</option>
<option value = "1962">1962</option>
<option value = "1961">1961</option>
<option value = "1960">1960</option>
<option value = "1959">1959</option>
<option value = "1958">1958</option>
<option value = "1957">1957</option>
<option value = "1956">1956</option>
<option value = "1955">1955</option>
<option value = "1954">1954</option>
<option value = "1953">1953</option>
<option value = "1952">1952</option>
<option value = "1951">1951</option>

</select>
<br />
Please enter your e-mail address:
<input type ="text" id = "email"/>
<br />
Please enter your address:
<input type ="text" id = "address"/>
<br />
Please enter your city:
<input type ="text" id = "city"/>
<br />
Please enter your postcode
<input type ="text" id = "postcode"/>
<br />
Please enter your telephone number:
<input type ="text" id = "telephoneno"/>
<br />
<input type= "submit" id = "submit" value ="Submit me!"/>
</body>
<?php
$conn = mysql_connect("localhost", "root", "") or die("cannot connect server "); 
mysql_select_db("nightsout") or die ("cannot find database");

if(isset($_POST['submit']))
{	
$username = $_POST['name'];
$password = $_POST['password'];
$day = $_POST['dayofbirth'];
$month = $_POST['monthofbirth'];
$year = $_POST['yearofbirth'];
$date = ($year.'-'.$month.'-'.$day);
$email = $_POST['email'];
$address = $_POST['address'];
$city = $_POST['city'];
$postcode = $_POST['postcode'];
$telephoneno = $_POST['telephoneno'];
$duplicate = mysql_query("SELECT * FROM users WHERE emailaddress = '$email'", $conn) or die('Cannot Execute:'. mysql_error());
if(mysql_num_rows($duplicate) == 0) 
{
		mysql_query("INSERT INTO users (username, password, DOB, emailaddress,  address, city, postcode, telephonenumber) 
VALUES ('{$username}', '{$password}', '{$date}', '{$email}' ,'{$address}', '{$city}', '{$postcode}', '{$telephoneno}')");
}else if(mysql_num_rows($duplicate) > '1')
{
	?>		
	<p>This E-mail address already exists please use another one or <a href="home.php">Login.</a> </p>
	<?php
}	
}

mysql_close($conn);
?>

Many thanks.

Pikachu2000 · November 28, 2010

Are you certain this worked? I don't see how it could have, as none of your input fields have name= attributes, therefore their values won't be in the $_POST array.

Also,

Using action=<?php echo $_SERVER['PHP_SELF']; ?>" is a known XSS vulnerability.

None of the data is validated or sanitized, therefore the script is wide open to SQL injection attacks.

type=text isn't a valid attribute for a <select> field, and those <select>s can be generated with much less coding.

You should separate your query string from the query execution so you can echo it along with any errors during development.

$query = "SELECT `field` FROM `table`";
if( $result = mysql_query($query) ) {
     // do whatever 
} else {
     echo "<br>Query string: $query<br>Returned error: ". mysql_error() . '<br>';
}

Here's some revised code, that's cleaned up and should work. It's also about 60 lines shorter, even with the comments. I have not, however added any field validation or sanitizing functions. Try this, see if it works as expected, then if you want help with validation/sanitizing, just say so.

<html>
<head>
<title> Sign up! </title>
</head>
<body>
<form id="signup" method="post" action="" onsubmit="return formValidator()">
Please enter your name:
<input type="text" id="name" name="name" />
<br />
Please enter the password you would like:
<input type="password" id="password" name="password" />
<br />
Please enter your Date Of Birth :
<?php
// These loops will build the <select> fields for you, without all of the extra coding:
echo "<select name=\"dayofbirth\" id=\"dayofbirth\" />\n";
$date=range(1, 31);
foreach( $date as $k => $v ) {
   echo "<option value=\"$k\">$v</option>\n";
}
echo "</select>\n";

echo "<select name=\"monthofbirth\" id=\"monthofbirth\" />\n";
$month=array( 1 => 'January', 'February', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December' );
foreach( $month as $k => $v ) {
   echo "<option value=\"$k\">$v</option>\n";
}
echo '</select>';

echo "<select name=\"yearofbirth\" id=\"yearofbirth\" />\n";
$year=range(1951, 1994);
foreach( $year as $k => $v ) {
   echo "<option value=\"$k\">$v</option>\n";
}
echo '</select>';
?>

<br />
Please enter your e-mail address:
<input type="text" id="email" name="email" />
<br />
Please enter your address:
<input type="text" id="address" name="address" />
<br />
Please enter your city:
<input type="text" id="city" name="city" />
<br />
Please enter your postcode
<input type="text" id="postcode" name="postcode" />
<br />
Please enter your telephone number:
<input type="text" id="telephoneno" name="telephoneno" />
<br />
<input type="hidden" name="submitted" value="yes" />
<input type="submit" name = "submit" id="submit" value="Submit me!" />
</body>

<?php
if( isset($_POST['submitted']) && $_POST['submitted'] == 'yes' ) {
   $conn = mysql_connect("localhost", "root", "") or die("cannot connect server ");
   mysql_select_db("nightsout") or die ("cannot find database");

   if(isset($_POST['submit'])) {
      $username = $_POST['name'];
      $password = $_POST['password'];
      $day = $_POST['dayofbirth'];
      $month = $_POST['monthofbirth'];
      $year = $_POST['yearofbirth'];
      $date = ($year.'-'.$month.'-'.$day);
      $email = $_POST['email'];
      $address = $_POST['address'];
      $city = $_POST['city'];
      $postcode = $_POST['postcode'];
      $telephoneno = $_POST['telephoneno'];
      
      //use a SELECT COUNT() query when all that is required is the number of records:
      $query = "SELECT COUNT(emailaddress) FROM users WHERE emailaddress = '$email'";
      $result = mysql_query($query, $conn) or die('Cannot Execute:'. mysql_error());
      $array = mysql_fetch_row($result);
      if( $array[0]  === 0) {
         // if exactly zero records have a matching email address, insert the record:
         $query = "INSERT INTO users (username, password, DOB, emailaddress, address, city, postcode, telephonenumber) VALUES (
         '{$username}', '{$password}', '{$date}', '{$email}' ,'{$address}', '{$city}', '{$postcode}', '{$telephoneno}'
         )";
         mysql_query($query);
         // make sure a record was added to the DB, and echo a message either way:
         if( mysql_affected_rows() === 1 ) {
            echo "Record Added.";
         } else {
            echo "There was a problem adding the record.";
         }
      } else {
         //if there are more than 0 records with the email address, notify the user.
         echo "<p>This E-mail address already exists please use another one or <a href=\"home.php\">Login.</a></p>";
      }
   }
   mysql_close($conn);
}
?>

Aftiii · November 28, 2010

Thanks a lot for the reply, i'll read through your changes (understand them as well) and then get back to you.

Thanks again.

Sign In

INSERTing into database not working

Recommended Posts

Aftiii

Link to comment

Share on other sites

Pikachu2000

Link to comment

Share on other sites

Aftiii

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information