MYSQLI INSERT query

Student Table : Sample Data and query to create table
config.php Database connection object $connection is taken from config.php file.
Adding single record to Student table.
<?Php
require "config.php";// Database connection

$id=50;
$name = 'my_name';
$class='Three';
$mark=70;
$gender='male';
$query="INSERT INTO student (id,name,class,mark,gender) values($id,'$name','$class','$mark','$gender')";
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;
$gender='male';
$query="INSERT INTO student (id,name,class,mark,gender) values(?, ?,?,?,?)";
$stmt=$connection->prepare($query);
if($stmt){ 
$stmt->bind_param("issds", $id, $name, $class,$mark,$gender);
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;
$gender='male';
$query="INSERT INTO student (name,class,mark,gender) values( ?,?,?,?)";
$stmt=$connection->prepare($query);
if($stmt){ 
$stmt->bind_param("ssds",  $name, $class,$mark,$gender);
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

MYSQLI Functions mysqli_num_rows() Number of rows in result set SELECT query UPDATE query
Subscribe to our YouTube Channel here


Subscribe

* indicates required
Subscribe to plus2net

    plus2net.com







    Post your comments , suggestion , error , requirements etc here





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