Basic Knowledge Demo #4 Get data from DB Edit-Update -Using Php/PEAR DB into Access or mySQL (full round trip) EXAMPLE

This is basically an enhancement of Alex's two beautiful simple examples:

http://www.activewidgets.com/javascript.forum.12382.1/example-saving-editable-grid-data.html
and
http://www.activewidgets.com/javascript.forum.12393.4/simple-php-mysql-example.html
(with some nice additions by (Tony and joakinen)

Here I have combined the TWO parts, ReadDB/Make JS array, and Edit/Update the Data base.

I have chosen here to add the nuance of PEAR DB (extension to PHP) because at least at my work, There are so many different DB's and so many bosses to convince.... I wanted to easily demo this on several DB types!!! This so far tested nicely with mySQL and Access (you pear DB experts may know a better way to GET the field names, if so. please tell...)

This is a complete example; you only need to change the dsn to point to your DB, set the Table Name, then Column number and field name that contains your Unique Record ID.
NOTE: of course you MUST also set the links in the HTML to properly point to aw.js, as.css

The complete app, both READ DB make GrID and UPDATE DBparts of PHP are in one source here, That is probably NOT a good idea, execpt for demo purposes. So have at it.

In theory this should be able to EDIT any simple DB table!!!
Here then is a first Pass:

<?php
// need this ini_set(); line **ONLY** if you have to manually install PEAR DB in
// your own space, on your Providers server, (**usually not needed!!!)
// Use 'echo dirname(__FILE__);' to find your_dir path... THEN:
//** ini_set("include_path", 'D:\inetpub\your_dir\PEAR' . PATH_SEPARATOR . ini_get("include_path"));

// include the PEAR DB abstraction layer
include_once("DB.php");


// ************************************************************************
// *** NEED EDIT ONLY these lines below to make work for most ANY table ***
// *** ALSO BE SURE TO SET THE LINKS to aw.js and aw.css in the HTML below!!!

// The data source and the Query.

    //$dsn = "odbc:///Subscribers"; // Access DB with DSN on local machine NO pass.
    $dsn = "mysql://root:rootPASS@localhost/test"; //mySQL root and rootpass on localhost.
    
    $tableName = "libSubscription"; // Table name for Read and Update query
    $Record_Ident = "Unique_ID"; // Field name of your DB RECORD UNIQUE ID! (for Update Query)
    $ColNum_of_UniqueID = 0; // Column No. in 'myCells' 2D array - contains Unique Record ID for data.

    $sql = "SELECT * FROM $tableName"; // The GET DATA query.


// *** END - NEED EDIT ONLY these lines above to make work for most ANY table ***
// ************************************************************************

// Connect to DB via DSN set above.
    $conn = DB::connect($dsn); // Connect with PEAR DB abstraction layer...
if (DB::isError($conn)) { // Error or a valid connection?
die ("<p/><b>Attempted DB: <i>".$dsn."</i> Connection - </b>". $conn->getMessage()
            ."\n<p/><i><b>Details:</b> ".$conn->getUserInfo()."</i>" );
        }
                
// output anyDB query results as 2D javascript array
function aw_cells($dataset){
$rows = array();
    while ( $record = $dataset->fetchRow() ) {
$cols = array();
foreach ($record as $value) {
$cols[] = '"'.addslashes($value).'"';
}
$rows[] = "\t[".implode(",", $cols)."]";
    }
    echo "[\n".implode(",\n",$rows)."\n];\n";
}

// output anyDB field names as javascript array
function aw_headers($dataset){
$field = $dataset->fetchRow(DB_FETCHMODE_ASSOC,0);
foreach ($field as $fieldName=>$value) {
$cols[] = '"'.addslashes($fieldName).'"';
}
     echo "[".implode(",",$cols)."];\n";
}

// ******************************************************************************************
// normally these two sections of IF statement would be two PHP pages. One for display one for UPDATE DB.
// **** **** ****

if(!$_POST['fieldName']) { // if NOT data update request, Just display grid.

// ** START HERE to display grid ********************************************
    $dataset = $conn->query($sql); // Execute the query!
     if (DB::isError($dataset)) { // Error or a valid query?
die ("<br><b>Attempted DB Query: <i>\"".$sql."\"</i> - </b>". $dataset->getMessage()
            ."\n<p/><i><b>Details:</b> ".$dataset->getUserInfo()."</i>" );
     }
?>

<html>
<head>

<!-- include AW stylesheet and script !!!SET links FOR YOUR SITE!!! -->

<link href="runtime/styles/xp/aw.css" rel="stylesheet" type="text/css" ></link>
<script src="runtime/lib/aw.js"></script>

    <style>
        .aw-column-0 {width: 35px; background-color: gray;}
        .aw-column-1 {width: 20px;}
        .aw-column-3 {width: 55px; text-align: right;}
        .aw-column-4 {width: 10px; text-aligh: center;}
    </style>
</head>
<body>
<script>

// insert javascript arrays produced by PHP functions
var myCells = <?= aw_cells($dataset) ?>
var myHeaders = <?= aw_headers($dataset) ?> //Do after myCells array to avoid missing first record.

// create grid
var obj = new AW.UI.Grid;

// set grid text
obj.setHeaderText(myHeaders);
obj.setCellText(myCells);

// set number of columns/rows
obj.setColumnCount(myHeaders.length);
obj.setRowCount(myCells.length);
obj.setSize(650, 200);

// Which columns to Show? this leaves off 0..(possibly not showing the Unique Record ID)
obj.setColumnIndices([1,2,3,4,5,6,7])

// make cells editable
obj.setCellEditable(true);

// if you show the Unique Record ID then surely my must make it unEDITABLE!!!!
obj.setCellEditable(false, <?= $ColNum_of_UniqueID ?>);// disable editing for record ID, NEVER edit UNIQUE RECORD ID

// write grid to the page
document.write(obj);



// when cell changed, do all necessary things with new entry, validate, send off, process return.
obj.onCellValidated = function(text, column, row){
var r = new AW.HTTP.Request;

     r.setURL("<?= $_SERVER['PHP_SELF'] ?>");
     //r.setURL("updateSingleCell.php");
        
r.setRequestMethod("POST");

r.setParameter("fieldName", myHeaders[column]); // field name from DB.
r.setParameter("RecordID", myCells[row][<?= $ColNum_of_UniqueID ?>]); // Unique_ID col Num
r.setParameter("newText", text);
r.request();

r.response = function(data){
// alert(data); // process response data
         newWin = window.open("","successWin","height=200,width=400,resizable,scrollbars");
         newWin.document.write(data);
         newWin.document.close();
         newWin.focus();
}
}
</script>
</body>
</html>


<?PHP
// ******************************************************************************************
} else { // ** START POST Request to UPDATE the data!!! (normally in seperate PHP file.) (e.g., updateSingelCell.php)
     // This is the entire code to update the DB. You could just as easily send the original cell data as well
     // and allow user to Accept new or Revert to old...

// if POST request, get field Name, New value and Unique ID for UPDATE sql.
    $fieldName = $_POST["fieldName"];
    $newText = $_POST["newText"];
    $PostedRecordID = $_POST["RecordID"];

// The Update query.
    $sqlUpdate = "UPDATE $tableName
        SET $fieldName = '$newText'
        WHERE ($Record_Ident = $PostedRecordID)
        ";

    $dataset = $conn->query($sqlUpdate); // Execute the Update query!
     if (DB::isError($dataset)) { // Error or a valid query?
die ("<html><body bgcolor='red'><p/><b>Attempted DB Query: <i>\"".$sqlUpdate."\"</i> - </b>". $dataset->getMessage()
            ."\n<p/><i><b>Details:</b> ".$dataset->getUserInfo()."</i></body></html>" );
        }

    echo "<html><body bgcolor='lightblue'><p/> *** You have made the following change:
     <br/><i>New Text:</i> <b>$newText</b><br/><b>IN:</b>
     <br/><i>Field Name:</i> <b>$fieldName</B>
     <br/><i>Record Unique ID:</i> <b>$UniqueId</b><br/><b>USING:</b>
     <br/><i>$sqlUpdate</i></body></html>";

} ?>

Geoff, thanks a lot, great stuff!