byujensen on Dynamic SQL for jqgrid

Sat, 29 Oct 2011 21:06:13 +0300

I am using Apache/PHP/MySQL web server and I'm trying to load a grid. I followed the demo to get a working grid with my data, and I have an index.php page that simply includes the maininteractionsgrid.php (pasted below). Now I would like to be able to add a WHERE statement to the sql based on a variable that has already been assigned in the index.php. I can get the WHERE statement to work if I specify a fixed number (i.e. siteid=10), but for some reason it won't work if I specify the variable (i.e. siteid=$siteid... $siteid has already been set to 10). Can someone PLEASE help me figure out why my code below isn't working? I have tried searching all over online and can't find anything as to why my code isn't going through. All I would like to do is get only the records for a certain site based on a passed variable, $siteid. Any help would be greatly appreciated. Thanks.

<?php

require_once 'jqinteractionsconfig.php';

// include the jqGrid Class

require_once ABSPATH."php/jqGrid.php";

// include the driver class

require_once ABSPATH."php/jqGridPdo.php";

// Connection to the server

$conn = new PDO(DB_DSN,DB_USER,DB_PASSWORD);

// Tell the db that we use utf-8

$conn->query("SET NAMES utf8");

// Create the jqGrid instance

$grid = new jqGridRender($conn);

// Write the SQL Query

$mysql = "SELECT InteractionID, InteractionDateTime, ContactID, ContactedBy, InteractionType, Notes, SiteID FROM tbl_interactions WHERE SiteID=$siteid";

$grid->SelectCommand = $mysql;

// Set the table to where you add the data

$grid->table = 'tbl_interactions';

// Set output format to json

$grid->dataType = 'json';

// Let the grid create the model

$grid->setColModel();

// Set the url from where we obtain the data

$grid->setUrl('maininteractionsgrid.php');

$grid->addCol(array(

"name"=>"Actions",

"formatter"=>"actions",

"editable"=>false,

"sortable"=>false,

"resizable"=>false,

"fixed"=>true,

"width"=>60,

"formatoptions"=>array("keys"=>true)

), "first");

$grid->setColProperty('InteractionID', array("editable"=>false));

// Set some grid options

$grid->setGridOptions(array(

"rowNum"=>25,

"rowList"=>array(25,50,75,100),

"sortname"=>"InteractionID"

));

// Enjoy

$grid->renderGrid('#grid','#pager',true, null, null, true,true);

$conn = null;

jQuery Grid Plugin - jqGrid - Topic: Dynamic SQL for jqgrid

byujensen on Dynamic SQL for jqgrid