I am having trouble with Pear Structures_DataGrid and MySQL. I have an index page that obtains all the records from a db (about 20K). This page works great, the problem is that I have a search page. When a users searchs for something, the results are show in the datagrid fine. However if there are more than one pages of results when the user tries to go to any other page I get mysql_fetch_assoc errors. The error is on the line with the while loop, but this is the same loop that I use for other pages that work correctly. I use a case statement to select which query to run based on what the user is searching for, all of the queries work fine on command line. Also if I hard code any of the queries the page (take out the case statement)works fine. I have echo'ed the query before actually running it to verify that it is what it is suppose to be. Can someone please tell me what I have wrong, I am out of ideas.
<?php
session_start();
include("../config.php");
require('Structures/DataGrid.php');
require_once('Structures/DataGrid/DataSource.php');
class Detail {
function detailLink($params)
{
extract($params);
$phone = $record['PHONE_NUMBER'];
return "<a href=\"../cust_detail.php?phone=$phone\">$label</a>";
}
}
//CASE Statement, Select Variable
switch ($_POST['Submit']) {
// if Submit => City Search
case 'City Search':
$query = "select * from `contact` where CITY = '$_POST[city]'";
break;
//If Submit => County Search
case 'County Search':
$query = "select * from `contact` where COUNTY = '$_POST[county]'";
break;
//If Submit => Market Search
case 'Market Search':
if ($_POST[market] == 'med') {
$query = "select * from `contact` where PRIMARY_SIC = '384104' OR (PRIMARY_SIC= '384103') GROUP BY PRIMARY_SIC";
}
elseif ($_POST[market] == 'law') {
$query = "select * from `contact` where PRIMARY_SIC = '541110'";
}
elseif ($_POST[market] == 'restate') {
$query = "select * from `contact` where PRIMARY_SIC = '62111'";
}
break;
//If Submit => Phone Search
case 'Phone Search':
$query = "select * from `contact` where PHONE_NUMBER like '$_POST[phone]%'";
break;
}
// database connection
$connection = mysql_connect(DB_HOST, DB_NAME, DB_PASS);
mysql_select_db(DB_DATA);
// query results
$data = mysql_query($query, $connection);
//Get Data
while ($record = mysql_fetch_assoc($data)) {
$dataset[] = $record;
}
mysql_close($connection);
?>
<html>
<head>
<title>Search Results</title>
<link href="../main.css" rel="stylesheet" type="text/css">
</head>
<body>
<div id="Layer1"><img src="../images/logo1.png" width="367" height="75"></div>
<div id="Layer2">
<?php
//Declare a new datagrid, and sort the records
$dg =& new Structures_DataGrid(15);
//$dg->sortRecordSet('name', 'ASC');
//Bind data
$source = Structures_DataGrid_DataSource::create($dataset);
$dg->bindDataSource($source);
//Format DataGrid
$dg->renderer->setTableHeaderAttributes(array('bgcolor' => '#ffffff'));
$dg->renderer->setTableOddRowAttributes(array('bgcolor' => '#CCCCCC'));
$dg->renderer->setTableEvenRowAttributes(array('bgcolor' => '#EEEEEE'));
$dg->renderer->setTableAttribute('width', '100%');
$dg->renderer->setTableAttribute('cellspacing', '1');
$dg->renderer->setTableAttribute('cellpadding', '2');
$dg->renderer->setTableAttribute('class', 'datagrid');
$dg->renderer->sortIconASC = "⇑";
$dg->renderer->sortIconDESC = "⇓";
$dg->renderer->allowEmptyRows(false, array('bgcolor' => '#FFFFFF'));
//Columns For DataGrid
$column = new Structures_DataGrid_Column('Name', 'COMPANY_NAME', 'COMPANY_NAME', array('width' => '18%'));
$dg->addColumn($column);
$column = new Structures_DataGrid_Column('Address', 'STREET_ADDRESS', 'STREET_ADDRESS', array('align' => 'center'));
$dg->addColumn($column);
$column = new Structures_DataGrid_Column('City', 'STREET_ADDRESS_CITY', 'STREET_ADDRESS_CITY', array('align' => 'center'));
$dg->addColumn($column);
$column = new Structures_DataGrid_Column('Business Type', 'PRIMARY_SIC_DESCRIPTION', 'PRIMARY_SIC_DESCRIPTION', array('width' => '27%'));
$dg->addColumn($column);
$column = new Structures_DataGrid_Column('Phone Number', 'PHONE_NUMBER', 'PHONE_NUMBER', array('align' => 'center'));
$dg->addColumn($column);
$column = new Structures_DataGrid_Column('More Detail', 'PHONE_NUMBER', 'PHONE_NUMBER', array('align' => 'center'), null, 'Detail::detailLink($label=More Detail)');
$dg->addColumn($column);
//Print DataGrid
$dg->render();
echo $dg->renderer->getPaging();
?>
</div>
<div id="Layer3">
<p><span class="style1">Search Results</span></p>
</div>
</body>
</html>
