user2214618 user2214618 - 24 days ago 8
PHP Question

php mysql + insert query errors

i am creating a page where users can edit their account and add some text using php and mysql . first i was able to do insert and update but now it display an error msg Duplicate entry for key email address
how can i solve this problem
this is the code of the account setting but it do not contains any email address field so i will display all be cause i do not know where is the desired code to be displayed

i edit the code maybe this one is producing the error ??

register .php



<?php require_once('for members/scripts/connect.php'); ?>


<?php
ob_start();
function countryQuery(){

$countryData = mysql_query("SELECT * FROM country") or die("could select database");

while($record = mysql_fetch_array($countryData)){

echo'<option value="' . $record['country_name'] . '">' . $record['country_name'] . '</option>';

}

}


function specializationQuery(){

$specData = mysql_query("SELECT * FROM specialization");

while($recordJob = mysql_fetch_array($specData)){

echo'<option value="' . $recordJob['specialization_name'] . '">' . $recordJob['specialization_name'] . '</option>';

}


}

function districtQuery(){

$distData = mysql_query("SELECT * FROM districts");

while($recorddist = mysql_fetch_array($distData)){

echo'<option value="' . $recorddist['district_name'] . '">' . $recorddist['district_name'] . '</option>';

}


}

function villageQuery(){

$villageData = mysql_query("SELECT * FROM village");

while($recordvillage = mysql_fetch_array($villageData)){

echo'<option value="' . $recordvillage['village_name'] . '">' . $recordvillage['village_name'] . '</option>';

}


}



//default value
$message = "Fields Marcked with an [*] are Required";
$username = "";
$fname = "";
$lname = "";
$specialization = "";
$email = "";
$pass1 = "";
$pass2 = "";
$district = "";
$village = "";

if(isset($_POST['username'])){
$username = $_POST['username'];
$fname = $_POST['fname'];
$lname = $_POST['lname'];
$email = $_POST['email'];
$pass1 = $_POST['pass1'];
$pass2 = $_POST['pass2'];
$bdate = $_POST['birthdate'];
$phone = $_POST['phonenumber'];
$country = $_POST['country'];
//$local_adress = $_POST['adress'];
$specialization = $_POST['specialization'];
$district = $_POST['district'];
$village = $_POST['village'];

//error handeling
if((!$username)||(!$fname)||(!$lname)||(!$email)||(!$pass1)||(!$pass2)||(!$specialization)||(!$district)||(!$village)){
$message = "**** Please insert the Required Fields below ****<br />";

if($fname == "")
{
$message = $message . "Enter First name<br/>";
}
if($lname == "")
{
$message = $message . "Enter Last name<br/>";
}

if($specialization == 0)
{
$message = $message . "Select Your Job<br />";
}
if($district == 0)
{
$message = $message . "Select Your District<br />";
}
if($village == 0)
{
$message = $message . "Select Your Village<br />";
}
if($email == "")
{
$message = $message . "Enter Email Adress<br/>";
}
if ($username == "") {
$message = $message . "Enter User Name<br/>";
}

if($pass1 == "")
{
$message = $message . "Enter password<br/>";
}
if($pass2 == "")
{
$message = $message . "rechek the password <br/>";
}

}

else if($pass1!=$pass2){
$message = "your password do not match!";
}else{
//securing the data
$username = preg_replace("#[^0-9a-z]#i","",$username);
$fname = preg_replace("#[^0-9a-z]#i","",$fname);
$lname = preg_replace("#[^0-9a-z]#i","",$lname);
//$pass1 = sha1($pass1);

$email = mysql_real_escape_string($email);
// checking for duplicate
$user_query = mysql_query("SELECT user_name FROM user WHERE user_name = '$username'LIMIT 1") or die("could not check the username");
$count_username = mysql_num_rows($user_query);

$email_query = mysql_query("SELECT email_address FROM user WHERE email_address = '$email'LIMIT 1") or die("could not check the email");
$count_email = mysql_num_rows($email_query);

if($count_username > 0){
$message = " your username is alredy in use";
}elseif($count_email > 0){
$message = "your email is alredy in use";
}else{

$query = mysql_query("INSERT INTO user(user_name, first_name, last_name, address, district_name, village_name, birth_date, email_address, specialization_name, password, registered_date)VALUES('$username', '$fname', '$lname', '$country', '$district', '$village', '$bdate', '$email', '$specialization', '$pass1', now())")or die("could not insert data");

$message = "you have now been registered";
//from the social website

$getid = mysql_fetch_array($query);
$_SESSION['user_id'] = $getid['user_rid'];
$_SESSION['login'] = 'true';
$_SESSION['login_user'] = $getid['username'];
header("Location: home.php");
}
}
}





?>

<!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=utf-8" />
<title>Register Page</title>
<link href="style/stylesheet.css" rel="stylesheet" type="text/css" />
</head>

<body>



<table width="200" border="0" align="center">
<tr>
<tr>
<td>
<div id="visitorHeader"></div>
<img src="web_header copy.jpg" alt="visitor header" /></td>
</tr>
<tr>
<td>
<h2 class="registerTitle">Registration Fields</h2>
<h4 class="registerTitle">Sign Up Today....</h4>
<!-- <div class="container center"> -->
<p style="color:#FF0000" align="center"><?php print("$message")?></p>
</td>

<tr>
<table width="680" border="0" align="center">
<form action="register.php" method="post">



<td>
</span><label for="firstname"><span class="Fields">First Name</span> <span class="requiredField">*</span></label></td>
<td>
<input type="text" name="fname" placeholder="Firstname" /></td>

<td><span class="Fields">Last Name</span><span class="requiredField">*</span></label></td>

<td><input type="text" name="lname" placeholder="Lastname" /></td>



<tr>
<td><label for="birthdate" class="Fields">Birth Date </label></td>
<td><input type="date" name="birthdate" value= "YYYY_MM_DD" onfocus="if (this.value == 'YYYY_MM_DD') {this.value = '';}" onblur="if (this.value == '') {this.value = 'YYYY_MM_DD';}" />
<br /></td>
<td class="Fields"><label for="phonenumber">Phone Number</label></td>
<td><input type="number" name="phonenumber" value="000-0-000 000" onfocus="if (this.value == '000-0-000 000') {this.value = '';}" onblur="if (this.value == '') {this.value = '000-0-000 000';}" /></td>

</tr>

<tr>
<td class="Fields"><label for="country">Country</label></td>
<td><select name="country" class="select">
<option value="0">-- Select Your Country --</option>
<?php countryQuery(); ?>
</select></td>
<td class="Fields"><label for="specialisation">Specialisation <span class="requiredField">*</span></label></td>
<td><select name="specialization" class="select">
<option value="0">-- Select Your Specialization --</option>

<?php specializationQuery(); ?>
</select></td>

</tr>

<tr>
<td class="Fields"><label for="district">District<span class="requiredField">*</span></label></td>
<td><select name="district" class="select">
<option value="0">-- Select Your District --</option>

<?php districtQuery(); ?>
</select></td>

<td class="Fields"><label for="village">Village<span class="requiredField">*</span></label></td>
<td><select name="village" class="select">
<option value="0">-- Select Your Village --</option>

<?php villageQuery(); ?>
</select></td>

</tr>

<tr>
<td class="Fields"><label for="email">Email Adress<span class="requiredField">*</span></label></td>
<td><input type="text" name="email" placeholder="Email Adress" />
<br /></td>
<td><label for="username"><span class="Fields">User Name</span> <span class="requiredField">*</span></label></td>
<td><input type="text" name="username" placeholder="Username" />
<br /></td>

</tr>

<tr>
<td class="Fields"><label for="password">Password<span class="requiredField">*</span></label></td>
<td><input type="password" name="pass1" placeholder="Password" />
<br /></td>
<td class="Fields"><label for="password2">Re_Password<span class="requiredField">*</span></label></td>
<td><input type="password" name="pass2" placeholder="Validate Password" />
<br /></td>

</tr>

<tr>
<td></td>
<td></td>
<td><input type="submit" value="Register"/></td>
<td></td>
</tr>

</form>
</table>
<!--</div>-->

</tr>
<tr>
<td><?php require_once('footer.php'); ?></td>
</tr>
</tr>
</tr>
</table>
</body>
</html>
<?php ob_end_flush(); ?>

Answer

It may be happening because your user table contain a column of email_address which has been defined as a KEY column on that table. And, when you run this code:

$about_query = mysql_query("INSERT INTO user (interest) VALUES ('$aboutme')")or die(mysql_error());

It will give you an error because value for email address is not given and it must be specified. I think that you must not use insert query here instead use a update query which you have already done in the statement below:

$edit_query = mysql_query("UPDATE user SET first_name= '$fname', last_name= '$lname', address= '$country', specialization_name= '$spec', interest = '$aboutme' WHERE user_id = '$userid'") or die(mysql_error());

So, either remove $about_query or make it an update statement.

Comments