mysqli.php

<?php
/**-------------------------------------------------
 * Standard MySQLi functionality
 * @package Code Snippets
 * @link http://rhythmshahriar.com/codes/
 * @author Rhythm Shahriar <rhy@rhythmshahriar.com>
 * @link http://rhythmshahriar.com
 * @copyright Copyright © 2017, Rhythm Shahriar
 ---------------------------------------------------*/

//define the site base url
define( 'BASE_URL', 'http://demo.com/' ); //<-- IMPORTANT: place a backslash at the end -->

//define database values
define( 'HOST_NAME', '127.0.0.1' );   #database host name e.g. localhost / 127.0.0.1
define( 'DB_NAME', 'database' );      #database name
define( 'DB_USER', 'root' );          #database username
define( 'DB_PASSWORD', '' );          #database password
define( 'CHARSET', 'utf8' );          #database charset e.g. utf/

//<!-- IMPORTANT: Do not modify the following functions -->
//create the database connection
$DB = mysqli_connect( HOST_NAME, DB_USER, DB_PASSWORD, DB_NAME );

//define the database charset
$DB->set_charset( CHARSET );

//close the connection
//mysqli_close( $DB );



//------------- EXAMPLE (ONLY FOR EXAMPLE PURPOSE)-----------------//

/** INSERT --------------------------- */
//create statement
$insert_statement = $DB->prepare( 'INSERT INTO ol_users (userId, userName, userAge ) VALUES ( ?, ?, ? )' );

/*------- first execution ------*/
//set the variable values
$userId   = 'demo';
$userName = 'Demo Name';
$usrAge   = 30;

//bind the statement
//string  = s
//integer = i
//double  = d
$insert_statement->bind_param( 'ssi', $userId, $userName, $usrAge );

//execute the statement
$insert_statement->execute();

/*------- further execution ( exceptional case )------*/
//set the variable values
$userId   = 'demo2';
$userName = 'Demo';
$usrAge   = 35;

//execute the statement
$insert_statement->execute();


/** UPDATE --------------------------- */
//create statement
$update_statement = $DB->prepare( "UPDATE ol_users SET userName = ?, userAge = ? WHERE userId = ?" );

//set the variable values
$userName = 'Update Demo';
$usrAge   = 45;
$userId   = 'demo';

//bind the statement
//string  = s
//integer = i
//double  = d
$update_statement->bind_param( 'sis', $userName, $usrAge, $userId );

//execute the statement
$update_statement->execute();



/** DELETE --------------------------- */
//create statement
$delete_statement = $DB->prepare( "DELETE FROM ol_users WHERE userName = ? AND userAge = ?" );

//set the variable values
$userName = 'Update Demo';
$usrAge   = 45;

//bind the statement
//string  = s
//integer = i
//double  = d
$delete_statement->bind_param( 'si', $userName, $usrAge );

//execute the statement
$delete_statement->execute();



/** SELECT --------------------------- */
//create statement
$select_statement = $DB->prepare( "SELECT * FROM ol_users WHERE userAge = ?" );

//set the variable values
$usrAge   = 35;

//bind the statement
//string  = s
//integer = i
//double  = d
$select_statement->bind_param( 'i', $usrAge );

//execute the statement
$select_statement->execute();

//something array (for technique 1 & 3)
$something = array();

//get the result object
$result = $select_statement->get_result();

//fetch data from database
//while( $fetch_data =  $result->fetch_assoc() ):
//    //technique 1: push data into array and use foreach
//    array_push( $something, $fetch_data );  #push the data into the array
//
//    //technique 2: directly access the database fetch data index
//    printf( "%s\n", $fetch_data[ 'userName' ] );
//endwhile;


//technique 3: create a data object
while( $fetch_data =  $result->fetch_object() ):
     $something[] = $fetch_data; //push the mysqli data object
endwhile;

//create the foreach function (for technique 1 & 3)
foreach( $something as $data ):
    echo $data->userName;
endforeach;