[SOLUTION] Serverside using join PDO PHP

aperez21

Hello That such!

Sharing source code using php pdo and join two tables

Greetings.

/CODE/

database

CREATE DATABASE data

USE data

CREATE TABLE IF NOT EXISTS city (
id int(11) NOT NULL AUTO_INCREMENT,
city_name varchar(150) NOT NULL DEFAULT '',
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

CREATE TABLE IF NOT EXISTS users (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(150) NOT NULL DEFAULT '',
surname varchar(150) NOT NULL DEFAULT '',
city_id int(11) DEFAULT NULL,
PRIMARY KEY (id),
KEY city_id (city_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

ALTER TABLE users
ADD CONSTRAINT users_ibfk_1 FOREIGN KEY (city_id) REFERENCES city (id) ON DELETE CASCADE ON UPDATE CASCADE;

INSERT INTO city (id, city_name) VALUES
(1, 'city 1'),
(2, 'city 2'),
(3, 'city 3');

INSERT INTO users (id, name, surname, city_id) VALUES
(1, 'Jey', 'Lue', 1),
(2, 'Joe', 'Doe', 2),
(3, 'Alan', 'Beer', 3);

aperez21

parametersdb.php

<?php
define('DB_NAME', 'database_name');
define('DB_USER', 'root');
define('DB_PASSWORD', '');
define('DB_HOST', 'localhost');
define('DB_PORT', '3306');
define('DB_MANAGER', 'mysql');

aperez21

connection.class.php

<?php
require_once ('parametersdb.php');
class Connection {
private static $instance;
private $db;
private function construct() {
try {
$this->db = new PDO(DB_MANAGER . ':host=' . DB_HOST . ';port=' . DB_PORT . ';dbname=' . DB_NAME, DB_USER, DB_PASSWORD);
$this->db->exec("SET CHARACTER SET utf8");
}
catch(PDOException $e) {
throw new Exception($e->getMessage());
}
}
public function prepare($sql) {
return $this->db->prepare($sql);
}
public function query($sql) {
return $this->db->query($sql);
}
public static function getInstance() {
if (!isset(self::$instance)) {
$object = __CLASS;
self::$instance = new $object;
}
return self::$instance;
}
public function close() {
$this->db = null;
}
public function __clone() {
trigger_error('The cloning of this object is not permitted', E_USER_ERROR);
}
}

aperez21

datatables.class.php
<?php
require_once ('connection.class.php');
class DataTables {
private static $instance;
private $db;
private function construct() {
$this->db = Connection::getInstance();
}
public static function getInstance() {
if (!isset(self::$instance)) {
$object = __CLASS;
self::$instance = new $object;
}
return self::$instance;
}

public function getJoinDatatables($table, $index_column, $columns, $join) {
    try {
     / / Paging
        $sLimit = "";
        if (isset($_GET['iDisplayStart']) && $_GET['iDisplayLength'] != '-1') {
            $sLimit = "LIMIT " . intval($_GET['iDisplayStart']) . ", " . intval($_GET['iDisplayLength']);
        }
        // Orden
        $sOrder = "";
        if (isset($_GET['iSortCol_0'])) {
            $sOrder = "ORDER BY";
            for ($i = 0;$i < intval($_GET['iSortingCols']);$i++) {
                if ($_GET['bSortable_' . intval($_GET['iSortCol_' . $i]) ] == "true") {
                    $sortDir = (strcasecmp($_GET['sSortDir_' . $i], 'ASC') == 0) ? 'ASC' : 'DESC';
                    $sOrder.= "`" . $columns[intval($_GET['iSortCol_' . $i]) ] . "` " . $sortDir . ", ";
                }
            }
            $sOrder = substr_replace($sOrder, "", -2);
            if ($sOrder == "ORDER BY") {
                $sOrder = "";
            }
        }
        // Filtrado
        $sWhere = "";
        if (isset($_GET['sSearch']) && $_GET['sSearch'] != "") {
            $sWhere = "WHERE (";
            for ($i = 0;$i < count($columns);$i++) {
                if (isset($_GET['bSearchable_' . $i]) && $_GET['bSearchable_' . $i] == "true") {
                    $sWhere.= "" . $columns[$i] . " LIKE :search OR ";
                }
            }
            $sWhere = substr_replace($sWhere, "", -3);
            $sWhere.= ')';
        }
        // Filtrado de columnas individuales
        for ($i = 0;$i < count($columns);$i++) {
            if (isset($_GET['bSearchable_' . $i]) && $_GET['bSearchable_' . $i] == "true" && $_GET['sSearch_' . $i] != '') {
                if ($sWhere == "") {
                    $sWhere = "WHERE ";
                } else {
                    $sWhere.= " AND ";
                }
                $sWhere.= "" . $columns[$i] . " LIKE :search" . $i . " ";
            }
        }
/ / Selection List
        $sSelect = "";
        for ($i = 0;$i < count($columns);$i++) {
            $sSelect.= $columns[$i] . ' as `' . $columns[$i] . '`, ';
        }
        $sSelect = substr_replace($sSelect, "", -2);
        // Consultas SQL consiguen dato disponible
        $sQuery = "SELECT SQL_CALC_FOUND_ROWS " . $sSelect . " FROM " . $table . "" . $join . "" . " " . $sWhere . "" . $sOrder . " " . $sLimit;
        $statement = $this->db->prepare($sQuery);
       / / Bind parameters
        if (isset($_GET['sSearch']) && $_GET['sSearch'] != "") {
            $statement->bindValue(':search', '%' . $_GET['sSearch'] . '%', PDO::PARAM_STR);
        }
        for ($i = 0;$i < count($columns);$i++) {
            if (isset($_GET['bSearchable_' . $i]) && $_GET['bSearchable_' . $i] == "true" && $_GET['sSearch_' . $i] != '') {
                $statement->bindValue(':search' . $i, '%' . $_GET['sSearch_' . $i] . '%', PDO::PARAM_STR);
            }
        }
        $statement->execute();
        $rResult = $statement->fetchAll();
        $iFilteredTotal = current($this->db->query('SELECT FOUND_ROWS()')->fetch());
       / / Get the total parameter of rows in the table
        $sQuery = "SELECT COUNT(" . $index_column . ")  
        FROM " . $table . "" . $join;
        $iTotal = current($this->db->query($sQuery)->fetch());
        $output = array("sEcho" => intval(isset($_GET['sEcho']) ? $_GET['sEcho'] : null), "iTotalRecords" => $iTotal, "iTotalDisplayRecords" => $iFilteredTotal, "aaData" => array());
        // Return array of values
        foreach($rResult as $aRow) {
            $row = array();
            for ($i = 0;$i < count($columns);$i++) {
                if ($columns[$i] == "version") {
                    // Special output formatting for 'version' column
                    $row[] = ($aRow[$columns[$i]] == "0") ? '-' : $aRow[$columns[$i]];
                } else if ($columns[$i] != ' ') {
                    $row[] = $aRow[$columns[$i]];
                }
            }
            $output['aaData'][] = $row;
        }
        echo json_encode($output);
    }
    catch(PDOException $e) {
        throw new Exception($e->getMessage());
    }
}
public function __clone() {
    trigger_error('The cloning of this object is not permitted', E_USER_ERROR);
}

}

aperez21

instance.php

<?php
header('Pragma: no-cache');
header('Cache-Control: no-store, no-cache, must-revalidate');
require_once ('datatables.class.php');
$datatables = DataTables::getInstance();
$datatables->getJoinDatatables('users u', 'u.id', array('u.name', 'u.surname', 'c.city_name'), ' LEFT JOIN city c ON u.city_id = c.id ');

jadhoo

Thank you very much aperez21. much appreciated for your response. but my mysql query is bit complex and consists of several joining tables , let me try doing it by using ur method. thanks again.