Unable to track the exact location of error when stmt-->execute() executes

Question

I have a table named as personal_detail. A form is used to fill this table with entries made by users. The query doesn't executes successfully and I can't find the exact location of the error in query. The message I'm getting after pressing the submit button is NOT CREATED. That is provided by me at the end of php file. if stmt-->execute() returns false.

This is the personal_detail table structure:

m/BvbLS.jpg

This is the form code:

<form action="personal.php" method="post">
    Name: <input name="name" type="text" size="20" maxlength="40" ><br>
    CNIC : <input name="cnic" type="text" size="20" maxlength="15"><br>
    Date: <input name="booking-date" type="date" size="20" ><br>
    Ocassion: <select name="ocassion" size="1">
        <option value="">Barat</option>
        <option value="">Walima</option>
        <option value="">option3</option>
        <option value="">option4</option>
        <option value="">option5</option>
        <option value="">option6</option>
    </select><br>
    Address:<input name ="address" type="text" size="20" maxlength="50"><br>
    Phone-No:<input name="phone-no" type="text" size="20" maxlength="11"><br>
    Bride-Mobile:<input name="bride-mobile" size="20" maxlength="11"><br>
    Groom-Mobile:<input name="groom-mobile" size="20" maxlength="11"><br>
    Family-Mobile:<input name="family-mobile" size="20" maxlength="11"><br>
    E-mail:<input name="email" type="text" size="20" maxlength="30"><br>
    Who may i thank for refering you?:<input name="refering" type="text" size="20" maxlength="40"><br>
    Do you provide consent to share images on our official web page:<br>
    <input type="radio" name="share" value="Yes">Yes <br>
    <input type="radio" name="share" value="No">No<br>
    If yes:<br>
    With identity: <br><input type="radio" name="permission" value="Yes">Yes<br>
    <input type="radio" name="permission" value="No">No<br>
    <input class="btn btn-primary btn-large" type="submit" value="Submit" name="Submit-Personal">
    <input class="btn btn-primary btn-large" type="reset" value="Reset">

and this is the php file:

$server="localhost";
$user="root";
$password="";
$database="camouflage_studio";

$con = mysqli_connect($server,$user,$password,$database);
if (mysqli_connect_errno()){
    echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

// prepare and bind
$stmt = $con->prepare("insert into personal_detail (Name, CNIC, Date, Ocassion, Address, Phone_No, Bride_Mobile, Groom_Mobile, Family_Mobile,EMail,Referring,Share,Permission) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");

$stmt->bind_param("ssssssiiissss", $name, $cnic, $date, $ocassion, $daddress, $phoneno, $bridemobile, $groommobile, $familymobile, $email, $refering, $share, $permission);

$name = $_POST['name'];
$cnic = $_POST['cnic'];
$date = $_POST['booking-date'];
$ocassion = $_POST['ocassion'];
$address = $_POST['address'];
$phoneno = $_POST['phone-no'];
$bridemobile = $_POST['bride-mobile'];
$groommobile = $_POST['groom-mobile'];
$familymobile = $_POST['family-mobile'];
$email = $_POST['email'];
$refering = $_POST['refering'];
$share = $_POST['share'];
$permission = $_POST['permission'];

$stmt->execute();
$result = mysqli_stmt_execute($stmt);
if($result)
    echo "New records created successfully";
else
    echo "not created";

// close connection
$stmt->close();
$con->close();

Why execute the stmt twice? Also, you should be using backticks on column names -- especially the ones that are mysql KEYWORDS like `Date`. Finally, you are doing no error checking in your code. If you want to debug your code, you are going to have some conditional error checking in place. — mickmackusa, May 28 '17 at 12:14
@MuhammadAatif perhaps have a look at the error checking on this other prepared statement answer I've posted. https://stackoverflow.com/a/43784433/2943403 Let us know what error message you get. — mickmackusa, May 28 '17 at 12:22
Your address is mistyped to daddress. Change that hope the query will work properly — Exprator, May 28 '17 at 13:05
Thanks @KishanKumar I did as mysqli_error($con); and then i removed many errors one by one as they were raised. — Muhammad Aatif, May 28 '17 at 14:06
@mickmackusa you were right. i removed one stmt. i don't know about backtickts. — Muhammad Aatif, May 28 '17 at 14:07
I'm curious about assigning the values *after* the bind_parameter. seems to me that it would be inserting blanks or nulls. — Tim Morton, May 28 '17 at 21:56
@TimMorton No, this is how is meant to be: http://php.net/manual/en/mysqli-stmt.bind-param.php — mickmackusa, May 28 '17 at 22:29
@mickmackusa Thanks for the correction. Quoth the manual, "[and oh, BTW,] Note that mysqli_stmt_bind_param() requires parameters to be passed by reference". I hadn't realized passing by reference had become so integrated. Guess I have some catching up to do http://php.net/manual/en/language.references.pass.php — Tim Morton, May 29 '17 at 02:05

score 0 · Accepted Answer · answered May 28 '17 at 22:48

Here is a code block that will perform a full battery of error checks.

You were using a mixture of procedural and object-oriented syntax; I've set everything to object-oriented.
I have backticked the table name and all columns (though I only really recommended Date to be backticked.
I have capitalized all mysql keywords.
I have eliminated the unnecessary variable declarations that only pass values from $_POST to bind_param().
execute() is hiding all the way at the end of the conditional starting with bind_param(). The reason/effect is that if bind_param() returns false, execute() won't even be called and the error message will be displayed.
Of course, when your code "goes live" be sure not to echo any of these error messages -- only display them while debugging.

if(!$con=new mysqli($server,$user,$password,$database)){
    echo "Connection Error: ",$con->connect_error;  // do not echo when live
}elseif($stmt=$con->prepare("INSERT INTO `personal_detail` (`Name`,`CNIC`,`Date`,`Ocassion`,`Address`,`Phone_No`,`Bride_Mobile`,`Groom_Mobile`,`Family_Mobile`,`EMail`,`Referring`,`Share`,`Permission`) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)")){
    if($stmt->bind_param('ssssssiiissss',$_POST['name'],$_POST['cnic'],$_POST['booking-date'],$_POST['ocassion'],$_POST['address'],$_POST['phone-no'],$_POST['bride-mobile'],$_POST['groom-mobile'],$_POST['family-mobile'],$_POST['email'],$_POST['refering'],$_POST['share'],$_POST['permission']) && $stmt->execute()){
        echo "New record created successfully"; // New ID# is ",$stmt->insert_id;
    }else{
        echo "New record not created";
        echo "Statement Error: ",$stmt->error;  // do not echo when public
    }
    $stmt->close(); 
}else{
    echo "Prepare Error: ",$con->error;  // do not echo when public
}
$con->close();

Your code is more efficient. i did remove the extra variables from my code. and if there an error occures I poped it as: echo mysqli_error($con); does it means i'm echoing errors publically? if yes, what are the negative aspects of doing like this in the 'live code'? — Muhammad Aatif, May 29 '17 at 11:57
@MuhammadAatif You **MUST** comment-out or delete those error messages! Making these messages public will give sneaky/nasty people information that may allow them to compromise your work. Just replace them with some very, very general text like `Syntax Error - Please contact web support / Try again later / whatever`. When something does go wrong, you don't want your users wondering if it was something wrong on their device. — mickmackusa, May 29 '17 at 12:08

Unable to track the exact location of error when stmt-->execute() executes

1 Answers1