Best data access methods for PHP/Codeigniter/jQuery/MySQL

Question

I'm originally a .NET dev by heart so I've always been use to having ADO.net and LINQ for strongly typed, easy data access into gridviews and other .net controls. In trying to learn PHP and MySQL with CodeIgniter and a dash of jQuery, but I'm having a hard time visualizing my data access methods.

I want to use a PHP or jquery grid for my grid controls (which I rely heavily on). What is the best way to populate them from a MySQL environment? I've heard lots about using JSON but I haven't studied anything on it yet. I'm also pretty sure CodeIgniter does a lot of data access as well.

What direction do I need to be taking this? Thank you Stackoverflow!

Answer 1

echo json_encode($result);

Answer 2

Let's get started with this:

Plain PHP

<?php
  class Report{
           ….
     public function __construct($city, $sidx, $sord, $_search, $searchField, $searchString, $searchOper){
         //connecting to db, stm, execute query, fetch array from result …..
            echo json_encode(array("name"=>"Alissa", "phone" => "555-55551", "city" => "Sussex");
           //or
            echo json_encode($result);
            //where result is an associative array, codeigniter can map such objects from ORM results
    }
 }

 $report = new Report($_GET['city'], $_GET['sidx'], $_GET['sord'], $_GET['_search'],$_GET['searchField'],$_GET['searchString'],$_GET['searchOper'])
?>

jQuery/html side using jqGrid

$("#grid").jqGrid({
       url: 'report.php?city=' + c + '&searchString=null&searchField=null&searchOper=null',
       datatype: 'json',
       mtype: 'GET',
       colNames: ['Name', 'Phone', 'City'],
       colModel: [
                { name:'rows.name',      index: 'name',   search:true, jsonmap: 'name',  width: 30, align: 'left', sortable:true},
                { phone:'rows.phone',    index: 'phone',               jsonmap: 'phone', width: 50, align: 'left'},
                { name:'rows.city',      index: 'city',                jsonmap: 'city',  width: 50, align: 'left', sortable: true},
       pager: '#pager',
       rowNum: 8,
       autowidth: true,
       rowList: [8, 16,32,48],
       sortname: 'name',
       sortorder: 'asc',
       viewrecords: false,
       caption: 'Customer',
       jsonReader : {
                      root: "rows",
                      repeatitems: false
                    },
       height: 650,
       width: 800
 });

No matter what PHP Framework you use, believe me. I use QCubed, sometimes I write plain PDO classes for this.

You can check this: http://www.codeigniter-jquery.com/codeigniter/using-json-in-codeigniter-with-jquery/

Answer 3

I'm not so sure what exactly were you asking, but this might be helpful: Datamapper

it makes query easy, built on top of codeigniter's Active Records