MySqli Basic usage (select, insert & update)
Today I'd like to show you the basic usage of MySqli, such as connect, select, insert, update and delete records. I hope this list will come in handy for you.
Installing MySqli
MySQL :: Download MySQL Installer
Connect to Database
MySqli offers two ways to connect to the database, procedural and object oriented, the recommended way to open a database connection is object oriented way, because it is secure, faster and efficient. The procedural style is much similar to old MySql and it may be helpful to users who are just switching to MySqli, but should keep away altogether.
$mysqli
= mysqli_connect('host','username','password','database_name');
$mysqli
= new
mysqli('host','username','password','database_name');
SELECT Multiple Records as Associative array
mysqli_fetch_assoc() :
Below is the code to fetch multiple records as an associative array. The returned array holds the strings fetched from database, where the column names will be the key used to access the internal data. As you can see below, data is displayed in an HTML table.
<?php
$mysqli
= new
mysqli('host','username','password','database_name');
if
($mysqli->connect_error)
{ die('Error
: ('.
$mysqli->connect_errno
.') '.
$mysqli->connect_error);
}
$results
= $mysqli->query("SELECT
id, product_code, product_desc, price FROM products");
print
'<table border="1">';
while($row
= $results->fetch_assoc())
{ print
'<tr>';
print
'<td>'.$row["id"].'</td>';
print
'<td>'.$row["product_code"].'</td>';
print
'<td>'.$row["product_name"].'</td>';
print
'<td>'.$row["product_desc"].'</td>';
print
'<td>'.$row["price"].'</td>';
print
'</tr>';
} print
'</table>';
$results->free();
$mysqli->close();
?>
INSERT a Record
Following MySQLi statement inserts a new row in the table.
<?php
$product_code
= '"'.$mysqli->real_escape_string('P1234').'"';
$product_name
= '"'.$mysqli->real_escape_string('42
inch TV').'"';
$product_price
= '"'.$mysqli->real_escape_string('600').'"';
$insert_row
= $mysqli->query("INSERT
INTO products (product_code, product_name, price) VALUES($product_code, $product_name, $product_price)");
if($insert_row){
print
'Success! ID of last inserted record is : '
.$mysqli->insert_id
.'<br />';
}else{
die('Error
: ('.
$mysqli->errno
.') '.
$mysqli->error);
} ?>
Update/Delete a Records
Updating and deleting records works similar way, just change to query string to MySql Update or delete.
$results
= $mysqli->query("UPDATE
products SET product_name='52 inch TV', product_code='323343' WHERE ID=24");
if($results){
print
'Success! record updated / deleted';
}else{
print
'Error : ('.
$mysqli->errno
.') '.
$mysqli->error;
}
Back