PHP MySQLi INSERT Query not working

Question

Continuing from my previous 2 posts 'PHP and MYSQL database connection and table creation only once' and 'Undefined variable in php registration from', i dont have any errors until I use below code to INSERT the POSTED DATA into my database. As I'm new to MYSQLI I'm not able to understand my errors and debug them. PLZ HELP..

//code for input field validation

if (!empty($error)) //send to Database if there's no error '
    {
    $query = $mysqli->prepare("INSERT INTO users ( Name, Username, Password, Email) VALUES (?, ?, ?, ?, ?)");
    $query ->bind_param('sssdi',$fullname, $username, $password2, $email);
    $stmt->execute();
    $newId = $stmt->insert_id;
    $stmt->close();
    //$mysqli->query($query);
    $result = @mysqli_query($query);
    if (!$result) {
    $error['ack'] = 'Failed to Register Your Account...!';
    } else {
    $error['ack2'] = 'Account Registered Successfully...!';
    }
    //$mysqli->close();//Close the DB Connection

My REGISTRATION.PHP form is available in the 'Undefined variable in php registration from' link.

This is my complete code Memberid)); "; mysql_select_db('USERS'); $retval = mysql_query($sql, $connect); if (!$retval) { die('COULD NOT CREATE TABLE\n: ' . mysql_error()); } ; mysql_close($connect); //end of DB_connection

$fullname = "";
$username = ""; 
$password = "";
$password2 = "";
$email = "";

if (isset($_POST['submit'])) {
session_start();
$error = array(); //Declare an array to store error messages  

//validation for fullname
if (!empty($_POST['fullname'])) {
    $fullname = mysql_real_escape_string(trim($_POST['fullname']));
} else {
    $error['fullname'] = 'Enter Fullname...';
}

//validation for username
if (!empty($_POST['username'])){
    $username = mysql_real_escape_string(trim($_POST['username']));
} else {
    $error['username'] = 'Enter Username...';
}

//Validation for password and confirm password
if (!empty($_POST['password'])) {
    if ($_POST['password'] != $_POST['password2']) {
        $error['password2'] = 'Passwords do not match...';
    } else {
        $password2 = mysql_real_escape_string($_POST['password']);
    }
    } else {
        $error['password'] = 'Enter Password...';
}

//validation for e-mail
if (!empty($_POST['email'])) {
    $email = mysql_real_escape_string($_POST['email']);
} else {
    $error['email'] = 'Enter your Email...';
} 

if (!empty($error)) //send to Database if there's no error '
{
$query = $mysqli->prepare("INSERT INTO users ( Name, Username, Password, Email) VALUES        (?, ?, ?, ?, ?)");
$query ->bind_param('sssdi',$fullname, $username, $password2, $email);
$stmt->execute();
$newId = $stmt->insert_id;
$stmt->close();
//$mysqli->query($query);
$result = @mysqli_query($query);
if (!$result) {
$error['ack'] = 'Failed to Register Your Account...!';
} else {
$error['ack2'] = 'Account Registered Successfully...!';
}


}//end of main if
?>
<html>
<form action="register.php" method="post" id="user_registration">
<p id="head">Create Account</p>

<input type="text" id="fullname" name="fullname"/>
**<span class="error" id="fullname"><?php echo $error_name; ?></span>**

<input type="text" id="username" name="username"/>
<span id="availability_status"></span>
**<span class="error" id="username"><?php echo $error_username; ?></span>**

<input type="password" id="password" name="password"/> 
**<span class="error" id="password"><?php echo $error_password; ?></span>**

<input type="password" id="password2" name="password2"/>
**<span class="error" id="divCheckPasswordMatch"><?php echo $error_password2;?></span>**

<input type="email" id="email" name="email"/>
**<span class="error" id="email"><?php echo $error_email; ?></span>**

<p class="submit">
<button type="submit"id="submit" name="submit" value="Register”>Register</button>
</p>

What is the actual error message you get? P.s. Linking to other topics is not that helpful, just post all the required code. — Gerben Jacobs, Mar 25 '14 at 14:22
And Y is this 'sssdi' used in bind_param(). any particular use of it..? — Charan Balse, Mar 25 '14 at 14:24
You'd see inmediately what the problem is if you implemented error handling for your database calls... — jeroen, Mar 25 '14 at 14:25
In fact, you should try a **tutorial** first. And then try your own code, you have to use exact examples from tutorial. — Your Common Sense, Mar 25 '14 at 14:28
@CharanBalse you don't have integer value in your query then why you passing 'sssdi'. it should be 'ssss'. — Ram Sharma, Mar 25 '14 at 14:37
and ya is there any good website or book from which i can learn 'PHP Data Objects and MySQLi' easily and thoroughly.. — Charan Balse, Mar 25 '14 at 14:40
@Ram Sharma Can u just update my INSERT query and inform me.. — Charan Balse, Mar 26 '14 at 15:22

score 1 · Answer 1 · answered Aug 12 '16 at 02:40

The problem with your query is that the values dont match. If you look at it you are only inserting 4 values but you have 5 ? for 5 values. you repeat that mistake when you bound the parameter "sssdi" represents 5. So either you forgot to put a variable in or just remove the extra and it should be fine. As far as i can tell all 4 variables should be strings so changing it to the query below should work.

$query = $mysqli->prepare("INSERT INTO users ( Name, Username, Password, Email) VALUES (?, ?, ?, ?)");
        $query ->bind_param('ssss',$fullname, $username, $password2, $email);
        $stmt->execute();

Ram Sharma · Accepted Answer · 2014-03-26T06:01:58.063

0

change your code something like this

$query = $mysqli->prepare("INSERT INTO users (Name, Username, Password, Email) VALUES (?, ?, ?, ?)");
 $query->bind_param("ssss", $fullname, $username, $password2, $email);
 $query->execute();

also double check your field name's are with first capital letter or not, if yes then change the field name as per your table.

edited Mar 26 '14 at 06:01

answered Mar 25 '14 at 14:30

Ram Sharma

8,676
7
43
56

$sql = "CREATE TABLE USERS( " . "Memberid int(10) NOT NULL AUTO_INCREMENT, " . "Name varchar(100) NOT NULL, " . "Username varchar(20) NOT NULL, " . "Password varchar(10) NOT NULL, " . "Email varchar(20) NOT NULL, " . "Activation varchar(40) DEFAULT NULL, " . "Status int(1) NOT NULL DEFAULT '1', " . "PRIMARY KEY (`Memberid`)); "; – Charan Balse Mar 25 '14 at 14:41
Getting an error when i tried ur code: 'Parse error, unexpected $end' – Charan Balse Mar 25 '14 at 14:51
Status is required field, include this in query as well and use "i" instead of "s" in bind_param. – Ram Sharma Mar 26 '14 at 06:04

PHP MySQLi INSERT Query not working

2 Answers2