Skip to content

Instantly share code, notes, and snippets.

@PatelUtkarsh
Created November 12, 2015 14:28
Show Gist options
  • Save PatelUtkarsh/06763134a1ff73cb9c17 to your computer and use it in GitHub Desktop.
Save PatelUtkarsh/06763134a1ff73cb9c17 to your computer and use it in GitHub Desktop.
PHP abstract function to create select query using array
<?php
/** $where['date'] = array(
'compare' => 'BETWEEN',
'value' => array(
$start,
$end
)
);
$where['27/11/2015'] = array(
'compare' => 'BETWEEN',
'value' => array(
$start,
$end
),
'is_val_col' => true
);
$where['colname'] = array(
'compare' => '>',
'value' => array('actual_value')
)
**/
public function get( $columns, $table_name = 'search', $offset = false, $per_page = false, $order_by = 'ID desc' ) {
$select = 'SELECT * FROM ' . $table_name;
$where = ' where 2=2 ';
foreach ( $columns as $colname => $colvalue ) {
if ( is_array( $colvalue ) && isset( $colvalue['compare'] ) && strtoupper( $colvalue['compare'] ) == 'BETWEEN' && count( $colvalue['value'] ) == 2 ) {
if ( !empty( $colvalue['is_val_col'] ) ){
$where .= " AND `{$colname}` BETWEEN {$colvalue['value'][0]} AND {$colvalue['value'][1]}";
} else{
$where .= " AND {$colname} BETWEEN '{$colvalue['value'][0]}' AND '{$colvalue['value'][1]}'";
}
} else if ( is_array( $colvalue ) ) {
if ( ! isset( $colvalue['compare'] ) ) {
$compare = 'IN';
} else {
$compare = $colvalue['compare'];
}
if ( ! isset( $colvalue['value'] ) ) {
$colvalue['value'] = $colvalue;
}
$col_val_comapare = ( $colvalue['value'] ) ? '(\'' . implode( "','", $colvalue['value'] ) . '\')' : '';
$where .= " AND {$table_name}.{$colname} {$compare} {$col_val_comapare}";
} else {
$where .= " AND {$table_name}.{$colname} = '{$colvalue}'";
}
}
$sql = $select . $where;
$sql .= " ORDER BY {$table_name}.$order_by";
if ( false !== $offset ) {
if ( ! is_integer( $offset ) ) {
$offset = 0;
}
if ( intval( $offset ) < 0 ) {
$offset = 0;
}
if ( ! is_integer( $per_page ) ) {
$per_page = 1;
}
if ( intval( $per_page ) < 0 ) {
$per_page = 1;
}
$sql .= ' LIMIT ' . $offset . ',' . $per_page;
}
// connection object $this->conn in my case
$result = mysql_query($sql,$this->conn);
return $result;
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment