Record not entering in database

[itsumarnazir]

I am facing very strange issue which is that record is not entering in database when I do not entered data in fields which are optional. Age is option field in my form and below image of database table also showing age can be null. (Remarks is also optional, and if i do no entered value in remarks field, then record successfully entered in database table, where as in age1, when I left it blank, the record did not store in database ) here is my form code

 <div class="row"> <div class="col-lg-4">
		 <div class="form-group"> <label>Registration Date:</label> <input type="date" value = "<?php echo date("Y-m-d"); ?>" name="regdate" id="regdate"  class="form-control" readonly style="background-color: #3fbbc0;">
		 </div> </div> </div>
						
				
		<div class="row"> <div class="col-lg-4"> <div class="form-group">
		<label>MR No:</label> <input type="text" name= "patientno"  id= "patientno" class="form-control"  value="<?php echo $number; ?>" readonly style="background-color: #3fbbc0;">
		</div> </div> 
<div class="col-lg-4"> <div class="form-group">
		<label>Patient Name:</label> <input type="text" name= "name2"  id= "name2" class="form-control" Required >
		</div> </div>
<div class="col-lg-4"> <div class="form-group">
		<label>Last Name :</label> <input type="text" name= "rel2"  id= "rel2" class="form-control" Required>
		</div> </div></div>


<div class="row"> <div class="col-lg-4"> <div class="form-group">
		<label>Age:</label> <input type="text" name= "age1"  id= "age1" class="form-control">
		</div> </div> 
<div class="col-lg-4"> <div class="form-group">
		<label>Gender:</label> <Select name="gender"  id='gender' class="form-control" Required>

<option value="">Select Gender</option>
<?php
include('db.php');
$sql = mysqli_query($con,"SELECT * FROM gender");
while($row=mysqli_fetch_array($sql))
{
echo '<option value="'.$row['gen1'].'">'.$row['gen1'].'</option>';
} ?>



</select>
		</div> </div>



	

 <div class="col-lg-4"> <div class="form-group">
		<label>Remarks :</label> <input type="text" name= "remarks"  id= "remarks" class="form-control">
		</div> </div></div>


<div class="row"> <div class="col-lg-4"> <div class="form-group">
		<label>Mobile:</label> <input type="text" data-inputmask="'mask': '0399-99999999'" name= "mble"  id= "mble" class="form-control" Required maxlength = "12">
		   <script>
    $(":input").inputmask();

   </script></div> </div>    


<div class="col-lg-4"> <div class="form-group">
		<label>Operator:</label><input type="text" name="abc" id="abc"  readonly class="form-control" value = "<?php echo htmlspecialchars($_SESSION["username"]); ?>"  style="background-color: #3fbbc0;">
	
		</div> </div></div>
<div class="row"> <div class="col-lg-4"> <div class="form-group">
		<input type="submit" name="submit" id="submit" value="Submit">
		</div> </div> </div>	    

here is database file form11.php

<?php
    // getting all values from the HTML form
    if(isset($_POST['submit']))
    {
        $regdate = $_POST['regdate'];
        $patientno = $_POST['patientno'];
        $name2 = $_POST['name2'];
  	$rel2 = $_POST['rel2'];
	$age1 = $_POST['age1'];
        $gender = $_POST['gender'];     	
	      
        $remarks = $_POST['remarks'];	
	$mble = $_POST['mble'];       
  	$abc = $_POST['abc']; 	
  	
	
	
    }

    // database details
    $host = "localhost";
    $username = "username";
    $password = "password";
    $dbname = "dbName";

    // creating a connection
    $con = mysqli_connect($host, $username, $password, $dbname);

    // to ensure that the connection is made
    if (!$con)
    {
        die("Connection failed!" . mysqli_connect_error());
    }

    // using sql to create a data entry query
    $sql = "INSERT INTO iap2 (regdate, patientno, name2, rel2, age1, gender,    remarks,  mble, uname1)
 VALUES ('$regdate', '$patientno', '$name2', '$rel2' ,'$age1', '$gender',    '$remarks', '$mble',  '$abc')"; 

   
  
    // send query to the database to add values and confirm if successful
    $rs = mysqli_query($con, $sql);
    if($rs)
    {
        echo "";
    }
  

    // close connection
    mysqli_close($con);

?>

[DaveMaxwell]

Without seeing the entire page, it’s one of two things:

1. You’re not using a POST in your form element.
2. You’re getting an error in the execution of the INSERT.

Add an echo in the first if statement, and an else to the If statement after the else and see which is the case.

But doing an insert like this is NEVER a good idea because you can’t trust what people will put into the forms.

@itsumarnazir

   // using sql to create a data entry query
    $sql = "INSERT INTO iap2 (regdate, patientno, name2, rel2, age1, gender,    remarks,  mble, uname1)
 VALUES ('$regdate', '$patientno', '$name2', '$rel2' ,'$age1', '$gender',    '$remarks', '$mble',  '$abc')"; 

Get into the habit of using prepared statements
instead. They prevent SQL injection by nature, which is inherently more secure.

[Thallius]

@DaveMaxwell

Get into the habit of using prepared statements
instead. They prevent SQL injection by nature, which is inherently more secure.

But at the end they do not prevent from XSS attacks which are still possible if you store something like

"<srcipt alert("XSS Attack")>

in your database and show it later on your HTML page.

[DaveMaxwell]

@Thallius

But at the end they do not prevent from XSS attacks

No, but they’re infinitely safer than just directly inserting them into a table…

[Thallius]

@DaveMaxwell

No, but they’re infinitely safer than just directly inserting them into a table…

Sure, I just want to say that starting to develop secure code can lead very fast into an endless story

[droopsnoot]

Are you entering a duplicate mobile phone number? I noticed you don’t trap the error that will be returned if you do.

How far through your code does it get when you debug it? If you echo $sql and then paste the query directly into phpmyadmin, does it work then, or does it give you a useful error message?

[itsumarnazir]

i mentioned my column unique in database, value is nto entered for duplicate record, but it also not generting error message, i want to generate error message

[mabismad]

By definition all set $_POST form fields are strings, regardless of what value they contain. Only unchecked checkbox or radio fields won’t be set and would be null values. An empty text type form field will be an empty string and your NULL = Yes setting will have no effect.

The only ways you can cause a null value to get used for a column are -

1. use the sql NULL keyword or the sql DEFAULT keyword in the sql statement for the value, which your current use of putting the variables directly into the sql query statement surrounded by single-quotes cannot accomplish. You would need to dynamically build the sql query statement and put the literal NULL or DEFAULT keyword into the sql query syntax.
2. dynamically build the sql query statement leaving out the column and its value, which will cause the defined default value to be used.
3. use a (true) prepared query (this won’t work with a pdo emulated prepared query), where a null value will be passed through to the database server.

Therefore, something outside the information you have posted in this thread is occurring.

You ALWAYS need error handling for statements that can fail. For database statements that can fail - connection, query, exec, prepare, and execute, the simplest way of adding error handling, without adding code at each statement, is to use exceptions for errors (this is the default setting now in php8+) and in most cases simply let php catch and handle the database exceptions, where php will use its error related settings to control what happens with the actual error information (database statement errors will ‘automatically’ get displayed/logged the same as php errors.)

What php version are you using? If it is 8+, unless you turn off the use of exceptions for errors (not recommended), any database statement error handling failure conditional logic you have now won’t ever get executed, since execution transfers to the nearest correct type of exception handling, or to php if there is no exception handling in your code.

Do you have php’s error_reporting set to E_ALL and dispaly_errors set to ON, preferably in the php.ini on your development system, so that php will help you by reporting and displaying all the errors it detects? Stop and start your web server to get any changes made to the php.ini to take effect and use a phpinfo() statement in a .php script to confirm that the settings actually took effect.

Lastly, ALL the post method form processing code needs to be inside the conditional statement testing if a post method form was submitted.

[mabismad]

@itsumarnazir

i mentioned my column unique in database, value is nto entered for duplicate record, but it also not generting error message, i want to generate error message

The general way of handling this is to test for a duplicate index error number (1062) in the try/catch exception handler for the INSERT query (which you will need to add) and setup a message for the user letting them know that the value already exists. For all other error numbers, just rethrow the exception and let php handle it.

[itsumarnazir]

but the unique column is not the primary key, will it still work ? I am using auto increment id as primary key where as unique key is mobile number

[mabismad]

Slightly off topic, but in your earlier threads with database queries, you were using prepared queries. Why are you now putting php variables/values directly into the sql query statement, where any sql special character in a value can break the sql query syntax, which is how sql injection is accomplished?

If this is because of how difficult it is to use the mysqli extension with prepared queries, this would be a good time to switch to the much simpler and more modern PDO extension. After you make the PDO, connection (if you need to see typical PDO connection code, someone can post it), the INSERT query you have shown in this thread would be -

$sql = "INSERT INTO iap2 (regdate, patientno, name2, rel2, age1, gender, remarks, mble, uname1)
 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
$stmt = $pdo->prepare($sql);
$stmt->execute([ $regdate, $patientno, $name2, $rel2, $age1, $gender, $remarks, $mble, $abc ]);

[mabismad]

@itsumarnazir

but the unique column is not the primary key, will it still work ?

A. Yes it will work.
B. You could always try the things that have been suggested and observe the result, so that you would learn for yourself if the stated method works.