I have a form that allows me to enter a user into my database. However, whenever I click on submit I receive the query failed error message. Below is my the form I have built:
register-admin.php
<form id="resgisterform" name="registerform" method="post" action="register-admin-exec.php">
<table width="300" border="0" align="center" cellpadding="2" cellspacing="0">
<tr>
<th>Username </th>
<td><input name="username" type="text" class="textfield" id="username" /></td>
</tr>
<tr>
<th>First Name </th>
<td><input name="first_name" type="text" class="textfield" id="first_name" /></td>
</tr>
<tr>
<th>Last Name </th>
<td><input name="last_name" type="text" class="textfield" id="last_name" /></td>
</tr>
<tr>
<th>Muvdigital Email </th>
<td><input name="muvdigital_email" type="text" class="textfield" id="muvdigital_email" /></td>
</tr>
<tr>
<th>Personal Email </th>
<td><input name="personal_email" type="text" class="textfield" id="personal_email" /></td>
</tr>
<tr>
<th>Title </th>
<td><input name="title" type="text" class="textfield" id="title" /></td>
</tr>
<tr>
<th>Address 1 </th>
<td><input name="address_1" type="text" class="textfield" id="address_1" /></td>
</tr>
<tr>
<th>Address 2 </th>
<td><input name="address_2" type="text" class="textfield" id="address_2" /></td>
</tr>
<tr>
<th>City </th>
<td><input name="city" type="text" class="textfield" id="city" /></td>
</tr>
<tr>
<th>State </th>
<td><input name="state" type="text" class="textfield" id="state" /></td>
</tr>
<tr>
<th>Zip Code </th>
<td><input name="zip" type="text" class="textfield" id="zip" /></td>
</tr>
<tr>
<th>Phone </th>
<td><input name="phone" type="text" class="textfield" id="phone" /></td>
</tr>
<tr>
<th>Password </th>
<td><input name="password" type="password" class="textfield" id="password" /></td>
</tr>
<tr>
<th>Confirm Password </th>
<td><input name="cpassword" type="password" class="textfield" id="cpassword" /></td>
</tr>
<tr>
<td> </td>
<td><input type="submit" name="Submit" value="Register" /></td>
</tr>
</table>
</form>
The values from this form are then brought over to the register-admin-exec.php page which is below
<?php
//Start session
session_start();
//Include database connection details
require_once('config.php');
//Array to store validation errors
$errmsg_arr = array();
//Validation error flag
$errflag = false;
//Function to sanitize values received from the form. Prevents SQL injection
function clean($str) {
$str = @trim($str);
if(get_magic_quotes_gpc()) {
$str = stripslashes($str);
}
return mysql_real_escape_string($str);
}
//define and validate the post values
/*if (isset ($_POST['admin_id']) && !empty ($_POST['admin_id'])) {
$admin_id = $_POST['admin_id'];
} else {
echo 'Error: admin id not provided!';
}*/
if (isset ($_POST['username']) && !empty ($_POST['username'])) {
$username = clean($_POST['username']);
} else {
echo 'Error: username not provided!';
}
if (isset ($_POST['first_name']) && !empty ($_POST['first_name'])) {
$first_name = clean($_POST['first_name']);
} else {
echo 'Error: first name not provided!';
}
if (isset ($_POST['last_name']) && !empty ($_POST['last_name'])) {
$last_name = clean($_POST['last_name']);
} else {
echo 'Error: last name not provided!';
}
if (isset ($_POST['muvdigital_email']) && !empty ($_POST['muvdigital_email'])) {
$muvdigital_email = clean($_POST['muvdigital_email']);
} else {
echo 'Error: muvdigital email not provided!';
}
if (isset ($_POST['personal_email']) && !empty ($_POST['personal_email'])) {
$personal_email = clean($_POST['personal_email']);
} else {
echo 'Error: personal email not provided!';
}
if (isset ($_POST['title']) && !empty ($_POST['title'])) {
$title = clean($_POST['title']);
} else {
echo 'Error: title not provided!';
}
if (isset ($_POST['phone']) && !empty ($_POST['phone'])) {
$phone = clean($_POST['phone']);
} else {
echo 'Error: phone not provided!';
}
if (isset ($_POST['address_1']) && !empty ($_POST['address_1'])) {
$address_1 = clean($_POST['address_1']);
} else {
echo 'Error: address 1 not provided!';
}
$address_2 = clean($_POST['address_2']);
if (isset ($_POST['city']) && !empty ($_POST['city'])) {
$city = clean($_POST['city']);
} else {
echo 'Error: city not provided!';
}
if (isset ($_POST['state']) && !empty ($_POST['state'])) {
$state = clean($_POST['state']);
} else {
echo 'Error: state not provided!';
}
if (isset ($_POST['zip']) && !empty ($_POST['zip'])) {
$zip = clean($_POST['zip']);
} else {
echo 'Error: zip not provided!';
}
if (isset ($_POST['password']) && !empty ($_POST['password'])) {
$password = clean($_POST['password']);
} else {
echo 'Error: password not provided!';
}
if (isset ($_POST['cpassword']) && !empty ($_POST['cpassword'])) {
$cpassword = clean($_POST['cpassword']);
} else {
echo 'Error: confirm password not provided!';
}
//encrypt the password
$salt = sha1($username);
$password = sha1($salt.$password);
//Check for duplicate login ID
if($username != '') {
$qry = "SELECT * FROM members WHERE username='".$username."'";
$result = mysql_query($qry);
if($result) {
if(mysql_num_rows($result) > 0) {
$errmsg_arr[] = 'Login ID already in use';
$errflag = true;
}
@mysql_free_result($result);
}
else {
die("Query failed");
}
}
//If there are input validations, redirect back to the registration form
if($errflag) {
$_SESSION['ERRMSG_ARR'] = $errmsg_arr;
session_write_close();
header("location: register-admin.php");
exit();
}
//Create INSERT query
$qry = "INSERT INTO admins (
'username',
'password',
'first_name',
'last_name',
'muvdigital_email',
'personal_email',
'titles',
'phone',
'address_1',
'address_2',
'city',
'state',
'zip')
VALUES (
'$username',
'$password',
'$first_name',
'$last_name',
'$muvdigital_email',
'$personal_email',
'$title',
'$phone',
'$address_1',
'$address_2',
'$city',
'$state',
'$zip')";
$result = mysql_query($qry);
//Check whether the query was successful or not
if($result) {
header("location: register-success.php");
exit();
}else {
die("Query failed $qry");
}
?>
I know it is failing at my insert statement because I have tried commenting out the previous validation check for duplicate login ids and it still fails. I cannot figure out why my insert statement isn't working. After echoing the $qry, i get
INSERT INTO admins ( 'username', 'password', 'first_name', 'last_name', 'muvdigital_email', 'personal_email', 'titles', 'phone', 'address_1', 'address_2', 'city', 'state', 'zip') VALUES ( 'johndoe', '7afbb2186cf26d85bdfe948d367fb6baa6739283', 'john', 'doe', 'john.doe@muvdigital.com', 'jdoe@gmail.com', 'intern', '6024013776', '18b main st', 'apt 12', 'Hooksett', 'NH', '03106')
so the $_POST function is working. I have tried manually entering the insert statement at the command line and i receive ERROR 1054 (42S22): Unknown column 'johndoe' in 'field list'.
The admin_id is an auto_increment field which is why I have commented it out (and I have tried uncommenting it and manually creating an admin_id, which stil does not work)
Anyone have an idea as to why this is happening?
You have quoted all your column names with single quotes, which is incorrect. They should be unquoted, except if you have used a MySQL reserved keyword (which you have not)
// Column names are unquoted, but VALUES() should be quoted.
$qry = "INSERT INTO admins (
username,
password,
first_name,
last_name,
muvdigital_email,
personal_email,
titles,
phone,
address_1,
address_2,
city,
state,
zip)
VALUES (
'$username',
'$password',
'$first_name',
'$last_name',
'$muvdigital_email',
'$personal_email',
'$title',
'$phone',
'$address_1',
'$address_2',
'$city',
'$state',
'$zip')";
$result = mysql_query($qry);