MYSQLI INSERT query

We will insert one record to our student table.
<?Php
require "config.php";// Database connection

$id=50;
$name = 'my_name';
$class='Three';
$mark=70;
$sex='male';
$query="INSERT INTO student (id,name,class,mark,sex) values($id,'$name','$class','$mark','$sex')";
if($connection->query($query)){
echo "
No of records inserted : ".$connection->affected_rows; }else{ echo $connection->error; } ?>
We will get error message if the query fails to execute.

MySQLI database connection file

With Parameters by using bind_param()
<?Php
require "config.php";// Database connection
//////////////////////////////
$id=50;
$name = 'my_name';
$class='Three';
$mark=70;
$sex='male';
$query="INSERT INTO student (id,name,class,mark,sex) values(?, ?,?,?,?)";
$stmt=$connection->prepare($query);
if($stmt){ 
$stmt->bind_param("issds", $id, $name, $class,$mark,$sex);
if($stmt->execute()){
echo "
No of records inserted : ".$connection->affected_rows; }else{ echo $connection->error; } }else{ echo $connection->error; } ?>
We can change id field to auto increment type. Now we need not enter value for id. We will get the value for id after the record is added by using insert_id
<?Php
require "config.php";// Database connection
//////////////////////////////
$name = 'my_name';
$class='Three';
$mark=70;
$sex='male';
$query="INSERT INTO student (name,class,mark,sex) values( ?,?,?,?)";
$stmt=$connection->prepare($query);
if($stmt){ 
$stmt->bind_param("ssds",  $name, $class,$mark,$sex);
if($stmt->execute()){
echo "<br>No of records inserted : ".$connection->affected_rows;
echo "<br>Insert ID : ".$connection->insert_id;

}else{
echo $connection->error;
}
}else{
echo $connection->error;
}
?>

Quick Add Without parameter

We must sanitize our variable $field_value before to prevent injection attack.
if($connection->query("insert into table_name (field_name) values('$field_value')")){
echo " Records added : ".$connection->affected_rows;
}else{
	echo " Records not added  ";
}

Adding date to MySQL

To store date in mysql date field we have to use YYYY-mm-dd format. This is how we will convert to required format first.
$date = new DateTime($dt);
$dt=$date->format('Y-m-d');
Here $dt is the variable storing date field value.

MySQLi data variable can be checked like string.
$stmt->bind_param('s', $dt);

To add null to a date field

If you are receving date field as blank from a form then use this PHP script.
if(strlen($dt) >2){
$date = new DateTime($dt_approved);
$dt=$date->format('Y-m-d');
}else{
$dt=null;
}

MySQL DUMP of student table

Visitors Rating
Your Rating





Post your comments , suggestion , error , requirements etc here .




We use cookies to improve your browsing experience. . Learn more
HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
©2000-2019 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer