PROGRAMMING TUTORIALS

My purpose in making this web site  is to share my own knowledge and the information that I have hardly reached. I hope I can help you.

Continue reading...


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.
//procedural style $mysqli = mysqli_connect('host','username','password','database_name'); //object oriented style (recommended) $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 //Open a new connection to the MySQL server $mysqli = new mysqli('host','username','password','database_name'); //Output any connection error if ($mysqli->connect_error) { die('Error : ('. $mysqli->connect_errno .') '. $mysqli->connect_error); } //MySqli Select Query $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>'; // Frees the memory associated with a result $results->free(); // close connection $mysqli->close(); ?>

INSERT a Record

Following MySQLi statement inserts a new row in the table.


<?php
//values to be inserted in database table $product_code = '"'.$mysqli->real_escape_string('P1234').'"'; $product_name = '"'.$mysqli->real_escape_string('42 inch TV').'"'; $product_price = '"'.$mysqli->real_escape_string('600').'"'; //MySqli Insert Query $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.

//MySqli Update Query $results = $mysqli->query("UPDATE products SET product_name='52 inch TV', product_code='323343' WHERE ID=24"); //MySqli Delete Query //$results = $mysqli->query("DELETE FROM products WHERE ID=24"); if($results){ print 'Success! record updated / deleted'; }else{ print 'Error : ('. $mysqli->errno .') '. $mysqli->error; }


     Back