MVP.PHP Data Synchronize

• Download source - 5.7 KB

Introduction

MVP.PHP is a library that consists of 2 major classes and other handy functions. MVP.PHP makes data tables model defined easier than ever, regardless of the framework used for development, and makes simple relationship between PHP arrays and SQL tables column.

MVP.PHP contains MVP and ENTITY and related functions defined directly in the .php file.

Background

Access mysql databases and defining tables is dependent on the framework and is not simple stuff. Due this reason, mvp.php tries to make defining tables easier and faster to migrate and manipulated by programmer and also brings him nature access to MySQL queries.

Understanding the Concept

As mentioned before, the library contains two major classes.

Entity: This class is used to define a table in a database and synchronize with PHP and mysql. This class is only used to generate table or modify columns in a migration (etc. from local host to a webserver) and never required to be included when updating, inserting or reading tables data.

MVP: This class is used for any inserting or updating or reading content of a table, or running mysql query and should be accessible in the entire project or data modifying library of the project.

Using the Code

MySQL Server Connecting

MVP class contains a useful static function which will connect to a mysql service described below:

<?php
Require ‘mvp.php’;
Mvp::autoconfig(‘localhost:3306’ , ‘myusername’ , ‘mypassword’ , ‘databasename’);
?>

The function above will connect mysql and also choose the database for this session. If database does not exist, it will create database named in 4^th parameter.

Notice: Using this function is necessary before using entity or MVP classes. They assume PHP application is connected to mysql server first.

Notice for beginners: MySQL connection should start in each page otherwise they are included by a parent. So if you place autoconfig function in top of the starting codes, the entire project will access the mysql.

Entity Class

Defining data table using entity class is very simple and described below.

After making an instance of entity class with [tablename] constructor parameter, entity contains few functions that delegate mysql column type like varchar, text, int, etc.

<?php
Require ‘mvp.php’;
$e = new entity(‘mytable’);
$e->target("mysql");
$e->varchar("username" , 200);
$e->varchar("password" , 100);
$e->text("welcomemessage");
$e->int("totalvisists");
$e->AsyncMySQL();
?>

AsyncMySQL: This function will start querying between PHP and mysql and will convert table definition to SQL query.

The important thing is when you using MVP or entity classes, RECORD_ID will be your record primary key as automatically generated and it’s also AUTO_INCREMNT so it’s not required you define it again in your query.

By running AsyncMySQL function, table with following description will be generated or if exists, still will be. Other option that will be considered by entity class is, if the target mysql table has fewer columns than new definition, it will be generated automatically. This will reduce manipulation of mysql tables in phpmyadmin after updating customer’s website from developing computer.

MVP Class

Regardless of the existing table or generated with entity, MVP class connects tables to PHP script as a dataset. For making easier the MVP, the result as dataset are PHP raw array. Also for inserting or updating records, PHP raw arrays are the input as the parameters so it makes it very simple to store and read data without wasting time for casting types.

Read Record vs. Read Records

Mvp contains two functions for reading table’s data: select and selects. The difference between these two functions is predictable. Select gets unique record (by RECORD_ID) while selects can get more than one record by implementing MySQL conditions explained by the sample below.

<?php
Require ‘mvp.php’;
$mvp = new mvp(‘mytable’); 
// select RECORD_ID = 1
$dataset = $mvp->select(1);
print_r($dataset); 
/* $dataset will be printed like:
Array(
    ‘username’ => ‘’ ,
    ‘password’ => ‘’,
    ...
);*/
$condition = array(
        ‘limit’ => 5 , ‘where’ => ‘RECORD_ID > 10’ , ‘order’ =>’RECORD_ID desc’,
        ‘column’ => ‘username , password’
);
$dataset = $mvp->selects($condition); 
print_r($dataset); 
/* $dataset will be printed like:
Array(
   [0] => array(
    ‘username’ => ‘’ ,
    ‘password’ => ‘’
   ),
   [0] => array(
    ‘username’ => ‘’ ,
    ‘password’ => ‘’
   ) 
);*/?>

As you see, for selects function, only one parameter should be passed and it’s an array that can contain 4 parameters name limit, column, where and order. But there is another difference with select should be considered and its return type in array.

In selects, even if you gain 1 record, it’s in a subarray in index 0 and when result contains more records, you can acess them with a foreach statement:

<?php
Require ‘mvp.php’;
$mvp = new mvp(‘mytable’);
foreach ($mvp->selects() as $q) {
        echo $q[‘username’] . "<br />";
}?>

Notice: mvp->selects(null) will return all records in table with default limit defined in mysql (normally 1000 records will all columns).

Inserting Records

Inserting data from php arrays, $_POST or $_GET or $_SESSION is an option of MVP to insert and update with array type.

<?php
Require ‘mvp.php’;
$mvp = new mvp(‘mytable’);
$data = array(‘username’ => $_POST[‘username’] , ‘password’ => md5($_POST[‘password’]));
$unique_id = $mvp->insert($data);
If ($unique_id === false) echo "There was a problem"; else echo "Okay! New record id: $unique_id"; 
?>

As the above code describes, inserting rows in table can be done by arrays which have the same column name in mysql table. Values before inserting will be secure for SQL injection. Consider if a column name in $data doesn’t exist in table, it won’t be inserted in table.

If inserting row happens successfully, a number (RECORD_ID) will be produced as return from insert function, otherwise false.

Inserting Directly from Users Post?!

MVP says you can insert form data directly from $_POST (data sent from users browser). Maybe this is not a very safe option, but it can be happen for pages we are not considering security and data manipulation as a threat.

<?php
Require ‘mvp.php’;
$mvp = new mvp(‘mytable’);
If (!null($_POST[‘username’])) {
//$data = array(‘username’ => $_POST[‘username’] , ‘password’ => md5($_POST[‘password’])); 
// saving directly $_POST array, but if we want md5 password before we can:
$_POST[‘password’] = md5($_POST[‘password’]);
$unique_id = $mvp->insert($_POST); 
If ($unique_id === false) echo "There was a problem"; else echo "Okay! New record id: $unique_id";
}
?>
<html>
<form method="post">
            <input type="text" name="username" />
            <input type="password" name="password" />
            <input type="submit" /> 
</form>
</html> 

Notice: In the code above, we saved users input directly but made a little bit change to password before inputting it mvp->insert.

Notice: null function is an mvp.php function which will check if a variable has any content or not. It will check both types of $x==”” and $x==null.

Notice: The same stuff can be possible for $_SESSION or $_GET global arrays.

Updating Records

Updating records is similar to inserting with 2 differences:

• Update function takes 2 argument, and first is RECORD_ID as int type
• Second parameter array index name should be existing in column name

<?php
Require ‘mvp.php’;
$mvp = new mvp(‘mytable’);
$record = 10;
$data = array(‘username’ => $_POST[‘username’] , ‘password’ => md5($_POST[‘password’]));
$unique_id = $mvp->update($record , $data);
If ($unique === true ) {
        echo "Record updated";
}?>

Deleting Records

Deleting records can happen with drop function by getting RECORD_ID.

<?php
Require ‘mvp.php’;
$mvp = new mvp(‘mytable’);
$result = $mvp->drop(20);
?>

Counting Records

Getting total records of a table and return result set with:

<?php
Require ‘mvp.php’;
$mvp = new mvp(‘mytable’);
$result = $mvp->count();
?>

Entry Exists

There is a shorthand getting a record with comparing having a value in on column of it. It is useful for situations in which you want to check username and password are existing in your table.

<?php
Require ‘mvp.php’;
$mvp = new mvp(‘mytable’);
$result = $mvp->entryexists($column , $value);
$record_id = $mvp->whereexists($column , $value);
?>

Getting Table Columns

You can grab the list of column names and types in MVP by getcolumns() function. It return array of the tables names and type.

MVP.PHP Functions

There are a series of useful functions in mvp.php file and when you require it, they are accessible without any initializing.

<?php 
Require ‘mvp.php’;
// use this instead of $mvp->insert()
$x = ToDB(‘table name’ , array(‘name’=>’val’ , ‘name1’ => ‘val1’));
$x = FromDb(‘table name’ , array(/* condition of selects($condition)*/);
$x = GetNextRecord("Table" , "RECORD_ID");
?>

Notice: GetNextRecord function is useful to get next record RECORD_ID after a specific record. Due to deleting some records in time, the next record can be increased more than 1 number in primary key.

Points of Interest

Usage of this library is useful because it’s framework-independent and very lightweight code and brings the access and migration so easy.

History

• Designing of MVP class
• Designing of entity class for definition tables and migration
• Updated article on 2014/12/13