How to Insert Data into MySQL Database
After creating a database and its table, you have to insert data into MySQL database. The data is the required main record you put and use for your project.
To insert a record into the MySQL database, you need to first create MySQL query and execute it using the PHP functions. There are two alternatives to PHP functions you can use to insert data into MySQL databases.
- mysqli_query()
- PDO::__query()
The mysqli_query() is easy to use a function to use for your project if you want to use only the MySQL database. If you want to use another database in future, you can use PDO function as it supports more than twelve different types of databases.
Inserting Data Using MySQLi Procedural
Inserting the data needs a query to create using the table fields and the values you want to put for them. You can follow the below-given examples to use for your projects.
The execution of the query is same as the others, only the query is different containing the insert query statement.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
$hostname = "localhost"; $username = "root"; $password = ""; $mydbname = "test" //make connection $dbconn = mysqli_connect($hostname,$username,$password,$mydbname); //check connection if(!$dbconn) { die("Connection Error: ".mysqli_connect_error()); } //create query //execute query and create database if(mysqli_query($dbconn, $sql)){ echo "Data Inserted successfully"; }else{ echo "Could not insert data " . mysqli_error($dbconn); } //close connection mysqli_close($dbconn); |
Inserting data using MySQLi Object-oriented
Using Object-oriented in your coding is the best practice to design and develop a project based on PHP. You can create the database connection, create your insertion query and execute it by using PHP object-oriented programming.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
$hostname = "localhost"; $username = "root"; $password = ""; $mydbname = "test" //make connection $dbconn = new mysqli($hostname,$username,$password,$mydbname); //check connection if($dbconn->connect_error) { die("Connection Error: ".$dbconn->connect_error); } //create query $sql = "create table user(id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,name VARCHAR(20) NOT NULL,email VARCHAR(20) NOT NULL UNIQUE)"; //execute query and create database if($dbconn->query($sql)){ echo "Table created successfully"; }else{ echo "Table creation failed ".$dbconn->connect_error; } //close connection $dbconn->close(); |