We will now use our users table inside the database examples:
Our users table has 3 columns: id, name, surname.
To INSERT some new data in our table we will:
- connect to the server (localhost)
- select a database (examples)
- create a SQL query
- run the query and store the result
- check if the query has been successful
|
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 26 27 28 29 30 31 32 33 34 35 36 37 |
<?php #insertData.php //connecting to server $dbc = mysqli_connect('localhost','root','') or die('could not connect: '. mysqli_connect_error() ); //selecting the database mysqli_select_db("examples") or die('could not select db'); //DATA TO INSERT.. $name = "john"; $surname = "doe"; //CREATING A DATABASE QUERY /** * $q = "INSERT INTO `DATABASE`.`TABLE` (`FIELD`, `FIELD`, `FIELD`) * VALUES ('value', 'value', 'value')"; */ //we can use NULL for the auto incremented primary key $q = "INSERT INTO `examples`.`users` (`id`, `name`, `surname`) VALUES (NULL, '$name', '$surname')"; //EXECUTING THE QUERY AND STORING THE RESULT IN A VARIABLE $result = mysqli_query($dbc, $q); //CHECKING IF THE QUERY AFFECTED A ROW USING mysqli_affected_rows() if(mysqli_affected_rows($dbc) == 1){ //positive response echo "data inserted"; } else { //negative response echo "could not insert data"; } //CLOSING DATABASE CONNECTION mysqli_close($dbc); ?> |
You can INSERT more than one row using the following syntax:
|
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 |
<?php #insertData.php //DATA TO INSERT.. $name1 = "john"; $surname1 = "doe"; $name2 = "bill"; $surname2 = "smith"; $name3 = "james"; $surname3 = "bond"; //CREATING A DATABASE QUERY /** * $q = " INSERT INTO `DATABASE`.`TABLE` (`FIELD`, `FIELD`, `FIELD`) * VALUES ('value', 'value', 'value'), * ('value', 'value', 'value'), * ('value', 'value', 'value') "; */ $q = "INSERT INTO `examples`.`users` (`id`, `name`, `surname`) VALUES (NULL, '$name1', '$surname1'), (NULL, '$name2', '$surname2'), (NULL, '$name3', '$surname3') "; ?> |